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.. :)

[Ole Raptor]

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

[Ole Raptor]

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!!

[Ole Raptor]

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.

[Ole Raptor]

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.

[Ole Raptor]

"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.

[Ole Raptor]

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

[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 Cguan_77
Thanks for that, I don't know why it did'nt work for me, but now that you said it, it works.
Another problem I have is that I have a space for a phone number, when the numbers exceed 3 it shows an error(overflow), I don't know why, could you please help on that.
Another thing I want to add to my program is a sequence, where an order no, automatically increments to the next, without you entering it.
Please Help
Thanks.

[cguan_77]

Another problem I have is that I have a space for a phone number, when the numbers exceed 3 it shows an error(overflow)

the variable holding the data for phone number you declare it as a integer or string? or how did you declare it?

sequence number? have you tried like this >> i = i + 1

[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 Cguan_77

I declared the phone number as an integer.
Regarding the sequence, yes I was using i=i+1, but maybe I used it in the wrong way. I said i=i+1, custid=i, custid=me.custid.text.
Please correct me where I'm wrong.
Thanks.

[jccaldz]

try this:

rs.recordset.movefirst
rs.Recordset.FindFirst "myfieldname like '" & productid.text & "'"

if rs..Recordset.nomatch then
msgbox "no match"
Exit sub
end if

rs.recordset.movefirst

Do
rs.Recordset.Findnext "myfieldname like '" & productid.text & "'.
'create a code to print the result.
loop until rs.Recordset.EOF

I used LIKE so that it will accept wild cards.

[cguan_77]

try declaring phone number as string not an integer...
regarding the sequence number.. you want it for what reason?

if you just want your database records to be in sequence... no need to do that.. database will take care of it..it will be defined by the primary key of your database...

[mpande]

Hi
The number works now, but why declare a number as string? Anyways atleast it works. I want the sequence for the program, so that at runtime, you don't forget which number you have not entered, I want it to increment automatically.

[cguan_77]

but why declare a phone number as string?
as you said in post#21, you have an space in phone number data...so if you declare it in string it can accept space...

the sequence i=i+1 will do

[omoridi]

wow
you can use find method
rs.Find "f1=123", 0, adSearchForward, 1
f1=your field name
123=your value
if es.eof =true means don't find any value
if not rs in find record