hi...im trying to send data from one table to another in the same database...i think i have a syntax error somewhere in the sql statement, but i can't find it...here is what i have...im using asp and access as my db...

<% @ Language="VBScript" %>
<% Option Explicit %>

<%
Dim connection
Dim name
Dim sSQL
Dim sConnString

sSQL = "Insert into users_tbl select name from (select name from tempusers_tbl tb where NOT EXISTS (select null from users_tbl where name = tb.name))"

Set connection = Server.CreateObject("ADODB.connection"

sConnString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath("users.mdb" & ";"

connection.Open sConnString

connection.execute(sSQL)

connection.Close
Set connection = Nothing
%>


-----thanks for your help-----

what error are you getting back?

Personally, just to keep the code clear I would have created a string with the info you want to insert and then build the insert sql using that, much simpler to see where it is failing...

also, to check the line that you have built, remove your execute line and then add the following:

<%=Response.write(sSql)%>

then you can see what you are trying to insert.

Regards
Vince

DMX Talkzone Manager

Visit my home: www.chez-vince.com
(Now completely CSS based and bye bye to all tables!)

VBScript | ASP | HTML | CSS | SQL | Oracle | Hosting