Click here to Skip to main content
15,881,139 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two columns "serial" & "device".the user should write the serial number in the textbox. if it exists, it will deduct one from devices. my problem is that I am little bit confused and I don't know how I can subtract (1) from the same serial in the textbox.

VB
Try


          Dim query As String = "select serial from verfication where serial=@serial"
          con.Open()
          cmd = New SqlCommand(query, con)
          cmd.Parameters.AddWithValue("@serial", serialtxtbox.Text)
          Dim dr As SqlDataReader
          dr = cmd.ExecuteReader()

          While dr.Read


              MsgBox("user available")
              con.Close()

              Exit Sub
          End While

          MsgBox("wrong serial")
          con.Close()

      Catch ex As Exception
          MsgBox("Please check your internet connection")

      End Try


What I have tried:

VB
While dr.Read


           dr.Close()

           '  con.Open()
           Dim query1 As String = " Update verfication SET device = device - 1 WHERE serial=@serial"

           cmd = New SqlCommand(query1, con)
           cmd.Parameters.AddWithValue("@serial", serialtxtbox.Text)
           'Dim dr1 As SqlDataReader
           dr = cmd.ExecuteReader()

           MsgBox("user available")


           Exit Sub
           End While



I am receiving this error from MySQL server manager

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DECLARE'.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@adminaccess".
Posted
Updated 1-Jun-20 19:50pm
v3
Comments
Afzaal Ahmad Zeeshan 1-Jun-20 23:14pm    
I don't think the error is coming from the code that is being shown in the SQL you have shared.

Maybe try debugging the code, or something, or show the complete code at least the code that has the DECLARE keyword or @adminaccess parameter.
Mohammad Abumoosa 2-Jun-20 1:32am    
i have added declare but nothing happend

1 solution

You don't use a DataReader with an SQL UPDATE command - only with queries that contain a SELECT statement.

Instead, use ExecuteNonQuery:
Dim query1 As String = " Update verfication SET device = device - 1 WHERE serial=@serial"
cmd = New SqlCommand(query1, con)
cmd.Parameters.AddWithValue("@serial", serialtxtbox.Text)
dim affected as Integer = cmd.ExecuteNonQuery()
The value in affected will tell you how many rows were updated. If it's zero, then the serial number wasn't found.

I'd recommend using that instead of blindly issuing the same message to the user.

Do note that MsgBox is very outdated - it's a "Hangover" from VB6, which died in 2001! You should be using the MessageBox Class (System.Windows.Forms) | Microsoft Docs[^] instead these days.
 
Share this answer
 
Comments
Maciej Los 2-Jun-20 2:05am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900