Re: Problem with insert query - C# Discussion Boards

<<Tash18>>6-Jan-10 1:28

6-Jan-10 1:28

I did as u said but i receive the following Exception:

System.Data.Odbc.OdbcException: ERROR [HY000] [INTERSOLV][ODBC SQL Server driver][SQL Server]Must declare variable '@Id'.

at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 635

Confused | :confused:

Thanx in advance...
Regards,
Tash

SeMartens6-Jan-10 1:31

6-Jan-10 1:31

Hmmm... try to rewrite to

cmd.Parameters.Add(new OdbcParameter("Id", uf_Id));

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 1:36

6-Jan-10 1:36

even after making the changes u said i receive xactly the same exception once again... must i change any thing in the insert query???

thanx in advance..
Regards,
Tash

SeMartens6-Jan-10 1:44

6-Jan-10 1:44

Ah I remember the good old days. You have to replace all the names with @ within the statement with ?. So your statement should look like this:

...VALUES (?,?,?,...

Be sure that you add the parameters within the same sequence as said in the statement.

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 1:45

6-Jan-10 1:45

U mean i have to replace @Id by ?

SeMartens6-Jan-10 1:46

6-Jan-10 1:46

Yes

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 1:55

6-Jan-10 1:55

Now My code is as follows:

rdr.Close();
																																							Console.WriteLine("{0}",uf_Id);
																																							cmd = new OdbcCommand("insert into EACS_User_1 values(?,?,?,?,?,?,?,?,?,?,?,?,?)",con);
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Id));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Title));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_FirstName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_MiddleName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_LastName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Type));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_PhoneNumber));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_MobileNumber));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_EmailID));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Branch));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Department));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_NoOfLoginAttempts));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Status));
																																							cmd.ExecuteNonQuery();

And i receive the following exception...
System.Data.Odbc.OdbcException: ERROR [21S01] [INTERSOLV][ODBC SQL Server driver][SQL Server]Insert error: column name or number of supplied values does not match table definition.

at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 635

Thanx in advance...
Regards,
Tash

SeMartens6-Jan-10 2:00

6-Jan-10 2:00

As the error message is saying the number of ? does not match the number of columns. So you got two options:

1) Write a ? for every column that exists in your table
2) Include the column names in your statement.

I would suggest using the second option, because it is more fail-safe than the first option. Rewrite your statement to include the column names:

INSERT INTO EACS_User_1 (Id, Title, ...) VALUES (...

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 2:18

6-Jan-10 2:18

Actually it was my mistake i dint inserthe correct noof ? for the noof columns i have... Now the code is :

else
																																						{
																																							rdr.Close();
																																							Console.WriteLine("{0}",uf_Id);
																																							cmd = new OdbcCommand("insert into EACS_User_1 values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",con);
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Id));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Title));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_FirstName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_MiddleName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_LastName));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Type));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_PhoneNumber));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_MobileNumber));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_EmailID));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Branch));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_Department));
																																							cmd.Parameters.Add(new OdbcParameter("?",uf_NoOfLoginAttempts));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.Parameters.Add(new OdbcParameter("?",""));
																																							cmd.ExecuteNonQuery();
																																							//Console.WriteLine("*UserGroup* Inserted");
																																						}

But again the appostrophe problem is there.... i get the following exception...

System.Data.Odbc.OdbcException: ERROR [HY000] [INTERSOLV][ODBC SQL Server driver][SQL Server]Error converting client characters into server's character set. Some character(s) could not be converted.

at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at ConsoleApplication1.Class1.perform() in c:\documents and settings\900846\my documents\visual studio projects\consoleapplication1\class1.cs:line 640

Thanx in advance..
Regards,
Tash

SeMartens6-Jan-10 2:26

6-Jan-10 2:26

Hmm did you checked that the database character set is identical to the one you use within your client app? Check the database char set by using the management studio to administer your sql database.

So you choosed option one and added the columns to your statement. This is not the best way because next time you add a column to the table you have to change your code too...

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 2:30

6-Jan-10 2:30

u mean the datatype that is assigned to that particular column in my database....

SeMartens6-Jan-10 2:40

6-Jan-10 2:40

No, like the error is saying the char set of your client app does not match the server. Check your local encoding with Encoding.Default and the code page used within the database (connect to the server and check the properties of the database with the management studio).

To get the local encoding use Encoding.Default:

http://msdn.microsoft.com/en-us/library/system.text.encoding.default.aspx[^]

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

<<Tash18>>6-Jan-10 20:35

6-Jan-10 20:35

Thanx a lot my issue is solved... actually the name had a symbol (’) and i thought it to be an apostrophe now i replace it with (') in my insert query.. The prepared statement which u specified also works fine... thanx for ur guidance.. Thanx alot...

Regards,
Tash

dojohansen7-Jan-10 5:31

dojohansen

7-Jan-10 5:31

It is indeed, but it's not too hard to fix. Make yourself a little helper method to add the parameters and make use of it. For example like this:

public static class Util
{
        public static void AddParameters(SqlCommand cmd, params object[] args)
        { 
            for (int i = 0; i < args.Length; i += 2)
            {
                cmd.Parameters.Add(GetParameter((string)args[i], args[i + 1]));
            }
        }

        public SqlParameter GetParameter(string name, object value)
        {
            SqlParameter p = new SqlParameter();
            p.ParameterName = name;
            p.SqlDbType = GetDbType(value);
            p.Direction = ParameterDirection.Input;
            p.Value = value;
            return p;
        }

        public SqlDbType GetDbType(object value)
        {
            if (value == null) throw new ArgumentNullException();

            if (t == typeof(int)) return SqlDbType.Int;
            if (t == typeof(string)) return SqlDbType.NVarChar;
            if (t == typeof(DateTime)) return SqlDbType.DateTime;
            if (t == typeof(Boolean)) return SqlDbType.Bit;
            if (t == typeof(byte[])) return SqlDbType.Image;
            if (t == typeof(SqlBinary)) return SqlDbType.Binary;
            if (t == typeof(Guid)) return SqlDbType.UniqueIdentifier;
            if (t == typeof(Int64)) return SqlDbType.BigInt;
            // ...

            throw new NotSupportedException("Missing support for type " + t.FullName + ".");
        }
}

A little bit of code, but you can now easily use it many places, for example like this:

SqlCommand getInsertCommand(string col1, int col2)
{
    SqlCommand cmd = new SqlCommand("INSERT [Table] VALUES (@col1, @col2);");
    Util.AddParameters(cmd, 
       "@col1", col1, 
       "@col2", col2);
    return cmd;
}

OriginalGriff6-Jan-10 0:37

6-Jan-10 0:37

The way SeMartens is suggesting is good, but the C# stuff is a little old: "Parameters.Add(...).Value = ..." has been depreciated. You should use "Parameters.AddWithValue(paramName, paramValue)" instead.

All those who believe in psycho kinesis, raise my hand.

<<Tash18>>6-Jan-10 0:40

6-Jan-10 0:40

I really dont get it..... Cry | :((

OriginalGriff6-Jan-10 1:01

6-Jan-10 1:01

OK, there are (at least) two ways to write to a database:
(I have broken these into several strings to make the lines shorter and easier to read.)

SqlCommand cmd = new SqlCommand("INSERT INTO EACS_User_1 " +
                                "(firstName, lastName) " +
                                "VALUES " +
                                "(" + uf_FirstName + "," + uf_LastName + ")");

which has a number of problems. One of these is that if your fields uf_FirstName and / or uf_LastName contain a quote, double quote, semicolon, or various other characters you havce a problem. The other is that this character dependancy can be used to do something called an SQL Injection Attack[^] on your database.
The other solution is called Parameterised Queries:

SqlCommand cmd = new SqlCommand("INSERT INTO EACS_User_1 " +
                                "(firstName, lastName) " +
                                "VALUES " +
                                "(@FN, @LN)");
cmd.Parameters.AddWithValue("@FN", uf_FirstName);
cmd.Parameters.AddWithValue("@LN", uf_LastName);

where @FN and @LN can be any text you like - the '@' character is just to make them easier to see and is not required (Good idea, though).
This gets rid of the problem - uf_FirstName and uf_LastName can contain any characters, including a mix of double and single quotes.

BTW: The convention is to use all UPPER CASE for SQL syntax keywords, so you can see them more easily.

All those who believe in psycho kinesis, raise my hand.

Ashfield6-Jan-10 1:16

Ashfield

6-Jan-10 1:16

Or use stored procedures.....

Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP

<<Tash18>>6-Jan-10 1:17

6-Jan-10 1:17

Actually i only find cmd.parameters.add i mean i dont get addwithvalue... should i include anything??? is it because i am using VS2003??? plz guide me..

Thanx in advance....
Regards,
Tash

OriginalGriff6-Jan-10 1:31

6-Jan-10 1:31

<<thashif>> wrote:
Actually i only find cmd.parameters.add i mean i dont get addwithvalue... should i include anything??? is it because i am using VS2003

Yes. AddWithValue was added with .NET 2.0 (VS 2005) so you don't have it! Either:
1) use .Parameters.Add("@FN").Value = uf_...
or
2) upgrade to a (slightly) more modern version! Laugh | :laugh:

Don't forget the Express versions of VS are free...

All those who believe in psycho kinesis, raise my hand.

<<Tash18>>6-Jan-10 1:42

6-Jan-10 1:42

Nope that doesnt work i mean after adding cmd.Parameters.Add("@Id"). i dont get 'Value' term at all...

Thanx in advance...
Regards,
Tash

OriginalGriff6-Jan-10 2:07

6-Jan-10 2:07

Sorry - been a while since I used it... Blush | :O

cmd.Parameters.Add("@FN", System.Data.SqlDbType.VarChar).Value = uf_LastName;

You will need to set the datatype to the type in your DB.
The AddWithValue version doesn't need the datatype...

All those who believe in psycho kinesis, raise my hand.

<<Tash18>>6-Jan-10 20:37