Monday, March 19, 2012

convert update from access to sql and have an error.

I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.

And here is the stack trace (which I don't know how to read except for the line the error is on)
 [SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\webroot\goodellweb\adm\adm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315

here is my code.

Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))

SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")

Thanks
MichaelSQL uses named parameters. Try:

Dim editSQL As String = "Update tbEmail Set fldName=@.fldName, fldEmail=@.fldEmail Where ID=@.recNum"

Dim SqlConn As New SqlConnection(ConnStr)

Dim Cmd As New SqlCommand(editSQL, SqlConn)

Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))

Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))

Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))

|||hanks douglas
That did take care of the error, but it is not updating. Maybe it is updating its just updating what was already in the database and now the new data. Is there a way to see if the new data in the textbox is present? When I do a
 Response.Write("email " & emailEdit.Text & " <br>")
at the end of the code it shows the data in the database. Should it be the new data?
Thanks
Michael|||Just tried this and it updated.

Cmd.Parameters.Add(New SqlParameter("@.fldName", "test"))

So my new data is not getting to the right place.
So what am i missing?
Thanks
Michael|||You need to have your data binding only take place when IsPostback is false:

If IsPostback=false then
' data bind ONLY here
End If

What you do otherwise is re-read the data from the database and "update" with the data from the database.

No comments:

Post a Comment