copying from table 1 inserting in table 2

gilgalbiblewhee · #1 (**permalink**) 09-15-04, 16:37

I have the following code but this is inserting manually. I want to make a query from table 1 and automatically enter the records into table 2. What needs to be modified?

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<!--#include file="adovbs.inc"-->
<!--#include file="pagetop.txt"-->
<!--#include file="isadmin.inc"-->
<%
if isAdmin then

   set rs = Server.CreateObject("ADODB.Connection")
    rs.Open "kjv"

    set linkSet = Server.CreateObject("ADODB.RecordSet")
    linkSet.Open "select * from results order by id desc", rs, adOpenStatic, adLockOptimistic, adCmdText
    linkSet.MoveFirst
    if linkSet.EOF then
        p_link_id = 1
    else
        p_id = linkSet("id") + 1
    end if

    p_link_id_in = Request.querystring("p_id")
    if p_link_id_in <> "" then
        linkSet.Find "id = " & p_link_id_in
    end if
    if p_link_id_in = "" or linkSet.EOF then
        p_book = ""
        p_link_desc = ""
        p_link_url = ""
    else
        p_book = linkSet("book")
        p_link_desc = linkSet("book_title")
        p_link_url = linkSet("book_spoke")
        p_link_id = p_link_id_in
        p_existing = p_link_id_in
    end if

    linkSet.Close
    set linkSet = Nothing
%>
    <CENTER>
    <FORM ACTION="edit_links_rec_action.asp" METHOD="post">
    <INPUT TYPE="hidden" NAME="p_existing" "
        VALUE="<%=p_existing%>">
    <h2>Edit Link</h2>
    <TABLE>
    <TR>
        <TD>Link ID</TD>
        <TD>
            <INPUT TYPE="hidden" NAME="p_link_id" VALUE="<%= p_link_id %>">
            <%= p_link_id %>
        </TD>
    </TR>
    <TR>
        <TD>Book #</TD>
        <TD><INPUT TYPE="text" NAME="p_name" SIZE="25" 
               VALUE="<%=p_book%>">
    </TR>
    <TR>
        <TD>Link Description</TD>
        <TD><INPUT TYPE="text" NAME="p_desc" SIZE="50" 
               VALUE="<%=p_link_desc%>" maxlength=MAXLENGTH="255">
    </TR>
    <TR>
        <TD>Link URL:  http://</TD>
        <TD><INPUT TYPE="text" NAME="p_url" SIZE="50" 
                VALUE="<%=p_link_url%>" maxlength=MAXLENGTH="100">
    </TR>

    </TABLE>

    <INPUT TYPE="submit">

    </form>
    </CENTER>
<%
    rs.Close
    set rs = Nothing
else

    Response.Write "You do not have access to this page."

end if
%>
</BODY>
</HTML>

rokslide · #2 (**permalink**) 09-22-04, 20:01

if you want it to happen automatically then you need to move to something other then Access that supports triggers...

look up database triggers on google and see if it makes sense and would do what you want. otherwise explain more about what you mean by automatic.

gilgalbiblewhee · #3 (**permalink**) 09-22-04, 23:11

An array index is out of range. What does that mean?

Quote:

Request object error 'ASP 0105 : 80004005'

Index out of range

/kjvresp_action.asp, line 19

An array index is out of range.

19 is in bold:

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<%    Response.buffer = TRUE= true   %>
<!--#include file="adovbs.inc"-->
<!--#include file="isadmin.inc"-->
<%
if isAdmin then
    set outpostDB = Server.CreateObject("ADODB.Connection")
Private Function GetConnectionString()
    GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath("kjv") & ";" & _
                "UID=;PWD=;"
End Function
    outpostDB = GetConnectionString()
    set deleteSet = Server.CreateObject("ADODB.RecordSet")
    deleteSet.Open "select * from results order by res_id", outpostDB, adOpenDynamic, adLockPessimistic, adCmdText
    p_thisDelete = 1
    while not deleteSet.EOF
        if cint(deleteSet("res_id")) = cint(Request.form("p_delete").item(p_thisDelete))  then
            deleteSet.Delete
            deleteSet.Update
            if p_thisDelete < Request.form("p_delete").Count then
                p_thisDelete = p_thisDelete + 1
            end if
        end if
        deleteSet.MoveNext
    wend
    deleteSet.Close
    set deleteSet = Nothing


    set outpostDB = Nothing

    Response.Clear
    Response.Redirect "http://127.0.0.1/kjvresp.asp"
else

    Response.Write "You do not have access to this page."

end if
%>
</BODY>
</HTML>

rokslide · #4 (**permalink**) 09-23-04, 00:48

firstly,.. you have a function embedded inside your code,... not a good way of doing it...

Array index out of ranges means you are trying to access a location in an arrya that does not exist... for example your array has 5 entrys and you are asking for entry 6 or 7 or 8....

gilgalbiblewhee · #5 (**permalink**) 09-23-04, 19:22

where am I asking that? (6, 7, 8...)

rokslide · #6 (**permalink**) 09-23-04, 19:32

I suspect it is here

Code:

cint(Request.form("p_delete").item(p_thisDelete))

Request.form("p_delete") is technically an array. You are asking for item p_thisDelete of the array. p_thisDelete starts off being 1 but you increment it so it will increase. it may increase until it extends beyond the bounds of your array.

Response.write out the value of this variable inside your loop and see what value it is.

You will need to remove this line

Code:

<%    Response.buffer = TRUE= true   %>

to see the results (btw it should be <%Response.buffer = TRUE%>)

Internet Services

Web Development

Digital Marketing

Consumer Tech