Update Query for SQL Sever VB.NET

I need help about update I have two columns in table, ID and Price. I need when I have like 10 in my column Price and I type like 12 then message box show (you do not have enough amount in price column

Dim cmd As New SqlCommand
cmd.Connection = cn
cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = "1"
cmd.CommandText = ("UPDATE Table_9 SET ID = @ID,Price =CAST((CAST(Price AS INT)-" & TextBox1.Text & ") AS VARCHAR(10)) WHERE ID = @ID")

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()

MessageBox.Show("success")

2 answers

  • answered 2019-12-12 07:54 Thakkar Rutvik

    Please use syntax like following.

    Example as below :

    command.CommandText = "UPDATE Student 
    SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add";
    

  • answered 2019-12-12 08:34 Vignesh Kumar A

    Try Below

    Dim cmd As New SqlCommand
    Dim Scmd As New SqlCommand
    
    Scmd.Connection = cn
    Scmd.CommandText = ("SELECT Price  FROM Table_9  WHERE ID = @ID")
    Smd.Parameters.Add("@ID", SqlDbType.VarChar).Value = "1"
    cn.Open()
    Dim DBPrice As Integer = Smd.ExecuteScalar()
    
    IF(DBPrice >Cint( TextBox1.Text)) Then
       cmd.Connection = cn
       cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = "1"
       cmd.Parameters.Add("@Price", SqlDbType.VarChar).Value = TextBox1.Text
       cmd.CommandText = ("UPDATE Table_9 SET ID = @ID,Price =CAST((CAST(Price AS INT)-@Price) AS VARCHAR(10)) WHERE ID = @ID")
       cmd.ExecuteNonQuery()   
       MessageBox.Show("success")
    Else
       MessageBox.Show("you do not have enough amount in price column")
    End If
    cn.Close()