SQL not updating

[rangerstud620]

I'm 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:

  	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 it's a problem with the database but I'm not sure.

Edited December 8, 2005 by rangerstud620

[Diesel]

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 it's variable name instead of ?

 

param = cmd.Parameters.Add("Memo", SqlDbType.Char)

param.Value = DirectCast(di.FindControl("txtMemo"), textbox).Text

[rangerstud620]

Thanks, that took care of the problem. But I'm still confused why it didn't work with the "?". I've 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 db's. Before that, this code (with the ?) worked fine, so that's why I thought I had a setting wrong in the db.

[Diesel]

I remember...

 

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

 

dba.SelectCommand.CommandText = "INSERT INTO ShoppingCart

(BookId, CustId, Quantity)

Values (?, ?, ?)"

 

Haven't used them in a while