"Data type mismatch in criteria expression"

[Dan]

I'm building a small C# application which stores some info into an Access
table; this is the 1st time I'm using Access and the OleDb namespace, as
usually I prefer MSDE (SqlClient namespace), and I'm having this problem:
when inserting or updating a record with 2 text fields and 1 integer field
(the primary key is autonumber) using a stored procedure, I get Data type
mismatch in criteria expression: I have checked the documentation and also
created a collection of parameters using the Visual Studio wizard, and it
seems there is no error in my code; nevertheless, it does not work... Here
is a sample:

a dummy table named TDummy has the fields:
ID = autonumber
first = text (50)
last = text (50)
age = integer

The stored procedure for insertion is:

INSERT INTO TDummy([first], [last], age) VALUES ([?], [?], [?]);

The command:

OleDbCommand cmd = new OleDbCommand("[...the name of the stored proc...]");
cmd.Connection = ...the connection...;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OleDbParameter("first", OleDbType.VarWChar, 50,
"first"));
cmd.Parameters.Add(new OleDbParameter("last", OleDbType.VarWChar, 50,
"last"));
cmd.Parameters.Add(new OleDbParameter("sourceID", OleDbType.Integer, 0,
"age"));

Later I set the three values e.g. cmd.Parameters[0].Value="Donald",
cmd.Parameters[1].Value="Duck", cmd.Parameters[2].Value = 60, and then I
call cmd.ExecuteNonQuery which always rets a data type mismatch. What's
wrong with this code? Thanx!

 

[Cowboy \(Gregory A. Beamer\)]

My suggestion is to get away from text and change to varchar or nvarchar.

CREATE TABLE TDummy
([ID] int IDENTITY(1,1) PRIMARY KEY,
first varchar(50),
last varchar(50)
age int)

The text(50) is an Access construct, not SQL Server (MSDE = SQL Server
Personal). That should wipe out your problem.

In SQL Server/MSDE, text is a BLOB (Binary Large OBject) field, not a
character (char) or variable character (varchar) field. If you want to load
a text type (not advised), you have to use the SQLBinary type.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************