How can I add the value of a single Cell from Access database to a string variable

Question

As the title says I have a MS Access database from where I need to find a specific dataset determined by a String Value. the reason for having to do this is so I can find the value of a single cell in this datase which has to be used as a path to find a certain file. my approach so far is the following:

 Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ExaptLokal.accdb")
Dim cmd As New OleDb.OleDbCommand
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String
 sql = "SELECT NC_KEY FROM EXAPT_NC_KOPF_DATEN WHERE NC_PROGRAMM_NAME =" & ProgrammNr.Text.ToString
        MsgBox(sql)
        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        fullpath = dt.ToString

at the end I would like to have the result from my SQL Query as the value of my "fullpath" variable but so far the da.Fill(dt) row is giving me a hard time saying there is a conflict with the datatype.

Is the datatable even needed in this case or might I be able to skip that step and get the result of the query directly in the fullpath variable?

Thanks to everyone in advance

Edit: Thanks for the help (though not the friendliest but who am I to judge) I finally got it to work with the Execute Scalar method. I would just wish newbies to this website would be greeted a little better lol have a great day

Answer 1

I moved the connection string to a class level variable so you can use it in other methods.

I separated your data access code from your user interface code passing the value from the text box to a function that returns the path.

I changed your select statement to use parameters. Always use parameters to avoid sql injection and avoid errors.

Use Using...End Using blocks to ensure that your database objects are closed and disposed. This Using block covers both the command and the connection.

You can pass the .CommandText and the .Connection directly to the constructor of the command.

When adding parameters to the parameters collection you provide the parameter name, the datatype form the database, and the size of the field. I had to guess at the type and size so, check your database for the actual values.

Since you are expecting a single value you can use .ExecuteScalar.

Private ConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ExaptLokal.accdb"

Private Function GetPath(ProgrammNr As String) As String
    Dim fullpath As String
    Dim sql = "SELECT NC_KEY FROM EXAPT_NC_KOPF_DATEN WHERE NC_PROGRAMM_NAME = @ProgrmmNr"
    Using conn As New OleDb.OleDbConnection(ConStr),
            cmd As New OleDb.OleDbCommand(sql, conn)
        cmd.Parameters.Add("@ProgrmmmNr", OleDbType.VarChar, 100).Value = ProgrammNr
        conn.Open()
        fullpath = cmd.ExecuteScalar.ToString
    End Using
    Return fullpath
End Function

Usage...

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim fullpath = GetPath(ProgrammNr.Text)
End Sub