Re: Compact SQL Database and ADO

Hi Daniel

I've since made this change to the code for 'N' in front of the string.

..CommandText = "INSERT INTO tblTest(fldIDField, fldTextField) VALUES
(@plngID, N'@pstrText')"

Compile OK but still crashes VB.

Thanks
Warren

"Warren" wrote:

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: Insert date into a datetime column in Visual Basic 2008
    ... Dim cmdString As String ... Dim dte As System.DateTime ... wholly contained within the SQL executed on the server. ... ADO, not ADO.Net, so I can't provide a working example for you). ...
    (microsoft.public.data.ado)
  • Re: Compact SQL Database and ADO
    ... making sure that the MDAC components are all in sync using the Component Checker: ... 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: IIF Function
    ... I've been doing this since the days of VB6 and ADO, ... > Turning Option Strict Off is extremely bad practice, ... You're treating BOTH a string and possibly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Source size and how to change
    ... It should not truncate sql string. ... It must be happening in the ADO call because the actual ...
    (microsoft.public.data.ado)
  • Getting Started
    ... I'm trying to get to grips with MS SQL and ADO at the same time. ... got a working method of adding a record to a table with unique ... function TMSSQLImporter.AddLocation(const Location: string): Integer; ...
    (borland.public.delphi.database.ado)