Hello,

Please forgive me if I'm asking this question in the wrong Newsgroup, or even the wrong
web forum. I'm trying to update a recordset object after making edits to a particular
record. Here is what my code looks like:

dtmNewDate = DateSerial(1997, 2, 3)

Set AConnection = CreateObject("ADODB.Connection")
strConnection = "Driver={DBISAM 4 ODBC Driver}; CatalogName=S:\RAPID\Z998\2008;
ReadOnly=False"
Aconnection.Mode = adModeReadWrite
AConnection.Open strConnection

'create SQL statement to be run in order to populate the recordset
strSQLEmployeeBDate = "SELECT * FROM Z998EMPL WHERE state = 'NY'"
'Create Recordset object
Set rsRecSet = CreateObject("ADODB.Recordset")
rsRecSet.LockType = 2
rsRecSet.Open strSQLEmployeeBDate, AConnection


While Not rsRecSet.EOF
 
 'rsRecSet.Fields("BIRTHDATE").value = dtmNewDate
 rsRecSet.Update
 rsRecSet.MoveNext

Wend

When I try to execute this code, I receive the following error when the update function is
reached: "DBISAM Engine Error 11949 SQL Parsing Error - Expected ( but instead found = in
UPDATE SQL statement at line 1, column 336"

I'm at a loss trying to figure out what's causing this error. Any ideas on what might be
causing it, or where I can look for answers? Thanks for any help!

Pete,

<< Please forgive me if I'm asking this question in the wrong Newsgroup, or
even the wrong web forum. I'm trying to update a recordset object after
making edits to a particular record. Here is what my code looks like: >>

What development environment are you using ?  Visual Studio 6, or Visual
Studio 2005/2008 ?  Normally with VB code and the ODBC driver, you should
not use the ADODB objects to access DBISAM because the ADO layer is very
unreliable.  Instead, see this link for more information on how to set up a
connection to use ODBCDirect:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=14&msg=697&page=1#msg697

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, thanks for your guidance! One question though, when I create my connection object
what should the code look like?

For ADO using VBScript it looks like this:

Set AConnection = ADO.CreateADOConnection

Would the ODBC equivalent look like this?

Set AConnection = ODBC.CreateODBCConnection

To answer your question, I am working in Test Complete, which is an automation tool that
supports VBScript.

Thanks again for your help!


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Pete,

<< Please forgive me if I'm asking this question in the wrong Newsgroup, or
even the wrong web forum. I'm trying to update a recordset object after
making edits to a particular record. Here is what my code looks like: >>

What development environment are you using ?  Visual Studio 6, or Visual
Studio 2005/2008 ?  Normally with VB code and the ODBC driver, you should
not use the ADODB objects to access DBISAM because the ADO layer is very
unreliable.  Instead, see this link for more information on how to set up a
connection to use ODBCDirect:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=14&msg=697&page=1#msg697

--
Tim Young
Elevate Software
www.elevatesoft.com

Pete,

<< Tim, thanks for your guidance! One question though, when I create my
connection object what should the code look like? >>

You can see an example at that link that I gave you.

<< To answer your question, I am working in Test Complete, which is an
automation tool that supports VBScript. >>

Hmm, then you may not be able to use ODBCDirect.  You're going to have to
ask them if it is possible, but I doubt that it is.

You can find out more information on ODBCDirect here:

http://msdn.microsoft.com/en-us/library/aa266543(VS.60).aspx

I just tried your code here with a different table, and it seems to work
fine with the table that I'm using and Visual Studio 6 (VB):

Private Sub Command1_Click()
dtmNewDate = DateSerial(1997, 2, 3)

Set AConnection = CreateObject("ADODB.Connection")
strConnection = "Driver={DBISAM 4 ODBC Driver};
CatalogName=c:\windows\temp;ReadOnly=False"
AConnection.Mode = adModeReadWrite
AConnection.Open strConnection

'create SQL statement to be run in order to populate the recordset
strSQLEmployeeBDate = "SELECT * FROM customer WHERE state = 'FL'"
'Create Recordset object
Set rsRecSet = CreateObject("ADODB.Recordset")
rsRecSet.LockType = 2
rsRecSet.Open strSQLEmployeeBDate, AConnection


While Not rsRecSet.EOF

 rsRecSet.Fields("LastInvoiceDate").Value = dtmNewDate
 rsRecSet.Update
 rsRecSet.MoveNext

Wend

End Sub

The table's structure is this:

CREATE TABLE IF NOT EXISTS "customer"
(
  "CustNo" AUTOINC,
  "Company" VARCHAR(30),
  "Addr1" VARCHAR(30),
  "Addr2" VARCHAR(30),
  "City" VARCHAR(15),
  "State" VARCHAR(20),
  "Zip" VARCHAR(10),
  "Country" VARCHAR(20),
  "Phone" VARCHAR(15),
  "FAX" VARCHAR(15),
  "TaxRate" FLOAT,
  "Contact" VARCHAR(20),
  "LastInvoiceDate" TIMESTAMP,
PRIMARY KEY ("CustNo") COMPRESS FULL
LOCALE CODE 0
);

Can you send me the structure of the table that you're trying to update ?

--
Tim Young
Elevate Software
www.elevatesoft.com