Inserting strings with SqlCeCommand and Parameters

  • Thread starter Thread starter Hollywood
  • Start date Start date
H

Hollywood

Note: I posted it in the sqlserver.ce newsgroup initially, but the CF
newsgroup always seemed to get more traffic and such questions had been
answered in the past..... so, I'll see if anyone has a thought!

I'm attempting to use a SqlCeCommand with parameters to insert some strings
into a table. However, while it inserts a new row into the table, it does
not insert any of the strings into the table, but all the other values are
properly inserted. Would post code, but its long and involved, but I do
something of the sort:

sqlCmd.Parameters.Add("@Id", SqlDbType.UniqueIndentifier);
sqlCmd.Parameters.Add("@IntTest", SqlDbType.Int);
sqlCmd.Parameters.Add("@NVarTest", SqlDbType.NVarChar);
sqlCmd.Parameters.Add("@NTextTest", SqlDbType.NText);
sqlCmd.CommandText = "INSERT INTO tTest (Id, IntTest, NVarTest, NTextTest)
VALUES (?, ?, ?, ?)";
sqlCmd.Prepare();
sqlCmd.Parameters[0].Value = new Guid();
sqlCmd.Parameters[1].Value = 99;
sqlCmd.Parameters[2].Value = "Test";
sqlCmd.Parameters[3].Value = "Test2";

sqlCmd.ExecuteNonQuery();

And here is the table:

CREATE TABLE tTest (Id uniqueidentifier Primary Key NOT NULL,
IntTest int NOT NULL,
NVarTest nvarchar(20) NOT NULL,
NTextTest ntext NOT NULL)

However, this fails to insert both "Test" and "Test2" into the table.

Thoughts?
 
You should not call sqlCmd.Prepare() until command is actually ready and
all parameter's values are set.
To fix the problem, you can move call to sqlCmd.Prepare() right before
sqlCmd.ExecuteNonQuery().
Better way is not to call it at all. Just let sqlCmd.ExecuteNonQuery() do
preparations for you.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
In SQL syntax, strings are delimited by single quotes, not double quotes.
Try modifying your code to use single quotes arount Test and Test2

-joe-
 
Yes, when you use SQL statements such as

INSERT 'This is a message' INTO tMessage

but not when dealing with parameters.
 
Back
Top