1

I am trying to write a paramaterized update query to insert values into an Sql Server Express Database. The query I have written is:

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandText = "update tblposts set title=@ptitle, pdate=@pd, 
                   content=@pcontent where pid=@p"
cmd.Parameters.AddWithValue("ptitle", txtTitle.Text)
cmd.Parameters.AddWithValue("pcontent", txtcontent.InnerText)
cmd.Parameters.AddWithValue("pd", DateTime.Now.ToString)
cmd.Parameters.AddWithValue("p", postid)

On running cmd.ExecuteNonQuery, I get number of rows affected as 1, but the change is not reflected in the database.

On printing the query using Debug.Write, I get the query not with the parameter values, but the names of the parameters itself (ie. @pcontent, @title etc)

What can be the mistake here?

Improve this question
3
  • Are you using the same database your application is? Are you running this in a transaction that gets rolled back? Commented Nov 19, 2012 at 11:51
  • i am not using a transaction. only this application is using the database Commented Nov 19, 2012 at 11:52
  • Are there any update triggers on this table? Commented Nov 19, 2012 at 11:53

1 Answer 1

Reset to default
4

In you're AddWithValue you need to include the @ symbol on the front of the parameter, so:

cmd.Parameters.AddWithValue("@ptitle", txtTitle.Text)
cmd.Parameters.AddWithValue("@pcontent", txtcontent.InnerText)
cmd.Parameters.AddWithValue("@pd", DateTime.Now.ToString)
cmd.Parameters.AddWithValue("@p", postid)

I'm guessing that it's executing correctly but there where clause is blank, so perhaps updating a blank row.

Anyway, try the above and it should update as expected.

Edit to Add

The CommandText will always only have the @value in there, it will not substitue the parameter values into the string.

You would need to loop through the cmd.Parameters collection to write out the values.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

adding the '@' symbol did not change the effect. I will also add, that I have tried the parameterized query for insert and select queries, and it works, but not working for update queries
Have you done a Debug.Write with the actual values? i.e. Debug.Write(postid)?
I am getting expected values for postid, but strangely old values for txtTitle.Text and txtContent.innerText. I guess the problem lies there
A very subtle mistake. I was reading values from database on page load event, and on click of a button, updating these values. I hadnt checked for IsPostBack condition. So the answer stands right. Thanks :)
Excellent, pleased to hear. Still, the pesky IsPostBack cycle is always a gotcha! Pleased it's sorted.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.