strings with commas

[mkohl]

Hello,

Is it possible to update a sql table with fields that have commas in it?

I am writing a program that takes an excel sheet, and then converts it and uploads it to the sql server. The problem that I am having is that many of these excel sheets come with fields that have commas in them. For example product descriptions usually have a word or two with a comma in it. I previously searched for commas and took them out, but I feel this is not the correct thing to do. Also, after completing this program, I will be passing it to other employee's so they can upload the tables for me. So, if there is commas in the fields the program will error out.

Is there a way format a string so the comma's will not be a problem when updating a sql table?



-Mike

 

[ThatRickGuy]

I think you can if you put the field name in square brackets [field,name] but I've never tried it before.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[mkohl]

maybe I should explain more:

example:

qy = "insert into wipers(description)values('piaa's wipers are good')

the comma between the "a" and "s" is whats causing the error. I was hoping that there was some type of escape character I could use to inform .net that I wanted the comma in my string.

-mike

 

[Ruffnekk]

You could choose to double the single quote to indicate it's an enclosed single quote. (it's not a comma by the way)

Example of how to achieve this:

qy = qy.Replace("'","''")

But the really correct way to solve this is to use parametrized query's. This also solves other possible issues regarding SQL Injection Attacks. Just look up SqlParameter or OleDbParameter classes in your VS help files for example code of how to use parameters in a SQL query.

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[ThatRickGuy]

better yet would be to use parameters.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Ruffnekk]

Please read my *whole* post Rick

Regards, Ruffnekk
---
Is it my imagination or do buffalo wings taste just like chicken?

 

[mkohl]

Thanks for the post guys. Until now I have been avoiding using paramaters(it just seem so easy to do it the way I was doing it), but I guess its time to actually do it the correct way.


Thanks again,
-mike

 

[ThatRickGuy]

LOL sorry Ruffnekk, I read goods

Mike, Parameters aren't all that scary once you've run through them a few times. There are tons of samples both on the forum here and out in google land.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[mkohl]

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

 

[chiph]

mkohl -
You'll find that your code will run slightly faster when using parameterized queries. This is because the database engine is able to cache your query so it can reuse it later (saves it time for checking your query for syntax, object existance, etc).

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[mkohl]

I was hoping I wouldn't be back here trying to solve my problem, but I'm stuck once again. I have a problem with my paramaters.

Situation:
I have two datagrids:
Datagrid1 - populates all the information from an excel file or access table.

Datagrid2 - populates column names from datagrid1. This is used to generate column names from datagrid1 so I can create a sql table having the same columns as the excel file(datagrid1 column names)


In datagrid2 I give the user a choice of selecting or deselecting columns that are populated in datagrid2. (user checks a checkbox in the datagrid if they want to include a column)

Problem:
The problem is my paramators are not inserting the formation into the correct columns "if the user does not import one of the columns" in datagrid2. example:

Datagrid2 column names
Product ID
price
description

if I insert all columns into the sql table using parameters it works fine, but if the user de-selects a column (ex. "product ID") then updates that table. The product ID information is inserted into the price column, but keep in mind that product ID should not be uploading into the database (it was deselected).

Code snippets:

'query statement
insert into test([RETAIL PRICE],[ONLINE PRICE],NAME,DESCRIPTION,[LONG DESCRIPTION],[WIPER STYLE],WEIGHT,LENGTH)values(@RETAILPRICE,@ONLINEPRICE,@NAME,@DESCRIPTION,@LONGDESCRIPTION,@WIPERSTYLE,@WEIGHT,@LENGTH)

'add parameter (will just show the first one to keep it short)
comm.Parameters.Add(paramTemp, SqlDbType.VarChar, size, name)

'values I get during debug are
comm.Parameters.Add(@retailprice, SqlDbType.VarChar, 5, [Retail Price])

In the dataset(where i am pulling row information from) the first column is not "retail price" it is "product Id" but if I do not want to import that column when creating and updating a sql table, then product ID information from the dataset is uploaded onto the server in "retial price" column.

If you need more code examples I posted some of my code for this routine in a previous post.

-mike