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

Assuming that your query always returns a row. To retrieve the value you could do it this way: `fullpath = dt.Rows(0)("NC_KEY")`. If not, you could check if rows were returned by If dt.Rows.Count <> 0 Then ... — F0r3v3r-A-N00b, Jul 01 '20 at 08:58
If you had done the research you should have before posting this question, you'd know that the way to get a single value from a database is to call `ExecuteScalar` on a command object. Even if you hadn't done that research, you should at least know how to get a value out of a `DataTable`. This site is supposed to be for stuff that you can work out despite doing the research, not as a substitute for that research. You'll also find that, unless `NC_PROGRAMM_NAME` has a numeric data type, your SQL code will generate a syntax error. learn to use parameters in queries. — jmcilhinney, Jul 01 '20 at 09:13
@jmcilhinney thanks for the info about the executeScalar I'll try to look into that more. and btw I have fondled with this problem for approx. 4 hours now with no conclusion and was getting desperate which is why I asked here. Also I only started working with vb.net a week ago so I still have a lot to learn and don't even really know the basics — FilipFilop, Jul 01 '20 at 09:18

score 0 · Accepted Answer · answered Jul 01 '20 at 18:16

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

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

1 Answers1