Using SQL in VB.Net connecting to Access Database

Question

I'm using Access as a backing data store (I know why not use MySQL right?). Anyway, getting over that, I want to use SQL to search for a booking date on my database. I can get it to look up todays date but I'd like some way of typing in a date and then finding results based on this. My code so far is this:

Public Class Bookings
    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String


    Private Sub Bookings_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dbProvider = "PROVIDER=Microsoft.Jet.OleDB.4.0;"
        dbSource = "Data Source = C:\Users\wm\Desktop\MAdams\Karting2000DB.mdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()

        sql = "SELECT * FROM tblBookings WHERE BookingDate = txtDate.Text"

        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")

        MsgBox("Database is now open")

        con.Close()

        MsgBox("Database is now closed")

        txtBookingNumber.Text = ds.Tables("AddressBook").Rows(0).Item("ID")
        txtCustID.Text = ds.Tables("AddressBook").Rows(0).Item("CustomerID")
    End Sub     
End Class

I'm pretty new to this so simpler the better!

Many thanks

the dbConnection object and DataAdpater should not be reused. Create and dispose of them as needed to release the resources they use. That is also a bad way to specify the DBfile location. Use Path.Combine and Environment.GetFolderPath — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Mar 4, 2015 at 14:35

Steve · Accepted Answer · 2015-03-04 15:09:00Z

2

You can't embed the text property of your txtDate textbox inside the sql string and hope that it is translated to the correct value.

You need to use a parameterized query

sql = "SELECT * FROM tblBookings WHERE BookingDate = @date"
da = New OleDb.OleDbDataAdapter(sql, con)
da.SelectCommand.Parameters.Add("@date", OleDbType.Date).Value = Convert.ToDateTime(txtDate.Text)
da.Fill(ds, "AddressBook")

This approach requires that your user types a valid date inside the textbox. If you are unsure if the date is correct then you need to use a TryParse method to extract a valid datetime object from your text

Dim bookDate as DateTime
if Not DateTime.TryParse(txtDate.Text, bookDate) then
    MessageBox.Show("Not a valid date")
else
    ..... the code above with the bookDate passed as parameter value.

As @Plutonix said in its comment there are a lot of best practices that need to be applied to your code

Do not implement global variables for the connection, adapter and other managed classes. They need to be fred immediately after usage and rebuilt when you need them again
Objects like the connection, adapters and eventually the DataReader implements the disposable interface. This means that you should enclose them in the Using statement to destroy them when you don't need them anymore
Do not hard code the connectionstring in your methods. If you want to distribute your application you need to change that string everywhere (unless your target pc has the same path) Use the app.Config (web.Config) and the ConfigurationManager class to retrieve it

edited Mar 4, 2015 at 15:09

answered Mar 4, 2015 at 14:14

Steve

217k22 gold badges242 silver badges296 bronze badges

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

6 Comments

Paul Williams Over a year ago

Fab thanks I have it working now. I know its no perfect and it needs tidying up but I will make the changes you have suggested.

Paul Williams Over a year ago

Also, if I wanted to search for a surname from a text box on a customer form what would the sql be? I'm really new to all this so any help is massively appreciated

Steve Over a year ago

It is just a matter to change the WHERE condition. Where is followed by a field name, an operator and a value. In your case, the field name is the surname field as spelled in the database table, the operator could be the = sign or LIKE and the value is a different placeholder for a parameter like you have now for the date field. See here introductory info on the WHERE clause

Paul Williams Over a year ago

Really sorry, as I said I'm new to this. As it is I've got the Surname search working, thanks for the help. Here is what I did:sql = "SELECT * FROM tblContacts WHERE Surname = @Surname" da = New OleDb.OleDbDataAdapter(sql, con) da.SelectCommand.Parameters.AddWithValue("@Surname", txtSurname.Text) da.Fill(ds, "AddressBook")

Paul Williams Over a year ago

Thanks Steve, your answer has been ticked.

|

Collectives™ on Stack Overflow

Using SQL in VB.Net connecting to Access Database

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related