none
Error: incorrect syntax near the keyword 'Into' RRS feed

  • Question

  • Hello Guys, can you help me fix the "incorrect syntax near the keyword 'Into' " Error.

     Private Sub BT_Save_Click(sender As System.Object, e As System.EventArgs) Handles BT_Save.Click
    
            Dim StrConn = "password=" & dbPWD & ";Persist Security Info=True;User ID=" & dbUserName & ";Initial Catalog=" & dbName & ";Data Source=" & dbAddress & ""
            Dim SQLConn As New SqlConnection(StrConn)
    
    
            For Each row As DataGridViewRow In DG_Import.Rows
    
    
                SQLConn.Open()
                SQLcmd.Connection = SQLConn
                SQLcmd.CommandType = System.Data.CommandType.Text
    
                SQLcmd.CommandText = "INSERT Into TB_Employees (LocalWord,Status,LastName,FirstName,SocialSecurity,ADPID,Func,HireDate,RehireDate,TerminationDate) Values (" & row.Cells(0).Value & ", '" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "', '" & row.Cells(3).Value & "', '" & row.Cells(4).Value & "', '" & row.Cells(5).Value & "', '" & row.Cells(6).Value & "', '" & row.Cells(7).Value & "', '" & row.Cells(8).Value & "', " & row.Cells(9).Value & " )"
    
                SQLcmd.Connection = SQLConn
                SQLcmd.ExecuteNonQuery()
    
            Next
    
            MessageBox.Show("Inserted")
    
        End Sub

    Thank You Very Much Guys.

     
    Monday, September 18, 2017 3:38 PM

Answers

  • Hi Julian,

    According to your description, you want to insert data into sql database, but you would confirm ADPID data type in the sql database, and HireDate, RehireDate, TerminationDate are null or not.

    I suggest you to use Parameters in sql insert statement , like this:

    Dim StrConn = "password=" & dbPWD & ";Persist Security Info=True;User ID=" & dbUserName & ";Initial Catalog=" & dbName & ";Data Source=" & dbAddress & ""
            Dim SQLConn As New SqlConnection(StrConn)
            Dim SQLcmd As New SqlCommand()
    
    
            For Each row As DataGridViewRow In DG_Import.Rows
                Dim LocalWord As String = row.Cells("LocalWord").Value
                Dim Status As String = row.Cells("Status").Value
                Dim FirstName As String = row.Cells("FirstName").Value
                Dim LastName As String = row.Cells("LastName").Value
                Dim SocialSecurity As String = row.Cells("SocialSecurity").Value
                If row.Cells("ADPID").Value <> "" Then
                    Dim ADPID As Integer = Convert.ToInt32(row.Cells("ADPID").Value)
                Else
                    Dim ADPID As Integer = 0
                End If
    
                Dim Func As String = row.Cells("Func").Value
                If row.Cells("HireDate").Value <> "" Then
                    Dim HireDate As DateTime = Convert.ToDateTime(row.Cells("HireDate").Value)
                Else
                    Dim HireDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
                If row.Cells("RehireDate").Value <> "" Then
                    Dim RehireDate As DateTime = Convert.ToDateTime(row.Cells("RehireDate").Value)
                Else
                    Dim RehireDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
                If row.Cells("TerminationDate").Value <> "" Then
                    Dim TerminationDate As DateTime = Convert.ToDateTime(row.Cells("TerminationDate").Value)
                Else
                    Dim TerminationDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
    
    
                SQLConn.Open()
                SQLcmd.Connection = SQLConn
                SQLcmd.CommandType = System.Data.CommandType.Text
    
                SQLcmd.CommandText = "INSERT Into TB_Employees (LocalWord,Status,LastName,FirstName,SocialSecurity,ADPID,Func,HireDate,RehireDate,TerminationDate)
                 Values (@LocalWord,@Status,@LastName,@FirstName,@SocialSecurity,@ADPID,@Func,@HireDate,@RehireDate,@TerminationDate)"
                SQLcmd.Parameters.AddWithValue("@LocalWord", LocalWord)
                SQLcmd.Parameters.AddWithValue("@Status", Status)
                SQLcmd.Parameters.AddWithValue("@LastName", LastName)
                SQLcmd.Parameters.AddWithValue("@FirstName", FirstName)
                SQLcmd.Parameters.AddWithValue("@SocialSecurity", SocialSecurity)
                SQLcmd.Parameters.AddWithValue("@ADPID", ADPID)
                SQLcmd.Parameters.AddWithValue("@Func", Func)
                SQLcmd.Parameters.AddWithValue("@HireDate", HireDate)
                SQLcmd.Parameters.AddWithValue("@RehireDate", RehireDate)
                SQLcmd.Parameters.AddWithValue("@TerminationDate", TerminationDate)
    
                SQLcmd.Connection = SQLConn
                SQLcmd.ExecuteNonQuery()
    
            Next
    
            MessageBox.Show("Inserted")
    

    You can modify your code and try it again.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Julian J T Tuesday, September 19, 2017 5:26 PM
    Tuesday, September 19, 2017 6:42 AM
    Moderator

All replies

  • Can you show us the INSERT statement with values, otherwise we can't see what's going on.

    Also, if you have SQL-Server Management Studio, drop the INSERT statement into a new query window, see if there are errors reported even before attempting to execute as Management Studio does parse the query before running it.

    If using VS2017, create a new text file in the project, give it the name of say Test.sql and pop in the script.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, September 18, 2017 4:02 PM
    Moderator
  • Probably Cell(9) contains a value (date) which cannot be simply inserted into SQL command. Sometimes apostrophes and concatenation work if used correctly, but the recommended approach is using Parameterized Queries. Check MSDN documentation and also:

    Monday, September 18, 2017 5:50 PM
  • Please use Command Parameters in your SQL statement rather than variables. It will resolve many potential issues, such as the syntax error you are receiving. There is an example at the below link:

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue%28v=vs.110%29.aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, September 18, 2017 7:47 PM
  • Hi Julian,

    According to your description, you want to insert data into sql database, but you would confirm ADPID data type in the sql database, and HireDate, RehireDate, TerminationDate are null or not.

    I suggest you to use Parameters in sql insert statement , like this:

    Dim StrConn = "password=" & dbPWD & ";Persist Security Info=True;User ID=" & dbUserName & ";Initial Catalog=" & dbName & ";Data Source=" & dbAddress & ""
            Dim SQLConn As New SqlConnection(StrConn)
            Dim SQLcmd As New SqlCommand()
    
    
            For Each row As DataGridViewRow In DG_Import.Rows
                Dim LocalWord As String = row.Cells("LocalWord").Value
                Dim Status As String = row.Cells("Status").Value
                Dim FirstName As String = row.Cells("FirstName").Value
                Dim LastName As String = row.Cells("LastName").Value
                Dim SocialSecurity As String = row.Cells("SocialSecurity").Value
                If row.Cells("ADPID").Value <> "" Then
                    Dim ADPID As Integer = Convert.ToInt32(row.Cells("ADPID").Value)
                Else
                    Dim ADPID As Integer = 0
                End If
    
                Dim Func As String = row.Cells("Func").Value
                If row.Cells("HireDate").Value <> "" Then
                    Dim HireDate As DateTime = Convert.ToDateTime(row.Cells("HireDate").Value)
                Else
                    Dim HireDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
                If row.Cells("RehireDate").Value <> "" Then
                    Dim RehireDate As DateTime = Convert.ToDateTime(row.Cells("RehireDate").Value)
                Else
                    Dim RehireDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
                If row.Cells("TerminationDate").Value <> "" Then
                    Dim TerminationDate As DateTime = Convert.ToDateTime(row.Cells("TerminationDate").Value)
                Else
                    Dim TerminationDate As DateTime = Format(DateTime.Now, "yyyy-MM-dd")
                End If
    
    
                SQLConn.Open()
                SQLcmd.Connection = SQLConn
                SQLcmd.CommandType = System.Data.CommandType.Text
    
                SQLcmd.CommandText = "INSERT Into TB_Employees (LocalWord,Status,LastName,FirstName,SocialSecurity,ADPID,Func,HireDate,RehireDate,TerminationDate)
                 Values (@LocalWord,@Status,@LastName,@FirstName,@SocialSecurity,@ADPID,@Func,@HireDate,@RehireDate,@TerminationDate)"
                SQLcmd.Parameters.AddWithValue("@LocalWord", LocalWord)
                SQLcmd.Parameters.AddWithValue("@Status", Status)
                SQLcmd.Parameters.AddWithValue("@LastName", LastName)
                SQLcmd.Parameters.AddWithValue("@FirstName", FirstName)
                SQLcmd.Parameters.AddWithValue("@SocialSecurity", SocialSecurity)
                SQLcmd.Parameters.AddWithValue("@ADPID", ADPID)
                SQLcmd.Parameters.AddWithValue("@Func", Func)
                SQLcmd.Parameters.AddWithValue("@HireDate", HireDate)
                SQLcmd.Parameters.AddWithValue("@RehireDate", RehireDate)
                SQLcmd.Parameters.AddWithValue("@TerminationDate", TerminationDate)
    
                SQLcmd.Connection = SQLConn
                SQLcmd.ExecuteNonQuery()
    
            Next
    
            MessageBox.Show("Inserted")
    

    You can modify your code and try it again.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Julian J T Tuesday, September 19, 2017 5:26 PM
    Tuesday, September 19, 2017 6:42 AM
    Moderator