getting variables into sql string from list box

Question

I am having a problem trying to get the contents of a list box into and SQL string via a variable(moon) Here are 3 SELECT strings from the main body of code below.The last two strings work fine but the first one doesn't.That's the one where I try and place the variable into the code I have tried a few variations on the code but nothing seems to work.Does anybody have any suggestions. THE SQL STRINGS:

            da = New OleDbDataAdapter("SELECT * FROM books WHERE [author] = '" & moon     "' ", myConnection) 'fails
           da = New OleDbDataAdapter("SELECT * FROM books", myConnection) 'works
           da = New OleDbDataAdapter("SELECT * FROM books WHERE author = 'molly brown' ", myConnection) 'works{

MAIN CODE BODY

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Data.DataTable

Public Class Form1


    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection
    Dim ds As DataSet = New DataSet
    Dim da As OleDbDataAdapter
    Dim tables As DataTableCollection = ds.Tables
    Dim source1 As New BindingSource()



    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim moon As String
        moon = ListBox1.Text

        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        dataFile = "C:\Documents and Settings\james\Desktop\Authors.accdb" ' change to access database location on your computer
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        da = New OleDbDataAdapter("SELECT * FROM books WHERE [author] = '" & moon & "' ", myConnection) 'fails
        'da = New OleDbDataAdapter("SELECT * FROM books", myConnection) 'works
        'da = New OleDbDataAdapter("SELECT * FROM books WHERE author = 'molly brown' ", myConnection) 'works

        da.Fill(ds, "books")

        ' replace "items" with the name of the table
        ' replace [Item Code], [Description], [Price] with the columns headers

        Dim view1 As New DataView(tables(0))
        source1.DataSource = view1
        DataGridView1.DataSource = view1
        DataGridView1.Refresh()

    End Sub


End Class

Please tell us what is not working Do you have an error message? Just saying doesn't work is not helpful. — Steve
– Steve, Commented Apr 21, 2014 at 20:47
Rather than concatenate your string in your OleDbAdapter, create a sql variable and concatenate it there. Then you can look at what sql you are actually creating and it will probably be pretty obvious. — crthompson
– crthompson, Commented Apr 21, 2014 at 20:48
when the form loads the listbox is not likely to have an item selected so moon will be an empty string — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Apr 21, 2014 at 21:10
Hi Plutonix, Thanks for your reply.The idea is to choose from the listbox once the form is loaded with the intention of it finding its way into the variable in the sql string...well thats the theory :) :) :) — user3480507
– user3480507, Commented Apr 22, 2014 at 19:26

Joel Coehoorn · Accepted Answer · 2014-04-22 19:25:22Z

1

Best practice is to use a new connection object for each call to the database, define objects with the smallest scope possible, and to use parameterized queries instead of substituting the value into your sql string.

Under no circumstances should you ever use string manipulation to put a user-selected value into your sql statement! Code like this is very bad:

da = New OleDbDataAdapter("SELECT * FROM books WHERE [author] = '" & moon  & "' ", myConnection)

Imagine what would happen in this example if you have an author like "Patrick O'Neil". There are many ways this problem can be further abused to cause real damage to your database, application, and users. Just don't use string concatenation for this.

Do it like this instead:

Public Class Form1

    Private Const provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    Private Const dataFile As String = "C:\Documents and Settings\james\Desktop\Authors.accdb" ' change to access database location on your computer
    Private connString As String = provider & dataFile      

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet()
        'Set a special placeholder for your value as part of a *constant* sql statement
        Dim sql As String = "SELECT * FROM books WHERE [author] = ? "

        Using cn As New OleDbConnection(connString), _
              cmd As New OleDbCommand(sql, cn), _
              da As New OleDbDataAdapter(cmd)

            'Set the value for that placeholder via a query parameter
            'Parameters work best when you set the actual type and length 
            ' to match your database. I had to guess at the length here.
            cmd.Parameters.Add("?", OleDbType.NVarChar, 50).Value = Listbox1.Text
            da.Fill(ds, "books")
        End Using

        DataGridView1.DataSource = ds.Tables("books")
        DataGridView1.Refresh()   
    End Sub   

End Class

edited Apr 22, 2014 at 19:25

answered Apr 21, 2014 at 21:13

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3480507 Over a year ago

Hi Joel Thankyou for that reply.Parameterised queries.Hmmm.Never heard of them.I am trying to work out the logic in your code but I am baffled(newbie)can you point me to any good tutorials/books on the subject.Mant thanks John James

Joel Coehoorn Over a year ago

Nothing I wouldn't have to google. Now that you know what to search on, you can do that as well as I.

Collectives™ on Stack Overflow

getting variables into sql string from list box

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related