insert and ado....

[dodgyone]

I know that this is totally wrong but it's a starting point to work from ...

SQL ("INSERT INTO Adodc1.PlantechOutput (Cadastre, NewCadastre, Misc) VALUES ('Adodc1.Recordset.Fields("Cadastre&quot', Adodc2.Recordset("NewCadastre&quot, 'Adodc1.Recordset.Fields("Misc&quot&quot

Could someone please exaplin how do create the correct syntax for the above...

Many thanks

 

[daimaou]

hi dodgyone,
That code seems a bit confusing what exactly are you trying to do?
Insert records on a table is that it?

If so just use the execute command on your connection. Lets say your connection is called cn. Then you'd do something like:

cn.Execute "INSERT INTO Your_Table(Fields separated by commas if u want) Values('" & Value1 & "', '" & Value2 & & "', '" & Value3 & "')"

and so on, this should be the way of doing it, I'm not sure what exactly you're after, but I suppose it'd be something like this.
Let me know if u need more help.

 

[sunaj]

Lisen to daimaou, he's right.
But the ADO way of doing it as actually to use the .addnew

e.g.
-----------------------------------------------------
Dim Rst As ADODB.Recordset

Set Rst = New ADODB.Recordset
Rst.Open "MyTable", con, adOpenKeyset, adLockOptimistic, adCmdTable

Rst.AddNew Array("name", "code", "comment&quot, Array(TheName, TheCode, "This was added by the program.&quot
Rst.update
--------------------------------------------------------

Sunaj

 

[dodgyone]

My connection to the DB is...

Adodc3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PlantechAddress + ";Persist Security Info=False"

Adodc3.RecordSource = "PlantechOutput"

Adodc3.Refresh

...and this connects to the DB fine.

My SQL is now...

ResultCadastre = Adodc1.Recordset.Fields("Cadastre&quot
ResultNewCadastre = Adodc2.Recordset("NewCadastre&quot
ResultMisc = Adodc1.Recordset.Fields("Misc&quot

This bit wrong --> Adodc3.Execute <-- &quot;INSERT INTO PlantechOutput (Cadastre,NewCadastre,Misc) VALUES (ResultCadastre,ResultNewCadastre,ResultMisc)&quot;


How can I execute this without having to create the conection variable etc. I'm not very experienced with this and would liked the easiest route possible ;o)

Thank you

 

[sunaj]

Hi,

I think we have to see your declarations (dim).
It looks like your Adodc3 is a recordset. If you want to use .execute, you need to have a connection:

----------------------------------
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.Open MyConnStr
----------------------------------
and then con.Execute(&quot;INSERT INTO ......


Sunaj

 

[dodgyone]

Sorry about this but I've only just started in this area... I'm now getting trouble with MyConnStr ... do I decare this as well in some way?

Dim con As ADODB.Connection
Set con = New ADODB.Connection
MyConnStr = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; + PlantechAddress + &quot;;Persist Security Info=False&quot;
con.Open MyConnStr

'the SQL statement to include the new Cadastre Reference
con.Execute &quot;INSERT INTO PlantechOutput(Cadastre,NewCadastre,Misc) VALUES (ResultCadastre,ResultNewCadastre,ResultMisc)&quot;

Sorry for being a nuisance...

 

[daimaou]

dodgyone, I think you should really make a connection to your DB it'll ease out things a lot.
sunaj has just posted the only code needed so as you can see it's no big deal.
Then just use the Execute or AddNew method (I personally still prefer using Execute, but that's just a personal opinion ).

 

[dodgyone]

But he doesn't mention or show anything about MyConnStr in the example he gave... only mentioned at the .Open stage. I presume that this is the connection string and the example above which I posted uses that but it doesn't work.

I know that all of this is obviously straight forward for you guys but not all of us are so fortunate. It all seems like cryptic messages to me....

Thanks...

 

[dodgyone]

Thanks... I've now solved the connection etc. Now the fields to add will not come up. I've connected to them and made reference to thme as shown below but no joy. Any ideas? It works when I put the results in '' so it must be that causing the problem!


ResultCadastre = Adodc1.Recordset.Fields(&quot;Cadastre&quot
ResultNewCadastre = Adodc2.Recordset(&quot;NewCadastre&quot
ResultMisc = Adodc1.Recordset.Fields(&quot;Misc&quot

con.Execute <-- &quot;INSERT INTO PlantechOutput (Cadastre,NewCadastre,Misc) VALUES (ResultCadastre,ResultNewCadastre,ResultMisc)&quot;

 

[sunaj]

You need to open your recordsets.
if you've made your connection as show above, you don't set the .ConnectionString, but open the recordset with the connection:


Dim con As ADODB.Connection
Dim rst as ADODB.recordset
'Open connection
con = New ADODB.Connection
con.Open MyConnStr

'Open a recordset with data from the database
set rst= new ADODB.recordset
rst.open &quot;SELECT * FROM MyTable WHERE Cadastre LIKE '%H'&quot;, con, adOpenStatic

'Insert values into Table PlantechOutput from the recordset we just opened.
con.execute &quot;INSERT INTO PlantechOutput (Cadastre) VALUES (&quot; & rst.fields(&quot;Misc&quot & &quot&quot;
------------------------------------------------------------
Sunaj

 

[daimaou]

ok, you're on the right direction now, I've seen what you're doing wrong, replace your Execute statement by:

con.Execute &quot;INSERT INTO PlantechOutput (Cadastre,NewCadastre,Misc) VALUES ('&quot; & ResultCadastre & &quot;', '&quot; & ResultNewCadastre & &quot;', '&quot; & ResultMisc & &quot;')&quot;

This should do it. I suppose all this values are text right? If any is of number format you won't need the &quot;'&quot;. If they are all text this will work

Let me know if u are still having problems

 

[dodgyone]

Thanks for that... I finally managed to solve the problem a little while ago (not passing the variable across correctly from somewhere else and the SQL syntax mistake)... thanks for pointing me in the right direction.

Cheers everyone...