[RESOLVED] db Error with Insert into table command

[drawlings]

I keep getting the following error message

-2147217904
No Value given for one or more required parameters

The code I am using is as follows

Dim rs As New ADODB.Recordset

rs.Open "insert into data_delete(ID,FullName,ShortName,Contact,OwnerCo,Pref,Fax1,Fax2,Telex1,Telex2,Memonic1,Memonic2,Ema il,Groups,CortexAccountNo) Values(DataRange.ID,datarange.Fullname,datarange.shortname,datarange.contact,datarange.OwnerCo,datar ange.Pref,datarange.fax1,datarange.fax2,datarange.telex1,datarange.telex2,datarange.memonic1,dataran ge.menonic2,datarange.Email,datarange.groups,datarange.cortexaccountno)", CONNECTIONSTRING, adOpenDynamic, adLockOptimistic

The basic idea behind this is to copy a deleted record into another table, but I am only bring across certain fields



Thanks

[Static]

its your Values.. you are passing in field names and not data..
you either need to pass in specific data or do a select...

"INSERT INTO data_delete (ID,Fullname,ShortName) VALUES (1,'John Doe','John')"

or if you want to use variables

"INSERT INTO data_delete (ID,Fullname,ShortName) VALUES (" & ID & ",'" & FullName & "','" & ShortName & "')"

or if you want Fields

"INSERT INTO data_delete (ID,Fullname,ShortName) VALUES (SELECT ID,FullName,ShortName FROM OTHER TABLE WHERE something = somethingelse)"

Note: I am not possitive on the SQL for the last one.. its been a while since I have written and insert that selects.. but the idea is there

[Hack]

You also have fields broken up in your open statement.

Take a look. Your database is seeing email as ema
il

It is also seeing datarange.Pref as datar ange.Pref

and it is seeing datarange.menonic2 as dataran ge.menonic2

Put the whole thing in a string, and pass the string to your recordset object. It makes things easier to read and debug. Example:

Dim rs As New ADODB.Recordset
Dim sSQL As String
sSQL = "insert into data_delete(ID,FullName,ShortName,Contact,OwnerCo,Pref,Fax1,Fax2,Telex1,"
sSQL = sSQL & "Telex2,Memonic1,Memonic2,Email,Groups,CortexAccountNo) "
sSQL = sSQL & "Values(DataRange.ID,datarange.Fullname,datarange.shortname,datarange.contact,datarange.OwnerCo, "
sSQL = sSQL & "datarange.Pref,datarange.fax1,datarange.fax2,datarange.telex1,datarange.telex2,datarange.memonic1, "
sSQL = sSQL & "datarange.menonic2,datarange.Email,datarange.groups,datarange.cortexaccountno) "
rs.Open sSQL, CONNECTIONSTRING, adOpenDynamic, adLockOptimistic

[drawlings]

Thanks for your suggestions, but none have worked and I am still get the same problem.

i need a record from Datarange and insert it in to data_delete. I only want to take certain records from DataRange, but I will take all if thats the only way

Thanks

[Static]

then u need the select

if data_delete has just the fields you want to insert into then this will work fine
(the names have to match...)

"INSERT INTO data_Delete SELECT ID,FullName,ShortName,Contact FROM DataRange WHERE something = somethingelse"

if the names dont match then u need to specify the fields

"INSERT INTO data_Delete (IDfield,xFullName,xShortname,xContact) SELECT ID,FullName,ShortName,Contact FROM DataRange WHERE something = somethingelse"


understand?

[drawlings]

[A51g]Static,

Thank you , that worked using the following code from your suggestion

"INSERT INTO data_Delete (IDfield,xFullName,xShortname,xContact) SELECT ID,FullName,ShortName,Contact FROM DataRange WHERE something = somethingelse"

Thanks

Dan

[Static]

youre welcome!!... (dont forget to mark the thread resolve... Thread Tools>Mark Resolved)