[xxrhy]

this is my code

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db,create_dbt dbt,create_dbs dbs,create_dbl dbl,create_dbd dbd where db.ID=dbd.ID AND dbd.ID=dbt.ID AND dbt.ID=dbs.ID AND dbs.ID=dbl.ID

This post has been edited by xxrhy: 13 August 2012 - 09:12 AM

[DarenR]

so what is the question

[xxrhy]

it said that no value given for one or more parameters.
i already put values on each database

[DarenR]

xxrhy, on 13 August 2012 - 12:12 PM, said:

this is my code

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db,create_dbt dbt,create_dbs dbs,create_dbl dbl,create_dbd dbd where db.ID=dbd.ID AND dbd.ID=dbt.ID AND dbt.ID=dbs.ID AND dbs.ID=dbl.ID

I dont know how your tables are structured but

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy 
from create_db db

inner join create_dbd dbd on dbd.ID=db.ID 
inner join create_dbt dbt on db.ID=dbd.ID 
inner join create_dbs dbs on dbs.ID= dbt.Id 
inner join create_dbl dbl on dbl.ID = dbs.ID

you can try something like that

[xxrhy]

i have 4 tables namely

create_db
ID,roomno
create_dbt
ID,timee
create_dbs
ID,subject,subjectss
create_dbl
ID,lecturer
create_dbd
ID,dayy

they all have primakey "ID" i want to join them all to be display in datagrid.

there's a error in the code you provide.
it said
"systanx error there is a missing query operation"

This post has been edited by xxrhy: 13 August 2012 - 09:39 AM

[DarenR]

mikssing which operation? it should tell you--- i am gussign it is the where clause---

change this line

inner join create_dbt dbt on db.ID=dbd.ID 

to

inner join create_dbt dbt on dbt.ID=dbd.ID 

[xxrhy]

"Syntax Error (missing operator) in query expression 'dbd.ID=db.ID inner join create_dbt dbt on db.ID=dbd.ID inner join create_dbs on dbs.ID=dbt.ID inner join create_dbl dbl on dbl.ID=dbs.I"

thats what exactly the error said

[DarenR]

did you try the updated thing i posted

[xxrhy]

yes still got the same error

[DarenR]

put a where clause at the end

like

where db.id = something you are searching for

[xxrhy]

still the same

i try

"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = 'roomno'"

and

"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = roomno"

and this

"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = subject"

[DarenR]

id usually isnt a name but a number---if yours is a name ' ' with name between the ' also if this is in a c# or vb.net did you open the sql command?

[xxrhy]

i open it in vb.net

[e_i_pi]

I notice you';re using reference to db.id as well as db.ID. If you're using VB.NET, then I'm guessing you're using Windows, in which case there could be case sensitivity issues at play here. If it's a MySQL database, try enclosing all the table names and column names in backticks, like this:

SELECT `db`.`roomno`, `dbt`.`timee`...

Also, make sure your table and column names match between the query and the database. Some of those column names look like they could be misspelt, such as subjectss and dayy.

[baavgai]

Take these things a step at a time.

Here's one of your queries, laid out legibly.

SELECT
		db.roomno, 
		dbt.timee, 
		dbs.subject, dbs.subjectss,
		dbt.lecturer,
		dbd.dayy 
	from create_db db
		inner join create_dbd dbd 
			on dbd.ID = db.ID 
		inner join create_dbt dbt 
			on dbt.ID = dbd.ID
		inner join create_dbs dbs 
			on dbs.ID = dbt.ID 
		inner join create_dbl dbl 
			on dbl.ID = dbs.ID
	where db.id = 'roomno'

Just for fun, let's strip out the where and loosen the joins. Also, we'll link everyone to db.ID and not do this messy cascading thing.

SELECT
		db.ID,
		db.roomno, 
		dbt.timee, 
		dbs.subject, dbs.subjectss,
		dbt.lecturer,
		dbd.dayy 
	from create_db db
		left outer join create_dbd dbd 
			on dbd.ID = db.ID 
		left outer join create_dbt dbt 
			on dbt.ID = db.ID
		left outer join create_dbs dbs 
			on dbs.ID = db.ID 
		left outer join create_dbl dbl 
			on dbl.ID = db.ID

This will get you every row from create_db, regardless of if they match up with the other tables. You'll see nulls in the fields where the table missed.

Having more meaningful table names could help you out here:

room
	room_id, roomno
room_time
	room_id, timee
room_subject
	room_id, subject, subjectss
room_lecturer
	room_id, lecturer
room_day
	room_id, dayy

This, frankly, looks like a mess. Lets assume you have a subject, taught but an instructor, in a given room, at a given time. Keying everything off room doesn't seem the way to go. Perhaps:

room
	room_id, roomno
subject
	subject_id, subject_name, subjectss
lecturer
	lecturer_id, lecturer_name
room_schedule
	room_id
	timee
	dayy
	subject_id
	lecturer_id

Hope this helps.