exception with special characters

[Wajih-ur-Rehman]

The following query runs perfectly fine with SQL Query Analyzer:

insert into TableName(ID,ParamName,ParamValue) values
(27706,'Param6','\0V­\0\0\0\0\0')

But if i try to execute the same query using OdbcCommand object, it throws
an exception. Any help plz?

 

[Jon Skeet [C# MVP]]

The following query runs perfectly fine with SQL Query Analyzer:

insert into TableName(ID,ParamName,ParamValue) values
(27706,'Param6','\0V­\0\0\0\0\0')

But if i try to execute the same query using OdbcCommand object, it throws
an exception. Any help plz?

Have you tried using parameters instead of including the nulls directly
in the SQL statement?

 

[Wajih-ur-Rehman]

I am having problems with parameterized queries with OdbcCommand object.
Event if i set it with ? instead of @ still it throws an exception. Can you
guide me a little on this? Do you think that it will solve the problem?

The following query runs perfectly fine with SQL Query Analyzer:

insert into TableName(ID,ParamName,ParamValue) values
(27706,'Param6','\0V­\0\0\0\0\0')

But if i try to execute the same query using OdbcCommand object, it throws
an exception. Any help plz?

Have you tried using parameters instead of including the nulls directly
in the SQL statement?

 

[Jon Skeet [C# MVP]]

I am having problems with parameterized queries with OdbcCommand object.
Event if i set it with ? instead of @ still it throws an exception. Can you
guide me a little on this? Do you think that it will solve the problem?

What exception is it throwing, exactly? Could you post a short but
complete program which demonstrates the problem?

See http://www.pobox.com/~skeet/csharp/complete.html for details of
what I mean by that.

Whether or not it solves this particular problem, parameterisation is
important enough that it's worth getting it working now, and then
seeing whether it helps with your immediate problem.

 

[Wajih-ur-Rehman]

Thanx Jon, You have always been so helpful. Here is the code that is causing
problem with parameterized queries (I dont want to use SqlCommand object
since in my whole application, i have used OdbcCommand object):

OdbcCommand cmd = new OdbcCommand();

OdbcConnection cn = new OdbcConnection("dsn=test;uid=;pwd=");

string query = "insert into Person (name,phone) values(?name,?phone)";


cmd.CommandType = CommandType.Text;

cmd.Connection = cn;

cmd.CommandText = query;


OdbcParameter param1 = new OdbcParameter();

param1.DbType = DbType.String;

param1.ParameterName = "?name";

param1.Value = "Test";

cmd.Parameters.Add(param1);

OdbcParameter param2 = new OdbcParameter();

param2.DbType = DbType.String;

param2.ParameterName = "?phone";

param2.Value = "123456";

cmd.Parameters.Add(param2);


cn.Open();


try

{

cmd.ExecuteNonQuery();

}

catch(Exception ex)

{

MessageBox.Show(ex.ToString());

}

- An exception is thrown that says " Message "ERROR [07002] [Microsoft][ODBC
Microsoft Access Driver] Too few parameters. Expected 4."
- I have also tried @ in place of ?, still doesnt work. However, the
exception text becomes a bit different in this case. Exception thrown is "
Message "ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 2."

Please tell me what i am doing wrong? Thanx again.

 

[Jon Skeet [C# MVP]]

Thanx Jon, You have always been so helpful. Here is the code that is causing
problem with parameterized queries (I dont want to use SqlCommand object
since in my whole application, i have used OdbcCommand object):

OdbcCommand cmd = new OdbcCommand();
OdbcConnection cn = new OdbcConnection("dsn=test;uid=;pwd=");
string query = "insert into Person (name,phone) values(?name,?phone)";

cmd.CommandType = CommandType.Text;
cmd.Connection = cn;
cmd.CommandText = query;

OdbcParameter param1 = new OdbcParameter();
param1.DbType = DbType.String;
param1.ParameterName = "?name";
param1.Value = "Test";
cmd.Parameters.Add(param1);

<snip>

I believe the problem is one which is slightly obscurely documented, in
OdbcCommand.Parameters:

<quote>
When CommandType is set to Text, the .NET Framework Data Provider for
ODBC does not support passing named parameters to an SQL statement or
to a stored procedure called by an OdbcCommand. In either of these
cases, use the question mark (?) placeholder. For example:

SELECT * FROM Customers WHERE CustomerID = ?

The order in which OdbcParameter objects are added to the
OdbcParameterCollection must directly correspond to the position of the
question mark placeholder for the parameter in the command text.
</quote>

In other words, you should use:

"insert into Person (name, phone) values (?, ?)"

At that point, it doesn't matter what names you give the parameters,
but they have to be in the right order.

 

[Wajih-ur-Rehman]

Thanx jon....It solved the problem of parameterized queries but i have tired
inserting that
'\0V­\0\0\0\0\0' as a parameter but that still doesnt work

 

[Jon Skeet [C# MVP]]

Thanx jon....It solved the problem of parameterized queries but i have tired
inserting that
'\0V­\0\0\0\0\0' as a parameter but that still doesnt work

What exception do you get when you're using that as a parameter value?
(And how exactly are you setting the value?)

 

[Wajih-ur-Rehman]

I am using this approach

//the things that i have not pasted are the same as before
string query = "insert into Person (name,phone) values(?,?)";
OdbcParameter param1 = new OdbcParameter();

param1.DbType = DbType.String;
param1.ParameterName = "?name";
param1.Value = "\0V­\0\0\0\0\0";
cmd.Parameters.Add(param1);

It is *not* thorwing any exceptions now. Instead, it is adding a row in the
database but the column in which i am trying to insert \0V­\0\0\0\0\0 is
empty

Thanx jon....It solved the problem of parameterized queries but i have tired
inserting that
'\0V­\0\0\0\0\0' as a parameter but that still doesnt work

What exception do you get when you're using that as a parameter value?
(And how exactly are you setting the value?)

 

[Jon Skeet [C# MVP]]

I am using this approach

//the things that i have not pasted are the same as before
string query = "insert into Person (name,phone) values(?,?)";
OdbcParameter param1 = new OdbcParameter();

It is *not* thorwing any exceptions now. Instead, it is adding a row in the
database but the column in which i am trying to insert \0V­\0\0\0\0\0 is
empty

Ah - is it *definitely* empty though, or does it just display as being
empty on some displays? Don't forget that some things (like the VS.NET
debugger in some cases, at least for VS.NET 2002) won't display
anything beyond the first null character in a string. If you fetch the
string from the database and print it out in the console (not the
debugger), is it still empty? What happens if you run a select statment
which returns the number of characters in a string?

 

[Wajih-ur-Rehman]

Thanx a lot Jon for all your help. I checked it again, it was showing it as
empty in the database but if i debug it, the string *does* contain the value
in it. So the insertion of unicode characters using Parameterized query
approach is working perfectly fine. Thanx again for all your help

Best Regards
Wajih

I am using this approach

//the things that i have not pasted are the same as before
string query = "insert into Person (name,phone) values(?,?)";
OdbcParameter param1 = new OdbcParameter();

It is *not* thorwing any exceptions now. Instead, it is adding a row in the
database but the column in which i am trying to insert \0V­\0\0\0\0\0 is
empty

Ah - is it *definitely* empty though, or does it just display as being
empty on some displays? Don't forget that some things (like the VS.NET
debugger in some cases, at least for VS.NET 2002) won't display
anything beyond the first null character in a string. If you fetch the
string from the database and print it out in the console (not the
debugger), is it still empty? What happens if you run a select statment
which returns the number of characters in a string?