plz help. SQL problem

  • Thread starter Thread starter Eva
  • Start date Start date
E

Eva

hi,

I have 2 comboboxes (CB). CB1 shows the roomNames (pulled
from my access DB) and CB2 should show the layouts that
are possible (RoomLayout) for that specifc room (these
values are also stored in the database, but in a
different table). What i want to do is load the correct
layouts for a particular room into CB2 (from my DB) based
on the room selected by the user in CB1. This requires me
to requery the database everytime the value of CB1 is
changed. The problem im having is that CB2 does not seem
to be populating.

Heres my code:

Private Sub Form2_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
''''this loads CB1 with the room names
DsRoomN1.Clear()
OleDbDAdapterRoomN.Fill(DsRoomN1)
End Sub

Private Sub CB1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
CB1.SelectedIndexChanged
Dim ObjParam As OleDb.OleDbParameter

ObjParam =
OleDbDAdapterPL.SelectCommand.Parameters.Add("@RoomNm",
OleDb.OleDbType.Char)
DsPossLayouts.Clear()
OleDbDAdapterPL.Fill(DsPossLayouts)


the query that i wrote in the query builder for CB2 is:

SELECT Room_Layouts.Poss_layout, Rooms_avail.Room_name,
Room_Layout.RoomID, Rooms_avail.RoomID AS Expr1 FROM
Room_Layouts INNER JOIN Rooms_avail ON
Room_Layouts.RoomID = Rooms_avail.RoomID WHERE
(Rooms_avail.Room_name = ?)

the question mark is the parameter that will need to be
inserted from CB1 (i.e the room_name).

Can anyone help me figure out how to make this work?

thx in advance
 
hi,

I have 2 comboboxes (CB). CB1 shows the roomNames (pulled
from my access DB) and CB2 should show the layouts that
are possible (RoomLayout) for that specifc room (these
values are also stored in the database, but in a
different table). What i want to do is load the correct
layouts for a particular room into CB2 (from my DB) based
on the room selected by the user in CB1. This requires me
to requery the database everytime the value of CB1 is
changed. The problem im having is that CB2 does not seem
to be populating.

Heres my code:

Private Sub Form2_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
''''this loads CB1 with the room names
DsRoomN1.Clear()
OleDbDAdapterRoomN.Fill(DsRoomN1)
End Sub

Private Sub CB1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
CB1.SelectedIndexChanged
Dim ObjParam As OleDb.OleDbParameter

ObjParam =
OleDbDAdapterPL.SelectCommand.Parameters.Add("@RoomNm",
OleDb.OleDbType.Char)
DsPossLayouts.Clear()
OleDbDAdapterPL.Fill(DsPossLayouts)

the query that i wrote in the query builder for CB2 is:

SELECT Room_Layouts.Poss_layout, Rooms_avail.Room_name,
Room_Layout.RoomID, Rooms_avail.RoomID AS Expr1 FROM
Room_Layouts INNER JOIN Rooms_avail ON
Room_Layouts.RoomID = Rooms_avail.RoomID WHERE
(Rooms_avail.Room_name = ?)

the question mark is the parameter that will need to be
inserted from CB1 (i.e the room_name).

Can anyone help me figure out how to make this work?

thx in advance
Click to expand...

Hi Eva.
A more elegant solution to your problem (which could be described as
"Creating a master-detail view" ) would be to use databinding for both the
master (rooms) and the child data(layouts).
Doing this with two datagrids is easier than it is doing it with a combo
and another control, but if I went through this hurdle, you don't have to.
I wrote a short piece about that. read here (Lesson 4):

http://weblogs.asp.net/rosherove/story/9088.aspx
 
Back
Top