Data Reader woes!!

[Morn]

Here we go again.

I am in a situation where I need to retrieve a primary key auto number value from a table in my Access database.

To set the scene:

Attached to a button is the code to enter personal details to the table, this causes a primary key to be auto generated.

Straight after that I want to retrieve the auto number so that I can put it in a variable and then a booking can be created for that customer.


To retrieve value i am after I have used this code:

Dim sqlsearch As String = "SELECT customerID from tblCustomer WHERE customerfirstname = firstname AND customersurname = surname AND postcode = postcode"

        'Creates a variable command so that the sql and connection variables are run
        Dim command1 As New System.Data.OleDb.OleDbCommand(sqlsearch, connect)

        connect.Open()

        MsgBox("A Connection to the Database is now open")


        connect.Close()

        MsgBox("The Connection to the Database is now Closed")

I understand that there are bits missing from this, however I can not get the data reader to work correctly. If anyone can help with the code here, I would be grateful.

Thanks in advance
Graham

 

[lingsn]

May be something like this?

[SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sqlsearch [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT customerID from tblCustomer WHERE customerfirstname = firstname AND customersurname = surname AND postcode = postcode"
[/SIZE][SIZE=2][COLOR=#008000]'Creates a variable command so that the sql and connection variables are run
[/COLOR][/SIZE][SIZE=2]connect.Open()
MsgBox("A Connection to the Database is now open")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommand(sqlsearch, connect)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rs [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataReader = command1.ExecuteReader
[/SIZE][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] rs.Read
[/SIZE][SIZE=2][COLOR=#008000]' Doing something on the data retrieve
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Loop
[/COLOR][/SIZE][SIZE=2]connect.Close()
MsgBox("The Connection to the Database is now Closed")
[/SIZE]

 

[Berry-tan]

While your VB.NET code may be sound, your approach to getting the newly-created Customer's ID is flawed. Imagine if two John Smith's in Manhattan were in your database: which one would show up?

Since you're using Microsoft Access, I'm going to assume it's a single-user system. What I would recommend is that you use this query to get the last user inserted:

Dim sqlsearch As String = "SELECT TOP 1 customerID FROM tblCustomer ORDER BY customer DESC;"

This query is also far quicker than checking the first name, last name, and postal code.

 

[cjard]

Actually, its more like the entire approach has a flaw

Typically, when you book an airplane ticket, you fill in all the details, then the system gives you a booking ref. You do not click "go" and the system gives a booking ref that it then uses to fill in all your details.

In DB terms it is this:
You collect and insert all the data the table will contain. At the point of insertion of that data, a new row is created, with an ID, and it is then that you relay that info back to the customer.

Im sure the DW2 link in my signaturewill have guidance for working with autonumber fields in access.. i.e. you set up a sql command and assign parameters to it, and after running the query, your values will be inserted into the db and also residual in the client side, will be your id..

This isnt really a situation where you would use a datareader, it's better suited to datatable work.