SQL in VB

[CM670]

Hi,

In my program i select a value from a database i.e

select name_f from owner where owner_id=text10.text

What i need to do is:

for each name_f
select employed from employment where name=name_f(name from previous sq query)

Does anyone know how to implement this in vb? I am new to VB and using VB5.

What i have so far is :


Dim sqll As String
DimRecords As Recordset

sqll = "select name_f from owner where owner_id='" & Text10.Text & "'"

Set Records = aphiscon.OpenRecordset(sSQL, dbOpenSnapshot)






I would appreciate any help with this.

Thanks

[MarkT]

I would think you would get the results you want by just updating your query with a join.

sql = "select emp.employed "
sql = sql & "from employment emp "
sql = sql & "inner join owner own on own.name_f = emp.name "
sql = sql & "where own.owner_id='" & text10.text & "'"

[CM670]

Thanks for your help but this is not returning the correct result.

How can i use the result of an SQL query in a for each loop?

[naveed217]

Thanks for your help but this is not returning the correct result.

How can i use the result of an SQL query in a for each loop?

You mean to say you want to browse through a file using the sql statement?
Like say:
strsql="Select * From Customers"
and you want to browse through the recordset?

[CM670]

yes,

eg-
strsql="select name from owner"

I want to browse through all the names.

[naveed217]

yes,

eg-
strsql="select name from owner"

I want to browse through all the names.

This is one of the basic database techniques one learns in the begining
Suppose you have all the records in a recordset Called RstOwners
Then do this:
Do while Not RstOwners.EOF

'Your code for example
debug. print RstOwner!OwnerName
'Assuming OwnerName is a field in the table
Loop

[naveed217]

yes,

eg-
strsql="select name from owner"

I want to browse through all the names.

This is one of the basic database techniques one learns in the begining
Suppose you have all the records in a recordset Called RstOwners
Then do this:
Do while Not RstOwners.EOF

'Your code for example
debug. print RstOwner!OwnerName
'Assuming OwnerName is a field in the table
Loop

[MarkT]

This is one of the basic database techniques one learns in the begining
Suppose you have all the records in a recordset Called RstOwners
Then do this:
Do while Not RstOwners.EOF

'Your code for example
debug. print RstOwner!OwnerName
'Assuming OwnerName is a field in the table
RstOwner.MoveNext
Loop

Make sure you add MoveNext to your loop

[naveed217]

Make sure you add MoveNext to your loop

OOPS! I forgot that. Thanks for correcting Mark

[techgnome]

I would think you would get the results you want by just updating your query with a join.

sql = "select emp.employed "
sql = sql & "from employment emp "
sql = sql & "inner join owner own on own.name_f = emp.name "
sql = sql & "where own.owner_id='" & text10.text & "'"

Thanks for your help but this is not returning the correct result.

How can i use the result of an SQL query in a for each loop?

Probably because it's an inner join, so it only returns rows in both tables...

This on the other hand,

sql = "select emp.employed "
sql = sql & "from owner own "
sql = sql & "left join  employment emp on own.name_f = emp.name "
sql = sql & "where own.owner_id='" & text10.text & "'"

might produce the results you're looking for... it starts with the owner table and does a left join to the employment table... if there is a record in the employment table, it will be returned, if not, you'll get NULL back.

-tg

[DataMiser]

Code from the OP has a bit of an issue

Dim sqll As String
DimRecords As Recordset

sqll = "select name_f from owner where owner_id='" & Text10.Text & "'"

Set Records = aphiscon.OpenRecordset(sSQL, dbOpenSnapshot)

[techgnome]

That will do it too...

-tg

[ELLIE_A]

Code from the OP has a bit of an issue

Dim sqll As String
DimRecords As Recordset

sqll = "select name_f from owner where owner_id='" & Text10.Text & "'"

Set Records = aphiscon.OpenRecordset(sSQL, dbOpenSnapshot)

Yeah, that may be the problem

[CM670]

Nothing seems to be working for me :

I have the tables owner and employment

The code i need is

if not EOF and text10.text is not null
Select name_f from owner where owner_id=text10.text

For each name (name_f from owner table)
select employed from employment where name_f=name (name_f from owner table)

if not EOF
if employment is null then
add name_f to grid

any ideas on how to write this in VB5??

[SamOscarBrown]

try this:

sSql= "select * from employment INNER JOIN owner "
sSql = sSql + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text

run your RS

this should return all your values, then

REM set grid column here
do while NOT rs.EOF
if ISNULL rs!employment then
grid1.row = grid1.row + 1 'assuming you have a header row
grid1.text = rs!name_f
rs.movenext
loop

SOMETHING like that....

[dac999]

To check if the SQL statement is correct design it in Access 97 to get the results you need. Then switch to the SQL view. Copy and paste from Access into VB6 with some modification if necessary.

[Nightwalker83]

You might also find the FAQ in the database development forum an interesting read.

[CM670]

try this:

sSql= "select * from employment INNER JOIN owner "
sSql = sSql + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text

run your RS

this should return all your values, then

REM set grid column here
do while NOT rs.EOF
if ISNULL rs!employment then
grid1.row = grid1.row + 1 'assuming you have a header row
grid1.text = rs!name_f
rs.movenext
loop

SOMETHING like that....

Thanks this has helped me a lot. I have done it exactly like this however it keeps throwing an error at the line where i run the recordset. The recordset is empty would this be the problem?

[DataMiser]

Show the code you are using now.

[CM670]

Dim sSQL as String 
Dim Records As Recordset

sSql= "select * from employment INNER JOIN owner "
sSql = sSql + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text

set Records. dbconn.openrecordset(sSql, dbOpenSnapShot)

grdMyGrid.Row(0)
grdMyGrid.Col(0)

Do While not Records.EOF
if ISNULL rs!employment then
grdMyGrid.row = GridMyGrid.row + 1 
grdMyGrid.text = rs!name_f
Records.movenext
loop

When i run my db query is says name_f is an ambiguous column?

[SamOscarBrown]

WELL, for one, your grdMyGrid.row = GridMyGrid.row + 1 is not correct.....you probably meant grdMyGrid.row = grdMyGrid.row + 1
And TWO, MY example used 'name_f' as a field in both tables (I though you had the same). Mine brought back a number greater than zero (5 in MY testing example).

You MAY need grdMyGrid.text = rs!employment.name_f --- I am not on the computer I did the example, so can't check that for you. I didn't test WHAT was brought back, I just counted num of records.

Check out ACCESS to see how to identify what was returned on that query...I will check later when I get the other machine.....

[SamOscarBrown]

Well, did a quick test.

Instead of using "select *.....", use "select employment.name_f, emplyment.XXXX1, employment.XXXX2...." where XXXX# is the name of the fields in the employment table you want to have returned.

[CM670]

sorry was trying to remember form memory what i had written as i was not on the machine where my project is stored when i replied.

yes i did mean grdMyGrid=grdMyGrid+1, thats what i have
name_f is a field in both tables.

I tried your example :
"Instead of using "select *.....", use "select employment.name_f, emplyment.XXXX1, employment.XXXX2...." where XXXX# is the name of the fields in the employment table you want to have returned."

This seems to be working as it is no longer throwing an error however the name is not showing in the grid. Do you know why this is?

[CM670]

When i run the query again, it is still saying that name_f is an ambigous column.

[SamOscarBrown]

Here's mine.....works just fine....make yours LIKE mine: Make sure you have returned at least one row....(msgbox rs.recordcount)

Private Sub Command4_Click()
 Dim rs As New Recordset
    dbConnection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn

    cmd.CommandText = "select employment.name_f, employment.firstname from employment INNER JOIN owner "
    cmd.CommandText = cmd.CommandText + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text
    
    Set rs = cmd.Execute

REM   use next line just for testing.....   if zero, then your query returned nothing (probably text1.text value)

   msgbox rs.recordcount

    Do While Not rs.EOF
          MsgBox rs!name_f + ", " + rs!firstname
    rs.MoveNext
    Loop
End Sub

[CM670]

thanks have done this. Still cant get it to put name_f in the grid though!

[SamOscarBrown]

post latest code....

[CM670]

Private Sub Form_Load()

With grdMyGrid

.row = 0

.col = 0
.ColWidth(.col) = 1600
.Text = "Name "
.ColAlignment(.col) = 2
.col = 1
.ColWidth(.col) = 1500
.Text = "address"
.ColAlignment(.col) = 2
.col = 2
.ColWidth(.col) = 1000
.Text = "Employed?"
.ColAlignment(.col) = 2

.Rows = 1


Dim sSQL as String
Dim Records As Recordset

sSql= "select * from employment INNER JOIN owner "
sSql = sSql + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text

set Records. dbconn.openrecordset(sSql, dbOpenSnapShot)

grdMyGrid.Row(0)
grdMyGrid.Col(0)

Do While not Records.EOF
if ISNULL rs!employment then
grdMyGrid.row = GridMyGrid.row + 1
grdMyGrid.text = rs!name_f
Records.movenext
loop

End if

End sub

[SamOscarBrown]

Ah,....you missed what I said earlier....instead of using:

sSql= "select * from employment INNER JOIN owner "

use:

sSql= "select employment.name_f, employment.XXXXX, employment.XXXX (ETC) from employment INNER JOIN owner " (where the XXXXs are the field names you want to get back....

For some reason, the select * doesn't give me rs!name_f, but when you do the select like above, it does.....Understand?

[techgnome]

select * WILL give you name_f .... BUT it appears in BOTH tables... so when you try to access rs!name_f ... it doesn't know which one you mean... do you want the one from the one table? Or the other table? It doesn't know... so it calls it ambiguous. To solve that, you HAVE to specify the fields you want... which is simply a good idea in the first place...

Next problem....
this code:

Do While not Records.EOF
if ISNULL rs!employment then
grdMyGrid.row = GridMyGrid.row + 1 
grdMyGrid.text = rs!name_f
Records.movenext
loop

All that does is set the text of the grid... and add rows to it... doesn't actually put the data in the grid... it's been a while since I've done VB6 coding... but ... if you replace your entire do while loop with this:

set grdMyGrid.DataSource = Records

That will assign your Records recordset to your grid... that should be all you need to to.

-tg

[SamOscarBrown]

As OP wrote the loop, you are correct, tg. However, it all depends on what he wants in the grid, and where he wants it put.

This following example puts name_f (from employment table) into the first column of my MSFlexGrid, starting with row # 1 (I placed a header row on my grid):

    cmd.CommandText = "select employment.name_f, employment.firstname from employment INNER JOIN owner "
    cmd.CommandText = cmd.CommandText + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text
    
    Set rs = cmd.Execute
    Grid1.Col = 0
    Grid1.Row = 0
    Do While Not rs.EOF
           Grid1.Row = Grid1.Row + 1
           Grid1.Text = rs!name_f
       rs.MoveNext
    Loop

If OP wants other fields in more columns, simple to modify the loop.

SO many ways of doing this stuff---mine is just one. As he was planning a for-loop in the OP, I just stayed in that vein, that's all. And I am not sure if he is using datagrids or flexgrids.....and that makes even more ways of displaying DB data.....

The last posting by CM definitely has an incorrect loop, as I told him how to change that earlier on, but he kept his original (even misspelling his grid name).

[CM670]

As OP wrote the loop, you are correct, tg. However, it all depends on what he wants in the grid, and where he wants it put.

This following example puts name_f (from employment table) into the first column of my MSFlexGrid, starting with row # 1 (I placed a header row on my grid):

    cmd.CommandText = "select employment.name_f, employment.firstname from employment INNER JOIN owner "
    cmd.CommandText = cmd.CommandText + "ON employment.name_f=owner.name_f where owner.id = " & Text1.Text
    
    Set rs = cmd.Execute
    Grid1.Col = 0
    Grid1.Row = 0
    Do While Not rs.EOF
           Grid1.Row = Grid1.Row + 1
           Grid1.Text = rs!name_f
       rs.MoveNext
    Loop

If OP wants other fields in more columns, simple to modify the loop.

SO many ways of doing this stuff---mine is just one. As he was planning a for-loop in the OP, I just stayed in that vein, that's all. And I am not sure if he is using datagrids or flexgrids.....and that makes even more ways of displaying DB data.....

The last posting by CM definitely has an incorrect loop, as I told him how to change that earlier on, but he kept his original (even misspelling his grid name).

Couldnt give out company code so was making up my own version similar to what i was doing. That explains the typos and confusion, And im a girl btw.

Got it now, thanks techgnome.