SQL selecting mulitiple records

[Morn]

I am try to create a data grid that will select all users with a surname that is stored in a variable that has been entered earlier.

The code below is there to quickly check the database to see if there are any records of that surname, the next section which will compile a DataGrid is going to do the rest of the work.

I keep getting an error saying that there is a missing parameter, I have tried varying ways to make this work to no avail.

I am wondering if it could be because there are potentially more than one record in the database with the same surname?

Dim customersurname As String = surnameText.Text

'CHECK TO SEE IF A CUSTOMER EXISTS WITH THE ID ENTERED
If surnameText.Text = "" Then
MsgBox("Please enter a valid customer number!")
Exit Sub
End If

Dim connect As New OleDb.OleDbConnection
connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\Holiday.mdb"

Dim sqlcheckcustomer As String = "SELECT DISTINCT [customerID] FROM tblCustomer WHERE [surname]=" & customersurname

Dim command1 As New System.Data.OleDb.OleDbCommand(sqlcheckcustomer, connect)

command1.Connection.Open()

Dim rd As System.Data.OleDb.OleDbDataReader = command1.ExecuteReader()

If rd.HasRows Then

rd.Read()

Me.customerIDText.Text = rd.Item("customerID")

rd.Close()

Else

MessageBox.Show("The customer surname you have entered does not exist! Please enter the correct customer surname or create a new customer!", "There is a problem!", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
'END CHECK.

As always any help is greatly appreciated
Graham

 

[CygNuS]

Does the error occur on command1.ExecuteReader() ?

Then try this:

Dim sqlcheckcustomer As String = "SELECT DISTINCT [customerID] FROM tblCustomer WHERE [surname]='" & customersurname & "'"

Strings parameters have to be between single quotes (')

This might work though this way of creating an SQL statement is generally not a good idea, it's better to make use of oledbparameters instead of literal text.

 

[Morn]

That did it, i appreciate that.

Thanks

 

[cjard]

For modern, efficient, secure ways of interacting with databases, consider reading the DW2 link in my signature