Cannot connect to database error

Question

As a new user to .Net programming I am trying to understand why this connection is failing. The catch exception is run to inform me of such. Can someone please point out what is wrong in my code. many thanks

Imports System.Data.OleDb

Public Class DbTest

    Public buttonName As String

    'Dim con1 As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\storage2.accdb")

    Private Sub racksfrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        lblRacks.Text = buttonName

        Dim connetionString As String
        Dim oledbCnn As OleDbConnection
        Dim oledbCmd As OleDbCommand
        Dim sql As String

        connetionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\storage2.accdb"
        sql = "SELECT [Rack_code] FROM Racks Where [Rack_code] = '" & buttonName & "'"
            oledbCnn = New OleDbConnection(connetionString)
        Try
            oledbCnn.Open()
            oledbCmd = New OleDbCommand(sql, oledbCnn)
            Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()
            While oledbReader.Read
                MsgBox(oledbReader.Item(0) & "  -  " & oledbReader.Item(1) & "  -  " & oledbReader.Item(2))
            End While
            oledbReader.Close()
            oledbCmd.Dispose()
            oledbCnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try


    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub



End Class

Answer 1

The error message means that your query contains a name that is not recognized as the name of a field or the name of the table. Thus, that name, is treated as a parameter but the OleDbCommand has no parameter defined thus the error message.
In your query you have only one field and is named Rack_Code while the table is named Racks.
You should check if these are really the names of your objects.

However your code has a couple of other problems.
You query the database asking for the value of Rack_Code but you already know that value because the WHERE clause is WHERE Rack_Code = ....

Finally, if that query succeed to run, you will have another problem because you have asked for only one field (the Rack_Code) but in the loop you try to show the values from three fields (Item(0), Item(1) and Item(2), but two items are missing from the SELECT field list.

EDIT Following your comment under the question. If you have a space between the word Rack and the word Code, then you should use that field name keeping the space and not substitute it with an underscore

sql = "SELECT [Rack code] FROM Racks Where [Rack code] = ....."

So, I would change your code to this one

Try
    sql = "SELECT [Rack Code], SecondFieldName, ThirdFieldName " & _ 
           "FROM Racks Where [Rack Code] = ?"
    Using oledbCnn = New OleDbConnection(connetionString)
    Using oledbCmd = New OleDbCommand(sql, oledbCnn)
        oledbCnn.Open()
        oledbCmd.Parameters.AddWithValue("@p1", Convert.ToInt32(buttonname))
        Using oledbReader = oledbCmd.ExecuteReader()
            if oledbReader.Read() Then
                txtCols.Text = oledbReader.Item(0).ToString() & _ 
                               "  -  " & oledbReader.Item(1).ToString() & _ 
                               "  -  " & oledbReader.Item(2).ToString())
            Else
                txtCols.Text = "No records found for the Rack Code used"
            End If
        End Using
    End Using
    End Using
Catch ex As Exception
    MsgBox("Problems executing command: " & ex.Message)
End Try

I have changed your query to use a parameterized approach, of course, I suppose that the variable buttonname is correctly initialized for some actual Rack Code value

Answer 2

Within your source there are a few points I would like you to check.

1. You do not set buttonName, or we can't see it. so it may be empty and your recordSet will probably be empty
2. You select only one field out of your table (see: SELECT [Rack_code] FROM Racks ...) but you try to access more than one field (see source below)
3. Anytime you catch an exception you should log/print/trace exception message. (see MSDN)

--

While oledbReader.Read
      ' Item(1) ?? Item(2) ??? try removing those and just select Item(0)
      MsgBox(oledbReader.Item(0) & "  -  " & oledbReader.Item(1) & "  -  " & oledbReader.Item(2))
End While

You can find some good examples how to query databases with oledb in MSDN - OleDbDataReader or Read-Method example

Answer 3

As explained in the comments, your WHERE parameter seems not to be set:

Public buttonName As String

Private Sub racksfrm_Load(ByVal sender As System.Object,... Handles MyBase.Load

lblRacks.Text = buttonName

No where does buttonName take on a value. Set this to some default value when initialized:

Public buttonName As String = "foo"

Second, your query is meaningless and wrong. If you know the Rack_Code (the WHERE) there is no reason for it to also be the SELECT column. It is wrong because in just a few lines, your code will be looking for THREE columns in the result:

 MsgBox(oledbReader.Item(0) & "  -  " & oledbReader.Item(1) & "  -  " & _
        oledbReader.Item(2))

Your query should be more like: SELECT X, Y, Z FROM Racks WHERE Rack_Code ='" & buttonName & "'"