Click here to Skip to main content
14,640,941 members
Rate this:
Please Sign up or sign in to vote.
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.



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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

You actually have a syntax error in your update statement.

You need to use the form

update TABLE set FIELD1=VALUE1, FIELD2=VALUE2
where WHERECLAUSE
   
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....
Rate this:
Please Sign up or sign in to vote.

Solution 1

You should use
cmd.Parameters.AddWithValue("@Status", Label12.Text.Trim())


and

cmd.Parameters.AddWithValue("@StartDate", DateTimePicker1.Value.ToShortDateString())
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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......



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
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100