SqlCommand Parameters.Add

[RN1]

Using SqlCommand, this is how I am updating a database table:

--------------------------------------------------------------------------------
Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs)
strSQL = "UPDATE Basket SET Quantity = Qty, Total = TotAmt WHERE
BasketID = BID AND ProductID = PID"

sqlCmd = New SqlCommand(strSQL, sqlConn)
With sqlCmd
.Parameters.Add("Qty", SqlDbType.Int).Value = CInt(iQty)
.Parameters.Add("TotAmt", SqlDbType.Money).Value = CInt(iQty)
* CType(ea.Item.FindControl("lblPrice"), Label).Text
.Parameters.Add("BID", SqlDbType.VarChar, 50).Value =
strBasketID
.Parameters.Add("PID", SqlDbType.VarChar, 50).Value =
CType(ea.Item.FindControl("lblID"), Label).Text
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
End Sub
--------------------------------------------------------------------------------

But the above code generates the following error pointing to the
sqlCmd.ExecuteNonQuery() line in the above code:

Invalid column name 'BID'.
Invalid column name 'PID'.

BID & PID are not the column names in the actual database table but
can't it be done in the way I have done above? In fact, Qty & TotAmt
are not the column names in the actual database table as well; so why
isn't the error pointing to Qty & TotAmt as they will be evaluated
before BID & PID, if I am not mistaken?

Thanks,

Ron

 

[Scott M.]

Parameter names must start with the "AT-Sign"

Parameters.Add("@BID", SqlDbType.VarChar, 50).Value = strBasketID
Parameters.Add("@PID", SqlDbType.VarChar, 50).Value =
CType(ea.Item.FindControl("lblID"), Label).Text

Without them there as markers, the CLR thinks the values you are looking for
are BID and PID.

-Scott

 

[Hans Kesting]

Scott M. formulated on donderdag :

Parameter names must start with the "AT-Sign"

Parameters.Add("@BID", SqlDbType.VarChar, 50).Value = strBasketID
Parameters.Add("@PID", SqlDbType.VarChar, 50).Value =
CType(ea.Item.FindControl("lblID"), Label).Text

Without them there as markers, the CLR thinks the values you are looking for
are BID and PID.

-Scott

Correct that the @-sign should be added to the parameter names (note:
also in the query), but I think it's SqlServer that is thinking "BID"
(without @) should refer to a column, not the .Net system.

Hans Kesting

 

[Peter Bromberg [C# MVP]]

Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs)
strSQL = "UPDATE Basket SET Quantity = @Qty, Total = @TotAmt WHERE
BasketID = @BID AND ProductID = @PID"

sqlCmd = New SqlCommand(strSQL, sqlConn)
With sqlCmd
.Parameters.Add("@Qty", SqlDbType.Int).Value = CInt(iQty)
.Parameters.Add("@TotAmt", SqlDbType.Money).Value = CInt(iQty)
* CType(ea.Item.FindControl("lblPrice"), Label).Text
.Parameters.Add("@BID", SqlDbType.VarChar, 50).Value =
strBasketID
.Parameters.Add("@PID", SqlDbType.VarChar, 50).Value =
CType(ea.Item.FindControl("lblID"), Label).Text
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
End Sub

-- Cheers,Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[RN1]

Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs)
    strSQL = "UPDATE Basket SET Quantity = @Qty, Total = @TotAmt WHERE
BasketID = @BID AND ProductID = @PID"

    sqlCmd = New SqlCommand(strSQL, sqlConn)
    With sqlCmd
        .Parameters.Add("@Qty", SqlDbType.Int).Value = CInt(iQty)
        .Parameters.Add("@TotAmt", SqlDbType.Money).Value = CInt(iQty)
* CType(ea.Item.FindControl("lblPrice"), Label).Text
        .Parameters.Add("@BID", SqlDbType.VarChar, 50).Value =
strBasketID
        .Parameters.Add("@PID", SqlDbType.VarChar, 50).Value =
CType(ea.Item.FindControl("lblID"), Label).Text
    End With

    sqlConn.Open()
    sqlCmd.ExecuteNonQuery()
    sqlConn.Close()
End Sub

-- Cheers,Peter
Site:http://www.eggheadcafe.com
UnBlog:http://petesbloggerama.blogspot.com
Short Urls & more:http://ittyurl.net











- Show quoted text -

Parameter names depend on the provider. When using the provider for
SQL Server, it should start with @ (e.g. @param1). For Oracle
provider, it should start with a colon )...for e.g. aram1. For
OleDb provider, just a question mark (?) would suffice

Ron

 

[Scott M.]

Parameter names depend on the provider. When using the provider for

SQL Server, it should start with @ (e.g. @param1). For Oracle
provider, it should start with a colon )...for e.g. aram1. For

OleDb provider, just a question mark (?) would suffice

Ron

Yes, that's what we've been telling you. You are using SQL, so we talked
about "@".

 

[SNK111]

Using SQLCommand, passing parameters, embedding SQL...

You can check this link ( shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html ) for information on using SQLCommand. It also describes how to pass parameters, call stored procedures.

Shahriar Nour Khondokar: shahriarnk.com