richard_gar
asked on
SQL Query Problems
How can I change the below code to SQL Parameters? (I think this is what I need)
I have a problem where if lvitem.text has a ' in the text then it will not insert into my SQL table.
Example:
'Richard's here' - Will not insert because of the '
'Richards here' - Will insert as there is no '
I need to be able to insert into the field reqardless of the string.
I have a problem where if lvitem.text has a ' in the text then it will not insert into my SQL table.
Example:
'Richard's here' - Will not insert because of the '
'Richards here' - Will insert as there is no '
I need to be able to insert into the field reqardless of the string.
For Each lvItem As ListViewItem In ListView1.Items
Dim query2 As String = "INSERT INTO tblOrderLines(custref,orderref, Barcode, Product, price) values ( " & _
"'" & strCustRef & "','" & strOrderRef & "','" & lvItem.Text & "','" & lvItem.SubItems(1).Text.ToString & "','" & lvItem.SubItems(2).Text.ToString & "')"
Dim result2 As Integer = New SqlCommand(query2, con).ExecuteNonQuery
MessageBox.Show(query2)
Next
Why don't you use parameters ? It's so much easy and clear that way!
ASKER
Yeah jpaulino I would like too but never done it before so would like a little help
Here is a simple example that I have copy/paste from another question. You just need to adapt, since the idea is the same.
Dim SQL As String = "UPDATE myTable SET myField = @value1 WHERE ID = @value2"
Using command As New SqlCommand(SQL, conn)
' Then the parameters
command.Parameters.Add("@value1", SqlDbType.VarChar).Value = "abc"
command.Parameters.Add("@value2", SqlDbType.Int).Value = 123
Dim result As Integer = command.ExecuteNonQuery()
' ...
End Using
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cheers, that worked.
I have another problem with doing a bulk insert with a CSV file. It is exactly the same as the problem above where if I have a ' in the field it errors.
Dim query1 As String = "BULK INSERT tblProducts FROM '" & App_Path() & "plu.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"
Dim result1 As Integer = New SqlCommand(query1, con).ExecuteNonQuery
I have another problem with doing a bulk insert with a CSV file. It is exactly the same as the problem above where if I have a ' in the field it errors.
Dim query1 As String = "BULK INSERT tblProducts FROM '" & App_Path() & "plu.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"
Dim result1 As Integer = New SqlCommand(query1, con).ExecuteNonQuery
look at code below
Open in new window