Accessing Databases [2008 express edition]

[JockGitJnr]

Hi fellow developers. I have having a little problem accessing a ms access Database using VB.NET. I have got most of the bulk of the code working and I have got it working before but this time it has stumpped me.

 Imports System.Data.OleDb
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Form2.Show()
        Dim EMP As OleDb.OleDbConnection
        Dim ConnectToMyDatabase As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= N:\VB.NET\ReadingFromDatabase\ReadingFromDatabase\Resources\Data.accdb;Persist Security Info=False;"
        EMP = New OleDb.OleDbConnection(ConnectToMyDatabase)
        EMP.Open()
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        cmd = New OleDbCommand("select * from Data where ID = txtPrimKey.Text", EMP)
        dr = cmd.ExecuteReader
        While dr.Read()
            Form2.txtID.Text = dr(0)
            Form2.txtName.Text = dr(1)
            Form2.txtGender.Text = dr(2)
            Form2.txtAge.Text = dr(3)
            Form2.TxtMoney.Text = dr(4)
        End While
    End Sub
End Class

The line that the error apears on is this one.

 dr = cmd.ExecuteReader

It says that there is no value given.

Thanks in advance.

JockGitJnr

[viperbyte]

How about:

Dim dr As OleDbDataReader = cmd.ExecuteReader()

[JockGitJnr]

Thanks for the help but the same error has come up. I have worked out it is to do with this line.

 cmd = New OleDbCommand("select * from Data where ID = txtPrimKey.Text", EMP)

If i put a number instead of the txtPrimKey.Text then it will work perfectly. I have also tried using

 cmd = New OleDbCommand("select * from MP where ID = PRIM", EMP)

but that still will not work.

If there any way of getting a the string PRIM to work.

[viperbyte]

Howdy. Everything between the double quotes is read as is. It's supposed to be something like this. This is freehand so it might not compile.

cmd = New OleDbCommand("select * from Data where ID = " & txtPrimKey.Text &"," & EMP)

[JockGitJnr]

Hello guys. Thought I would give you an Update as I have finally worked it out.

 Imports System.Data.OleDb
Public Class Form2

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim KKK As Integer = CInt(Form1.txtPrimkey.Text)
        Dim EMP As OleDb.OleDbConnection
        Dim ConnectToMyDatabase As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= N:\VB.NET\ReadingFromDatabase\ReadingFromDatabase\Resources\Data.accdb;Persist Security Info=False;"
        EMP = New OleDb.OleDbConnection(ConnectToMyDatabase)
        EMP.Open()
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand("select * from MP where ID = " & KKK, EMP)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            txtID.Text = dr(0)
            txtName.Text = dr(1)
            txtGender.Text = dr(2)
            txtAge.Text = dr(3)
            TxtMoney.Text = dr(4)
        End While
    End Sub
End Class
'cmd = New OleDbCommand("select * from MP where Name = '" & txtName.Text & "'", EMP) This is for a string.

[Cimperiali]

a couple of small hints:

1)you should put connection string (as the path to a db) in some configuration file you could change at runtime (look at config file for win app in msdn). Or at least make your oledb accessible from where the exe is running.

2)even the inline queries can have parameters...

a small sample of the second hint

        Dim KKK As Integer 
       'a bit of validation of incoming data...
        if not integer.tryparse(Form1.txtPrimkey.Text,KKK) then
            messagebox.show("please, provide a numeric value for record key")
            Form1.txtPrimkey.focus()
            exit sub
        end if 

        Dim EMP As OleDb.OleDbConnection

        'the following should not be a hard coded path: you should read the
        'ConnectionString value froma config file
        Dim ConnectToMyDatabase As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= N:\VB.NET\ReadingFromDatabase\ReadingFromDatabase\Resources\Data.accdb;Persist Security Info=False;"
        EMP = New OleDb.OleDbConnection(ConnectToMyDatabase)
        EMP.Open()
        Dim cmd As OleDb.OleDbCommand

        'parameters will save you in the future
        cmd = New OleDb.OleDbCommand("select * from MP where ID = @kkk", EMP)

        cmd.Parameters.Add(New OleDb.OleDbParameter("@kkk", KKK))

        Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
        '...