[RESOLVED] Getting Error on updating table

[accmaster]

Hi there,

Please have a look on my code.

                SqlStr = "UPDATE TblFleetSetup SET EquipmentTypeCode = @EquipmentTypeCode , Make = @Make Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)
                .AddWithValue("@EquipmentTypeCode", Val(CboEquipmentTypeName.SelectedValue))
                .AddWithValue("@Make", txtMake.Text.Replace("'", "").Trim)
                cmd.ExecuteNonQuery()

I am getting an error

Additional information: Must declare the scalar variable "@EquipmentTypeCode".

Can you please help me out what is the issue on my code.

Thanks
Ladak

[ident]

Have a look at https://blogs.msdn.microsoft.com/spi...alar-variable/

[accmaster]

Hi Ident,

Thanks for your reply

I changed my code but still getting error

 SqlStr = "UPDATE TblFleetSetup SET EquipmentTypeCode = @EquipmentTypeCode , Make = @Make Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)

                .Add("@EquipmentTypeCode", SqlDbType.Int).Value = Val(CboEquipmentTypeName.SelectedValue)
                .Add("@Make", SqlDbType.NVarChar).Value = txtMake.Text.Replace("'", "").Trim
                cmd.ExecuteNonQuery()

Additional information: Must declare the scalar variable "@EquipmentTypeCode".

Can you please tell me what is my mistake?

Ladak

[si_the_geek]

You aren't showing the full code... as the lines start with .Add , it seems you are using a With block, which you haven't shown.

As you should be adding the parameters to cmd (which is set up the line before the first .Add), any With block before that cannot be appropriate, because it wouldn't be referring to the correct object (at best it would be referring to the object that was previously referenced by the same variable).

[accmaster]

Here is my code

        Dim SqlStr As String
        Dim cmd = New SqlCommand


        Dim Sold As String = ""
        If ChkSold.Checked = True Then Sold = "Yes"
        If ChkSold.Checked = False Then Sold = "No"


If Modex = "Edit Mode" Then

                SqlStr = "Delete From TblFleetSetup_Registration Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)
                cmd.ExecuteNonQuery()


                SqlStr = "Delete From TblFleetSetup_Engine Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)
                cmd.ExecuteNonQuery()

            With cmd.Parameters
               
                
                SqlStr = "UPDATE TblFleetSetup SET EquipmentTypeCode = @EquipmentTypeCode , Make = @Make Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)

                .AddWithValue("@EquipmentTypeCode", Val(CboEquipmentTypeName.SelectedValue))
                .AddWithValue("@Make", txtMake.Text.Replace("'", "").Trim)


            End With
            cmd.ExecuteNonQuery()

        End If
        '

It shows error

Additional information: Must declare the scalar variable "@EquipmentTypeCode".

Thanks
Ladak

[accmaster]

Trying for last 2 hours, no luck.

Ladak

[si_the_geek]

As implied by my previous post, you should change this section:

            With cmd.Parameters
               
                
                SqlStr = "UPDATE TblFleetSetup SET EquipmentTypeCode = @EquipmentTypeCode , Make = @Make Where FleetCode = " & Val(txtEntryNumber.Text)
                cmd = New SqlCommand(SqlStr, Conn)

                .AddWithValue("@EquipmentTypeCode", Val(CboEquipmentTypeName.SelectedValue))
                .AddWithValue("@Make", txtMake.Text.Replace("'", "").Trim)


            End With
            cmd.ExecuteNonQuery()

to this:

            SqlStr = "UPDATE TblFleetSetup SET EquipmentTypeCode = @EquipmentTypeCode , Make = @Make Where FleetCode = " & Val(txtEntryNumber.Text)
            cmd = New SqlCommand(SqlStr, Conn)
            With cmd.Parameters
                .AddWithValue("@EquipmentTypeCode", Val(CboEquipmentTypeName.SelectedValue))
                .AddWithValue("@Make", txtMake.Text.Replace("'", "").Trim)
            End With
            cmd.ExecuteNonQuery()

ie: do not start a With block until after the object it refers to has been created.

By the way, you don't need to have the .Replace("'", "") in there, using parameters means that the ' character wont cause a problem.

[accmaster]

Thanks si_the_geek

Issue Resolved. I just realized my mistake.

Thanks
Ladak

[ident]

This is why you should always show all relevant code.