VB.NET

VB.NET RSS

Syntax error on update database code

Please support our VB.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2008

Posts: 4

Reputation: witternb is an unknown quantity at this point

Solved Threads: 0

witternb

Offline

Newbie Poster

Syntax error on update database code

Jun 12th, 2008

Hello Everyone,

I am very new to VB and currently using VB Express 2008 to create a simple program. However, I am running into a syntax error on my update code. I have done some research on the web but cannot find a common thread. I am hoping some of the experts could take a look and help me understand where I am going wrong with the syntax.

Code:

Help with Code Tags

(Toggle Plain Text)

Public Shared Function UpdateAcctUsers(ByVal oldusermaint As UserMaint, ByVal newusermaint As UserMaint) As Boolean
        Dim connection As OleDbConnection = FINDB.GetConnection()
        Dim updateStatement As String _
            = "UPDATE tblValidUsers SET " _
            & "IST = @NewIST, " _
            & "BS = @NewBS, " _
            & "CIS = @NewCIS, " _
            & "CBS = @NewCBS, " _
            & "LCIS = @NewLCIS, " _
            & "LCBS = @NewLCBS, " _
            & "RF = @NewRF, " _
            & "CF = @NewCF, " _
            & "FEAM = @NewFEAM, " _
            & "IC = @NewIC, " _
            & "STD = @NewSTD, " _
            & "PF = @NewPF, " _
            & "CS = @NewCS, " _
            & "WHERE fldUser = @OldfldUser " _
            & "AND IST = @OldIST " _
            & "AND BS = @OldBS " _
            & "AND CIS = @OldCIS " _
            & "AND CBS = @OldCBS " _
            & "AND LCIS = @OldLCIS " _
            & "AND LCBS = @OldLCBS " _
            & "AND RF = @OldRF " _
            & "AND CF = @OldCF " _
            & "AND FEAM = @OldFEAM " _
            & "AND IC = @OldIC " _
            & "AND STD = @OldSTD " _
            & "AND PF = @OldPF " _
            & "AND CS = @OldCS "
        Dim updateCommand As New OleDbCommand(updateStatement, connection)
        updateCommand.Parameters.AddWithValue("@NewIST", newusermaint.IST)
        updateCommand.Parameters.AddWithValue("@NewBS", newusermaint.BS)
        updateCommand.Parameters.AddWithValue("@NewCIS", newusermaint.CIS)
        updateCommand.Parameters.AddWithValue("@NewCBS", newusermaint.CBS)
        updateCommand.Parameters.AddWithValue("@NewLCIS", newusermaint.LCIS)
        updateCommand.Parameters.AddWithValue("@NewLCBS", newusermaint.LCBS)
        updateCommand.Parameters.AddWithValue("@NewRF", newusermaint.RF)
        updateCommand.Parameters.AddWithValue("@NewCF", newusermaint.CF)
        updateCommand.Parameters.AddWithValue("@NewFEAM", newusermaint.FEAM)
        updateCommand.Parameters.AddWithValue("@NewIC", newusermaint.IC)
        updateCommand.Parameters.AddWithValue("@NewSTD", newusermaint.STD)
        updateCommand.Parameters.AddWithValue("@NewPF", newusermaint.PF)
        updateCommand.Parameters.AddWithValue("@NewCS", newusermaint.CS)
        updateCommand.Parameters.AddWithValue("@OldfldUser", oldusermaint.fldUser)
        updateCommand.Parameters.AddWithValue("@OldIST", oldusermaint.IST)
        updateCommand.Parameters.AddWithValue("@OldBS", oldusermaint.BS)
        updateCommand.Parameters.AddWithValue("@OldCIS", oldusermaint.CIS)
        updateCommand.Parameters.AddWithValue("@OldCBS", oldusermaint.CBS)
        updateCommand.Parameters.AddWithValue("@OldLCIS", oldusermaint.LCIS)
        updateCommand.Parameters.AddWithValue("@OldLCBS", oldusermaint.LCBS)
        updateCommand.Parameters.AddWithValue("@OldRF", oldusermaint.RF)
        updateCommand.Parameters.AddWithValue("@OldCF", oldusermaint.CF)
        updateCommand.Parameters.AddWithValue("@OldFEAM", oldusermaint.FEAM)
        updateCommand.Parameters.AddWithValue("@OldIC", oldusermaint.IC)
        updateCommand.Parameters.AddWithValue("@OldSTD", oldusermaint.STD)
        updateCommand.Parameters.AddWithValue("@OldPF", oldusermaint.PF)
        updateCommand.Parameters.AddWithValue("@OldCS", oldusermaint.CS)
        Try
            connection.Open()
            Dim count As Integer = updateCommand.ExecuteNonQuery
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
        End Try
    End Function

Public Shared Function UpdateAcctUsers(ByVal oldusermaint As UserMaint, ByVal newusermaint As UserMaint) As Boolean
        Dim connection As OleDbConnection = FINDB.GetConnection()
        Dim updateStatement As String _
            = "UPDATE tblValidUsers SET " _
            & "IST = @NewIST, " _
            & "BS = @NewBS, " _
            & "CIS = @NewCIS, " _
            & "CBS = @NewCBS, " _
            & "LCIS = @NewLCIS, " _
            & "LCBS = @NewLCBS, " _
            & "RF = @NewRF, " _
            & "CF = @NewCF, " _
            & "FEAM = @NewFEAM, " _
            & "IC = @NewIC, " _
            & "STD = @NewSTD, " _
            & "PF = @NewPF, " _
            & "CS = @NewCS, " _
            & "WHERE fldUser = @OldfldUser " _
            & "AND IST = @OldIST " _
            & "AND BS = @OldBS " _
            & "AND CIS = @OldCIS " _
            & "AND CBS = @OldCBS " _
            & "AND LCIS = @OldLCIS " _
            & "AND LCBS = @OldLCBS " _
            & "AND RF = @OldRF " _
            & "AND CF = @OldCF " _
            & "AND FEAM = @OldFEAM " _
            & "AND IC = @OldIC " _
            & "AND STD = @OldSTD " _
            & "AND PF = @OldPF " _
            & "AND CS = @OldCS "
        Dim updateCommand As New OleDbCommand(updateStatement, connection)
        updateCommand.Parameters.AddWithValue("@NewIST", newusermaint.IST)
        updateCommand.Parameters.AddWithValue("@NewBS", newusermaint.BS)
        updateCommand.Parameters.AddWithValue("@NewCIS", newusermaint.CIS)
        updateCommand.Parameters.AddWithValue("@NewCBS", newusermaint.CBS)
        updateCommand.Parameters.AddWithValue("@NewLCIS", newusermaint.LCIS)
        updateCommand.Parameters.AddWithValue("@NewLCBS", newusermaint.LCBS)
        updateCommand.Parameters.AddWithValue("@NewRF", newusermaint.RF)
        updateCommand.Parameters.AddWithValue("@NewCF", newusermaint.CF)
        updateCommand.Parameters.AddWithValue("@NewFEAM", newusermaint.FEAM)
        updateCommand.Parameters.AddWithValue("@NewIC", newusermaint.IC)
        updateCommand.Parameters.AddWithValue("@NewSTD", newusermaint.STD)
        updateCommand.Parameters.AddWithValue("@NewPF", newusermaint.PF)
        updateCommand.Parameters.AddWithValue("@NewCS", newusermaint.CS)
        updateCommand.Parameters.AddWithValue("@OldfldUser", oldusermaint.fldUser)
        updateCommand.Parameters.AddWithValue("@OldIST", oldusermaint.IST)
        updateCommand.Parameters.AddWithValue("@OldBS", oldusermaint.BS)
        updateCommand.Parameters.AddWithValue("@OldCIS", oldusermaint.CIS)
        updateCommand.Parameters.AddWithValue("@OldCBS", oldusermaint.CBS)
        updateCommand.Parameters.AddWithValue("@OldLCIS", oldusermaint.LCIS)
        updateCommand.Parameters.AddWithValue("@OldLCBS", oldusermaint.LCBS)
        updateCommand.Parameters.AddWithValue("@OldRF", oldusermaint.RF)
        updateCommand.Parameters.AddWithValue("@OldCF", oldusermaint.CF)
        updateCommand.Parameters.AddWithValue("@OldFEAM", oldusermaint.FEAM)
        updateCommand.Parameters.AddWithValue("@OldIC", oldusermaint.IC)
        updateCommand.Parameters.AddWithValue("@OldSTD", oldusermaint.STD)
        updateCommand.Parameters.AddWithValue("@OldPF", oldusermaint.PF)
        updateCommand.Parameters.AddWithValue("@OldCS", oldusermaint.CS)
        Try
            connection.Open()
            Dim count As Integer = updateCommand.ExecuteNonQuery
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
        End Try
    End Function

Thanks in advance for your help!

Brad

•

Join Date: Jun 2007

Posts: 22

Reputation: dadelsen is an unknown quantity at this point

Solved Threads: 5

dadelsen

Offline

Newbie Poster

Re: Syntax error on update database code

Jun 12th, 2008

one problem is the comma before the WHERE.

Replace

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

 & "CS = @NewCS, " _
            & "WHERE fldUser = @OldfldUser " _

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

 & "CS = @NewCS  " _
            & "WHERE fldUser = @OldfldUser " _

If more syntax errors please post back

•

Join Date: Jun 2008

Posts: 4

Reputation: witternb is an unknown quantity at this point

Solved Threads: 0

witternb

Offline

Newbie Poster

Re: Syntax error on update database code

Jun 12th, 2008

That was it...Thank you very much for your assistance!

Quick Reply to Thread

Show Printable Version

Similar Threads

MySQL syntax error 1064 (MySQL)
Syntax error in INSERT INTO statement (Java)
UPDATE query problem (ASP.NET)
Problems on Insert and Update Statement (C#)
Problems on Insert and Update (C#)
Insert and Update Errors (C#)
how to validate text file, loading into listbox (Visual Basic 4 / 5 / 6)
Error connecting to database (ASP.NET)

Other Threads in the VB.NET Forum

Previous Thread: byref in vb.net
Next Thread: Problem in retrieving data using data binding

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes