Unable to write data using ADODB

[DarkestHour]

Hey all, thanks for reading this and trying to help.

The problem i am having is when trying to insert a new line into my database using ADODB.

Here is the example code where the problem occurs.

Func _Datapop()
$DBFc = ObjCreate('ADODB.Connection') ; initializes ADODB connection
$DBFc.Open ('Provider=vfpoledb.1;Data Source="C:\Documents and Settings\user\My Documents\Database";Password="";Collating Sequence=MACHINE') ; sets VFP connection and data location
$DBFr = ObjCreate('ADODB.Recordset') ; initializes ADODB record processing
$DBFr.CursorType = 1 ; 
$DBFr.LockType = 3 ;
$DBFr.Open ("SELECT TOP 1 recno from datapop order by ctrdate desc, crttime desc", $DBFc)
$DBFr.Execute ("INSERT INTO datapop (recno, partno, jobno, opno, wc, ctrdate, crttime, crtoper, itemno, req, method, loccls, result, notes, rev) VALUES ('3', '11', '111', '11', 'TestWC', {01/10/11}, '7:53 AM', 'Oper', '01', 'Testing', 'TestMethod', 'LOC / Class', 'Result', 'Notes', 'D')")
$DBFc.Close

Now i've tried opening the Connection first but it does not allow me to just pass the open command without a command string, i've tried putting the INSERT statement into the open command text but it just fails in the same way.

My com error handler returns this as it's run. I've checked and it does not write the data.

Lastdllerror: 14007

and error number: 80020006 with a description of Unkown Name.

I can select and read data just fine, but for the life of me i cant get to it write the data.

Thanks for any help you can provide!

-Kevin

[kylomas]

Kevin,

Change your SQL insert statement to use the connection object, not the recordset object.

What is the purpose of the recodset, anyway, and, the provider string look funny (at least something that I have not run across in my limited experience).

kylomas

Also:

why are you trying to insert into "datapop (the function name)...you should be trying to insert into the database (which apears to be named "database")

Lastly, Kevin, do not be offended but I don't see how this can be running at all. What does your error handler look like?

You are either way over my head or pokin and hopin with examples.

Edited January 11, 2011 by kylomas

[DarkestHour]

Oh great! That Worked like a charm.

The select and insert statement calls the table Datapop.

The provider string links to a Free Table Directory rather then a single database file. This lets me organize things by file structure on my hard drive. The Collating Sequence statement is there because it provides the best performance when working with this foxpro databases.

As for using RecordSet, elsewhere in this program i use it to manipulate data that is already in my table and so i have it incase i need to add a bit of code for some manipulation of the data.

Again, thank you very much for the assistance!

[kylomas]

Kevin,

I see..way over my head it is than, good luck !!

kylomas

[GEOSoft]

I don't support this UDF any longer but you may get some use from it.

http://dundats.mvps.org/autoit/udf_code.aspx?udf=arrayx

Make sure to download the zip and extract both files to the AutoIt3\Include\ folder. If you try to copy/paste code from the page it won't work since you will be missing the constants file.

[kylomas]

GeoSoft,

Sure you got the right thread, nothing here having to do with arrays.

kylomas

[GEOSoft]

Damn!! Right you are, wrong link. It should be

http://dundats.mvps.org/autoit/udf_code.aspx?udf=access

[DarkestHour]

Damn!! Right you are, wrong link. It should be

http://dundats.mvps.org/autoit/udf_code.aspx?udf=access

Good link though. I'll have to see about modifying it to use vfpoledb.1

Thanks