参数化更新查询与VB在ASP.net SQL SERVER [英] Parameterized Update Query for Sql server in ASP.net with VB

问题描述

我想写一个paramaterized更新查询将值插入到SQL服务器前preSS数据库。我写的查询是：

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)

在运行 cmd.ExecuteNonQuery ，我也会受到影响1的行数，但变化不会反映在数据库中。

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

在使用 Debug.Write ，我得到的查询与参数值，但参数本身的名称（即@pcontent，@title打印查询等）

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?

推荐答案

在你 AddWithValue 您需要在包括在 @ 符号参数的前面，所以

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)

我猜它执行正确，但是有where子句是空白的，所以也许在更新一个空行。

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.

修改添加

的的CommandText 永远只拥有 @value 在那里，它不会顶替参数值进入字符串。

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

您将需要通过 cmd.Parameters 收集循环写出来的值。

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

这篇关于参数化更新查询与VB在ASP.net SQL SERVER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！