Problem with Excel to SQL Import

I have attached code that reads a row at a time from an excel spreadsheet and writes it to a SQL table, checking wheater the record first exists in the table before writing. I am getting an error .

The SQL table has the first column set as an autoincrementing identity field. I am not writing to that field. Only the fields after it.
The error is occurring near the bottom of the code on the line:  cmd.ExecuteNonQuery().
Error is :  The name "Hispanic" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any help would be appreciated.

See code......

Option Explicit On
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPrinting
Imports DevExpress.XtraPrinting.Native
Imports Excel
 
Public Class DBMigration
    Dim FlagA As String = "OFF"
    Dim i As Integer
 
 
    Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click
        CheckFileBox()
 
        If FlagA = "ON" Then
            FlagA = "OFF"
            Me.Close()
            Exit Sub
        Else
            MoveNoMatch()
        End If
    End Sub
    Private Sub CheckFileBox()
 
        Dim Answer As String
        Dim MyNote As String
 
 
        If txtExcel.Text = "" Then
            MyNote = "You Must Enter a Spreadsheet to Import. Do You Wish To Continue?"
            'Display MessageBox
            Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "WARNING")
 
            If Answer = vbNo Then
                FlagA = "ON"
            Else
                'Code for Yes button Press
                txtExcel.Focus()
            End If
        End If
    End Sub
 
    Private Sub btnGroups_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGroups.Click
        ' Update the text box folder if the user clicks OK 
        If (OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
            txtExcel.Text = OpenFileDialog1.FileName
        End If
    End Sub
 
    Private Sub MoveNoMatch() 'Now a function that takes file path for the XL FIle
        Dim FileName As String
        FileName = txtExcel.Text.Trim
 
        Dim DT As New System.Data.DataTable("Test")
        Dim ConnectionString As New System.Data.SqlClient.SqlConnection("Data Source=10.10.1.30,1433;Initial Catalog=C:\GITR\GIREPOSITORY\GIREPOSITORY.MDF;User ID=xxxxxx;Password=xxxxxx")
        Dim DA As New SqlDataAdapter("Select * From Test", ConnectionString)
        DA.Fill(DT)  ' Fill the DataTable from SQL so we need not Query the DB again and agian
 
        Dim XL As New Excel.Application
        Dim CurrWS As Excel.Worksheet
        Dim NewWS As Excel.Worksheet = Nothing
        XL.Workbooks.Open(FileName) 
 
        CurrWS = XL.Sheets("SERIAL")
        For Each ws As Worksheet In XL.Worksheets
            If ws.Name = "NOMATCH" Then
                NewWS = ws  'If sheet already there
                Exit For
            End If
        Next
 
        If NewWS Is Nothing Then
            NewWS = XL.Worksheets.Add ' Add a new sheet
            NewWS.Name = "NOMATCH"
            NewWS.Cells(1, 1) = "JPSID"
            NewWS.Cells(1, 2) = "Ethnicity"
            NewWS.Cells(1, 3) = "MaleFemale"
            NewWS.Cells(1, 4) = "Race"
            NewWS.Cells(1, 5) = "DOB"
            NewWS.Cells(1, 6) = "SubjectName"
            NewWS.Cells(1, 7) = "FirstName"
            NewWS.Cells(1, 8) = "MiddleName"
            NewWS.Cells(1, 9) = "LastName"
            NewWS.Cells(1, 10) = "Suffix"
            NewWS.Cells(1, 11) = "Address1"
            NewWS.Cells(1, 12) = "Address2"
            NewWS.Cells(1, 13) = "City"
            NewWS.Cells(1, 14) = "Zip"
            NewWS.Cells(1, 15) = "HomePhone"
            NewWS.Cells(1, 16) = "WorkPhone"
            NewWS.Cells(1, 17) = "CellPhone"
            NewWS.Cells(1, 18) = "RelativePhone"
            NewWS.Cells(1, 19) = "EMailAddress"
            NewWS.Cells(1, 20) = "Verify"
 
        End If
 
 
        Dim Serial As String
 
        'Start SQL For Patient Table Insert
        Dim cmd As System.Data.SqlClient.SqlCommand
        cmd = New System.Data.SqlClient.SqlCommand()
        cmd.CommandType = CommandType.Text
        cmd.Connection = ConnectionString
        'End It
        i = CurrWS.UsedRange.Rows.Count
        ProgressBarControl1.Properties.Maximum = i ' For Progress Bar
 
        For i As Integer = CurrWS.UsedRange.Rows.Count To 2 Step -1
            ProgressBarControl1.PerformStep()
            ProgressBarControl1.Update()
            Serial = CurrWS.Cells(i, "A").Value
 
            If DT.Select("JPSID='" & Serial & "'").Length <= 0 Then
                CurrWS.Cells(i, "A").EntireRow.Copy()
 
                'Check if This row is already in the NoMatch Sheet or Not
                If NewWS.Cells.Find(What:=Serial,  SearchOrder:=XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlNext) Is Nothing Then
 
                    NewWS.Activate()  'To avoid the Error you are getting
                    NewWS.Rows(NewWS.UsedRange.Rows.Count + 1).EntireRow.Select()
                    NewWS.Paste()  'Paste it to sheet
 
                    cmd.CommandText = ("INSERT INTO [Test] (JPSID, Ethnicity, MaleFemale, " & _
                    "Race, DOB, SubjectName, FirstName, MiddleName, LastName, Suffix, Address1, Address2, " & _
                    "City, Zip, HomePhone, CellPhone, WorkPhone, RelativePhone, EMailAddress, Verify " & _
                    ") VALUES(" & CurrWS.Cells(i, "A").Value & "," & CurrWS.Cells(i, "B").Value & ", " & _
                    CurrWS.Cells(i, "C").Value & "," & CurrWS.Cells(i, "D").Value & ", " & _
                    CurrWS.Cells(i, "E").Value & "," & CurrWS.Cells(i, "F").Value & ", " & _
                    CurrWS.Cells(i, "G").Value & "," & CurrWS.Cells(i, "H").Value & ", " & _
                    CurrWS.Cells(i, "I").Value & "," & CurrWS.Cells(i, "J").Value & ", " & _
                    CurrWS.Cells(i, "K").Value & "," & CurrWS.Cells(i, "L").Value & ", " & _
                    CurrWS.Cells(i, "M").Value & "," & CurrWS.Cells(i, "N").Value & ", " & _
                    CurrWS.Cells(i, "O").Value & "," & CurrWS.Cells(i, "P").Value & ", " & _
                    CurrWS.Cells(i, "Q").Value & "," & CurrWS.Cells(i, "R").Value & ", " & _
                    CurrWS.Cells(i, "S").Value & "," & CurrWS.Cells(i, "T").Value) 'Start post to table
 
                    ConnectionString.Open()
                    cmd.ExecuteNonQuery()
 
                End If
 
                CurrWS.Cells(i, "A").EntireRow.Delete() ' Now delete  row in original sheet
            End If
        Next
        XL.ActiveWorkbook.Save()
        XL.ActiveWorkbook.Close()
        XL.Application.Quit()
        XL = Nothing
        MessageBox.Show("Transfer Complete", "Excel Import", MessageBoxButtons.OK, MessageBoxIcon.Information)
 
    End Sub
 
 
    Private Sub DBMigration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Initializing progress bar properties
        ProgressBarControl1.Properties.Step = 1
        ProgressBarControl1.Properties.PercentView = True
        ProgressBarControl1.Properties.Minimum = 0
    End Sub
End Class

Open in new window

LVL 1
wsenterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HoggZillaCommented:
Write your SQL out to the console or to a message box. Let's see wha the string looks like.
0
HoggZillaCommented:
Based on what I see, you need the inserted values in single quotes.
0
wsenterAuthor Commented:
I corrected the quotes but getting syntax error. Can you take a look ?
cmd.CommandText = ("INSERT INTO [Test] (JPSID, Ethnicity, MaleFemale, " & _
 "Race, DOB, SubjectName, FirstName, MiddleName, LastName, Suffix, Address1, Address2, " & _
 "City, Zip, HomePhone, CellPhone, WorkPhone, RelativePhone, EMailAddress, Verify") & _
                    " VALUES('" & CurrWS.Cells(i, "A").Value & "','" & CurrWS.Cells(i, "B").Value & "', '" & _
                    CurrWS.Cells(i, "C").Value & "','" & CurrWS.Cells(i, "D").Value & "', '" & _
                    CurrWS.Cells(i, "E").Value & "','" & CurrWS.Cells(i, "F").Value & "', '" & _
                    CurrWS.Cells(i, "G").Value & "','" & CurrWS.Cells(i, "H").Value & "', '" & _
                    CurrWS.Cells(i, "I").Value & "','" & CurrWS.Cells(i, "J").Value & "', '" & _
                    CurrWS.Cells(i, "K").Value & "','" & CurrWS.Cells(i, "L").Value & "', '" & _
                    CurrWS.Cells(i, "M").Value & "','" & CurrWS.Cells(i, "N").Value & "', '" & _
                    CurrWS.Cells(i, "O").Value & "','" & CurrWS.Cells(i, "P").Value & "', '" & _
                    CurrWS.Cells(i, "Q").Value & "','" & CurrWS.Cells(i, "R").Value & "', '" & _
                    CurrWS.Cells(i, "S").Value & "','" & CurrWS.Cells(i, "T").Value & "')"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

HoggZillaCommented:
What error are you receiving. Can you print or display the SQL statement before it tries to execute. Let's see what is trying to run.
0
wsenterAuthor Commented:
I tried to use debug.print(cmd.CommandText.ToString)  No work.

Can you suggest how to ?
0
HoggZillaCommented:
Put the commandText into a variable and write the variable to a message box.
0
IUFITSCommented:
I would consider using a parameterized statement if I were you for mulitple reasons.  First, it will handle the quotes for you.  Second, it will help minimize the risk of anyone either using SQL injection techniques or simply messing the query up because of the text they include in the spreadsheet.  An added bonus is that you can prepare it and the SQL Server will cache your execution plan so it may run faster the next time you run it.  Even if you don't accept this answer, I highly recommend you do it.  :)   Further, you can reuse that command and just change the values of the parameters which is handy if you're doing a loop inserting statements for example, you don't have to rebuild the SQL each time.
I'll include a basic example that sets one up and then you can see it executes like you normally would:
 
 


Dim sql As String = "Select * From MyTableName " & _
		" Inner Join SecondTable On SecondTable.Guid = MyTableName.Id " & _
		"        Where Id = @Id And LastName = @LastName"
 
Dim cmd As New SqlCommand(sql, _connection)
cmd.Parameters.Add("@Id", SqlDbType.Int, 4)
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20)
 
cmd.Prepare()
 
cmd.Parameters("@Id").Value = Id
cmd.Parameters("@LastName").Value = "Smith"
 
Dim dr As SqlDataReader = cmd.ExecuteReader

Open in new window

0
wsenterAuthor Commented:
"Put the commandText into a variable and write the variable to a message box. "

It never would work because the error was generated within the SQL statement itself. I figured it out and fixed it myself but thanks for your input.
0
IUFITSCommented:
You mind sharing the solution for posterity in case anyone else searchs on this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.