retrieving a record...how...

[Israelsimba]

i know that a select statement is the best way to go around retrieving records from a database. the problem im facing is selecting a record then assigning a certain field from that record to a variable eg x.....
so in simple english, it has to be something like....select clientnumber from members where firstname= txtfirstname.text

the code im using to retrieve the record is

Set ri = deSub.conn.Execute("SELECT clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")

so how do i assign the clientnumber to a variable x, so that i can display it in a textbox.
thank you in advance!!

[Israelsimba]

you see i am trying different methods to achieve this....i have tried to retrieve using the followinh sql statement but its failing to come up. its returning the whole statement as it is.

Dim r As String
r = ("SELECT clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")
deSub.conn.Execute (r)
txtcnumber.Text = r

[jireh]

Dim rs As Recordset

Set rs= deSub.conn.Execute("SELECT clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")Set ri = deSub.conn.Execute("SELECT clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")

txtcnumber.Text = rs!clientnumber

[abu taher]

Set ri = deSub.conn.Execute("SELECT clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")

why you use this. plz explain.

[jireh]

ooops! sorry, please omit the Set keyword or maybe not, I forget its years when I stop vb6 but anyhow it must be that way. Do you know how to use a recordset? You pass a query then the recordset will hold the returned data of your select statement then now you access the data thru recordset like my example.

[Israelsimba]

i have managed to get the retrived client number into a variable x. i want to use this variable to insert a client number in the cooperate table when inserting a record there. the reason i did this was that i am entering details into a cooperatesubs table first then a client number is autogenerated there..i then retrieve that number for saving into cooperate table...i hope this is clear...after retrieving the record, i want to insert a record into the details table now..the first time i insert the record it saves fine...but when it asks me "do you want to insert another record" then i click yes. after entering that record it gives me an error upon saving...NOTE if i close the form and run it again it saves fine but when i try to save a second record without closing it gives me an error....

syntax error(missing operator) in querry expression "x" "x" "x" "x" "x" "x"

X,X,X are the details i would have entered.
the code im using is.

deSub.conn.Execute ("INSERT INTO cooperate (membernumber, companysize, numberofcopies, companyname, country, datejoined, startingmonth, city, postaladdress1, physicaladdress1, companyemail, companyphone, companywebsite, firstname, lastname, occupation, telephone, cellphone, email, totalcopies) VALUES (" & txtcclientnumber.Text & ", " & cmbs.Text & ", " & txtccopies.Text & ", '" & txtccname.Text & "', '" & txtccountry.Text & "', '" & DTPicker1.Value & "', '" & txtcsmonth.Text & "', '" & txtccity.Text & "', '" & txtaddress.Text & "', '" & txtcaddress.Text & "', '" & txtcemail.Text & "', " & txtcphone.Text & ", '" & txtcwebsite.Text & "', '" & txtcontactname.Text & "', '" & txtcontactsurname.Text & "', '" & txtcontactoccupation.Text & "', " & txtcontactphone.Text & ", " & txtcontactcell.Text & ", '" & txtcontactemail.Text & "', " & x * t & ")")

[akhileshbc]

VALUES (" & txtcclientnumber.Text & ", " & cmbs.Text & ", " & txtccopies.Text &

You are missing this '(single quotes).... :)

[aktharshaik]

Hi!
I don't know how u have managed to assign the client number to a variable. but here is one way to do so.
when u execute the query using the Execute function, all the values retrieved are as a recordset.
For Eg:

Dim myGrade as String
Dim myClientNo as Long

myName = deSub.conn.Execute("SELECT clientgrade FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")(0)
myClientNo = deSub.conn.Execute("SELECT clientgrade, clientnumber FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")(1)

Here (0) and (1) tell which retrieved value is to be used.

Also be careful if any null values are to be retrieved and handle the error respectively.

[AndreRet]

With so many "'" and txt...Text values you are certainly prone for errors. I would personally settle for the following -

ri!clientname = txtClientName.text
'Follow this with all your textboxes, it is much easier to control.
ri.Update

If you do understand what I mean by this, let me know and I will elaborate further.

[Israelsimba]

AndreRat..i was using that method before ut it was giving me an error....multiple steps generated an error and i had to move to the insert statement...
thanx anyway

[AndreRet]

When you get the multiple steps error it normally refers to the TYPE of data you are trying to update - i.e. You are trying to add text to a field that is set for a date entry, or text into a numerical field etc.

Check your database for the type of entries you are adding, and try again. Remeber that data like dates for instance is very sensitive about the way they are added. You can for example not add "December 2009" in place of "12/2009. This will generate an error.

I hope this solves your problem, but then again, I might be wrong...