Dean
|
| Posted: 10/16/2005, 12:39 PM |
|
I am new to writing my own sql. I have tried every thing I can think of. I placed this in the page's Before Unload.
I am trying to copy records from one Access table to another (same connection) but another field is needed in the new table. There are multiple records in each table. Here is what I have thus far.
Dim iSQL
Dim ds
set ds = new clsDBAccessConnection
iSQL = "INSERT INTO Inv_Client ( Inventory, Amt, ClientID )" &_
"SELECT Inv_STD.Inventory, Inv_STD.Amt, 2" &_
"FROM Inv_STD;"
ds.open
ds.Execute (iSQL)
ds.close
Where the 2 is, I need that to be a session variable: NewClientID Session("NewClientID")
I already have that defined and I know that works because of another sql function that I have working.
|
|
|
 |
Edd
Posts: 547
|
| Posted: 10/16/2005, 3:15 PM |
|
And this doesn't work?
iSQL = "INSERT INTO Inv_Client ( Inventory, Amt, ClientID )" &_
"SELECT Inv_STD.Inventory, Inv_STD.Amt, " & Session("NewClientID") & _
" FROM Inv_STD;"
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
 |
|
Dean
|
| Posted: 10/16/2005, 3:27 PM |
|
Why yes it does work.
I must of had a comma or something out of wac. I tried substituting different variables with actual amounts and everything.
Works Great!!
Thanks.!!
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/16/2005, 4:21 PM |
|
I actually think the problem was that there was no space before the "FROM" in your SQL and it was presented to the database as
"SELECT Inv_STD.Inventory, Inv_STD.Amt, 2FROM Inv_STD;"
I have been caught a 100 times on that one.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
Dean L Covey
|
| Posted: 10/19/2005, 9:21 PM |
|
I'm stuck again!. Simular procedure to before. I wish there was a way to test the sql command on the desktop or somehow get error messages.
' Write your own code here.
Dim iSQL
Dim ds
set ds = new clsDBAccessConnection
iSQL = "INSERT INTO Customer_Recurr_Set ( Inventory, Amt, [Tax Rate], [Taxing Authority], CustID )" & " SELECT Inv_Client.Inventory, Inv_Client.Amt, Inv_Client.[Tax Rate], Inv_Client.[Taxing Authority], " & Session("CustID") & " FROM Inv_Client WHERE Inv_Client.ClientID)=" & Session("CookieClient")
ds.open
ds.Execute (iSQL)
ds.close
' -------------------------
'End Custom Code
|
|
|
|
Edd
Posts: 547
|
| Posted: 10/20/2005, 4:49 AM |
|
Try Adding after you execute
If ds.Errors.Count > 0 Then
Response.Write iSQL & "<BR>"
Response.Write CCProcessError(ds)
Response.End
End If
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
Dean L Covey
|
| Posted: 10/20/2005, 4:21 PM |
|
Edd is a God that Answers prayers!!
The correct SQL ended up being:
iSQL = "INSERT INTO Customer_Recurr_Set ( Inventory, Amt, [Tax Rate], [Taxing Authority], CustID )" & " SELECT Inv_Client.Inventory, Inv_Client.Amt, Inv_Client.[Tax Rate], Inv_Client.[Taxing Authority], " & Session("CustID") & " FROM Inv_Client WHERE Inv_Client.ClientID = " & Session("CookieClient")
This little tid bit walked me through each error until I got it all.
GREAT!!
|
|
|
|
rhino
|
| Posted: 12/14/2005, 4:29 AM |
|
forgetting spaces when continuing sql over multiple lines is a classic mistake
always a good place to check first when things don't work
|
|
|
|
|
