Recieve Must declare the scalar variable "@TextS" trying to update in VB.NET

jeremyj54
jeremyj54 used Ask the Experts™
on
I am recieving "Error while updating record Must declare the scalar variable @TextS" when running the following code.

 con = dbConnect()
            cmd.Connection = con
            If txtResource.Text = "ALL" Then
                cmd.CommandText = "UPDATE OPERATION_BINARY SET BITS = CAST(REPLACE(BIT, @TextS, @TextR) as VARBINARY(MAX)) where CAST(CAST(OPERATION_BINARY.BITS AS VARBINARY(MAX)) AS VARCHAR(8000)) LIKE '%' + @Text + '%'"

                MsgBox(cmd.CommandText)
            Else
                cmd.CommandText = "UPDATE OPERATION_BINARY SET BITS = CAST(REPLACE(BITS, @TextS, @TextR) as VARBINARY) " & _
                "FROM OPERATION INNER JOIN " & _
                "OPERATION_BINARY ON OPERATION.WORKORDER_TYPE = OPERATION_BINARY.WORKORDER_TYPE AND " & _
                "OPERATION.WORKORDER_BASE_ID = OPERATION_BINARY.WORKORDER_BASE_ID And " & _
                "OPERATION.WORKORDER_LOT_ID = OPERATION_BINARY.WORKORDER_LOT_ID And " & _
                "OPERATION.WORKORDER_SPLIT_ID = OPERATION_BINARY.WORKORDER_SPLIT_ID And" & _
                "OPERATION.WORKORDER_SUB_ID = OPERATION_BINARY.WORKORDER_SUB_ID And OPERATION.SEQUENCE_NO = OPERATION_BINARY.SEQUENCE_NO " & _
                "WHERE (OPERATION.RESOURCE_ID = @Resource)"
            End If
            cmd.ExecuteNonQuery()
            cmd.Parameters.AddWithValue("@Resource", txtResource.Text)
            cmd.Parameters.AddWithValue("@TextS", Trim(txtSearch.Text))
            cmd.Parameters.AddWithValue("@TextR", Trim(txtReplace.Text))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
It looks like you are using an incorrect parameter here:

... LIKE '%' + @Text + '%'" ...

...or perhaps you just didn't show where you added it to the Parameters collection. You're also missing a space between "And" and "OPERATION" here:

...
_SPLIT_ID And" & _
                "OPERATION.WORKORDER_SUB_ID = OPERATION_BINARY.WORKORDER_SUB_ID
...
jeremyj54

Author

Commented:
You are correct on both of those.  I changed them both and recieve the same error message.

 con = dbConnect()
            cmd.Connection = con
            If txtResource.Text = "ALL" Then
                cmd.CommandText = "UPDATE OPERATION_BINARY SET BITS = CAST(REPLACE(BIT, @TextS, @TextR) as VARBINARY(MAX)) where CAST(CAST(OPERATION_BINARY.BITS AS VARBINARY(MAX)) AS VARCHAR(8000)) LIKE '%' + @TextS + '%'"

                MsgBox(cmd.CommandText)
            Else
                cmd.CommandText = "UPDATE OPERATION_BINARY SET BITS = CAST(REPLACE(BITS, @TextS, @TextR) as VARBINARY) " & _
                "FROM OPERATION INNER JOIN " & _
                "OPERATION_BINARY ON OPERATION.WORKORDER_TYPE = OPERATION_BINARY.WORKORDER_TYPE AND " & _
                "OPERATION.WORKORDER_BASE_ID = OPERATION_BINARY.WORKORDER_BASE_ID And " & _
                "OPERATION.WORKORDER_LOT_ID = OPERATION_BINARY.WORKORDER_LOT_ID And " & _
                "OPERATION.WORKORDER_SPLIT_ID = OPERATION_BINARY.WORKORDER_SPLIT_ID And " & _
                "OPERATION.WORKORDER_SUB_ID = OPERATION_BINARY.WORKORDER_SUB_ID And OPERATION.SEQUENCE_NO = OPERATION_BINARY.SEQUENCE_NO " & _
                "WHERE (OPERATION.RESOURCE_ID = @Resource)"
            End If
            cmd.ExecuteNonQuery()
            cmd.Parameters.AddWithValue("@Resource", txtResource.Text)
            cmd.Parameters.AddWithValue("@TextS", Trim(txtSearch.Text))
            cmd.Parameters.AddWithValue("@TextR", Trim(txtReplace.Text))
SQL Server Architect
Commented:
You are executing the cmd before you add the parameters.

cmd.ExecuteNonQuery()
            cmd.Parameters.AddWithValue("@Resource", txtResource.Text)
            cmd.Parameters.AddWithValue("@TextS", Trim(txtSearch.Text))
            cmd.Parameters.AddWithValue("@TextR", Trim(txtReplace.Text))

make it like this:

            cmd.Parameters.AddWithValue("@Resource", txtResource.Text)
            cmd.Parameters.AddWithValue("@TextS", Trim(txtSearch.Text))
            cmd.Parameters.AddWithValue("@TextR", Trim(txtReplace.Text))
cmd.ExecuteNonQuery()

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial