SQL not updating

R

rangerstud620

Active member
Joined
Jun 14, 2005
Messages
27
Im trying to update a SQL db using ASP.NET but I keep getting the following error: "Line 1: Incorrect syntax near ?. Line 1: Incorrect syntax near ?."

Here is my code:
Code: 
	  	Dim di As DataListItem
	  	Dim cmd As New SqlCommand("UPDATE YPVerification SET [MEMO] = ?, [STATUS] = ?, [DATE_VERIFIED] = ? WHERE IMG_ID = ?", objConnection)
	  	Dim param As SqlParameter
	  	Dim rbList as RadioButtonList = E.Item.FindControl("rbList")
		Dim CurrDate as New DateTime()
		CurrDate = DateTime.Now
		Dim strCurrDate as String = CurrDate.ToString
		
	  	di = E.Item
	  	cmd.CommandType = CommandType.text
	  	Update parameters
	  	param = cmd.Parameters.Add("?", SqlDbType.Char)
	  	param.Value = DirectCast(di.FindControl("txtMemo"), textbox).Text
		
	  	If rbList.Items.FindByValue("Y").Selected = True then
		 	param = cmd.Parameters.Add("?", SqlDbType.Char)
		  	param.Value = "Y"
			param = cmd.Parameters.Add("?", SqlDbType.Char)
	  		param.Value = strCurrDate
		Else
			param = cmd.Parameters.Add("?", SqlDbType.Char)
			param.Value = "N"
			param = cmd.Parameters.Add("?", SqlDbType.Char)
	  		param.Value = "N/A"
		End If
		param = cmd.Parameters.Add("?", SqlDbType.Int)
		param.value = E.Item.ItemIndex + 1
	
	  	Update database			  
	  	cmd.Connection.Open()
	  	cmd.ExecuteNonQuery()  ***ERRORS HERE***
	  	dataList.EditItemIndex = -1
	  	cmd.Connection.Close()

Can anyone see the problem? I keep thinking its a problem with the database but Im not sure.
 
Last edited by a moderator:
Dude....When you created an sqlcommand, you defined the query...

Dim cmd As New SqlCommand("UPDATE YPVerification SET [MEMO] = ?, [STATUS] = ?, [DATE_VERIFIED] = ? WHERE IMG_ID = ?", objConnection)

But instead of using ?, you have to define variables which you will later give values to.

Variables are prefixed with @

So, for instance

Dim cmd As New SqlCommand("UPDATE YPVerification SET [MEMO] = @Memo, [STATUS] = @Status, [DATE_VERIFIED] = @VerDate WHERE IMG_ID = @Image", objConnection)


and then when you create each parameter, specify its variable name instead of ?

param = cmd.Parameters.Add("Memo", SqlDbType.Char)
param.Value = DirectCast(di.FindControl("txtMemo"), textbox).Text
 
Thanks, that took care of the problem. But Im still confused why it didnt work with the "?". Ive used ?s before and have not had a problem. We actually just got done doing some work on our SQL Server that involved moving/changing some SQL dbs. Before that, this code (with the ?) worked fine, so thats why I thought I had a setting wrong in the db.
 
I remember...

If you use a DataAdapter, you can specifiy ? as a parameter...

dba.SelectCommand.CommandText = "INSERT INTO ShoppingCart
(BookId, CustId, Quantity)
Values (?, ?, ?)"

Havent used them in a while
 
Back
Top