Re: Compact SQL Database and ADO

The N part wasn't intended to be used there - it was intended to be used in
your static string testing that you said worked in VB6 on the machines that
were having problems.

I don't have any other ideas - you might well just be hitting upon a bug in
the ADO/OLE DB drivers for SQL CE in which case you need to call MS PSS for
support - if it's a bug they won't charge you.

Dan

Warren wrote on Thu, 8 Jan 2009 17:50:04 -0800:

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: Compact SQL Database and ADO
    ... SQL Server Compact 3.5 Service Pack 1 Redistributable for Windows ... Set prmID = .CreateParameter ... Set prmText = .CreateParameter("pstrText", adVarWChar, ...
    (microsoft.public.data.ado)
  • Compact SQL Database and ADO
    ... Set prmID = .CreateParameter ... Set prmText = .CreateParameter ... The INSERT statement assigned to the CommandText property causes VB6 to ... The following statements work when assigned to the CommandText property. ...
    (microsoft.public.data.ado)
  • Re: Create Storedprocedure with VB6? Is this possible?
    ... VB6 and ADO no .NET ... can SQL Server Management Studio." ... Can VB6 create a stored procedure on the fly. ... something similar) exactly what you're sending to the db engine (via ADO), ...
    (microsoft.public.vb.database.ado)
  • Re: T-SQL and OLE Automation
    ... A library created as type ActiveX.exe will run out of the SQL Server ... To deploy a .dll, you make it, using the VB6 file/make command, then copy to ... Creating COM libraries with VB6 is probably better discussed in a VB ... > OLE automation and I'm not sure how to write the application on the VB6 ...
    (microsoft.public.sqlserver.programming)
  • Re: T-SQL and OLE Automation
    ... components from VB6 now that VB.Net is so well established as that code will ... Choose a path to save the .dll & remember where it is. ... If you are running your SQL Server on that machine, ... >> Greg Linwood ...
    (microsoft.public.sqlserver.programming)