INSERT Data from SELECT query

Discussion in 'ASP General' started by paulmitchell507, Aug 6, 2008.

  1. I think I am attempting a simple procedure but I just can't figure out
    the correct syntax. My asp (classic) page runs a SELECT query to
    obtain dates and ID's from 2 tables

    uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
    (((calendar.cal_Date) Between [holiday_tbl].[startdate] And
    [holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

    This works fine.

    What I would like to do next is insert the returned values (cal_date)
    and (holiday_ID) into a seperate table called holiday_dates. I am
    happy to insert the results one recordset at a time, but I don't know
    how to do it. I know that uSQL is returning results

    'Loop until we've hit the EOF
    Do Until objRS.EOF = True
    response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
    objRS.movenext
    Loop

    I would appreciate any help
     
    paulmitchell507, Aug 6, 2008
    #1
    1. Advertising

  2. paulmitchell507 wrote:
    > I think I am attempting a simple procedure but I just can't figure out
    > the correct syntax. My asp (classic) page runs a SELECT query to
    > obtain dates and ID's from 2 tables
    >
    > uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
    > (((calendar.cal_Date) Between [holiday_tbl].[startdate] And
    > [holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID
    >
    > This works fine.


    But it's non-standard syntax. Better would be (and not the use of table
    aliases:
    uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
    "INNER JOIN holiday_tbl As h ON " & _
    "c.cal_Date Between h.startdate AND h.enddate " & _
    "WHERE Email_sent=0 AND Staff_ID=" & Staff_ID

    Better yet would be the use of a parameter token to pass the staff_id value.
    I would do it like this:

    uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
    "INNER JOIN holiday_tbl As h ON " & _
    "c.cal_Date Between h.startdate AND h.enddate " & _
    "WHERE Email_sent=0 AND Staff_ID=?"

    dim cmd:set cmd=createobject("adodb.command")
    cmd.CommandText = uSQL
    set cmd.ActiveConnection = YourOpenConnectionObject
    cmd.CommandType = 1 'adCmdText
    Set objRS = cmd.Execute(,array(Staff_ID))
    etc.

    >
    > What I would like to do next is insert the returned values (cal_date)
    > and (holiday_ID) into a seperate table called holiday_dates. I am
    > happy to insert the results one recordset at a time, but I don't know
    > how to do it. I know that uSQL is returning results
    >
    > 'Loop until we've hit the EOF
    > Do Until objRS.EOF = True
    > response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
    > objRS.movenext
    > Loop
    >
    > I would appreciate any help


    You left out some information that would have been helpful:
    -the datatypes of the cal_date and holiday_ID fields
    -the names and datatypes of the fields you want to insert these values into

    You also left out some information that should be provided with every
    database-related question you ask (think about creating a template with this
    info):
    database type and version
    The parentheses in your WHERE clause lead me to believe you used the Access
    Query Builder to construct this query, but this is just a guess - please
    don't make us guess. :)

    Is this holiday_dates table in the same database? if so, there is absolutely
    no need to open and loop through a recordset here. a simple INSERT...SELECT
    statement will do this job nicely. Here is the sql statement:

    iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
    "SELECT cal_date, holiday_ID from Calendar As c" & _
    "INNER JOIN holiday_tbl As h ON " & _
    "c.cal_Date Between h.startdate AND h.enddate " & _
    "WHERE Email_sent=0 AND Staff_ID=?"

    dim cmd:set cmd=createobject("adodb.command")
    cmd.CommandText = uSQL
    set cmd.ActiveConnection = YourOpenConnectionObject
    cmd.CommandType = 1 'adCmdText
    cmd.Execute ,array(Staff_ID)

    As to why parameter tokens are better than dynamic sql, here is my canned
    reply:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    SQL Server:

    http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&




    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Aug 6, 2008
    #2
    1. Advertising

  3. On Aug 6, 12:14 pm, "Bob Barrows [MVP]" <>
    wrote:
    > paulmitchell507 wrote:
    > > I think I am attempting a simple procedure but I just can't figure out
    > > the correct syntax.  My asp (classic) page runs a SELECT query to
    > > obtain dates and ID's from 2 tables

    >
    > > uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
    > > (((calendar.cal_Date) Between [holiday_tbl].[startdate] And
    > > [holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

    >
    > > This works fine.

    >
    > But it's non-standard syntax. Better would be (and not the use of table
    > aliases:
    > uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
    > "INNER JOIN  holiday_tbl As h ON " & _
    > "c.cal_Date Between  h.startdate AND h.enddate " & _
    > "WHERE Email_sent=0 AND Staff_ID=" & Staff_ID
    >
    > Better yet would be the use of a parameter token to pass the staff_id value.
    > I would do it like this:
    >
    > uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
    > "INNER JOIN  holiday_tbl As h ON " & _
    > "c.cal_Date Between  h.startdate AND h.enddate " & _
    > "WHERE Email_sent=0 AND Staff_ID=?"
    >
    > dim cmd:set cmd=createobject("adodb.command")
    > cmd.CommandText = uSQL
    > set cmd.ActiveConnection = YourOpenConnectionObject
    > cmd.CommandType = 1 'adCmdText
    > Set objRS = cmd.Execute(,array(Staff_ID))
    > etc.
    >
    >
    >
    > > What I would like to do next is insert the returned values (cal_date)
    > > and (holiday_ID) into a seperate table called holiday_dates.  I am
    > > happy to insert the results one recordset at a time, but I don't know
    > > how to do it.  I know that uSQL is returning results

    >
    > > 'Loop until we've hit the EOF
    > > Do Until objRS.EOF = True
    > > response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
    > > objRS.movenext
    > > Loop

    >
    > > I would appreciate any help

    >
    > You left out some information that would have been helpful:
    > -the datatypes of the cal_date and holiday_ID  fields
    > -the names and datatypes of the fields you want to insert these values into
    >
    > You also left out some information that should be provided with every
    > database-related question you ask (think about creating a template with this
    > info):
    > database type and version
    > The parentheses in your WHERE clause lead me to believe you used the Access
    > Query Builder to construct this query, but this is just a guess - please
    > don't make us guess. :)
    >
    > Is this holiday_dates table in the same database? if so, there is absolutely
    > no need to open and loop through a recordset here. a simple INSERT...SELECT
    > statement will do this job nicely. Here is the sql statement:
    >
    > iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
    > "SELECT cal_date, holiday_ID from Calendar As c" & _
    > "INNER JOIN  holiday_tbl As h ON " & _
    > "c.cal_Date Between  h.startdate AND h.enddate " & _
    > "WHERE Email_sent=0 AND Staff_ID=?"
    >
    > dim cmd:set cmd=createobject("adodb.command")
    > cmd.CommandText = uSQL
    > set cmd.ActiveConnection = YourOpenConnectionObject
    > cmd.CommandType = 1 'adCmdText
    > cmd.Execute ,array(Staff_ID)
    >
    > As to why parameter tokens are better than dynamic sql, here is my canned
    > reply:
    > Your use of dynamic sql is leaving you vulnerable to hackers using sql
    > injection:http://mvp.unixwiz.net/techtips/sql....sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >
    > See here for a better, more secure way to execute your queries by using
    > parameter markers:http://groups-beta.google.com/group/microsoft.public.inetserver.asp.d...
    >
    > Personally, I prefer using stored procedures, or saved parameter queries
    > as
    > they are known in Access:
    >
    > Access:http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvO...
    >
    > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYx...
    >
    > SQL Server:
    >
    > http://groups.google.com/group/microsoft.public.inetserver.asp.genera...
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"


    Wow! what a fantastic reply.
    I will follow your template for future posts..of which there will be
    many!
    I have taken your advice and re-coded all me asp pages to use saved
    parameter queries.
    You guessed correctly, I have an access 2k database.
     
    paulmitchell507, Aug 7, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul
    Replies:
    0
    Views:
    649
  2. Learner
    Replies:
    1
    Views:
    1,004
    Marina Levit [MVP]
    Jan 30, 2006
  3. Luqman
    Replies:
    1
    Views:
    645
    Eliyahu Goldin
    Jul 10, 2007
  4. select query data type mismatch

    , Jul 1, 2008, in forum: ASP General
    Replies:
    3
    Views:
    322
    Daniel Crichton
    Jul 2, 2008
  5. palmiere
    Replies:
    1
    Views:
    431
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page