|
|
Thanx but i hav a doubt.. actually i googled and i found out that we can use strX.replace("'","''") something like this... but i dont know how to place it in my insert query....
Thanx in advance
Regards,
Tash
|
|
|
|
|
Well e.g. instead of uf_id you write: uf_id.Replace("'","''").
That's it. But again, this is not the way you should insert data into your sql server.
Post the code where you execute the statement and we can show you where to put in the prepared statement.
|
|
|
|
|
XmlDataDocument doc9 = new XmlDataDocument();
doc9.Load(ConfigurationSettings.AppSettings["usr"]);
XmlNodeList nodes9 = doc9.GetElementsByTagName("Row");
foreach(XmlNode node9 in nodes9)
{
if(node9.HasChildNodes && node9.ChildNodes.Count > 0)
{
XmlElement Element9 = (XmlElement) node9;
string u_Id = Element9.GetElementsByTagName("Id")[0].InnerText;
string u_Title = Element9.GetElementsByTagName("Title")[0].InnerText;
string u_FirstName = Element9.GetElementsByTagName("FirstName")[0].InnerText;
string u_MiddleName = Element9.GetElementsByTagName("MiddleName")[0].InnerText;
string u_LastName = Element9.GetElementsByTagName("LastName")[0].InnerText;
string u_FileName = Element9.GetElementsByTagName("FileName")[0].InnerText;
if(u_Id==ugf_UserId)
{
string path4 = ConfigurationSettings.AppSettings["usrfile"]+"//"+u_FileName+".xml";
if (File.Exists(path4))
{
XmlDataDocument doc10 = new XmlDataDocument();
doc10.Load(ConfigurationSettings.AppSettings["usrfile"]+"//"+u_FileName+".xml");
XmlNodeList nodes10 = doc10.GetElementsByTagName("User");
foreach(XmlNode node10 in nodes10)
{
if(node10.HasChildNodes && node10.ChildNodes.Count > 0)
{
XmlElement Element10 = (XmlElement) node10;
string uf_Id = Element10.GetElementsByTagName("Id")[0].InnerText;
string uf_Title = Element10.GetElementsByTagName("Title")[0].InnerText;
string uf_FirstName = Element10.GetElementsByTagName("FirstName")[0].InnerText;
string uf_MiddleName = Element10.GetElementsByTagName("MiddleName")[0].InnerText;
string uf_LastName = Element10.GetElementsByTagName("LastName")[0].InnerText;
string uf_Type = Element10.GetElementsByTagName("Type")[0].InnerText;
string uf_PhoneNumber = Element10.GetElementsByTagName("PhoneNumber")[0].InnerText;
string uf_MobileNumber = Element10.GetElementsByTagName("MobileNumber")[0].InnerText;
string uf_EmailID = Element10.GetElementsByTagName("EmailID")[0].InnerText;
string uf_Branch = Element10.GetElementsByTagName("Branch")[0].InnerText;
string uf_Department = Element10.GetElementsByTagName("Department")[0].InnerText;
string uf_NoOfLoginAttempts = Element10.GetElementsByTagName("NoOfLoginAttempts")[0].InnerText;
string uf_Status = Element10.GetElementsByTagName("Status")[0].InnerText;
if(uf_Id==u_Id)
{
cmd = new OdbcCommand("select UserID from eacs_user_1 where userid ='"+uf_Id+"'" ,con);
rdr = cmd.ExecuteReader();
if(rdr.Read())
{
rdr.Close();
}
else
{
rdr.Close();
Console.WriteLine("{0}",uf_Id);
cmd = new OdbcCommand("insert into EACS_User_1 values('"+uf_Id+"','"+uf_Title+"','"+uf_FirstName.Replace("'","''")+"','"+uf_MiddleName+"','"+uf_LastName+"','"+uf_PhoneNumber+"','"+uf_MobileNumber+"','"+uf_EmailID+"','"+uf_Branch+"','"+uf_Department+"','"+uf_NoOfLoginAttempts+"','"+uf_Status+"','','','','','','')",con);
cmd.ExecuteNonQuery();
}
}
else
{
Console.WriteLine("Please check User file-{0} fields do not match",u_FileName);
}
}
}
}
else
{
Console.WriteLine("User file {0} not present",u_FileName);
}
}
}
}
This is only a part of my code that i have posted...
Thanx in advance..
Regards,
Tash
|
|
|
|
|
Okay, so change the line where you build the command like this:
<br />
<br />
cmd = new OdbcCommand("insert into EACS_User_1 VALUES (@Id,@Title,@FirstName,...)", con);<br />
cmd.Parameters.AddWithValue("@Id", uf_id);<br />
cmd.Parameters.AddWithValue("@Title", uf_Title);<br />
cmd.Parameters.AddWithValue("@FirstName", uf_FirstName);<br />
.<br />
.<br />
.<br />
cmd.ExecuteNonQuery()<br />
<br />
That should do the work.
Regards
Sebastian
|
|
|
|
|
Actually i only find cmd.parameters.add i mean i dont get addwithvalue... should i include anything??? is it because i am using VS2003???
Thanx in advance..
Regards,
Tash
|
|
|
|
|
You are using .NET 2.0, that is why there is no AddWithValue-method. Instead of AddWithValue use Add like this:
cmd.Parameters.Add(new OdbcParameter("@Id", uf_Id));
Regards
Sebastian
|
|
|
|
|
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
Thanx in advance...
Regards,
Tash
|
|
|
|
|
Hmmm... try to rewrite to
cmd.Parameters.Add(new OdbcParameter("Id", uf_Id));
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
U mean i have to replace @Id by ?
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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();
}
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
|
|
|
|
|
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...
|
|
|
|
|
u mean the datatype that is assigned to that particular column in my database....
|
|
|
|
|
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[^]
|
|
|
|
|
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
|
|
|
|
|
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;
}
|
|
|
|
|
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.
|
|
|
|
|
I really dont get it..... 
|
|
|
|
|
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.
|
|
|
|
|
Or use stored procedures.....
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
forgot the link: