Follow up:
I did get it to work using paramaters. My code is kind of tricky since each excel file or microsoft access file is different, but here is some code if someone has the same issue:
Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Dim conn As SqlConnection 'sql connection
Dim comm As SqlCommand 'sql command
Dim qy As String 'sql querry
Dim cs As String 'sql connection string
Dim i As Integer
Dim ii As Integer
Dim int As Integer
Dim icount As Integer 'holds the number of columns in the datagrid
Dim x As Integer
Dim mytable As String
Dim temp As String
Dim aTemp(0) As String
Dim itemp As Integer
Dim row As DataRow
Dim sParam(0) As String
Dim iParam As Integer
Dim rowcount As Integer
Dim pvalue As Double
Dim svalue As Double
statusbar.Panels(0).Text = "Processing:"
'unhides the progress bar
statusbar.Panels(1).MinWidth = 0
statusbar.Panels(1).Width = 100
statusbar.Refresh()
statusbar.progressbar.Value = 0
rowcount = ds.Tables(0).Rows.Count
pvalue = 100 / rowcount
'connection string
cs = "Initial catalog=" + frmGenerator.databasename + "; data source =" + frmGenerator.server
cs = cs + ";user id=" + frmGenerator.loginid + ";password =" + frmGenerator.password
'user inputs the table they would like to upload
mytable = InputBox("enter table name that you would like to insert the information into", "TableGenerator")
'view serverinfo - user must type in password
frmConn = New ServerConnection
frmConn.ShowDialog()
Try
int = dt.Rows.Count - 1
For Each row In ds.Tables(0).Rows
qy = "insert into " + mytable + "("
For i = 0 To dt.Rows.Count - 2
If i = dt.Rows.Count - 2 Then
If Datagrid2.Item(int, 0) = False Then
temp = Datagrid2.Item(i, 1)
sParam(iParam) = temp
iParam += 1
ReDim Preserve sParam(iParam)
aTemp = temp.Split(" ")
itemp = aTemp.GetUpperBound(0)
If itemp > 0 Then
temp = "[" + temp + "]"
End If
qy = qy + temp + ")"
Exit For
End If
End If
If Datagrid2.Item(i, 0) = True Then
'check to see if name has a space in it
temp = Datagrid2.Item(i, 1)
sParam(iParam) = temp
iParam += 1
ReDim Preserve sParam(iParam)
aTemp = temp.Split(" ")
itemp = aTemp.GetUpperBound(0)
If itemp > 0 Then
temp = "[" + temp + "]"
End If
qy = qy + temp + ","
End If
Next
If Datagrid2.Item(int, 0) = True Then
'check to see if name has a space in it
temp = Datagrid2.Item(i, 1)
sParam(iParam) = temp
iParam += 1
ReDim Preserve sParam(iParam)
aTemp = temp.Split(" ")
itemp = aTemp.GetUpperBound(0)
If itemp > 0 Then
temp = "[" + temp + "]"
End If
qy = qy + temp + ")values("
End If
Dim paramTemp As String
For icount = 0 To iParam - 2
paramTemp = GetParamater(sParam(icount))
qy = qy + "@" + paramTemp + ","
Next
paramTemp = GetParamater(sParam(icount))
qy = qy + "@" + paramTemp + ")"
conn = New SqlConnection(cs)
conn.Open()
comm = New SqlCommand(qy, conn)
Dim size As String
Dim sType As String
Dim name As String
For icount = 0 To iParam - 1
size = Datagrid2.Item(icount, 3)
name = "[" + Datagrid2.Item(icount, 1) + "]"
paramTemp = "@" + GetParamater(sParam(icount))
sType = Datagrid2.Item(icount, 2)
Select Case sType
Case "varchar"
comm.Parameters.Add(paramTemp, SqlDbType.VarChar, size, name)
Case "char"
comm.Parameters.Add(paramTemp, SqlDbType.Char, size, name)
Case "int"
comm.Parameters.Add(paramTemp, SqlDbType.Int, size, name)
End Select
comm.Parameters(paramTemp).Value = row.Item(icount)
Next
comm.ExecuteNonQuery()
conn.Close()
ii += 1
iParam = 0
svalue = svalue + pvalue
statusbar.progressbar.Value = svalue
Next
'add info to lstprocessing
lstProcessing.Items.Add("updated sql table: " + mytable)
statusbar.Panels(0).Text = "Ready"
'hides the progress bar
statusbar.progressbar.Value = 0
statusbar.progressbar.Hide()
statusbar.Panels(1).MinWidth = 0
statusbar.Panels(1).Width = 1
statusbar.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
Finally
conn = Nothing
comm = Nothing
qy = Nothing
cs = Nothing
icount = Nothing
i = Nothing
x = Nothing
End Try
End Sub
![[monkey]](/data/assets/smilies/monkey.gif "[monkey] [monkey]")
