SQL Statment
-
Hellow to all ... i am trying to run an SQL statment on my DB database i wanna pick from the statment a field after inserting i tried to run this code but i always recive this error " Multiple-step OLE DB operation generated errors .... " string SQLContract; int ContractIndx; ClsInfo Assist_Var = new ClsInfo(); try { SQLContract = "INSERT Contract( " + "ClientNum,ContractType,BDate,EDate," + "UDate,PDate,ContratOption,PropertyNum,"+ "HierMeters,MeterPrice,Remarks)" + "VALUES( " + txt_Client_Name.Text + "," + Assist_Var.PrepareStr("B") + "," + Assist_Var.PrepareStr(dtFromDate.Text) + "," + Assist_Var.PrepareStr(dtToDate.Text) + "," + Assist_Var.PrepareStr(dtChange.Text) + "," + Assist_Var.PrepareStr(dtLastPay.Text) + "," + Assist_Var.Val(txtYears.Text) + "," + Assist_Var.Val(txtPropertyID.Text) + "," + Assist_Var.Val(txtMeters.Text) + "," + Assist_Var.Val(txtMeterPrice.Text) + "," + Assist_Var.PrepareStr(txtRemarks.Text) + "); " + "SELECT @Indx = @@IDENTITY" ; OleDbConnection OleDbConn = new OleDbConnection(Assist_Var.SQLConnection()); OleDbConn.Open(); OleDbCommand OleDbCommand = new OleDbCommand(); OleDbCommand.CommandText = SQLContract; OleDbCommand.CommandType = CommandType.Text; OleDbCommand.Connection = OleDbConn; OleDbParameter MyPara = new OleDbParameter(); MyPara = new OleDbParameter("@Indx",OleDbType.Integer , 4); MyPara.Direction = ParameterDirection.Output; OleDbCommand.Parameters.Add(MyPara); OleDbCommand.ExecuteNonQuery(); ContractIndx = Convert.ToInt32(OleDbCommand.Parameters["@Indx"].Value); MessageBox.Show(ContractIndx.ToString()); OleDbConn.Close(); OleDbConn.Dispose(); OleDbCommand.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } thxx to u ...
-
Hellow to all ... i am trying to run an SQL statment on my DB database i wanna pick from the statment a field after inserting i tried to run this code but i always recive this error " Multiple-step OLE DB operation generated errors .... " string SQLContract; int ContractIndx; ClsInfo Assist_Var = new ClsInfo(); try { SQLContract = "INSERT Contract( " + "ClientNum,ContractType,BDate,EDate," + "UDate,PDate,ContratOption,PropertyNum,"+ "HierMeters,MeterPrice,Remarks)" + "VALUES( " + txt_Client_Name.Text + "," + Assist_Var.PrepareStr("B") + "," + Assist_Var.PrepareStr(dtFromDate.Text) + "," + Assist_Var.PrepareStr(dtToDate.Text) + "," + Assist_Var.PrepareStr(dtChange.Text) + "," + Assist_Var.PrepareStr(dtLastPay.Text) + "," + Assist_Var.Val(txtYears.Text) + "," + Assist_Var.Val(txtPropertyID.Text) + "," + Assist_Var.Val(txtMeters.Text) + "," + Assist_Var.Val(txtMeterPrice.Text) + "," + Assist_Var.PrepareStr(txtRemarks.Text) + "); " + "SELECT @Indx = @@IDENTITY" ; OleDbConnection OleDbConn = new OleDbConnection(Assist_Var.SQLConnection()); OleDbConn.Open(); OleDbCommand OleDbCommand = new OleDbCommand(); OleDbCommand.CommandText = SQLContract; OleDbCommand.CommandType = CommandType.Text; OleDbCommand.Connection = OleDbConn; OleDbParameter MyPara = new OleDbParameter(); MyPara = new OleDbParameter("@Indx",OleDbType.Integer , 4); MyPara.Direction = ParameterDirection.Output; OleDbCommand.Parameters.Add(MyPara); OleDbCommand.ExecuteNonQuery(); ContractIndx = Convert.ToInt32(OleDbCommand.Parameters["@Indx"].Value); MessageBox.Show(ContractIndx.ToString()); OleDbConn.Close(); OleDbConn.Dispose(); OleDbCommand.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } thxx to u ...
OK this is obviously a SQL Server statement, so why aren't you using the Sql... classes? They don't use the ODBC classes, so are more performant (plus they will avoid this error). Also, you should probably use a Stored Procedure to do this and remove the gaping security hole you have here. Don't use direct text statements because they are wide open to SQL Injection attacks. BTW - you missed the most important piece of information about your error - the actual error code. This error is fairly generic, and the error code (the 800... one) is useful for identifying the actual error that triggered it. One final thought - don't use @@IDENTITY. This is not guaranteed to return you the last identity because if you have a trigger on the table that performs an insert into a different table with an identity column on it, @@IDENTITY will return the identity of that table instead. Use SCOPE_IDENTITY() instead.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
OK this is obviously a SQL Server statement, so why aren't you using the Sql... classes? They don't use the ODBC classes, so are more performant (plus they will avoid this error). Also, you should probably use a Stored Procedure to do this and remove the gaping security hole you have here. Don't use direct text statements because they are wide open to SQL Injection attacks. BTW - you missed the most important piece of information about your error - the actual error code. This error is fairly generic, and the error code (the 800... one) is useful for identifying the actual error that triggered it. One final thought - don't use @@IDENTITY. This is not guaranteed to return you the last identity because if you have a trigger on the table that performs an insert into a different table with an identity column on it, @@IDENTITY will return the identity of that table instead. Use SCOPE_IDENTITY() instead.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.ok Pete so what do u suggest that i should do ... where do i find all the things that u told me about ? thank u ...
-
ok Pete so what do u suggest that i should do ... where do i find all the things that u told me about ? thank u ...
microuser_2000 wrote:
where do i find all the things that u told me about ?
MSDN[^] This article will give you information about SQL Injection Attacks and tips on how to prevent them[^]. The classes Pete talked about are SqlConnection[^], SqlCommand[^] and SqlParameter[^]
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
microuser_2000 wrote:
where do i find all the things that u told me about ?
MSDN[^] This article will give you information about SQL Injection Attacks and tips on how to prevent them[^]. The classes Pete talked about are SqlConnection[^], SqlCommand[^] and SqlParameter[^]
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
i saw the linkes that i gave me i don't see anything that i am doing wrong .. and if i validate the data in the forms befor i run the sql statment i can skep the injection attacks part ..
-
i saw the linkes that i gave me i don't see anything that i am doing wrong .. and if i validate the data in the forms befor i run the sql statment i can skep the injection attacks part ..
microuser_2000 wrote:
if i validate the data in the forms befor i run the sql statment i can skep the injection attacks part ..
:omg: Not really no. Don't think you can outwit a clever attacker. Personally, I believe that if I sufficiently sanitise the input then I won't be attacked. But, I am not infallible (no one is, not even you!) so I still use parameterised queries as an extra safety mechanism. It doesn't cost me anything, the code actually looks cleaner and easier to read and I have extra safety. Also, in some database systems using the same parameterised query, but just changing the parameter values means you get faster query execution too because it doesn't have to recompile the SQL each time. Only give up safety if there are tangible benefits. Better safe than sorry.
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
microuser_2000 wrote:
if i validate the data in the forms befor i run the sql statment i can skep the injection attacks part ..
:omg: Not really no. Don't think you can outwit a clever attacker. Personally, I believe that if I sufficiently sanitise the input then I won't be attacked. But, I am not infallible (no one is, not even you!) so I still use parameterised queries as an extra safety mechanism. It doesn't cost me anything, the code actually looks cleaner and easier to read and I have extra safety. Also, in some database systems using the same parameterised query, but just changing the parameter values means you get faster query execution too because it doesn't have to recompile the SQL each time. Only give up safety if there are tangible benefits. Better safe than sorry.
Upcoming events: * Glasgow Geek Dinner (5th March) * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
yaa , i will try it .. anyway .. anyone have idea how can i pick the value that i had insert to a table .. i use the code that i show in the first message of this post in VB.NET and it work's fine . i don't know why in CS it don't work ...