Re: Compact SQL Database and ADO

Hi Daniel

I've run the component checker as suggested and there are some mismatched
file versions. The verisons on the test computer are a little newer than the
expected version but I'd say this is due to a couple of Windows Update
security fixes I've installed for ADO 2.8 E.g. msado15.dll expected
ver:2.81.1117.0 installed ver:2.81.1128.0.

I couldn't get the code to run with the 'N' in front of the text due to a
syntax error.

Set prmText = .CreateParameter("pstrText", adVarChar, adParamInput, 100,
"TEST 3")

I tried "N'TEST 3'" but this still failed.

Thanks
Warren

"Daniel Crichton" wrote:

This suggests an issue with ADO / OLE DB then, as those will be causing an
exception that VB is unable to handle. First thing I'd suggest doing is
making sure that the MDAC components are all in sync using the Component Checker:

http://support.microsoft.com/kb/307255/

and look for any mismatches.

What happens when you change the working SQL statements to the ones I
provided with the N before the strings so that they're using Unicode? Do you
still get the same crash?

I haven't used SQL CE with ADO myself, I do still write a lot of VB6 apps
with ADO to SQL Server 2000 and 2005 and haven't come across this, so it
does look like it's probably an issue with the SQL CE OLE DB drivers.

Dan


Warren wrote on Wed, 7 Jan 2009 14:03:34 -0800:

Hi Daniel

I get a Windows dialog box titled 'Program Error' and it says 'VB6.exe
has generated errors and will be closed by Windows. You will need to
restart the program. An error log is being created.'

This occurs on .Execute statement.

Nothing gets written to the event log and I can't find where the dump
file (if any) is written to.

fldTextField is defined as nvarchar(100).

Thanks
Warren

"Daniel Crichton" wrote:

>> Warren wrote on Tue, 6 Jan 2009 20:05:01 -0800:

>>> Hi

>>> I'm having a problem inserting data into my database using an ADO
>>> Command object and parameters. Works on Vista but fails on Win2K
>>> and
>>> WinXP.

>>> VB6 SP6 application
>>> ADO 2.8 SP1
>>> Windows 2000 Pro
>>> SQL Server 3.5 Compact 3.5 SP1 Database
>>> SQL Server Compact 3.5 Service Pack 1 Redistributable for Windows
>>> Desktop

>>> Code as follows:

>>> Dim cmdCommand As ADODB.Command
>>> Dim prmID As ADODB.Parameter
>>> Dim prmText As ADODB.Parameter

>>> Set mconSQLCompact = New ADODB.Connection mconSQLCompact.Open
>>> "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
>>> Source=C:\Database.sdf;" 'SSCE:Database Password='password';"
>>> mconSQLCompact.CursorLocation = adUseServer
>>> Set cmdCommand = New ADODB.Command
>>> With cmdCommand .CommandTimeout = 180 .CommandType =
>>> adCmdText
>>> Set .ActiveConnection = mconSQLCompact .CommandText =
>>> "INSERT
>>> INTO tblTest(fldIDField, fldTextField) VALUES (@plngID, @pstrText)"
>>> Set prmID = .CreateParameter("plngID", adInteger, adParamInput)
>>> .Parameters.Append prmID
>>> Set prmText = .CreateParameter("pstrText", adVarWChar,
>>> adParamInput, 100)
>>> .Parameters.Append prmText prmID.Value = 1
>>> prmText.Value = "Test 1"
>>> .Execute , , adExecuteNoRecords
>>> End With mconSQLCompact.Close
>>> Set mconSQLCompact = Nothing
>>> Set prmID = Nothing
>>> Set prmText = Nothing
>>> Set cmdCommand = Nothing

>>> The INSERT statement assigned to the CommandText property causes VB6
>>> to crash. If I don't write to the nVarChar field and only to the int
>>> field it works. I've tried changing the field type to nText but VB6
>>> still crashes.
>>> I've also tried using adLongVarWChar for the parameter data type but
>>> no go.

>> What error message does VB6 give you?


>>> The following statements work when assigned to the CommandText
>>> property.

>>> INSERT INTO tblTest(fldIDField, fldTextField) VALUES (1, 'Test 1')
>>> DELETE FROM tblTest
>>> UPDATE tblTest SET fldTextField='Test 1' WHERE fldIDField=1

>> That's not the same as what you're running though, when you specify
>> adVarWChar for the parameter the equivalent SQL would be

>> INSERT INTO tblTest(fldIDField, fldTextField) VALUES (1, N'Test 1')

>> UPDATE tblTest SET fldTextField=N'Test 1' WHERE fldIDField=1

>> Notice the N before the string to denote that it's Unicode (ie Wide).


>> Is the underlying table defined with the fldTextField column as
>> nvarchar?

>> If you can provide the error message you're seeing it might help to
>> determine what is going on.

>> --
>> Dan




.

Relevant Pages

  • Re: Compact SQL Database and ADO
    ... I've since made this change to the code for 'N' in front of the string. ... What happens when you change the working SQL statements to the ones I ... I haven't used SQL CE with ADO myself, I do still write a lot of VB6 apps ...
    (microsoft.public.data.ado)
  • Re: Cursor conflict
    ... Stefan Berglund wrote: ... Do you use VB6? ... Yes I'm using VB 6 and use ADO reference Microsoft ActiveX Data Objects ... But when I work with SQL 2000 that not used SP3, ...
    (microsoft.public.vb.general.discussion)
  • Re: Using parameters with the low level ADO API, trying to avoid the memory leaks.
    ... MS ADO passes SQL directly to OLEDB provider. ...
    (borland.public.delphi.database.ado)
  • Re: Programmatically changing a SQL view in a ADP
    ... One of the differences between DAO and ADO is that DAO combines data ... there isn't a way to do this successfully with ADOX. ... But T-SQL, SQL Server's ... Dim cn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Smartest way toa add records manually
    ... class modules to represent tables, collections, etc., and started isolating ... we've transitioned from Access as a back end to SQL Server as ... Then I discovered why I transitioned to ADO. ...
    (microsoft.public.access.adp.sqlserver)
Loading