Results 1 to 11 of 11

Thread: Recordset Help

  1. #1

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    58

    Unhappy Recordset Help

    HI guys, i doing a video Club assignment and am having problems in retrieving information from a recordset.

    I am using adodb connection. Ok here is my problem
    I have an Access database with the following tables
    Catalogue (catid,title,qty,.....)
    Media(catid,serialno,status,........)
    Reservtion(Resid,catid,serialno,date,......)

    I have done a Sql satatement that joins all the three table:

    set pbrst=Rstsel ("SELECT * FROM reservation, media, catalogue WHERE reservation.serialno=media.serialno And media.catid=catalogue.catid And reservation.membershipid= '" & cboMembershipId.Text & "'")

    so i get a recorset witn all these information

    Now how do i get only the Catid in a MsHFlexgrid
    I have done the following: set Mshflexgrid1.datasource = pbrst!catid
    But get an error


    Error message is:
    Items cannot be found in the collection corresponding to the requested name or ordinal


    Please friends help me...

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,428

    Re: Recordset Help

    VB Code:
    1. ("SELECT reservation.*, media.*, catalogue.* FROM reservation, media, catalogue WHERE reservation.serialno=media.serialno And media.catid=catalogue.catid And reservation.membershipid= '" & cboMembershipId.Text & "'")

    You need to qualify the results for each table.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    58

    Re: Recordset Help

    Thanks for reply

    But i get the same error message...

    Dont you think that the problem comes when

    Set MSHFlexGrid1.DataSource = pbrst!Catid

    is executed????

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Recordset Help

    The MSHFlexGrid will automatically show all columns when it is bound to a recordset.

    Set MSHFlexGrid1.DataSource = pbrst

    If you are only going to show the CatId column use a different query.

    Or don't bind the grid to the recordset load the data in code.

    VB Code:
    1. Do Until pbrst.EOF
    2.     mshflexgrid1.additem pbrst!catid 'assumes FixedCols = 0
    3.     pbrst.MoveNext
    4. Loop

  5. #5

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    58

    Re: Recordset Help

    Thanks brucevde
    This solved th problem.
    when SET MSHFLEXGRID1.DATASOURCE=PBRST was executed, i got the following fields:

    resid,reservation.serialno,media.serialno,media.catid,catalogue.catid
    This is why an error occured because PBRST!CATID vb donot know which catid to display

    now my question is:
    how to bind the recordset so that i can populate my grid.... May be SET MSHFLEXGRID1.DATASOURCE= PBRST!CATID can be modified

    please help because i will need this not only for mshflexgrid but to populate listviews also ...


    thanks for replies guys
    Last edited by roubesh14; May 3rd, 2006 at 10:51 AM.

  6. #6
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Recordset Help

    You can't bind the FlexGrid to a specific column.

    You could "hide" all the unwanted columns.

    SET MSHFLEXGRID1.DATASOURCE= PBRST
    MSHFlexGrid1.ColWidth(-1) = 0 'first set the width of all columns to 0
    MSHFlexGrid1.ColWidth(1) = 1000 'or whatever you need

    You do realize the FlexGrid will probably display duplicate CatIds. You would be better off adding them to the grid manually.

  7. #7
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,044

    Re: Recordset Help

    Quote Originally Posted by roubesh14
    please help because i will need this not only for mshflexgrid but to populate listviews also ...


    thanks for replies guys
    To populate a listview you may try something like this...
    VB Code:
    1. Private Sub Test()
    2.     Dim li As ListItem
    3.     With rsRecord
    4.         .Open "SELECT * FROM TableName", cnn
    5.         Do While Not .EOF
    6.             Set li = lvwIOF.ListItems.Add(Text:=.Fields(0))
    7.             For a = 1 To 6
    8.                 li.ListSubItems.Add Text:=.Fields(a)) & vbNullString
    9.             Next
    10.             .MoveNext
    11.         Loop
    12.     End With
    13. End Sub
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  8. #8

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    58

    Re: Recordset Help

    Thanks Guys. You saved me

    Now that my list views are populated, i have to handle an item click event so that
    when i click on an item (example filmtitle) i can have the filmid of that film to passs a sql. How do i do that guys.. i guess its something like Listview.listindex or key

    thanks for replies guys......

  9. #9
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Recordset Help

    Quote Originally Posted by roubesh14
    Thanks Guys. You saved me

    Now that my list views are populated, i have to handle an item click event so that
    when i click on an item (example filmtitle) i can have the filmid of that film to passs a sql. How do i do that guys.. i guess its something like Listview.listindex or key

    thanks for replies guys......
    The easiest way would be to store it in a variable (form level variable)
    VB Code:
    1. Option Explicit
    2.  
    3. Private strFilmTitle As String
    4.  
    5. Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
    6. 'referencing the columns in a listview start with:
    7. 'Item = 1 rown
    8. 'Item.Subitems(2) etc = 2nd and subsequent rows
    9. 'so, it will all depend on where, in your listview, the filmtitle is displayed
    10. 'if it is in the first column then
    11. strFilmTitle = Item.Text
    12. 'if it is in the second or subsquent columns then
    13. strFilmTitle = Item.Subitems(x) 'where x = the column number
    14. End Sub

  10. #10

    Thread Starter
    Member
    Join Date
    May 2006
    Posts
    58

    Re: Recordset Help

    thank guys
    My next question is :
    I have 2 forms

    frmCatalogue...............................frmDelete
    Private Sub Form_Load()................Private Sub cmdOk_Click()

    How do i access form_load of form catalogue when i click OK from form Delete?

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,502

    Re: Recordset Help

    Do you mean you want to load the form, or you specifically want to run the Form_load sub?

    To load the form (which will also call Form_load):
    VB Code:
    1. 'Just load:
    2. Load frmCatalogue
    3.  
    4. 'Load and show:
    5. frmCatalogue.Show

    If you just want to execute the form_load sub, you can do this:
    VB Code:
    1. Call frmCatalogue.Form_load
    ..note tho that if you are doing this, it would be better to make a different sub for this purpose (which you move the code to from Form_load code, and is then called from Form_load too).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width