Exception
-
My code looks like this: List items; private void button2_Click(object sender, EventArgs e) { //finalize button click items = new List(); for (int i = 0; i < passagelistBox.Items.Count; i++) { items.Add(passagelistBox.Items[i].ToString()); } foreach (string currentitem in items) { DoInsertData(currentitem.Split(new char[] {','})); } } private void DoInsertData(string[] item) { string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES (@p1, @p2)"; cmd.CommandText = sql; SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cmd.Connection = null; } while I wxecute this code at the line : cmd.ExecuteNonQuery(); a exception is raised as follws: System.Data.SqlClient.SqlException was unhandled Message="String or binary data would be truncated.\r\nThe statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8152 Procedure="" Server="localhost" State=2 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStre
-
My code looks like this: List items; private void button2_Click(object sender, EventArgs e) { //finalize button click items = new List(); for (int i = 0; i < passagelistBox.Items.Count; i++) { items.Add(passagelistBox.Items[i].ToString()); } foreach (string currentitem in items) { DoInsertData(currentitem.Split(new char[] {','})); } } private void DoInsertData(string[] item) { string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES (@p1, @p2)"; cmd.CommandText = sql; SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cmd.Connection = null; } while I wxecute this code at the line : cmd.ExecuteNonQuery(); a exception is raised as follws: System.Data.SqlClient.SqlException was unhandled Message="String or binary data would be truncated.\r\nThe statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8152 Procedure="" Server="localhost" State=2 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStre
why do you use those two parameters @p1 and @p2 when you can simply do something like this: string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES ('" + item[0] + "','" + item[1] + "')"; I presume that column_num and row_num are string values in the database. Usually you use parameters when you use a stored procedure to execute a query. So you don't need these lines SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; Tell me if it helped... :)
Just call me Valy... :)
-
why do you use those two parameters @p1 and @p2 when you can simply do something like this: string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES ('" + item[0] + "','" + item[1] + "')"; I presume that column_num and row_num are string values in the database. Usually you use parameters when you use a stored procedure to execute a query. So you don't need these lines SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; Tell me if it helped... :)
Just call me Valy... :)
-
My code looks like this: List items; private void button2_Click(object sender, EventArgs e) { //finalize button click items = new List(); for (int i = 0; i < passagelistBox.Items.Count; i++) { items.Add(passagelistBox.Items[i].ToString()); } foreach (string currentitem in items) { DoInsertData(currentitem.Split(new char[] {','})); } } private void DoInsertData(string[] item) { string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES (@p1, @p2)"; cmd.CommandText = sql; SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cmd.Connection = null; } while I wxecute this code at the line : cmd.ExecuteNonQuery(); a exception is raised as follws: System.Data.SqlClient.SqlException was unhandled Message="String or binary data would be truncated.\r\nThe statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8152 Procedure="" Server="localhost" State=2 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStre
SqlConnection conn = new SqlConnection(connectionString); //the sql connection conn.Open(); string strQuery = "insert into Temp(ProductName,Cantity) values ('"+item[0]+"',cast ('"+item[1]+"' as numeric(5,0)))"; //the query SqlCommand com1 = new SqlCommand(strQuery, conn); //the sql command com1.ExecuteNonQuery(); conn.Close(); These is a sample of my code that works just fine. Are you sure the fields in the table are corect... In my sample the table Temp has 2 fields, ProductName is nchar(50) and Cantity is numeric(5,0). Be careful with the " ' " also (string values must be placed between ' in a query otherwise it doesn't work).
Just call me Valy... :)
-
why do you use those two parameters @p1 and @p2 when you can simply do something like this: string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES ('" + item[0] + "','" + item[1] + "')"; I presume that column_num and row_num are string values in the database. Usually you use parameters when you use a stored procedure to execute a query. So you don't need these lines SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; Tell me if it helped... :)
Just call me Valy... :)
Parameters are certainly not only for stored procedures. Please read about good practice and parameters before giving bad advice. Here is a start: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx[^]
-
My code looks like this: List items; private void button2_Click(object sender, EventArgs e) { //finalize button click items = new List(); for (int i = 0; i < passagelistBox.Items.Count; i++) { items.Add(passagelistBox.Items[i].ToString()); } foreach (string currentitem in items) { DoInsertData(currentitem.Split(new char[] {','})); } } private void DoInsertData(string[] item) { string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES (@p1, @p2)"; cmd.CommandText = sql; SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cmd.Connection = null; } while I wxecute this code at the line : cmd.ExecuteNonQuery(); a exception is raised as follws: System.Data.SqlClient.SqlException was unhandled Message="String or binary data would be truncated.\r\nThe statement has been terminated." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8152 Procedure="" Server="localhost" State=2 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStre
It looks like you're trying to insert a string value into a field when the length of the string is greater than the length of the dB field. For example, you're trying to do something akin to inserting the string 'This string is too long for my data field' into a VARCHAR(10) field.
Rhys "The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it" They say a little knowledge is a dangerous thing, but it's not one half so bad as a lot of ignorance." Terry Pratchett
-
why do you use those two parameters @p1 and @p2 when you can simply do something like this: string sql = "INSERT INTO tblPassages (column_num, row_num) VALUES ('" + item[0] + "','" + item[1] + "')"; I presume that column_num and row_num are string values in the database. Usually you use parameters when you use a stored procedure to execute a query. So you don't need these lines SqlParameter p1 = new SqlParameter("@p1", item[0]); SqlParameter p2 = new SqlParameter("@p2", item[1]); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); p1.SqlDbType = SqlDbType.VarChar; p2.SqlDbType = SqlDbType.VarChar; Tell me if it helped... :)
Just call me Valy... :)
Please look at the problem when trying to help and please (as already has been said) don't give bad advice. Looking at the message of the generated exception; Message="String or binary data would be truncated.\r\nThe statement has been terminated." ...the cause is pretty obvious and if you're not aware of the cause, I imagine a Google search on this would tell you exactly what you need to know.
Rhys "The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it" They say a little knowledge is a dangerous thing, but it's not one half so bad as a lot of ignorance." Terry Pratchett
-
SqlConnection conn = new SqlConnection(connectionString); //the sql connection conn.Open(); string strQuery = "insert into Temp(ProductName,Cantity) values ('"+item[0]+"',cast ('"+item[1]+"' as numeric(5,0)))"; //the query SqlCommand com1 = new SqlCommand(strQuery, conn); //the sql command com1.ExecuteNonQuery(); conn.Close(); These is a sample of my code that works just fine. Are you sure the fields in the table are corect... In my sample the table Temp has 2 fields, ProductName is nchar(50) and Cantity is numeric(5,0). Be careful with the " ' " also (string values must be placed between ' in a query otherwise it doesn't work).
Just call me Valy... :)
As you've been told before, you should read (and understand) the problem and not give bad advice like building your SQL statements by string concatenation.:doh: Hint: Google for "SQL injection". The simple reason for the exception is (as the exception messages tells very clearly) that "String or binary data would be truncated." while trying to put some data into the database. So obviously at least one of the values is longer than what the corresponding DB field can hold.
Regards, mav -- Black holes are the places where God divided by 0...