here is my update query syntax.my problem is.if i update only logo1 and logo3 with null logo2 and img null then my query should be update the other record.just dont update null value record but other should be update.but below my query can not update any record.
UPDATE tbitem set itemname= @itemname,note=@note,
price=@price,qty=@qty,logo1=@logo1,
logo2=@logo2,logo3=@logo3,img=@img
where id =@id
and @logo1 is
null and @logo2
is null
and @logo3
is null
and @img is
null
I have this Insert statement working fine: [Code]....
It inserts a new record into a single table. The PK is a field named QID.what the equivalent Update code would be? I'm not sure of the syntax for the Update values (old and new) and Where clause.I'm assuming something beginning with: existingQ.Update
i built an oledb update stmt using the syntax from a 4 guys tutorial, http://www.4guysfromrolla.com/articles/071002-1.3.aspx , but am getting the above error
sample how i build an update field into the update string
Dim targetAttendeeCount As Integer = _ CType(e.Item.Cells(0).FindControl("targetAttendeeCountTextBox"), TextBox).Text Dim parmTarAttCnt As OleDbParameter = _ New OleDbParameter("@targetAttendeeCount", OleDbType.Integer) parmTarAttCnt.Value = targetAttendeeCount myCommand.Parameters.Add(parmTarAttCnt)
so none of the variables are being resolved, i know they are being returned from, in this case a datagrid, because i have printed them separately, so am not sure at this point how to get the vars to resolve, i was hoping to not have to use all the quotes + apostrohes and thought the @syntax was simplifying that but not so. is there some way to code this with the @'s?
I have a FormView which is bound to an SQLDataSource. When a user clicks on a save button the form, I need to write any changes made to the FormView back to the table.
I have a Stored Procedure on my MSSQL 2005 database that should be executed when the user clicks on the save button.
It appears the Update is firing, but when I trap the DbCommand object in the SQLDataSource's 'Updating' event, all of the parameters that should be passed to the Stored Procedure are coming through as NULLs. Consequently, the Stored Procedure isn't updating anything.
In the click event of the save button I'm explicitly firing the SQLDataSource's Update method:
[Code]....
The parameters in the SQLDataSource <UpdateParameters></UpdateParameters> section appear to be correct.
Can anyone give me an idea on what to investigate to figure out why it appears the Update is only picking up NULLs?
we have a AutoGenerateColumns-enabled WmxDataGrid in which we are able to update table rows in a DataBase connected with a SqlDataSource.
Each time we clear a field an empty string is written to the database where we want to write DBNull values.
At the moment we generate the update string via UPDATE... SET.. and the SQL Server function NULLIF(value, '').
Works proper but when I want to add the values to e.NewValues in BeginUpdate event I'm not able to read the values from the auto generated text boxes. How do I do that? Is there an easier way to automatically convert empty strings to NULL values?
I am going to outline my setup then where i am failing
UpdatePanel1 - Contents : 3 textboxes and AddButton UpdatePanel2 - Contents : 1 GridView and SubmitButton
AddButton adds new row to DataTable with data from textboxes, binds DataTable to GridView SubmitButton sends data to database. The Adding of a row works to add the row to the gridview, but it won't add a second or third and so on, only adds the 1. SubmitButton belives the DataTable that GridView is databound to is null and won't send anything to the database. How can i get the datatable to persist between the two update panels? Do i use Sessions as my storage location? Page
I remember being told that you can add some syntax to the end of a statement that will do something if the field is null. Pretty vague, but I cant seem to find anything about it via google.
I definitely isnt how to set a field to be nullable. I know that.
Here is an example of what I want.
int? field1 = 0;
field1 = int.parse(string2); (where string2 is a passed value, that could be null)
I thought there was something you could put at ?? if it is a null, such as:
field1 = int.parse(string2) ??;
Or do I have to check every if every string2 is null before making that statement.
Dim connStr As String = " Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=W:PatioOrderApp_DataPatio.mdb" Dim con As New OleDb.OleDbConnection(connStr) Dim instr As String = " INSERT INTO tOrders([first_name],[last_name],[order],[comment]) Valuse(?,?,?,?)" If IsValid Then Using con Using cmd As New OleDb.OleDbCommand(instr, con) cmd.CommandType = CommandType.Text cmd.Parameters.AddWithValue("first_name", DBNull.Value) cmd.Parameters.AddWithValue("last_name", DBNull.Value) cmd.Parameters.AddWithValue("order", DBNull.Value) cmd.Parameters.AddWithValue("comment", menuDrinks.SelectedItem.ToString) con.Open() cmd.ExecuteNonQuery() End Using End Using con.Close() End If
I hv Writte nA sql Query in whic hi want to use an Inner join as well as THe left OUter Join in it ..i hv written it in the sql server it executes properly.but when i run the same code ..from my application .it starts gving the Error Incorrect Syntax Near Keyword INNER..
working with sql server 2005/ visual studio 2005 asp.net c# i just wanted to know ... to insert a date to the database in a field that gets "dateTime" i need to insert the origin syntax. what is the right syntax for the datetime in the database? and to invent for users to insert a wrong sytax ... wich validations i need to put on the field? same question on the type "image" in database it gets like a jpg file? how do i insert an image to the database? If i have a textbox that contain a number ... and i want to ask somthing about the number. how do i do it ? .... like i now that i need to convert it to int i tried this way but it didnt work:
I am just not able to get the syntax right for the below code,
Dim HeaderCount As Integer = readerXML.HeaderCount() Dim NewTable As String NewTable = "CREATE TABLE " + tbCustomerFileName.Text + " (" + " RowId smallint" + " IDENTITY(1,1)" + " PRIMARY KEY CLUSTERED," For I = 0 To [HeaderCount] NewTable = NewTable + readerXML.Item(I) + " DEFAULT 'New Position - title not formalized yet'," + " varchar(50) NOT NULL," Next [I] + ")" ...this part gives me an error ssaying incorrect syntax.
I am trying to upload an excel file and based on the number of headers in the file, I want to generate columns, however I am just not able to get this going because i am getting syntax error, the main problem being I am not able to put the brackets at the right place.
Incorrect syntax near '>'. I made a listview with some dropdownmenu and selectboxes to select from untill the query shows in the listview.
Now the query goes well and shows the first page of 4 rows as I wanted it.
The pager shows 5 pages and if I a want to click on the next page I get this message.
Incorrect syntax near '>'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '>'.
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:AvcilarEvleri %>" SelectCommand="SELECT * FROM Tabel1 WHERE ((([Regio_Object] = @Regio_Object) AND ([Plaats_Object] = @Plaats_Object) AND ([Wijk_Object] = @Wijk_Object) AND (Prijsklasse => @Prijsklasse1) AND (Prijsklasse <= @Prijsklasse2) AND (Prijsklasse => @Prijsklasse3) AND (Prijsklasse <= @Prijsklasse4 AND (Oppervlakte_Object => @Oppervlakte_Object1) AND (Oppervlakte_Object <= @Oppervlakte_Object2)) AND ((Type_Object = @Type_Object1) OR (Type_Object = @Type_Object2) OR (Type_Object = @Type_Object3)))"> <SelectParameters> <asp:ControlParameter ControlID="DropSehir1" Name="Regio_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropIl1" Name="Plaats_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropDownList5" Name="Wijk_Object" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat1" Name="Prijsklasse1" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat2" Name="Prijsklasse2" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat3" Name="Prijsklasse3" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropFiyat4" Name="Prijsklasse4" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropMetre1" Name="Oppervlakte_Object1" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="DropMetre2" Name="Oppervlakte_Object2" PropertyName="SelectedValue" Type="String" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object1" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object2" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="CheckBoxList1" Name="Type_Object3" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
And the code;
Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Panel1.Visible = False Panel4.Visible = False Panel5.Visible = False Panel6.Visible = False Panel7.Visible = False Panel8.Visible = False Panel9.Visible = False Panel10.Visible = False Panel2.Visible = True Panel3.Visible = True Dim strchklist As String = "" Dim li As ListItem For Each li In CheckBoxList1.Items If li.Selected Then strchklist += ",'" & li.Value & "'" End If Next If strchklist = "" Then Response.Write("No item Selected") End If SqlDataSource4.SelectCommand = "SELECT * FROM Tabel1 WHERE ([Regio_Object] = @Regio_Object) AND (Plaats_Object = @Plaats_Object) AND (Wijk_Object = @Wijk_Object) AND (Prijsklasse >= @Prijsklasse3) AND (Prijsklasse <= @Prijsklasse4) AND (Oppervlakte_Object >= @Oppervlakte_Object1) AND (Oppervlakte_Object <= @Oppervlakte_Object2) AND Kamers_Object IN (" & strchklist.Substring(1) & ")" SqlDataSource4.DataBind() End Sub
I am sure it has something to do with the query because and with the checkboxes.
I'm using the following code to select a number of fields from a Table, this works perfectly, however I actually want to select from a stored procedure not a table and im not sure what the correct syntax is,
I have a problem with my SQL statement that only needs to show future dates in the Gridview.I use vb, vs 2005.I want a DateTime Field(TEndDate) to act as a control field in that only future dates (WHERE TEndDate >= Now.Date.Date) should be returned.It asks for my paramaterized value when I'm building the query.I'm unsure about the source I should select for the query.(The options are: None/Control/Cookie/Form/Profile/Querystring/Session)I'm losing my mind with this!Here is my code:
Where can I find documentation on the syntax that is expected for LinqDataSource.Select? It seems to be different from what I would do with Linq inline in C# code. For example, concatenateing something in Linq I would normally just do this in the projection:
new { FullName = (person.FirstName + " " + person.LastName ) } However, with a LinqDataSource the only way I've found to do this is like this: new ( String.Concat(person.FirstName, " ", person.LastName) as FullName)
I literally spent hours trying to figure this out, and only succeeded when I found a blog where someone gave this verbatim as an example. So I still don't understand why the syntax is different, the braces versus parens, aliasing, Concat operators. Next time I do this I want a better understanding so I am not playing this guessing game. It's like being handed a compiler, and trying to write a program when you don't know what language the compiler compiles.1. Are there different "flavors" of Linq? Like a VB Linq, and C# Linq?2. If so, is the LinqDataSource using a VB flavor of Linq?3. Really, what is really important to me, where can I find documentation on this particular falvor of Linq that the LinqDataSource expects? The documentation for the LinqDataSource itself gives nothing more than extremely simplistic examples.
I have a typical gridview/formview master control setup. When I try to update or insert I don't get any errors but it also doesn't work. When I try to insert data all I get are null values and when I update, none of the values are updated. The formview looks like its working, but just doesn't. The primary key is an identity and it auto-increments by one.
I get the following error on the e.Command.Parameters("@latestRowId").Value line. It was working, but now not and I'm not sure why? Conversion from type 'DBNull' to type 'Integer' is not valid.
May I know in SQL Server, what is the similar syntax for %TYPE in Oracle, i.e. the variable defined will follow the data type defined in the database column?
Can someone please tell me what normally causes this error message (stack trace at bottom of this post)? I can't even tell what line or what page is producing the error.
Following are the relevant (I think) 2 code procedures...the Update command below seems to "work", because later I see that the image is updated---but I can't immediately see that: I get the above error message.
'FormView.ItemUpdating Event Protected Sub AdvertisementForm_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles AdvertisementForm.ItemUpdating ' Get the connection string from Web.config. Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabaseConnectionString").ToString()) ' Create a command object. Dim cmd As New SqlCommand() ' Assign the connection to the command. cmd.Connection = conn ' Set the command text ' SQL statement or the name of the stored procedure. cmd.CommandText = "UPDATE Advertisements SET UserName = @UserName, Age = @Age, Avatar = ISNULL(@Avatar, Avatar) WHERE UserId = @UserId" ' Set the command type ' CommandType.Text for ordinary SQL statements; cmd.CommandType = CommandType.Text ' Get the person ID, first name and last name from the ' EditItemTemplate of the FormView control. Dim strUserId As String = DirectCast(AdvertisementForm.Row.FindControl("UserIdTextBox"), TextBox).Text Dim strUserName As String = DirectCast(AdvertisementForm.Row.FindControl("UserNameTextBox"), TextBox).Text Dim strAge As String = DirectCast(AdvertisementForm.Row.FindControl("AgeTextBox"), TextBox).Text ' Append the parameters to the SqlCommand and set values. cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = Guid.Parse(strUserId) cmd.Parameters.Add("@UserName", SqlDbType.NChar).Value = strUserName cmd.Parameters.Add("@Age", SqlDbType.NChar).Value = strAge ' Find the FileUpload control in the EditItemTemplate of ' the FormView control. Dim uploadAvatar As FileUpload = DirectCast(AdvertisementForm.FindControl("uploadAvatar"), FileUpload) If uploadAvatar.HasFile Then ' Append the Picture parameter to the SqlCommand. ' If a picture is specified, set the parameter with ' the value of bytes in the specified picture file. cmd.Parameters.Add("@Avatar", SqlDbType.VarBinary).Value = uploadAvatar.FileBytes Else ' Append the Picture parameter to the SqlCommand. ' If no picture is specified, set the parameter's ' value to NULL. cmd.Parameters.Add("@Avatar", SqlDbType.VarBinary).Value = DBNull.Value End If ' Open the connection. conn.Open() ' Execute the command. cmd.ExecuteNonQuery() End Using ' Switch FormView control to the ReadOnly display mode. AdvertisementForm.ChangeMode(FormViewMode.ReadOnly) ' Rebind the FormView control to show data after updating. BindFormView() End Sub Private Sub BindFormView() ' Get the connection string from Web.config. Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabaseConnectionString").ToString()) ' Create a DataSet object. Dim dsPerson As New DataSet(). ' Create a SELECT query. Dim strSelectCmd As String = "SELECT UserId, Avatar FROM Advertisements" ' Create a SqlDataAdapter object Dim da As New SqlDataAdapter(strSelectCmd, conn) ' Open the connection conn.Open() ' Fill the DataTable named "Advertisements" in DataSet with the rows ' returned by the query. da.Fill(dsAdvertisements, "Advertisements") Dim dsAdvertisements As DataSet = New DataSet() ' Bind the FormView control. AdvertisementForm.DataBind() End Using End Sub Server Error in '/www.mywebsite.com' Application. Incorrect syntax near ','. [Code].... Stack Trace: [Code]....
