Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 3rd, 2004, 02:36 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old June 3rd, 2004, 02:40 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hello!!!

db.execute returns a recordset to you..

just read the data from it, then construct the second sql and execute it...

HTH

Gonzalo
Reply With Quote
  #3 (permalink)  
Old June 3rd, 2004, 02:44 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old June 3rd, 2004, 02:49 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

ok.. are you using dao or ado??

HTH

Gonzalo
Reply With Quote
  #5 (permalink)  
Old June 3rd, 2004, 02:54 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old June 3rd, 2004, 02:58 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
Reply With Quote
  #7 (permalink)  
Old June 3rd, 2004, 03:16 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old June 3rd, 2004, 03:19 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

ahhhhhh.. are you coding inside access???

HTH

Gonzalo
Reply With Quote
  #9 (permalink)  
Old June 3rd, 2004, 03:22 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old June 3rd, 2004, 03:36 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Put together 2 selects results in a single one romulo.petri SQL Language 3 March 6th, 2008 03:43 AM
Check two string values on a single variable?? ramesh.kumarm XSLT 2 June 29th, 2006 04:29 AM
How to send multiple columnsí data of a single row swadhinm ASP.NET 1.x and 2.0 Application Design 2 June 1st, 2005 08:59 AM
How to store single value in variable? sacred21 Classic ASP Basics 1 December 29th, 2004 05:25 AM
send variable value with (?) doosti HTML Code Clinic 1 November 10th, 2004 03:49 PM



All times are GMT -4. The time now is 09:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.