Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Question:

can any shifu and pros help me to take a look, what i should correct??

Error message:

........Incorrect Syntax near '('.....................
*error occurred on line which underlined in code section below.



VB
Public Sub updateai()
       Dim nsr = DataGridView1.SelectedRows.Count - 1
       Dim updateline As Integer = DataGridView1.SelectedRows(nsr).Cells(0).Value
                       Dim updateme As String = "UPDATE ICDTBL SET (CreateBy, StartDate, CloseDate, StartTime, CloseTime, Supplier, ActualETA, OrderNum, Remark, Status) VALUES (@CreateBy, @StartDate, @CloseDate, @StartTime, @CloseTime, @Supplier, @ActualETA, @OrderNum, @Remark, @Status) WHERE IDNO = " & updateline & ""
       Dim cmd As New SqlCommand(updateme, connection)

       If RichTextBox1.Text = "" And TextBox6.Text = "" And TextBox8.Text = "" And TextBox9.Text = "" Then
           MsgBox("blank content will not be send out")
       Else
           Try
               If connection.State = ConnectionState.Closed Then
                   connection.Open()
               End If
               cmd.Parameters.AddWithValue("@CreateBy", Label10.Text.Trim)
               If DateTimePicker1.Value < Date.Today And DateTimePicker2.Value < Date.Today And DateTimePicker1.Value > DateTimePicker2.Value Then
                   MsgBox("Start Date and Close Date cannot less than today's date AND Start Date cannot more than Close Date")
               ElseIf DateTimePicker3.Value < myTime And DateTimePicker4.Value < myTime And DateTimePicker2.Value > DateTimePicker3.Value Then
                   MsgBox("Start Time and Close Time cannot less than Current Time AND Start Time cannot more than Close Time")
               Else
                   cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString)
                   cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString)
                   cmd.Parameters.AddWithValue("@StartTime", DateTimePicker3.Value.ToShortTimeString)
                   cmd.Parameters.AddWithValue("@CloseTime", DateTimePicker4.Value.ToShortTimeString)
               End If
               cmd.Parameters.AddWithValue("@Supplier", TextBox6.Text.Trim)
               cmd.Parameters.AddWithValue("@ActualETA", RichTextBox1.Text.Trim)
               cmd.Parameters.AddWithValue("@OrderNum", TextBox8.Text.Trim)
               cmd.Parameters.AddWithValue("@Remark", TextBox9.Text.Trim)
               If Label12.Text = "" Then
                   MsgBox("Please specify task status")
               Else
                   cmd.Parameters.AddWithValue("@Status", Label12.Text.Trim)
               End If
               cmd.ExecuteNonQuery()
               MsgBox("Information updated")
               connection.Close()
               RichTextBox1.Text = ""
               TextBox6.Text = ""
               TextBox8.Text = ""
               TextBox9.Text = ""
               Label12.Text = ""
               loadalll()
           Catch ex As SqlException
               MsgBox(ex.Message)
           End Try

       End If
   End Sub
Posted
Comments
Bernhard Hiller 12-Jun-13 3:02am    
By the way, that's not a VB.Net question, but SQL! A correct differentiation will help you find the solution yourself.

You actually have a syntax error in your update statement.

You need to use the form

SQL
update TABLE set FIELD1=VALUE1, FIELD2=VALUE2
where WHERECLAUSE
 
Share this answer
 
Comments
donaldliaw87 12-Jun-13 2:02am    
i try your advice... and give you what i get.... thx 1st!
uspatel 12-Jun-13 2:02am    
good catch.......
donaldliaw87 12-Jun-13 2:05am    
hi damian s you mean i have to code like this e.g: UPDATE table SET CreateBy=@CreateBy, ?? sorry i'm new to vb.net please guide me if i'm wrong....
_Damian S_ 12-Jun-13 2:07am    
I think you might have to use the actual value, rather than a parameter... give it a try...
donaldliaw87 12-Jun-13 2:23am    
erm my parameter which past values, i try what you taught still getting the same error msg....
You should use
cmd.Parameters.AddWithValue("@Status", Label12.Text.Trim())


and

cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString())
 
Share this answer
 
Thanks to:

Uma Shankar Patel
Damian S

Solution:

>correction of my sql update statement
>put a for loop to force datagridview index not to negative or more than......



VB
Public Sub updateai()
        Dim nsr = DataGridView1.SelectedRows.Count
        For i = 0 To (nsr - 1)
            Dim updateline As Integer = DataGridView1.SelectedRows(i).Cells(0).Value
            Dim updateme As String = "UPDATE ICDTBL SET CreateBy=@CreateBy, StartDate=@StartDate, CloseDate=@CloseDate, StartTime=@StartTime, CloseTime=@CloseTime, Supplier=@Supplier, ActualETA=@ActualETA, OrderNum=@OrderNum, Remark=@Remark, Status=@Status WHERE IDNO = " & updateline & ""
            Dim cmd As New SqlCommand(updateme, connection)

            If RichTextBox1.Text = "" And TextBox6.Text = "" And TextBox8.Text = "" And TextBox9.Text = "" Then
                MsgBox("blank content will not be send out")
            Else
                Try
                    If connection.State = ConnectionState.Closed Then
                        connection.Open()
                    End If
                    cmd.Parameters.AddWithValue("@CreateBy", Label10.Text.Trim())
                    If DateTimePicker1.Value < Date.Today And DateTimePicker2.Value < Date.Today And DateTimePicker1.Value > DateTimePicker2.Value Then
                        MsgBox("Start Date and Close Date cannot less than today's date AND Start Date cannot more than Close Date")
                    ElseIf DateTimePicker3.Value < myTime And DateTimePicker4.Value < myTime And DateTimePicker2.Value > DateTimePicker3.Value Then
                        MsgBox("Start Time and Close Time cannot less than Current Time AND Start Time cannot more than Close Time")
                    Else
                        cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString())
                        cmd.Parameters.AddWithValue("@CloseDate", DateTimePicker2.Value.ToShortDateString())
                        cmd.Parameters.AddWithValue("@StartTime", DateTimePicker3.Value.ToShortTimeString())
                        cmd.Parameters.AddWithValue("@CloseTime", DateTimePicker4.Value.ToShortTimeString())
                    End If
                    cmd.Parameters.AddWithValue("@Supplier", TextBox6.Text.Trim())
                    cmd.Parameters.AddWithValue("@ActualETA", RichTextBox1.Text.Trim())
                    cmd.Parameters.AddWithValue("@OrderNum", TextBox8.Text.Trim())
                    cmd.Parameters.AddWithValue("@Remark", TextBox9.Text.Trim())
                    If Label12.Text = "" Then
                        MsgBox("Please specify task status")
                    Else
                        cmd.Parameters.AddWithValue("@Status", Label12.Text.Trim())
                    End If
                    cmd.ExecuteNonQuery()
                    MsgBox("Information updated")
                    connection.Close()
                    RichTextBox1.Text = ""
                    TextBox6.Text = ""
                    TextBox8.Text = ""
                    TextBox9.Text = ""
                    Label12.Text = ""
                    loadalll()
                Catch ex As SqlException
                    MsgBox(ex.Message)
                End Try
            End If
        Next
 
Share this answer
 

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