Problem with listviews

I have this sub:

Code:

Private Sub lvwRSPageDemo_DblClick() Dim dbDatabase As Database Dim strSQL As String Set rs = New ADODB.Recordset strSQL = "SELECT * FROM (PROGRAMME LEFT JOIN TCOURSE ON PROGRAMME.Pid = TCOURSE.Pid) LEFT JOIN TRAINER ON TCOURSE.Cid = TRAINER.Cid" rs.Open strSQL, conn, adOpenStatic, adLockReadOnly 'MsgBox rs.Fields("Tid") If Len(lvwRSPageDemo.SelectedItem.Text) = 0 Then Exit Sub End If Call rs.MoveFirst Do Until rs.EOF = True If lvwRSPageDemo.SelectedItem.Text = rs.Fields("ProgrammeName") Then MsgBox lvwRSPageDemo.SelectedItem.Text inputByName = rs.Fields("ProgrammeName") pid = rs.Fields("PROGRAMME.Pid") MsgBox rs.Fields("TName") If (rs.Fields("TCOURSE.Cid") = "") Then cid = "0" End If If (rs.Fields("TCOURSE.Cid") <> "") Then cid = rs.Fields("TCOURSE.Cid") End If If (rs.Fields("Tid") = "") Then tid = "0" End If If (rs.Fields("Tid") <> "") Then pid = rs.Fields("Tid") End If Unload Me Load frmTDetail frmTDetail.Show rs.Close Exit Sub End If Call rs.MoveNext Loop 'rs.Close End Sub

And this Image:

http://i153.photobucket.com/albums/s.../listviews.jpg

If u can see, there are 2 data which is all field are the same but only the name is the different, for the first time, i use the primary key to validate which data i double click with. but the code above will take the first record even if i click the second record, so i bet i want to validate using the trainer name because only name and the primary key is the unique, but then i think again, it is possible if the name might be the 2 person, so it is not unique. So anyone have an idea so that i can validate using the primary key, or the listview will validate the number of rows i click?
Hope to see the reply soon...

thank u very much

Re: Problem with listviews

You need a unique field.

Can you add an ID field to the database, which is unique and primary. Each ID is a different number.

That is how I would do it.

Re: Problem with listviews

Quote:

Originally Posted by Midol

You need a unique field.

Can you add an ID field to the database, which is unique and primary. Each ID is a different number.

That is how I would do it.

I already have a unique ID which is the primary key, but i dont show it in the listviews because it would give a bad views to user. So i want, the ID will not show in the listviews but it have in the back, which more like a hidden ID. but i know the listviews just can check the first column word

Code:

If lvwRSPageDemo.SelectedItem.Text = rs.Fields("ProgrammeName") Then

the "lvwRSPageDemo.SelectedItem.Text" will return a first word of column in the listview. so maybe there is a way to change it so the listview can read a hidden ID when i click the data, if it doesn have, then there is must be another way that i dont know.

thank u very much

Re: Problem with listviews

Use the .Tag property of each item (which is a String that most controls provide, for you use as you want) to store the ID.

You can then retrieve it using: lvwRSPageDemo.SelectedItem.Tag

Re: Problem with listviews

si_the_geeks technique is what I'd use in this case. I misunderstood your initial post.

In the program I am currently writing I have a few flexgrids and I just hide specific columns (set width to 0) for info I need and that I don't want the user to see - not sure if that's possible with the control you are using.

Re: Problem with listviews

Quote:

Originally Posted by Midol

In the program I am currently writing I have a few flexgrids and I just hide specific columns (set width to 0) for info I need and that I don't want the user to see - not sure if that's possible with the control you are using.

Yes...you would do the same thing with a ListView.

Re: Problem with listviews

Yes, i already did that, but i'm not sure which primary key should i use. because in my case, the only unique now is the TRAINER primary key but TRAINER not neccessary assign to a particular Subject, so it might have or might not have, here the explanation in the picture
http://i153.photobucket.com/albums/s...mie/list-1.jpg

as u can see, all row of record have a same data only in the column TRAINER NAME, but there have a one row which doesnt have TRAINER NAME, if i'm using the TRAINER ID, then i click the record that doesnt have the TRAINER NAME, the detail of the record will not found because no TRAINER ID were found. So is there anyway to validate it? i really2 cannot think, i'm stuck here.

About the tag properties, i try to check the result when use the tag properties

Code:

msgbox lvwRSPageDemo.SelectedItem.tag

but display null value. what is the function of the tag properties actually, well while waiting for reply, i try google it first.

Thanks a bunch for helping me :afrog:

Re: Problem with listviews

The .Tag property can store any type of variables or objects, not just strings.

Re: Problem with listviews

Quote:

Originally Posted by RobDog888

The .Tag property can store any type of variables or objects, not just strings.

Is that really true? MSDN says that it is "A string expression identifying the object. The default is a zero-length string ("")." (bolding mine)

Re: Problem with listviews

Oh snap, I have my .NET brain on right now. In .NET its definately an Object but lets veryif for VB6...

Re: Problem with listviews

I stored an Excel.Application instance in a listview item .Tag property without error but it may be just the String of the objects name.

Code:

Option Explicit Private Sub Form_Load() Dim itmX As ListItem Dim oApp As Object Set oApp = CreateObject("Excel.Application") ListView1.ColumnHeaders.Add , , "Test" Set itmX = ListView1.ListItems.Add(, , "Test") itmX.Tag = oApp End Sub

Re: Problem with listviews

Here what have i modified:

Code:

Private Sub ShowPage() Dim intRecord As Integer Dim lvwItem As ListItem rs.AbsolutePage = intPage lvwRSPageDemo.ListItems.Clear For intRecord = 1 To rs.PageSize 'If Not (rs.BOF = True Or rs.EOF = True) Then If (rs.Fields("Tid") <> "") Then Set lvwItem = lvwRSPageDemo.ListItems.Add(, , rs.Fields.Item("Tid").Value) Else Set lvwItem = lvwRSPageDemo.ListItems.Add(, , rs.Fields.Item("PROGRAMME.Pid").Value) End If lvwItem.SubItems(1) = rs.Fields.Item("ProgrammeName") If (rs.Fields("CTitle") <> "") Then lvwItem.SubItems(2) = rs.Fields.Item("CTitle").Value lvwItem.SubItems(3) = rs.Fields.Item("CModule").Value End If If (rs.Fields("TName") = "") Then lvwItem.SubItems(4) = "" End If If (rs.Fields("TName") <> "") Then lvwItem.SubItems(4) = rs.Fields.Item("TName").Value End If If (rs.Fields("Tid") = "") Then lvwItem.SubItems(5) = "" End If If (rs.Fields("Tid") <> "") Then lvwItem.SubItems(5) = rs.Fields.Item("Tid").Value End If 'Else 'MsgBox "entah" 'End If rs.MoveNext If rs.EOF Then Exit For Next intRecord lblPageInfo.Caption = "Page " & intPage & " of " & intPageCount cmdPrev.Enabled = (intPage > 1) cmdFirst.Enabled = (intPage > 1) cmdNext.Enabled = (intPage < intPageCount) cmdLast.Enabled = (intPage < intPageCount) End Sub

i check to use wheter programme ID or Trainer ID
it work until reach to a detail part:

Code:

Public Sub SearchByName() Dim strSQL As String MsgBox pid MsgBox tid Set rs = New ADODB.Recordset strSQL = "SELECT * FROM (PROGRAMME LEFT JOIN TCOURSE ON PROGRAMME.Pid = TCOURSE.Pid) LEFT JOIN TRAINER ON TCOURSE.Cid = TRAINER.Cid WHERE TRAINER.Tid = " & tid & " OR PROGRAMME.Pid = " & pid & "" rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText fillfields End Sub

how i want to manipulate the SQL, because when i click the third record, it contain no "tid" then the sql will verify using the Pid, well then sql found the first pid which is a wrong result, because i click the third data. i'm stuck here.

erm, can someone provide me an example or code snippet of how to use tag property in my case? i already google it, nothing usefull found

thank u very much

Re: Problem with listviews

The .Tag property allows you to store a string for each item in the list.. it is up to you what you store in it, and what you do with it later.

You want something that is unique for each record, so use the primary key for the table you are working with... However, it seems that you are using 3 tables here (assuming the data for ShowPage comes from a similar SQL statement), so to be able to find that data again you need to use the PK's of all 3 of the tables.

I'm not sure if I got the correct field for TCourse, but you could store the PK's like this:

Code:

lvwItem.Tag = rs.Fields("Tid").Value & "#" _ & rs.Fields("PROGRAMME.Pid").Value & "#" _ & rs.Fields("TCOURSE.Cid").Value

When it comes to using that data, you can Split the .Tag to get the individual values, eg:

Code:

Dim TempSplit() as String Dim tid as String, pid as String, cid as String TempSplit = Split(lvwRSPageDemo.SelectedItem.tag, "#") tid = TempSplit(0) pid = TempSplit(1) cid = TempSplit(2)

And then build your SQL statement as needed based on these values.

Note that if a value is blank it means the field is Null, so instead of "field = value " you need to use "field Is Null".

Re: Problem with listviews

http://i153.photobucket.com/albums/s...e/relation.jpg i try to normalize my table as above. i think from there, much easier for me to do a searching process, am i rite? but i still have the problem which when the:

PCTid Pid Cid Tid
1 1 2 3
2 1 2

As u can see, the Tid contain no value, which is null value, so i though i use SQL :

Code:

strSQL1 = "SELECT PCTid FROM PROGCOURSETRAINER WHERE Pid = " & pid & " AND Cid = " & cid & " AND Tid IS " & tid & "" MsgBox strSQL1 rs.Open strSQL1, conn, adOpenKeyset, adLockPessimistic, adCmdText result = rs.Fields("PCTid") MsgBox result strSQL = "SELECT * FROM ((PROGCOURSETRAINER LEFT JOIN PROGRAMME ON PROGCOURSETRAINER.Pid =PROGRAMME.Pid)LEFT JOIN TCOURSE ON PROGCOURSETRAINER.Cid = TCOURSE.Cid)LEFT JOIN TRAINER ON PROGCOURSETRAINER.Tid = TRAINER.Tid WHERE PCTid = " & result & "" MsgBox strSQL Set rs = New ADODB.Recordset rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText fillfields

the first strSQL1 is to obtain PCTid to being use in second strSQL to display the detail. But the "Tid IS " & tid & "" only can validate for null value. if the the subject have trainer, then syntax error will occur, then i google it, found something about Turning off the ANSI_NULLS OFF, but all information about ANSI_NULLS OFF is for SQL Server. but SQL is standard language, then i try :

Code:

SET ANSI_NULLS OFF

But got an error, can anyone help me?

Re: Problem with listviews

As you have the PROGCOURSETRAINER table, why not simply store PCTid in the .Tag, and use that to get the data later?

eg:

Code:

strSQL = "SELECT PROGRAMME.*, TCOURSE.*, TRAINER.* " _ & "FROM ((PROGCOURSETRAINER " _ & " LEFT JOIN PROGRAMME ON PROGCOURSETRAINER.Pid =PROGRAMME.Pid) " _ & " LEFT JOIN TCOURSE ON PROGCOURSETRAINER.Cid = TCOURSE.Cid) " _ & " LEFT JOIN TRAINER ON PROGCOURSETRAINER.Tid = TRAINER.Tid " _ & "WHERE PCTid = " & lvwRSPageDemo.SelectedItem.tag

Note that it is always a bad idea to use "SELECT *", you should always specify the fields that you want (even if it is all of them), as it is more efficient. The minor improvement I made is still not ideal, but is better than it was.

Re: Problem with listviews

Quote:

Originally Posted by si_the_geek

As you have the PROGCOURSETRAINER table, why not simply store PCTid in the .Tag, and use that to get the data later?

eg:

Code:

strSQL = "SELECT PROGRAMME.*, TCOURSE.*, TRAINER.* " _ & "FROM ((PROGCOURSETRAINER " _ & " LEFT JOIN PROGRAMME ON PROGCOURSETRAINER.Pid =PROGRAMME.Pid) " _ & " LEFT JOIN TCOURSE ON PROGCOURSETRAINER.Cid = TCOURSE.Cid) " _ & " LEFT JOIN TRAINER ON PROGCOURSETRAINER.Tid = TRAINER.Tid " _ & "WHERE PCTid = " & lvwRSPageDemo.SelectedItem.tag

Note that it is always a bad idea to use "SELECT *", you should always specify the fields that you want (even if it is all of them), as it is more efficient. The minor improvement I made is still not ideal, but is better than it was.

Is it can store the PCTid? The SQL will read

Code:

SELECT PROGRAMME.*, TCOURSE.*, TRAINER.* FROM ((PROGCOURSETRAINER LEFT JOIN PROGRAMME ON PROGCOURSETRAINER.Pid =PROGRAMME.Pid) LEFT JOIN TCOURSE ON PROGCOURSETRAINER.Cid = TCOURSE.Cid) LEFT JOIN TRAINER ON PROGCOURSETRAINER.Tid = TRAINER.Tid WHERE PCTid = '#1#15'

if i click the data without the trainer, the #1 represent the Programme ID and the #15 represent the Course ID

how the SQL want to read the "PCTid = '#1#15'" ?
if i split it, i still have the same problem in the detail form in which, how to validate the Null value in the TRAINER?
please help me, this already a week i stuck here :eek2:

Thank u

Re: Problem with listviews

Problem solve, i just use if then clause, thanks to si_the_geek

Code:

Public Sub SearchByName() Dim strSQL As String Dim result As String Set rs = New ADODB.Recordset MsgBox tid If tid = 0 Then strSQL = "SELECT PCTid FROM PROGCOURSETRAINER WHERE Pid = " & pid & " AND Cid = " & cid & " AND Tid IS Null" rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText result = rs.Fields("PCTid") End If If tid <> 0 Then strSQL = "SELECT PCTid FROM PROGCOURSETRAINER WHERE Pid = " & pid & " AND Cid = " & cid & " AND Tid = " & tid & "" rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText result = rs.Fields("PCTid") MsgBox result End If strSQL = "SELECT * FROM ((PROGCOURSETRAINER LEFT JOIN PROGRAMME ON PROGCOURSETRAINER.Pid =PROGRAMME.Pid)LEFT JOIN TCOURSE ON PROGCOURSETRAINER.Cid = TCOURSE.Cid)LEFT JOIN TRAINER ON PROGCOURSETRAINER.Tid = TRAINER.Tid WHERE PCTid = " & result & "" Set rs = New ADODB.Recordset rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText fillfields End Sub

but i already change the database design as travis propose, now i have to make a large change in the add function of the 4 table, PROGRAMME, TCOURSE, TRAINER and PROGCOURSETRAINER. now i'm stress to think the flow of the addition Of each.

Here is the flow:
First, i have a PROGRAMME form which have only one field ProgrammeName, then user have to option either want to save or proceed to add TCOURSE, if user want to save the newly PROGRAMME, then user just click save.

Next, user click "ADD SUBJECT" which will add in the table TCOURSE, in the TCOURSE form, user enter the TCOURSE attribute and have a combobox contain programme name list allow user to select which PROGRAMME that the current newly course be assign. then user can save the current TCOURSE, or user can proceed to add TRAINER.

Lastly, user click "ADD TRAINER" which will add in the table TRAINER, in the TRAINER form, the user enter the TRAINER attribute and also have a combobox contain SUBJECT name list that allow user to select which SUBJECT that the current trainer be assign, then user can save the current TRAINER.

The problem is arise when in the FORM ADD SUBJECT which, i need to save the Foreign key in the PROGCOURSETRAINER but cannot because the foreign key for TRAINER will be null and it impossible because the related table is required in the table TRAINER, so i add the foreign key in the TRAINER form which i can get the Tid and store all the three foreign key in the PROGCOURSETRAINER but i cannot get the PROGRAMME Key.

Now i'm stuck here, does anyone have a better way to solve this? well thanks for reading my "Problem Novel" :D