Re: Compact SQL Database and ADO

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



.

Loading