asked on

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

Steve Hogg

Write your SQL out to the console or to a message box. Let's see wha the string looks like.

Steve Hogg

Based on what I see, you need the inserted values in single quotes.

ASKER CERTIFIED SOLUTION

wsenter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Hogg

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.

wsenter

ASKER

I tried to use debug.print(cmd.CommandText.ToString) No work.

Can you suggest how to ?

Steve Hogg

Put the commandText into a variable and write the variable to a message box.

IUFITS

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

wsenter

ASKER

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

IUFITS

You mind sharing the solution for posterity in case anyone else searchs on this question?