Syntax Error in query

[ladivito]

Hi,

i had some error when retrieving data from database when the symbol is inside a name. as in picture

my code are as below :

 Private Sub enamecombo_SelectedIndexChanged(sender As Object, e As EventArgs) Handles enamecombo.SelectedIndexChanged


        Try
            con2.ResetState()
            con2.Dispose()
            eictextbox.Clear()
            ds2.Clear()
            ds2.Dispose()


            dbProvider2 = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
            dbSource2 = "Data Source = C:\Users\Paul\Desktop\SkyPOS\SkyPOS\empdb.accdb"


            con2.ConnectionString = dbProvider2 & dbSource2


            con2.Open()
            sql2 = "Select * from tblemp where ename = '" & enamecombo.Text & "' "
            da2 = New OleDb.OleDbDataAdapter(sql2, con2)
            da2.Fill(ds2, "tblemp")


            Dim command2 As New System.Data.OleDb.OleDbCommand(sql2, con2)
            Dim reader2 As System.Data.OleDb.OleDbDataReader = command2.ExecuteReader()




            While reader2.Read()
                '  eictextbox.Text = reader2.Item("eic").ToString()
                eictextbox.Text = ds2.Tables("tblemp").Rows(0).Item("eic").ToString()
            End While


            con2.Close()
            con2.Dispose()
            ds2.Dispose()
            da2.Dispose()
            reader2.Close()
            con2.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try


    End Sub

 

[jmcilhinney]

You should pretty much never use string concatenation to insert values into SQL code and this is one of the reasons why. Always use parameters. To learn how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

 

[ladivito]

Im new to .net and im not really understand where is the error of my code.
i saw ur blog & there is lots of sql inside, my sql only 1 line and to get 1 item from the database only and....so im really no idea

please help to point out where is the problem. Thank you

 

[jmcilhinney]

Read the blog post and you will know why the issue occurs and how to avoid it. The point here is to learn something and avoid similar issues in the future.

 

[ladivito]

i just what to solve the error bring from the ' and not more secure code...etc
i put a string.format at the sql but not working...

 

[CharlieMay]

I'm going to go ahead and explain why this is a problem because at the very least, it should get you thinking

Let's say the user selects Ty'ing in your combobox

So you build a String for your SQL Statement and Inject the combobox selection into it.

sql2 = "Select * from tblemp where ename = '" & enamecombo.Text & "' "

If you do a MsgBox(sql2) you will see
Select * from tblemp where ename = 'Ty'ing'

Now since we wrap text fields with apostrophes ' to denote them as such, then the interpreter that takes in that string becomes confused because you have an extra '

So read through the blog post that has been suggested. It not only is the solution to your problem it ALSO makes your statements more secure.

 

[jmcilhinney]

i just what to solve the error bring from the ' and not more secure code...etc
i put a string.format at the sql but not working...

Why would you use String.Format when my blog post specifically says not to? It seems to me that you're not prepared to make the effort to read something that would require you to think a bit. People like me write blogs like that so that we don't have to keep repeating the same advice over and over. Apparently your time is far more important than ours though, so we should keep solving your problems for you rather than provide resources so that you can actually learn.

 

[ladivito]

Thanks CharlieMay for your explaination...

problem solved with adding .replace as below
sql2 = "Select * from tblemp where ename = '" & enamecombo.Text.Replace("'", "''") & "' "

 

[jmcilhinney]

Thanks CharlieMay for your explaination...

problem solved with adding .replace as below
sql2 = "Select * from tblemp where ename = '" & enamecombo.Text.Replace("'", "''") & "' "

And that is still a bad solution but if a few minutes reading to find the proper solution is too much trouble then you get what you deserve.