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

Open in new window

richard_garAsked:
Who is Participating?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
I have used VarChar for all (SqlDbType.VarChar) but change to the correct datatypes.

I don't have VS right now, but should be something like this:

       Dim SQL As String = "INSERT INTO tblOrderLines(custref,orderref, Barcode, Product, price) values (@custref,@orderref, @Barcode, @Product, @price)" 
       Using command As New SqlCommand(SQL, con)  
                  command.Parameters.Add("@custref", SqlDbType.VarChar).Value = strCustRef
                  command.Parameters.Add("@orderref", SqlDbType.VarChar).Value = strOrderRef 
                  command.Parameters.Add("@Barcode", SqlDbType.VarChar).Value = lvItem.Text ' you don't need to replace symbols this way
                  command.Parameters.Add("@Product", SqlDbType.VarChar).Value = lvItem.SubItems(1).Text.ToString 
                  command.Parameters.Add("@price", SqlDbType.VarChar).Value = lvItem.SubItems(2).Text.ToString

                   Dim result As Integer = command.ExecuteNonQuery()  
                    ' ... 
         
       End Using

Open in new window

0
 
Reza RadCommented:
replace every  (  '  ) in your string with two quotation:  (  ''  )
look at code below

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.Replace("'","''") & "','" & lvItem.SubItems(1).Text.ToString & "','" & lvItem.SubItems(2).Text.ToString & "')"
            Dim result2 As Integer = New SqlCommand(query2, con).ExecuteNonQuery
            MessageBox.Show(query2)
        Next

Open in new window

0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Why don't you use parameters ? It's so much easy and clear that way!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
richard_garAuthor Commented:
Yeah jpaulino I would like too but never done it before so would like a little help
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
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

Open in new window

0
 
richard_garAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.