Sql Command with C#
-
Hi, I want use this code to enter some data to data base data base is an access data base rec is an instancce of class include some string data member that you see in code
// open Connection private OleDbConnection Conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../../db.mdb"); private OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); Conn.Open(); string cmdText = "INSERT INTO tbl1 (fname, lname, relative,tel, " + "mobile, b_tel, fax, email, adrs, www, job, user)" + " VALUES ( '" + rec.FirstName + "', '" + rec.LastName + "', '" + rec.Relative + "', '" + rec.Tel + "', '" + rec.Mobile + "', '" + rec.B_tel + "', '" + rec.Fax + "', '" + rec.Email + "', '" + rec.Adrs + "', '" + rec.WWW + "', '" + rec.JobTitle + "', '" + rec.user + "' )"; dataAdapter.InsertCommand = new OleDbCommand(cmdText, Conn); dataAdapter.InsertCommand.ExecuteNonQuery(); Conn.Close();but in runTime this error occurs : "Syntax error in INSERT INTO statment" please help me.--------------------- Areff Bahrami(KAVEH) Areff.HB@Gmail.com ---------------------
-
Hi, I want use this code to enter some data to data base data base is an access data base rec is an instancce of class include some string data member that you see in code
// open Connection private OleDbConnection Conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../../db.mdb"); private OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); Conn.Open(); string cmdText = "INSERT INTO tbl1 (fname, lname, relative,tel, " + "mobile, b_tel, fax, email, adrs, www, job, user)" + " VALUES ( '" + rec.FirstName + "', '" + rec.LastName + "', '" + rec.Relative + "', '" + rec.Tel + "', '" + rec.Mobile + "', '" + rec.B_tel + "', '" + rec.Fax + "', '" + rec.Email + "', '" + rec.Adrs + "', '" + rec.WWW + "', '" + rec.JobTitle + "', '" + rec.user + "' )"; dataAdapter.InsertCommand = new OleDbCommand(cmdText, Conn); dataAdapter.InsertCommand.ExecuteNonQuery(); Conn.Close();but in runTime this error occurs : "Syntax error in INSERT INTO statment" please help me.--------------------- Areff Bahrami(KAVEH) Areff.HB@Gmail.com ---------------------
Congratulations! That is some of the best looking string concatenation code I have seen in years! Too bad it has a bug in it so you have to crawl through it and find what typing mistake you made. If only there were better ways to execute database queries from code.... wait... X|
"Alot of the people on this forum are incredibly stupid, thinking that the internet is real"
Score: 1.0 in the Soap Box -
Hi, I want use this code to enter some data to data base data base is an access data base rec is an instancce of class include some string data member that you see in code
// open Connection private OleDbConnection Conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../../db.mdb"); private OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); Conn.Open(); string cmdText = "INSERT INTO tbl1 (fname, lname, relative,tel, " + "mobile, b_tel, fax, email, adrs, www, job, user)" + " VALUES ( '" + rec.FirstName + "', '" + rec.LastName + "', '" + rec.Relative + "', '" + rec.Tel + "', '" + rec.Mobile + "', '" + rec.B_tel + "', '" + rec.Fax + "', '" + rec.Email + "', '" + rec.Adrs + "', '" + rec.WWW + "', '" + rec.JobTitle + "', '" + rec.user + "' )"; dataAdapter.InsertCommand = new OleDbCommand(cmdText, Conn); dataAdapter.InsertCommand.ExecuteNonQuery(); Conn.Close();but in runTime this error occurs : "Syntax error in INSERT INTO statment" please help me.--------------------- Areff Bahrami(KAVEH) Areff.HB@Gmail.com ---------------------
Had a similar issue with Access recently, and for unknown reasons, using double quotes would insert where singles wouldn't. Might try that. Further, you might want to look into parameterized queries for a number of reasons. Easier to work with, much nicer to look at and edit, less prone to error, and much more secure.
string sql = "insert into tbl (Field1, Field2, Field3) values (@Field1, @Field2, @Field3)";
OleDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
cmd.AddParameter(new OleDbParameter("@Field1", rec.Field1));
cmd.AddParameter(new OleDbParameter("@Field2", rec.Field2));
cmd.AddParameter(new OleDbParameter("@Field3", rec.Field3));
IDataReader idr = cmd.ExecuteReader();
Try code model generation tools at BoneSoft.com.
-
Hi, I want use this code to enter some data to data base data base is an access data base rec is an instancce of class include some string data member that you see in code
// open Connection private OleDbConnection Conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../../../db.mdb"); private OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); Conn.Open(); string cmdText = "INSERT INTO tbl1 (fname, lname, relative,tel, " + "mobile, b_tel, fax, email, adrs, www, job, user)" + " VALUES ( '" + rec.FirstName + "', '" + rec.LastName + "', '" + rec.Relative + "', '" + rec.Tel + "', '" + rec.Mobile + "', '" + rec.B_tel + "', '" + rec.Fax + "', '" + rec.Email + "', '" + rec.Adrs + "', '" + rec.WWW + "', '" + rec.JobTitle + "', '" + rec.user + "' )"; dataAdapter.InsertCommand = new OleDbCommand(cmdText, Conn); dataAdapter.InsertCommand.ExecuteNonQuery(); Conn.Close();but in runTime this error occurs : "Syntax error in INSERT INTO statment" please help me.--------------------- Areff Bahrami(KAVEH) Areff.HB@Gmail.com ---------------------
The reply from BoneSoft is essentially the correct approach (use parameters), but the example code he provided likely will onmly work with the SqlClient namespace rater than the OledbClient variation. For OledbClient, the parameters are signified by using ? placeholders rather than parameter names, so the sql becomes
string sql = "insert into tbl (Field1, Field2, Field3) values ( ?, ?, ?)";
The rest is still correct, the OledbParameters still require a unique name in the constructor, but the name is not used, so the parameters must be added in exactly the order they are used in the sql query.
-
The reply from BoneSoft is essentially the correct approach (use parameters), but the example code he provided likely will onmly work with the SqlClient namespace rater than the OledbClient variation. For OledbClient, the parameters are signified by using ? placeholders rather than parameter names, so the sql becomes
string sql = "insert into tbl (Field1, Field2, Field3) values ( ?, ?, ?)";
The rest is still correct, the OledbParameters still require a unique name in the constructor, but the name is not used, so the parameters must be added in exactly the order they are used in the sql query.
Huh, ya learn something every day. Guess I've been pretty successful avoiding OleDb. I just typed that in from thought, I figured somebody would find something wrong with it, just didn't expect that. I'll have to remember that. Actually, I started typing SqlCommand and had to go back and change it all. :~
Try code model generation tools at BoneSoft.com.
-
The reply from BoneSoft is essentially the correct approach (use parameters), but the example code he provided likely will onmly work with the SqlClient namespace rater than the OledbClient variation. For OledbClient, the parameters are signified by using ? placeholders rather than parameter names, so the sql becomes
string sql = "insert into tbl (Field1, Field2, Field3) values ( ?, ?, ?)";
The rest is still correct, the OledbParameters still require a unique name in the constructor, but the name is not used, so the parameters must be added in exactly the order they are used in the sql query.
OleDbParameter has the same constructors, like
(string name, object value), how do you setup your parameters with OleDb then? Just give them any name if it just goes by order?
Try code model generation tools at BoneSoft.com.
-
OleDbParameter has the same constructors, like
(string name, object value), how do you setup your parameters with OleDb then? Just give them any name if it just goes by order?
Try code model generation tools at BoneSoft.com.
BoneSoft wrote:
Just give them any name if it just goes by order?
Correct, as long as the names are unique, only order matters. Since the names are still keys in the collection, they need to be unique or you get the unexpected side effect of redefining a parameter... Kinda sucks, but I guess it's a compatibility thing...