I receive this error

4f39dda7d2306f99bd623d1011e6cf34

when I have this code:

Private Sub EditUsers()
        Try
            Dim dt As New DataTable
            dt = ExecuteQuery("UPDATE tblUsers SET UserID = " & txtUserID.Text & ", UserName = '" & txtPassword.Text & "', Password = " &
                              txtPassword.Text & ", LastName = '" & txtLastName.Text & "', FirstName = '" & txtFirstName.Text & "', Position = '" &
                              txtPosition.Text & "', Address = " & txtAddress.Text & ", EmailAddress = " & txtEmailAddress.Text & ", SSS_ID = " &
                              txtSSS.Text & ", ContactNumber = " & txtPhone.Text & " ")
            Call Clear()
            MessageBox.Show("Record updated.", "User Record", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
            txtUserName.Focus()
            Call fillLvUsers()

        Catch ex As Exception
            MsgBox(ex.ToString)
            txtUserName.Focus()
        End Try
End Sub

Here's for the connection in mdlConnection.

Imports System.Data.SqlClient
Module mdlConnection
    Private conn As New SqlConnection("Data Source=PC;Initial Catalog=Users;Integrated Security=True")
    Public Function ExecuteQuery(ByVal command As String)
        Dim sqlDA As New SqlDataAdapter(command, conn)
        Dim sqlDataTable As New DataTable
        Try
            sqlDA.Fill(sqlDataTable)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        Return sqlDataTable
    End Function
End Module

What should I do? Please. I need help.
Everything else is working fine with my codes in Add and Delete.

Thank you!

Edited 2 Years Ago by jmProg: Lack of information

From the error above, it says that the error occurs in my mdlConnection.
Why is it so? It works fine with the other sql commands I made. Just for the UPDATE, there seems to be an error. Please. I need help. Any suggestions regarding this is greatly appreciated. Thank you! :)

Edited 2 Years Ago by jmProg: Wrong notes

Are all the fields filled. The error indicates that the SQL is incorrect. If a field is blank your SQL will look something like:

Select * from MyTable where ID = 

since the ID field is blank, your SQL will not be valid. This will be the same for the SQL you provided. Since you declares the variables (LastName, FirstName, etc..), you must supply a value. So your statement now becomesocomplex. YOu have to check whether the field has a value; if it does, ass it to the SQL Statement.

Edited 2 Years Ago by Maligui

Replace

dt = ExecuteQuery("UPDATE tblUsers SET UserID = " & txtUserID.Text & ", UserName = '" & txtPassword.Text & "', Password = " &
                          txtPassword.Text & ", LastName = '" & txtLastName.Text & "', FirstName = '" & txtFirstName.Text & "', Position = '" &
                          txtPosition.Text & "', Address = " & txtAddress.Text & ", EmailAddress = " & txtEmailAddress.Text & ", SSS_ID = " &
                          txtSSS.Text & ", ContactNumber = " & txtPhone.Text & " ")

with

dim query as string = "UPDATE tblUsers SET UserID = " & txtUserID.Text & ", UserName = '" & txtPassword.Text & "', Password = " &
                          txtPassword.Text & ", LastName = '" & txtLastName.Text & "', FirstName = '" & txtFirstName.Text & "', Position = '" &
                          txtPosition.Text & "', Address = " & txtAddress.Text & ", EmailAddress = " & txtEmailAddress.Text & ", SSS_ID = " &
                          txtSSS.Text & ", ContactNumber = " & txtPhone.Text & " "
Debug.WriteLine(query)
dt = ExecuteQuery(query)

and post the output of Debug.WriteLine in this thread. Because the problem is with your query it would help to know what query the DBMS is executing. Incidentally, with some constructive code formatting you can get

dim query as string = _
      "UPDATE tblUsers " &
      "   SET UserID        =  " & txtUserID.Text & "," &
      "       UserName      = '" & txtPassword.Text & "'," &
      "       Password      =  " & txtPassword.Text & "," &
      "       LastName      = '" & txtLastName.Text & "'," &
      "       FirstName     = '" & txtFirstName.Text & "'," &
      "       Position      = '" & txtPosition.Text & "'," &
      "       Address       =  " & txtAddress.Text & "," &
      "       EmailAddress  =  " & txtEmailAddress.Text & "," &
      "       SSS_ID        =  " & txtSSS.Text & "," &
      "       ContactNumber =  " & txtPhone.Text
Debug.WriteLine(query)
dt = ExecuteQuery(query)

It is a little easier to read and debug and you can see that you are using txtPassword.Text for both the username and password fields. Once you get it working you might want to consider using parameterized queries.

Edited 2 Years Ago by Reverend Jim

Comments
This article has been dead for over six months. Start a new discussion instead.