problem joining two tables

[straight_edge]

Hi

I have a problem when joing two tables from an access database

I have two tables 'ServiceUser' and 'SUAttendance' and I need to join them to create a timetable, the tables are joined over SUID and I wish to display the names of the service users attending on a perticular day.

The code below should display in a textbox all of the first names of the service users who attend Mondays AM but it returns an error highlighting
'da.Fill(ds, "DayCareDatabase")' and saying 'No value given for one or more required parameters'.

If anyone can help I would be very grateful

Listing below is my code

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = N:\Yr3\Indv Study\DayCareDatabase.mdb"
con.Open()

sql = "SELECT SU_first_name FROM ServiceUser, SUAttendance WHERE ServiceUser.SUID = SUAttendance.SUID AND Day = 'Mon' and AM = True"

da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "DayCareDatabase")
con.Close()

txtMonAM.Text = ds.Tables("DayCareDatabase").Rows(0).Item(0)

 

[cjard]

The dataadapter is capable of opening and closing the connection itself..

Immediately before you call Fill, put the line:

MessageBox.Show("params col cnt = " & cmd.Parameters.Count)


What does it say?

 

[straight_edge]

Problem solved, there was a syntax error in the sql statement, if the error message had been more helpful I would have seen it earlier

 

[cjard]

I checked that.. and it looked fine to me.. What was the syntax error?

 

[Arg81]

Was it the fact you hadn't included brackets on your "where" clause?

WHERE ServiceUser.SUID = SUAttendance.SUID AND Day = 'Mon' and AM = True" should be
WHERE (ServiceUser.SUID = SUAttendance.SUID) AND (Day = 'Mon' and AM = True")

because it would get confused on the "and" between 'Mon' and AM...

 

[straight_edge]

In my tables I had used SU_ID and in my join I was using SUID, I was looking for a problem in the dataset because of the error message

 

[cjard]

Was it the fact you hadn't included brackets on your "where" clause?

WHERE ServiceUser.SUID = SUAttendance.SUID AND Day = 'Mon' and AM = True" should be
WHERE (ServiceUser.SUID = SUAttendance.SUID) AND (Day = 'Mon' and AM = True")

because it would get confused on the "and" between 'Mon' and AM...

It doesnt get confused on ANDs.. there is no bitwise logical combination in Access AFAICR.. The designer puts () in willy nilly to be explicit. Nost of the time they arent needed, but in cases of ORring, they are. It is easier to code the system to just put extra unnecessary brackets in than to work out where they are and are not needed.

I asked mainly because I was curious to see if it was an error that we should have picked up = call it a customer service performance monitoring thing

As nothing in the question could have told us the schema of the DB, there wasnt much we could do! The only tip to offer, i guess: Always make sure your queries run in the analyzer you use (in this case, access) before investigating the IDE designer..