SqlCommand Parameters.Add

  • Thread starter Thread starter RN1
  • Start date Start date
R

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
 
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
 
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
Click to expand...

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
 
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
 
Sub UpdateDataGrid(obj As Object, ea As DataGridCommandEventArgs)
    strSQL = "UPDATE Basket SET Quantity = @Qty, Total = @TotAmtWHERE
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 -
Click to expand...

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. :param1. For
OleDb provider, just a question mark (?) would suffice

Ron
 
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. :param1. For
Click to expand...
OleDb provider, just a question mark (?) would suffice
Ron
Click to expand...

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