 |
| VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
June 3rd, 2004, 01:36 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Trying to send SQL results to a single variable
hello folks,
I am trying to create some code which will allow me to run a sql select command, save the results in a variable, and then use that variable to run a sql insert command.
Dim db As Database
Dim strSQL1 As String
Dim strSQL2 As String
' Dim CompanyIdPlaceHolder As String
' Not sure how to use this as of yet.
strSQL1 = "Select max(CompanyID) from Company"
'Contacts is a seperate table linked to Company by the field CompanyID
strSQL2 = "Insert into Contacts (FirstName, LastName, CompanyID)Values " & "(" & conQuote & "No" & conQuote & ", " & conQuote & "Contact" & conQuote & ", " & **results from strSQL1** & ")"
'I am trying to get the results of the first command to be part of the sql command executed here.
db.Execute strSQL1
'db.Execute strSQL2
|
|
June 3rd, 2004, 01:40 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
hello!!!
db.execute returns a recordset to you..
just read the data from it, then construct the second sql and execute it...
HTH
Gonzalo
|
|
June 3rd, 2004, 01:44 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Sorry if this is very basic, but that is exactly the problem that I am having. I don't know what you mean when you say db.execute returns a recordset to me and how I read the data from it.
I have been trying things such as:
CompanyIdPlaceHolder = db.Execute strSQL2
and I keep getting errors. Any additional guidance is helpful!
thanks,
nathaniel
Quote:
quote:Originally posted by gbianchi
hello!!!
db.execute returns a recordset to you..
just read the data from it, then construct the second sql and execute it...
HTH
Gonzalo
|
|
|
June 3rd, 2004, 01:49 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
ok.. are you using dao or ado??
HTH
Gonzalo
|
|
June 3rd, 2004, 01:54 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I am not sure actually. are there major differences between the two?
Quote:
quote:Originally posted by gbianchi
ok.. are you using dao or ado??
HTH
Gonzalo
|
|
|
June 3rd, 2004, 01:58 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
in fact yes... there are to diferents way to connect to the database, and in fact dao only works with access 97, 2000 depending on the version of dao that you are using..
how did you setup your connection to the database??
you have a connection object or use opendatabase????
HTH
Gonzalo
|
|
June 3rd, 2004, 02:16 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
The database is all local (just tables using Access 2000) so I don't think either is in use. Sorry for the limited information; I have just taken over this database (with limited experience) from someone else and we are trying to make some updates to it.
thanks for your help.
Nathaniel
Quote:
quote:Originally posted by gbianchi
in fact yes... there are to diferents way to connect to the database, and in fact dao only works with access 97, 2000 depending on the version of dao that you are using..
how did you setup your connection to the database??
you have a connection object or use opendatabase????
HTH
Gonzalo
|
|
|
June 3rd, 2004, 02:19 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
ahhhhhh.. are you coding inside access???
HTH
Gonzalo
|
|
June 3rd, 2004, 02:22 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I am coding in the VB/code portion of Access. does that help?
Quote:
quote:Originally posted by gbianchi
ahhhhhh.. are you coding inside access???
HTH
Gonzalo
|
|
|
June 3rd, 2004, 02:36 PM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
mmm.. ok.. let's try something..
you can dim a recordset. the execute command will return you a recordset, then you can read the data from it..
sorry but i will not code it for you, i dont have a lot of experience inside access...
but look for it in the help file...
HTH
Gonzalo
|
|
|
