Visual Basic 2008 New Search Query

Question

I'm trying to do a search through an access database I added to a project but I get this error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll" Additional information: No value given for one or more required parameters.

The idea was to search the database for text entered into a textbox, then display the information on that row within more text boxes.

The code dr = cmd.ExecuteReader is also highlighted as an issue when debugging. I'm using visual basic 2008, and quite new to the whole coding scene so explanations as to why the issue has occurred would be appreciated!

Imports System.Windows.Forms
Imports System.Data.OleDb
Public Class frmSearch
Public con As OleDbConnection = New OleDbConnection
Public dr As OleDbDataReader

Dim dbProvider As String
Dim dbSource As String

Dim BillingSystemFolder As String
Dim TheDatabase As String
Dim FullDatabasePath As String



Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'BillingdatabaseDataSet.BillingInfo' table. You can move, or remove it, as needed.
    Me.BillingInfoTableAdapter.Fill(Me.BillingdatabaseDataSet.BillingInfo)

    dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    'Setup the provider

    TheDatabase = "/billingdatabase.accdb"
    BillingSystemFolder = Application.StartupPath
    FullDatabasePath = BillingSystemFolder & TheDatabase
    'Set the database and the location of it

    dbSource = "Data Source = " & FullDatabasePath
    'Set the data source

    con.ConnectionString = dbProvider & dbSource
    'Set the connection string

End Sub

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    con.Open()
    txtJobNum.Clear()
    txtName.Clear()
    txtSurname.Clear()
    Dim str As String
    str = "SELECT * FROM BillingInfo WHERE (Code = " & CodeText.Text & ")"
    Dim cmd As OleDbCommand = New OleDbCommand(str, con)
    dr = cmd.ExecuteReader
    While dr.Read()
        txtSurname.Text = dr("Surname").ToString
        txtName.Text = dr("First Name").ToString
        txtJobID.Text = dr("Customer ID").ToString
    End While
    con.Close()
End Sub

End Class

Dont concatente to make SQL queries. Use SQL Parameters ([example](http://stackoverflow.com/a/29187199/1070452)) they solve many problems. `the whole coding scene` its a scene? Here, I thought it was a profession — Ňɏssa Pøngjǣrdenlarp, Jan 31 '16 at 22:33

score 2 · Answer 1 · edited May 23 '17 at 10:28

Probably the field Code is a text field. In this case when you want to search using a particular value for that field you should enclose the value between single quotes.

Something like this

str = "SELECT * FROM BillingInfo WHERE (Code = '" & CodeText.Text & "')"

However this is really a bad practice because this allows to create an Sql Injection attack or it will simply fail because your value contains a single quote.

The correct method is using a parameterized query like this

str = "SELECT * FROM BillingInfo WHERE (Code = @p1)"
Dim cmd As OleDbCommand = New OleDbCommand(str, con)
cmd.Parameters.Add("@p1", OleDbType.VarWChar).Value =  CodeText.Text 
dr = cmd.ExecuteReader

Visual Basic 2008 New Search Query

1 Answers1