problems with INSERT to database
-
Hi, I want to insert into my database some information, I have everything in file a.txt: INSERT INTO TLK (kodas, liga) VALUES ('(01-99)', 'Traumu priežastys (ne iš TLK10 klasifikacijos)') INSERT INTO TLK (kodas, liga) VALUES ('01-99', 'Ne TLK10 traumu priežasciu klasifikacija') INSERT INTO TLK (kodas, liga) VALUES ('1', 'Transporto ivykis') a.txt contains about 10 000 records. I run this program, but everytime it breaks, it shows error that data would be truncated... how can I fix that? using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Kikilis { public partial class Form1 : Form { public Form1() { InitializeComponent(); } string eilute; int id = 0; StringBuilder errorMessages = new StringBuilder(); private void button1_Click(object sender, EventArgs e) { try { System.IO.StreamReader sr = new System.IO.StreamReader("a.txt"); eilute = sr.ReadLine(); string j = "Data Source=KOMPAS;Initial Catalog=MEDIS;Integrated Security=True"; SqlConnection c = new SqlConnection(j); c.Open(); while (eilute != "" || eilute == null) { SqlCommand cmd = new SqlCommand(eilute, c); cmd.ExecuteNonQuery(); cmd.Dispose(); eilute = sr.ReadLine(); } c.Close(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } Console.WriteLine(errorMessages.ToString()); } } } }
-
Hi, I want to insert into my database some information, I have everything in file a.txt: INSERT INTO TLK (kodas, liga) VALUES ('(01-99)', 'Traumu priežastys (ne iš TLK10 klasifikacijos)') INSERT INTO TLK (kodas, liga) VALUES ('01-99', 'Ne TLK10 traumu priežasciu klasifikacija') INSERT INTO TLK (kodas, liga) VALUES ('1', 'Transporto ivykis') a.txt contains about 10 000 records. I run this program, but everytime it breaks, it shows error that data would be truncated... how can I fix that? using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Kikilis { public partial class Form1 : Form { public Form1() { InitializeComponent(); } string eilute; int id = 0; StringBuilder errorMessages = new StringBuilder(); private void button1_Click(object sender, EventArgs e) { try { System.IO.StreamReader sr = new System.IO.StreamReader("a.txt"); eilute = sr.ReadLine(); string j = "Data Source=KOMPAS;Initial Catalog=MEDIS;Integrated Security=True"; SqlConnection c = new SqlConnection(j); c.Open(); while (eilute != "" || eilute == null) { SqlCommand cmd = new SqlCommand(eilute, c); cmd.ExecuteNonQuery(); cmd.Dispose(); eilute = sr.ReadLine(); } c.Close(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } Console.WriteLine(errorMessages.ToString()); } } } }
That means that some of the insert statements have data that is too large to fit in the specified maximum size for that field in the database. Why don't you include the instert statement that caused the error in the error message? --- b { font-weight: normal; }
-
Hi, I want to insert into my database some information, I have everything in file a.txt: INSERT INTO TLK (kodas, liga) VALUES ('(01-99)', 'Traumu priežastys (ne iš TLK10 klasifikacijos)') INSERT INTO TLK (kodas, liga) VALUES ('01-99', 'Ne TLK10 traumu priežasciu klasifikacija') INSERT INTO TLK (kodas, liga) VALUES ('1', 'Transporto ivykis') a.txt contains about 10 000 records. I run this program, but everytime it breaks, it shows error that data would be truncated... how can I fix that? using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Kikilis { public partial class Form1 : Form { public Form1() { InitializeComponent(); } string eilute; int id = 0; StringBuilder errorMessages = new StringBuilder(); private void button1_Click(object sender, EventArgs e) { try { System.IO.StreamReader sr = new System.IO.StreamReader("a.txt"); eilute = sr.ReadLine(); string j = "Data Source=KOMPAS;Initial Catalog=MEDIS;Integrated Security=True"; SqlConnection c = new SqlConnection(j); c.Open(); while (eilute != "" || eilute == null) { SqlCommand cmd = new SqlCommand(eilute, c); cmd.ExecuteNonQuery(); cmd.Dispose(); eilute = sr.ReadLine(); } c.Close(); } catch (SqlException ex) { for (int i = 0; i < ex.Errors.Count; i++) { errorMessages.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "LineNumber: " + ex.Errors[i].LineNumber + "\n" + "Source: " + ex.Errors[i].Source + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } Console.WriteLine(errorMessages.ToString()); } } } }
Without studying your code too closely - are you sure that you are not getting truncation errors because you are trying to insert a string which is too large for the field? ie. a 50 char string when the field is defined as VarChar(40)? Also, this line:
while (eilute != "" || eilute == null)
looks suspect. While loops normally run while a variable
!= null. Regards, Rob Philpott. -
That means that some of the insert statements have data that is too large to fit in the specified maximum size for that field in the database. Why don't you include the instert statement that caused the error in the error message? --- b { font-weight: normal; }
different record every time, for example now it stoped after inserting second record: in table kodas is nvarchar 100; liga is nvarcha 150; INSERT INTO TLK (kodas, liga) VALUES ('A69.9', 'Spirochetu sukelta infekcija, nepatikslinta') INSERT INTO TLK (kodas, liga) VALUES ('A70', 'Papugu chlamidiju infekcija') INSERT INTO TLK (kodas, liga) VALUES ('A70-A74', 'Kitos chlamidiju sukeltos ligos')