SQL Syntax Update Statement in VB.Net

JB4375
JB4375 used Ask the Experts™
on
I have a form to track equipment. I seach on an item, data comes up, and l need to update a field and select update to overwrite the current row with the values in the text boxes. I have what I think should work but getting stumped on the syntax and have the following error:
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Also is there some way to hold the sequence value aka record number from the search? The values to be entered into the table are still present in the textboxes from the previous search. I don't really want another text box on the form, and I won't be updating every record I search for so I wouldn't want to call the update sub from the search sub every time in order to pass the value.

As it stands right now I've just set strSequence = "1" , and only search on the first record in order to work with the SQL Update. Thanks!!
Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox2.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox3.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox4.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        Else
            Call Update_SQL()
            Call ClearAll()
            UserMsgBox(Me, "Your new record was submitted successfully.")
        End If


    End Sub

    Public Sub Update_SQL()
        Dim strAssetTag, strLocation, strNotes, strSequence, strSerial As String
        strSerial = TextBox2.Text
        strAssetTag = TextBox3.Text
        strLocation = TextBox4.Text
        strNotes = TextMulti.Text
        strSequence = "1"

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter
        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = strSerial, AssetTag = strAssetTag, Location = strLocation, Notes = strNotes, Requestor = strUser, Submitted = strDate WHERE Sequence = strSequence)", sConn)


        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
        sConn.Open()
        sComm.ExecuteNonQuery()
        sConn.Close()

    End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glanced 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:
You need to add parameter objects to your SqlCommand. Something like:
Public Sub Update_SQL()
    Dim strAssetTag, strLocation, strNotes, strSequence, strSerial As String
    strSerial = TextBox2.Text
    strAssetTag = TextBox3.Text
    strLocation = TextBox4.Text
    strNotes = TextMulti.Text
    strSequence = "1"

    Dim sConn As Data.SqlClient.SqlConnection
    Dim sComm As Data.SqlClient.SqlCommand
    Dim sAdapt As Data.SqlClient.SqlDataAdapter
    sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
    sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = :serial, AssetTag = :asset, Location = :location, Notes = :notes, Requestor = :requestor, Submitted = :dte WHERE Sequence = :sequence)", sConn)

    sComm.Parameters.Add(New SqlParameter(":serial", strSerial))
    sComm.Parameters.Add(New SqlParameter(":asset", strAssetTag))
    sComm.Parameters.Add(New SqlParameter(":location", strLocation))
    sComm.Parameters.Add(New SqlParameter(":notes", strNotes))
    sComm.Parameters.Add(New SqlParameter(":requestor", strUser))
    sComm.Parameters.Add(New SqlParameter(":dte", strDate))
    sComm.Parameters.Add(New SqlParameter(":sequence", strSequence))

    sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
    sConn.Open()
    sComm.ExecuteNonQuery()
    sConn.Close()

End Sub

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
And remove the trailing parentheses from the query.
Public Sub Update_SQL()
    Dim strAssetTag, strLocation, strNotes, strSequence, strSerial As String
    strSerial = TextBox2.Text
    strAssetTag = TextBox3.Text
    strLocation = TextBox4.Text
    strNotes = TextMulti.Text
    strSequence = "1"

    Dim sConn As Data.SqlClient.SqlConnection
    Dim sComm As Data.SqlClient.SqlCommand
    Dim sAdapt As Data.SqlClient.SqlDataAdapter
    sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
    sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = :serial, AssetTag = :asset, Location = :location, Notes = :notes, Requestor = :requestor, Submitted = :dte WHERE Sequence = :sequence", sConn)

    sComm.Parameters.Add(New SqlParameter(":serial", strSerial))
    sComm.Parameters.Add(New SqlParameter(":asset", strAssetTag))
    sComm.Parameters.Add(New SqlParameter(":location", strLocation))
    sComm.Parameters.Add(New SqlParameter(":notes", strNotes))
    sComm.Parameters.Add(New SqlParameter(":requestor", strUser))
    sComm.Parameters.Add(New SqlParameter(":dte", strDate))
    sComm.Parameters.Add(New SqlParameter(":sequence", strSequence))

    sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
    sConn.Open()
    sComm.ExecuteNonQuery()
    sConn.Close()

End Sub

Open in new window

Ashley BryantSenior Software Engineer

Commented:
Also...

You are referencing a variable named "strDate", but I don't see that being initialized or having a value set anywhere.  Did you just want that to be the current date or do you have a calendar object or textbox that that value should be coming from?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
@Kaufmed: OK... I'm getting the following error:
Incorrect syntax near ':'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ':'.
Incorrect syntax near ':'.
Also, what do I do about getting a value to strSequence from the previous search?
@Ashley: It was an oversight. I got so caught up in the SQL query. strDate = Now.
 
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
My fault. Change all the colons (:) to "at" symbols (@) in the query and in each parameter line.
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
So:
sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = @serial, AssetTag = @asset, Location = @location, Notes = @notes, Requestor = @requestor, Submitted = @dte WHERE Sequence = @sequence", sConn)

sComm.Parameters.Add(New SqlParameter("@serial", strSerial))
sComm.Parameters.Add(New SqlParameter("@asset", strAssetTag))
sComm.Parameters.Add(New SqlParameter("@location", strLocation))
sComm.Parameters.Add(New SqlParameter("@notes", strNotes))
sComm.Parameters.Add(New SqlParameter("@requestor", strUser))
sComm.Parameters.Add(New SqlParameter("@dte", strDate))
sComm.Parameters.Add(New SqlParameter("@sequence", strSequence))

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
With regard to strSequence, declare the variable outside of this function, but still within the class. Then the value will persist for as long as your object exists. You should mark it "private". You can initialize its value to "1" in your constructor, then in this function, just increment the value--basically change

    strSequence = "1"

to

    strSequence = (Convert.ToInt32(strSequence) + 1).ToString()
Ashley BryantSenior Software Engineer

Commented:
For the date, change this:

Submitted = @dte

to this:

Submitted = GETDATE()

and delete/comment this line:

sComm.Parameters.Add(New SqlParameter("@dte", strDate))

Author

Commented:
OK... that worked perfectly on the first record with strSequence = "1". With regrads to setting the value as:  strSequence = (Convert.ToInt32(strSequence) + 1).ToString()
I'm confused as to how that would concide with the it's declaration in the seach sub. Could I use this WITHIN the seach sub I've listed below?
Could you elaborate on how to mark it private?
 

Public Sub SQL_Search()
        Dim strSequence As String = ""
        Dim strSerial As String = ""
        Dim strAssetTag As String = ""
        Dim strLocation As String = ""
        Dim strNotes As String = ""
        Dim strColumn As String = ""
        Dim strSearch = TextBox1.Text

        If DropDownList1.Text = "Serial Number" Then
            strColumn = "Serial"
        End If
        If DropDownList1.Text = "Asset Tag" Then
            strColumn = "AssetTag"
        End If
        If DropDownList1.Text = "Location" Then
            strColumn = "Location"
        End If

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter

        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)
        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

        sConn.Open()

        Dim objDR As Data.SqlClient.SqlDataReader = sComm.ExecuteReader

        Do While objDR.Read()
            strSequence = objDR("Sequence").ToString()
            strSerial = objDR("Serial").ToString()
            strAssetTag = objDR("AssetTag").ToString()
            strLocation = objDR("Location").ToString()
            strNotes = objDR("Notes").ToString()
        Loop

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes

        End If

        objDR.Close()
    End Sub

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
That's my mistake. I forgot that you attached the code for your other sub :)

You can change your Sub to a Function and return the integer value. In the function that calls SQL_Search(), you can pass the return value to Update_SQL() as below.
Public Function SQL_Search() As Integer
    Dim seq As Integer = -1  ' To indicate unitialized
...
...

        Do While objDR.Read()
            strSequence = objDR("Sequence").ToString()
            strSerial = objDR("Serial").ToString()
            strAssetTag = objDR("AssetTag").ToString()
            strLocation = objDR("Location").ToString()
            strNotes = objDR("Notes").ToString()
            seq = objDR.GetInt32(0)  ' Because "sequence" is the first column you selected
        Loop

...
...
    Return seq
End Function

Public Sub Update_SQL(ByVal seq As Integer)
...
    sComm.Parameters.Add(New SqlParameter("@sequence", seq))
...
End Sub

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
You can remove line 7 from my last post. It should not be there now :)

Author

Commented:
K... One last question. From where I'm calling Update SQL from the Update.Click.
What does the Call Update_SQL() statement look like? Having a problem with that statement.

Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox2.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox3.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox4.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        Else
            Call Update_SQL(seq) '***** This is the problem line.
            Call ClearAll()
            UserMsgBox(Me, "Your new record was submitted successfully.")
        End If


    End Sub

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
Hmm...

My assumption from before was that you were calling the two functions back to back. But that does not seem to be the case from looking at your last post. This is my error again, but you can store the sequence number that was generated to the textbox that you originally had--so

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes
        End If

becomes

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = seq.ToString()
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes

        End If

Then, in the code from your last post, have the following (line 13 changed):
Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
    If DropDownList1.Text = "Select Option" Then
        UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
    ElseIf TextBox1.Text = "" Then
        UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
    ElseIf TextBox2.Text = "" Then
        UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
    ElseIf TextBox3.Text = "" Then
        UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
    ElseIf TextBox4.Text = "" Then
        UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
    Else
        Call Update_SQL(Convert.ToInt32(TextBox2.Text)) '***** This is the problem line.
        Call ClearAll()
        UserMsgBox(Me, "Your new record was submitted successfully.")
    End If
End Sub

Open in new window

Author

Commented:
Ok... Hold on TextBox2.Text holds the serial number of the device. There is no textbox that displays Seq AKA the record number in the table. I intentionally left it off for fear of someone attempting to change that value by typing something different in the box.
The flow goes as follows:
  1. User searches on a string.
  2. Results are displayed: TB2=Serial, TB3=AssetTag TB4=Location TextMulti=Notes
  3. User enters changed data into one of the TextBoxes and Clicks Update
  4. Sub uses Seq value to update the entire record accordingly.
Hopes this helps.
 

Author

Commented:
I've supplied the code below in case it would help.

Protected Sub search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles search.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "You must enter a 'Search Item'")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "You must enter 'Search Entry'")
        Else
            Call SQL_Search()
        End If
    End Sub

    Public Function SQL_Search() As Integer
        Dim Seq As Integer = -1 'To indicate uninitialized
        Dim strSerial As String = ""
        Dim strAssetTag As String = ""
        Dim strLocation As String = ""
        Dim strNotes As String = ""
        Dim strColumn As String = ""
        Dim strSearch = TextBox1.Text

        If DropDownList1.Text = "Serial Number" Then
            strColumn = "Serial"
        End If
        If DropDownList1.Text = "Asset Tag" Then
            strColumn = "AssetTag"
        End If
        If DropDownList1.Text = "Location" Then
            strColumn = "Location"
        End If

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter

        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)
        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

        sConn.Open()

        Dim objDR As Data.SqlClient.SqlDataReader = sComm.ExecuteReader

        Do While objDR.Read()
            strSerial = objDR("Serial").ToString()
            strAssetTag = objDR("AssetTag").ToString()
            strLocation = objDR("Location").ToString()
            strNotes = objDR("Notes").ToString()
            Seq = objDR.GetInt32(0)  ' Because "sequence" is the first column you selected
        Loop

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes

        End If

        objDR.Close()
        Return Seq
    End Function

 Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox2.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox3.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox4.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        Else
            Call Update_SQL(seq)
            Call ClearAll()
            UserMsgBox(Me, "Your new record was submitted successfully.")
        End If


    End Sub

    Public Sub Update_SQL(ByVal seq As Integer)
        Dim strAssetTag, strDate, strLocation, strNotes, strSerial As String
        strDate = Now
        strSerial = TextBox2.Text
        strAssetTag = TextBox3.Text
        strLocation = TextBox4.Text
        strNotes = TextMulti.Text


        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter
        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = @serial, AssetTag = @asset, Location = @location, Notes = @notes, Requestor = @requestor, Submitted = @dte WHERE Sequence = @sequence", sConn)

        sComm.Parameters.Add(New SqlParameter("@serial", strSerial))
        sComm.Parameters.Add(New SqlParameter("@asset", strAssetTag))
        sComm.Parameters.Add(New SqlParameter("@location", strLocation))
        sComm.Parameters.Add(New SqlParameter("@notes", strNotes))
        sComm.Parameters.Add(New SqlParameter("@requestor", strUser))
        sComm.Parameters.Add(New SqlParameter("@dte", strDate))
        sComm.Parameters.Add(New SqlParameter("@sequence", seq))

        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
        sConn.Open()
        sComm.ExecuteNonQuery()
        sConn.Close()

    End Sub

Open in new window

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
Man...  it must be the end of the day... because I sure am having trouble reading... :(

You are correct, I misread Serial as Sequnce on that textbox. You basically need a form-level variable that I alluded to earlier. You would set it in SQL_Search() and read it in Update_SQL(). I've included my "Public Class From1" line to show you where the form-level variable goes.
Public Class Form1
    Private sequence As Integer

    Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Update.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox2.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox3.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox4.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        Else
            Call Update_SQL()
            Call ClearAll()
            UserMsgBox(Me, "Your new record was submitted successfully.")
        End If
    End Sub

    Public Sub Update_SQL()
        Dim strAssetTag, strDate, strLocation, strNotes, strSerial As String
        strDate = Now
        strSerial = TextBox2.Text
        strAssetTag = TextBox3.Text
        strLocation = TextBox4.Text
        strNotes = TextMulti.Text


        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter
        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = @serial, AssetTag = @asset, Location = @location, Notes = @notes, Requestor = @requestor, Submitted = @dte WHERE Sequence = @sequence", sConn)

        sComm.Parameters.Add(New SqlParameter("@serial", strSerial))
        sComm.Parameters.Add(New SqlParameter("@asset", strAssetTag))
        sComm.Parameters.Add(New SqlParameter("@location", strLocation))
        sComm.Parameters.Add(New SqlParameter("@notes", strNotes))
        sComm.Parameters.Add(New SqlParameter("@requestor", strUser))
        sComm.Parameters.Add(New SqlParameter("@dte", strDate))
        sComm.Parameters.Add(New SqlParameter("@sequence", Me.sequence))   ' Reading the form variable

        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
        sConn.Open()
        sComm.ExecuteNonQuery()
        sConn.Close()
    End Sub

    Public Sub SQL_Search()
        Dim strSerial As String = ""
        Dim strAssetTag As String = ""
        Dim strLocation As String = ""
        Dim strNotes As String = ""
        Dim strColumn As String = ""
        Dim strSearch = TextBox1.Text

        If DropDownList1.Text = "Serial Number" Then
            strColumn = "Serial"
        End If
        If DropDownList1.Text = "Asset Tag" Then
            strColumn = "AssetTag"
        End If
        If DropDownList1.Text = "Location" Then
            strColumn = "Location"
        End If

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter

        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)
        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

        sConn.Open()

        Dim objDR As Data.SqlClient.SqlDataReader = sComm.ExecuteReader

        Do While objDR.Read()
            strSerial = objDR("Serial").ToString()
            strAssetTag = objDR("AssetTag").ToString()
            strLocation = objDR("Location").ToString()
            strNotes = objDR("Notes").ToString()
            Me.sequence = objDR.GetInt32(0)  ' Setting the form variable
        Loop

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes
        End If

        objDR.Close()
    End Sub


End Class

Open in new window

Author

Commented:
Hmmm...... I'm not getting an error, but it's not updating either. Code below as it exists now.
Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click
        If DropDownList1.Text = "Select Option" Then
            UserMsgBox(Me, "No value selected for 'Seach Item'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox1.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Seach Entry'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox2.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Serial Number'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox3.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Asset Tag'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        ElseIf TextBox4.Text = "" Then
            UserMsgBox(Me, "No value entered for 'Location'. You must search for a record before you can update it. " & vbCrLf & vbCrLf & "If you want to add a new record, Select 'Clear' and enter the values in the 'Results' fields. Then click the 'Add' button.")
        Else
            Call Update_SQL()
            Call ClearAll()
            UserMsgBox(Me, "Your new record was submitted successfully.")
        End If

    End Sub

Public Sub Update_SQL()
        Dim strAssetTag, strDate, strLocation, strNotes, strSerial As String
        strDate = Now
        strSerial = TextBox2.Text
        strAssetTag = TextBox3.Text
        strLocation = TextBox4.Text
        strNotes = TextMulti.Text


        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter
        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("UPDATE Barcode SET Serial = @serial, AssetTag = @asset, Location = @location, Notes = @notes, Requestor = @requestor, Submitted = @dte WHERE Sequence = @sequence", sConn)

        sComm.Parameters.Add(New SqlParameter("@serial", strSerial))
        sComm.Parameters.Add(New SqlParameter("@asset", strAssetTag))
        sComm.Parameters.Add(New SqlParameter("@location", strLocation))
        sComm.Parameters.Add(New SqlParameter("@notes", strNotes))
        sComm.Parameters.Add(New SqlParameter("@requestor", strUser))
        sComm.Parameters.Add(New SqlParameter("@dte", strDate))
        sComm.Parameters.Add(New SqlParameter("@sequence", Me.sequence)) 'Reading the form variable

        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)
        sConn.Open()
        sComm.ExecuteNonQuery()
        sConn.Close()

    End Sub

Public Sub SQL_Search()
        Dim strSerial As String = ""
        Dim strAssetTag As String = ""
        Dim strLocation As String = ""
        Dim strNotes As String = ""
        Dim strColumn As String = ""
        Dim strSearch = TextBox1.Text

        If DropDownList1.Text = "Serial Number" Then
            strColumn = "Serial"
        End If
        If DropDownList1.Text = "Asset Tag" Then
            strColumn = "AssetTag"
        End If
        If DropDownList1.Text = "Location" Then
            strColumn = "Location"
        End If

        Dim sConn As Data.SqlClient.SqlConnection
        Dim sComm As Data.SqlClient.SqlCommand
        Dim sAdapt As Data.SqlClient.SqlDataAdapter

        sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)
        sComm = New Data.SqlClient.SqlCommand("SELECT Sequence, Serial, AssetTag, Location, Notes, Requestor, Submitted FROM Barcode WHERE UPPER(" & strColumn & ")Like'%" & strSearch & "%'", sConn)
        sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

        sConn.Open()

        Dim objDR As Data.SqlClient.SqlDataReader = sComm.ExecuteReader

        Do While objDR.Read()
            strSerial = objDR("Serial").ToString()
            strAssetTag = objDR("AssetTag").ToString()
            strLocation = objDR("Location").ToString()
            strNotes = objDR("Notes").ToString()
            Me.sequence = objDR.GetInt32(0)  ' Setting the form variable

        Loop

        If objDR.HasRows = False Then
            UserMsgBox(Me, "No Value Found. Check Your Search Entry.")
        Else
            TextBox2.Text = strSerial
            TextBox3.Text = strAssetTag
            TextBox4.Text = strLocation
            TextMulti.Text = strNotes

        End If

        objDR.Close()

    End Sub

Open in new window

Author

Commented:
Private sequence As Integer    <---- Listed under the inherits statement at the top. Didn't mean to leave that  out.
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
Ok. You need to initialize Me.sequence at some point--I mentioned the constructor earlier, but you can also do it on the same line as the declaration, like:

    Private sequence As Integer = 1

You also need to increment it at  some point, but I can't remember where you said that was being done. Whichever function does the incrementing should have a line like

    Me.sequence = Me.sequence + 1

Author

Commented:
Ok... declaration is done. No idea which function is incrementing or where I would  put:
Me.sequence = Me.sequence + 1
The initialization makes sense, but if I'm grabbing the Sequence value during the search, and the private declaration allows it to be held so that it knows which row to apply the update, what's the purpose of the increment statement?
 
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
I wasn't sure where you were making new sequence numbers. If the DB already has them by the time you do the search, then you can disregard incrementing.

Author

Commented:
Oh... ok. Yeah I put the sequence field in place for that reason. You threw me on that one because as records are added or removed the sequence number could be anything.
Something is still messed up. I've update the search popup message to read: Here's your info, and then report back with the sequence number.
For testing I've got the table setup sequenced 1-5 in order.  
I searched on a value that was associated with Sequence 3. It reported back with the correct data and Sequence = 3. However when I click the update button nothings happening.
Interestingly I entered sequence = 1 in the update sub, and it would take the information from the from PLUS the changes and overwrite the top record aka sequence 1 every time.
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced 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:
Can you set a breakpoint at the line

    sComm.Parameters.Add(New SqlParameter("@sequence", Me.sequence)) 'Reading the form variable

and inspect what value Me.sequence holds? You might want to inspect the other variables as well to make sure their values are correct also.

Author

Commented:
Ok, here's the deal. Because the table produced a duplicate row by overwriting row one, I deleted that row. Sequence fields now range from 2-5.
I searched for data that was on row 2, search worked correctly, msgbox shows sequence equals 2.
However, the breakpoint within the update shows the sequence value as 1. All other values are correct.
 

Author

Commented:
Any idea what needs to take place for that value to hold?

Commented:
JB4375,

I don't know why the value would not hold, but perhaps you might try a property, and trap the "Set" accessor of the property...  It could be you've excluded code that is inadvertently modifying the sequence...

You might have to set the 'Step over properties and operators (Managed only)' to false, if VS 2008...

Jake
Private m_Sequence As Integer ' Replace your sequence declaration.

Private Property Sequence() As Integer
   Get
      Return m_Squence
   End Get
   Set(ByVal value As Integer)
      m_Sequence = value ' <- BREAKPOINT
   End Set
End Property

Open in new window

Author

Commented:
Jake,
It's possible, but I think what's acutally taking place is that I'm setting the value at Form level to "1".
Within the search sub, I search on row 2 AKA sequence = "2", for example.
Once I click update all the values are still within the textboxes and I use those textboxes to update the table, along with any changes.  
I'm thinking that because I'm not passing the sequence value of 2 from the Search sub to the Update sub, it's getting back the value of 1.
Case and Point: I played around with this last night, and created a texbox for the sequence value. I didn't want to display the textbox so I set textbox5.visible = False on me.load.
It strikes me as being kinda amateurish but it works. I'd still be interested to hear your thoughts on my theory and my solution. Thanks for your time.
 
 

Commented:
JB4375,

I think you've nailed it, although unless you explicitly set the value to 1 in the first place, it should default to 0.

If your not passing the value from your forms, then that's most certainly the issue.  

As for using the textboxes, do you use DataBinding, or you just pull the values?  DataBinding might make things easier for you, however, it might be a waste if you've already got all your code working.
I used to do everything myself, but have noticed in my latest project that I save a LOT of time and hassle if I use databinding, just an off-topic suggestion =)

Do you need other assistance?

Jake

Author

Commented:

I think I good now. Thanks, for your help!!
Update: I've gotten inches away from making it work, UNTIL the customer added that they wanted to search by location AND filter the results. Obviously, any search that returns mulitple results would only display the first result to the fields within the form.
After some research I realized:
a. Gridview was my best option, and does the databinding.
b. That a lot of the functionality was built in to gridview.
c. I can still use the results fields to add a record, and Edit & Delete on the grid.
d. Working on filtering options within gridview now.
One thing about coding; it's in the struggle that we learn the most, right?
 

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