Do until loop

[mpande]

Good Day People
Please help me, I'm trying to do a do until loop, which can search the database to match the value that is entered in the vb6.0 textbox. but I don't know what to call the value in the database.
It looks something like this.
dim productid as integer
productid=me.productid.text
Do until productid=product_id(this is the value I don't know what to call, it is the value in the database)
PLEASE HELP.

[cguan_77]

hi, this might give you an idea:

num = 1 ' initialize counter
 'rs is the reference to your recordset change if needed
    Do While Not rs.EOF
        ' See if the data has been found
        If product_id = rs!product_id Then
            'do something here
        End If

        ' If not found move to the next record.
        num = num + 1
        rs.MoveNext
    Loop

and just curious why you need me on this code;

dim productid as integer
productid=me.productid.text

why not do like this:
productid=productid.text

i hope it helps.. :)

[ProfessorPC]

if it is a specific item you are looking for why search through mulitple records?

sSQL = "Select fields from table where id = " & productid & "

then you can run your do while.

[vb5prgrmr]

or if it is a partial product id no then...

"Select * from table where productid like '" & sometext & "*'"

if it is text or if not remove the single ticks (')

Good Luck

[mpande]

hi, this might give you an idea:

num = 1 ' initialize counter
 'rs is the reference to your recordset change if needed
    Do While Not rs.EOF
        ' See if the data has been found
        If product_id = rs!product_id Then
            'do something here
        End If

        ' If not found move to the next record.
        num = num + 1
        rs.MoveNext
    Loop

and just curious why you need me on this code;

dim productid as integer
productid=me.productid.text

why not do like this: productid=productid.text

i hope it helps.. :)

Thanks for your help Cguan
But it still won't work have been at it, trying to find the problem but to no avail, is it not possible to reference a variable in VB6.0 to a variable in oracle database. Because thats all I need.
Do until productid(vb6.0)=productid(database)-this is the problem
if productid(vb6.0)=productid(database) then
do something
loop
end if

[ProfessorPC]

what are you using to connect to the DB? i am guessing something like this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open 'connection string
Set rs = New ADODB.Recordset
rs.Open "Select fields from table where id = " & productid.text & "", cn
While Not rs.EOF
do something
rs.MoveNext
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

[mpande]

what are you using to connect to the DB? i am guessing something like this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
cn.Open 'connection string
Set rs = New ADODB.Recordset
rs.Open "Select fields from table where id = " & productid.text & "", cn
While Not rs.EOF
do something
rs.MoveNext
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks For your help, Proffessor PC.
I think it works, but won't see that properly now because it gives an error later on when I say: if productid=rs!product_id then
rs.open"select fields from table", "update field set data"
but it gives me an error on open(wrong number of arguments or invalid property assignment), could you please help, i have also tried .execute instead of .open, but it gives me the same error message.
Please help!!!!

[cguan_77]

Thanks For your help, Proffessor PC. I think it works, but won't see that properly now because it gives an error later on when I say: if productid=rs!product_id then rs.open"select fields from table", "update field set data" but it gives me an error on open(wrong number of arguments or invalid property assignment), could you please help, i have also tried .execute instead of .open, but it gives me the same error message. Please help!!!!

hi mpande, the code that Proffessor PC gives you it seems to work fine.. so maybe you can post the code that you try :)

[mpande]

hi mpande, the code that Proffessor PC gives you it seems to work fine.. so maybe you can post the code that you try :)

Hi cguan the code that I'm using is this:
If productid = rs!product_id Then
Set updatedb = New ADODB.Connection
updatedb.Open "DSN=waterdb;Password=andile;User ID=eco;Data Source=waterdb"
Set rs1 = New ADODB.Recordset
rs1.Open "Select product_id,qty,stock_date,trans_type,qtydb from stock", "update stock set product_id=productid", "update stock set qty=qty", "update stock set stock_date=date", "update stock set trans_type=trans_type", "update stock set qtydb=qtydb"
With Adodc1
.Recordset.Update
.Recordset.Fields("product_id") = productid
.Recordset.Fields("qty") = qty
.Recordset.Fields("stock_date") = dop
.Recordset.Fields("trans_type") = "trans_type"
.Recordset.Fields("qtydb") = qtydb

End With

Else: MsgBox ("invalid product id")
End If
db.CommitTrans
rs.MoveNext
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Loop

But the problem is at rsi.open, it gives me an error saying-wrong number of arguments or invalid property assignment.
Please help!!

[ProfessorPC]

Hi cguan the code that I'm using is this: If productid = rs!product_id Then Set updatedb = New ADODB.Connection updatedb.Open "DSN=waterdb;Password=andile;User ID=eco;Data Source=waterdb" Set rs1 = New ADODB.Recordset rs1.Open "Select product_id,qty,stock_date,trans_type,qtydb from stock", "update stock set product_id=productid", "update stock set qty=qty", "update stock set stock_date=date", "update stock set trans_type=trans_type", "update stock set qtydb=qtydb" With Adodc1 .Recordset.Update .Recordset.Fields("product_id") = productid .Recordset.Fields("qty") = qty .Recordset.Fields("stock_date") = dop .Recordset.Fields("trans_type") = "trans_type" .Recordset.Fields("qtydb") = qtydb

End With

Else: MsgBox ("invalid product id") End If db.CommitTrans rs.MoveNext rs.Close Set rs = Nothing db.Close Set db = Nothing Loop

But the problem is at rsi.open, it gives me an error saying-wrong number of arguments or invalid property assignment. Please help!!

okay lets see what we can do ;) lol. so you get the data from the product id. you are updating the table with the values in txt boxes? if that is the way your are working it lets try this.

rs1.Open "UPDATE Stock (qty, stock_date, trans_type, qtydb ) Values (" & qtyfield &", " & stock_datefield &", " & trans_type &", " & qtydbfield &")", updatedb

been a long day hope that works lol

[mpande]

okay lets see what we can do ;) lol. so you get the data from the product id. you are updating the table with the values in txt boxes? if that is the way your are working it lets try this.

rs1.Open "UPDATE Stock (qty, stock_date, trans_type, qtydb ) Values (" & qtyfield &", " & stock_datefield &", " & trans_type &", " & qtydbfield &")", updatedb

been a long day hope that works lol

Hi Professor P

Thanks for your help, it's truly appreciated.The update actually works, but then the problem is that it does'nt update the value corresponding with the product_id, instead it updates all the all the values here is the code I'm using:
db.Open "DSN=eco;Password=andile;User ID=eco;Data Source=waterdb"
Set rs = New ADODB.Recordset

rs.Open "select product_id,qty,qtydb from product_line where product_id=" & Me.productid.Text & " ", db
Do While Not rs.EOF
If productid = rs!product_id Then
Set updatedb = New ADODB.Connection
updatedb.Open "DSN=eco;Password=andile;User ID=eco;Data Source=waterdb"

Set rs1 = New ADODB.Recordset

qtydba = qtydba + qty

updatedb.Execute "UPDATE product_line set qtydb=" & qtydba & "", updatedb

Else: MsgBox ("invalid product_id")
rs.Close
End If
Loop
db.Close

Could you please help.
Thanks.

[ProfessorPC]

ok we need to set it where the productid = the productid

"UPDATE product_line set qtydb=" & qtydba & " WHERE  product_id = " & productid & "",

[mpande]

hi, this might give you an idea:

num = 1 ' initialize counter
 'rs is the reference to your recordset change if needed
    Do While Not rs.EOF
        ' See if the data has been found
        If product_id = rs!product_id Then
            'do something here
        End If

        ' If not found move to the next record.
        num = num + 1
        rs.MoveNext
    Loop

and just curious why you need me on this code;

dim productid as integer
productid=me.productid.text

why not do like this: productid=productid.text

i hope it helps.. :)

Hi Proffessor P

I guess now I know why they call you proffessor P, thanks for you help, it works now.
The other problem was that I want to increment the qtydb value, but it wont increment, this is what I'm using qtydb=qtydb+qty, it does the calculation, it's just that, it doubles the qty value, instead of giving me the qtydb value in the database and then adding it with the qty value.
Please Help.
Thanks.

[ProfessorPC]

"UPDATE product_line set qtydb= qtydb + " & qtydba & " WHERE  product_id = " & productid & "",

[mpande]

hi, this might give you an idea:

num = 1 ' initialize counter
 'rs is the reference to your recordset change if needed
    Do While Not rs.EOF
        ' See if the data has been found
        If product_id = rs!product_id Then
            'do something here
        End If

        ' If not found move to the next record.
        num = num + 1
        rs.MoveNext
    Loop

and just curious why you need me on this code;

dim productid as integer
productid=me.productid.text

why not do like this: productid=productid.text

i hope it helps.. :)

Hi Proffessor P
Thanks for the help, it works.
Another thing is that, how can you attach a calender, on the date textbox, so that instead of the user entering the date, they simply choose the date from the calendar.
Thanks.

[cguan_77]

use Microsoft Calendar Control..go to Project->Components then click Microsoft Calendar Control..

[mpande]

use Microsoft Calendar Control..go to Project->Components then click Microsoft Calendar Control..

Hi cguan_77
It's been long, thanks, I'v found the control, but now how(code) do you enter the date into the database with the control.

[ProfessorPC]

you grab its text.

[mpande]

you grab its text.

ok, what do you call the control, you know with a textbox, it is .text, I'm trying to use the normal way, dim date as string, date=calendar1._____, is what I don't know, have tried most of them, but they give me an error(type mismatch) at runtime.
Thanks
Cheers.

[cguan_77]

text1.text= calendar1.value