Error in SQL Light Compact 4
-
Thank you. solved. When I keep the last column in DataGridView null, compiler give the following error in line
System.NullReferenceException: 'Object reference not set to an instance of an object.'
System.NullReferenceException: 'Object reference not set to an instance of an object.'
I have check marked null properties for this column in SQL server.
That's not an SQL error - that's a C# error:
myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows[i].Cells[10].Value.ToString());
If the cell value is
null, you can't callToString(or any other property / method) on it. You could trymyCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows[i].Cells[10].Value?.ToString());
Depending on your C# version.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Hi, I want to save all of my DataGridView content directly into a SQL server. I'm Using SQL Light Compact 4.0 All codes related to save function are in a button:
private void btnSave_Click(object sender, EventArgs e)
{
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = connection;for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.CommandText = @"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, End, Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"; myCommand.Parameters.AddWithValue("@Number", dataGridView1.Rows\[i\].Cells\[0\].Value.ToString()); myCommand.Parameters.AddWithValue("@CostCenter", dataGridView1.Rows\[i\].Cells\[1\].Value.ToString()); myCommand.Parameters.AddWithValue("@pType", dataGridView1.Rows\[i\].Cells\[2\].Value.ToString()); myCommand.Parameters.AddWithValue("@ServiceType", dataGridView1.Rows\[i\].Cells\[3\].Value.ToString()); myCommand.Parameters.AddWithValue("@Receiver", dataGridView1.Rows\[i\].Cells\[4\].Value.ToString()); myCommand.Parameters.AddWithValue("@WorkCenter", dataGridView1.Rows\[i\].Cells\[5\].Value.ToString()); myCommand.Parameters.AddWithValue("@Start", dataGridView1.Rows\[i\].Cells\[6\].Value.ToString()); myCommand.Parameters.AddWithValue("@End", dataGridView1.Rows\[i\].Cells\[7\].Value.ToString()); myCommand.Parameters.AddWithValue("@Remaining", dataGridView1.Rows\[i\].Cells\[8\].Value.ToString()); myCommand.Parameters.AddWithValue("@Status", dataGridView1.Rows\[i\].Cells\[9\].Value.ToString()); myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows\[i\].Cells\[10\].Value.ToString()); connection.Open(); myCommand.ExecuteNonQuery(); connection.Close(); } }When I press the button, compiler gives error in line
myCommand.ExecuteNonQuery();
'There was an error parsing the query. [ Token line number = 1,Token line offset = 92,Token in error = End ]'
Wh
What nobody mentioned is you are constantly adding new parameters to the SqlCommand.Parameters, on every pass of the loop. You're not reusing parameters, you're creating new ones on every iteration of the loop. On the first iteration of the loop, your Parameters collection will have 11 parameters. On the second, 22 parameters. On the 3rd, 33 parameters... Create the parameters OUTSIDE the loop, then just set the values inside.
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";SqlCeCommand myCommand = new SqlCeCommand(@"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, End, Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"); myCommand.Connection = connection; // // You'll have to set the DB type of each parameter to what the database expects. // myCommand.Parameters.Add("@Number", SqlDbType.NVarChar); myCommand.Parameters.Add("@CostCenter", ...); myCommand.Parameters.Add("@pType", ...); myCommand.Parameters.Add("@ServiceType", ...); myCommand.Parameters.Add("@Receiver", ...); myCommand.Parameters.Add("@WorkCenter", ...); myCommand.Parameters.Add("@Start", ...); myCommand.Parameters.Add("@End", ...); myCommand.Parameters.Add("@Remaining", ...); myCommand.Parameters.Add("@Status", ...); myCommand.Parameters.Add("@ReceiveDate", ...); connection.Open(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.Parameters\["@Name"\] = dataGridView1.Rows\[i\].Cells\[0\].Value.ToString(); myCommand.Parameters\["@CostCenter"\] = ... ... myCommand.ExecuteNonQuery(); } connection.Close();DO NOT STORE DATES AS STRINGS IN THE DATABASE! Store them as the appropriate DateTime type.
-
That's not an SQL error - that's a C# error:
myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows[i].Cells[10].Value.ToString());
If the cell value is
null, you can't callToString(or any other property / method) on it. You could trymyCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows[i].Cells[10].Value?.ToString());
Depending on your C# version.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I added a line for clearing sql table before saving new data and then add new data. There are two problems:
private void btnSave_Click(object sender, EventArgs e)
{
update.PerformClick();
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = connection;connection.Open(); //Clearing data table myCommand.CommandText = "DELETE FROM PMinfo"; connection.Close(); myCommand.CommandText = @"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, \[End\], Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"; connection.Open(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.Parameters.AddWithValue("@Number", dataGridView1.Rows\[i\].Cells\[0\].Value); myCommand.Parameters.AddWithValue("@CostCenter", dataGridView1.Rows\[i\].Cells\[1\].Value); myCommand.Parameters.AddWithValue("@pType", dataGridView1.Rows\[i\].Cells\[2\].Value); myCommand.Parameters.AddWithValue("@ServiceType", dataGridView1.Rows\[i\].Cells\[3\].Value); myCommand.Parameters.AddWithValue("@Receiver", dataGridView1.Rows\[i\].Cells\[4\].Value); myCommand.Parameters.AddWithValue("@WorkCenter", dataGridView1.Rows\[i\].Cells\[5\].Value); myCommand.Parameters.AddWithValue("@Start", dataGridView1.Rows\[i\].Cells\[6\].Value); myCommand.Parameters.AddWithValue("@End", dataGridView1.Rows\[i\].Cells\[7\].Value); myCommand.Parameters.AddWithValue("@Remaining", dataGridView1.Rows\[i\].Cells\[8\].Value); myCommand.Parameters.AddWithValue("@Status", dataGridView1.Rows\[i\].Cells\[9\].Value); myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows\[i\].Cells\[10\].Value); myCommand.ExecuteNonQuery(); } connection.Close(); }1- Clearing table doesn't work. 2- When I can more than 1 rowsin DataGridView, compliler gives following error:
System.ArgumentException: 'The SqlCeParameter with this name is already contained by this SqlCeP
-
I added a line for clearing sql table before saving new data and then add new data. There are two problems:
private void btnSave_Click(object sender, EventArgs e)
{
update.PerformClick();
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = connection;connection.Open(); //Clearing data table myCommand.CommandText = "DELETE FROM PMinfo"; connection.Close(); myCommand.CommandText = @"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, \[End\], Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"; connection.Open(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.Parameters.AddWithValue("@Number", dataGridView1.Rows\[i\].Cells\[0\].Value); myCommand.Parameters.AddWithValue("@CostCenter", dataGridView1.Rows\[i\].Cells\[1\].Value); myCommand.Parameters.AddWithValue("@pType", dataGridView1.Rows\[i\].Cells\[2\].Value); myCommand.Parameters.AddWithValue("@ServiceType", dataGridView1.Rows\[i\].Cells\[3\].Value); myCommand.Parameters.AddWithValue("@Receiver", dataGridView1.Rows\[i\].Cells\[4\].Value); myCommand.Parameters.AddWithValue("@WorkCenter", dataGridView1.Rows\[i\].Cells\[5\].Value); myCommand.Parameters.AddWithValue("@Start", dataGridView1.Rows\[i\].Cells\[6\].Value); myCommand.Parameters.AddWithValue("@End", dataGridView1.Rows\[i\].Cells\[7\].Value); myCommand.Parameters.AddWithValue("@Remaining", dataGridView1.Rows\[i\].Cells\[8\].Value); myCommand.Parameters.AddWithValue("@Status", dataGridView1.Rows\[i\].Cells\[9\].Value); myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows\[i\].Cells\[10\].Value); myCommand.ExecuteNonQuery(); } connection.Close(); }1- Clearing table doesn't work. 2- When I can more than 1 rowsin DataGridView, compliler gives following error:
System.ArgumentException: 'The SqlCeParameter with this name is already contained by this SqlCeP
Alex Dunlop wrote:
I reviewed the loop but I couldn't find out the reason
Hint: What do you suppose the "Add" part of "AddWithValue" might mean?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Alex Dunlop wrote:
I reviewed the loop but I couldn't find out the reason
Hint: What do you suppose the "Add" part of "AddWithValue" might mean?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
It adds a value to the end of the SqlParameterCollection.
-
It adds a value to the end of the SqlParameterCollection.
And the error message is telling you "The SqlCeParameter with this name is already contained by this SqlCeParameterCollection" Put the two together, and ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
And the error message is telling you "The SqlCeParameter with this name is already contained by this SqlCeParameterCollection" Put the two together, and ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I think I need to put open and close parameters of sql inside the loop so in each iteration I open the sql write a row into sql table and close it and open it again and loop.... Am I right? Maybe I'd give it a try tomorrow.
-
I think I need to put open and close parameters of sql inside the loop so in each iteration I open the sql write a row into sql table and close it and open it again and loop.... Am I right? Maybe I'd give it a try tomorrow.
See Dave's reply[^] below.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
See Dave's reply[^] below.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I solved it by adding
myCommand.Parametes.Clear();
The final code:
private void btnSave_Click(object sender, EventArgs e)
{
update.PerformClick();
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = connection;connection.Open(); myCommand.CommandText = "Delete From PMinfo"; myCommand.ExecuteNonQuery(); connection.Close(); myCommand.CommandText = @"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, \[End\], Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"; connection.Open(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.Parameters.Clear(); myCommand.Parameters.AddWithValue("@Number", dataGridView1.Rows\[i\].Cells\[0\].Value); myCommand.Parameters.AddWithValue("@CostCenter", dataGridView1.Rows\[i\].Cells\[1\].Value); myCommand.Parameters.AddWithValue("@pType", dataGridView1.Rows\[i\].Cells\[2\].Value); myCommand.Parameters.AddWithValue("@ServiceType", dataGridView1.Rows\[i\].Cells\[3\].Value); myCommand.Parameters.AddWithValue("@Receiver", dataGridView1.Rows\[i\].Cells\[4\].Value); myCommand.Parameters.AddWithValue("@WorkCenter", dataGridView1.Rows\[i\].Cells\[5\].Value); myCommand.Parameters.AddWithValue("@Start", dataGridView1.Rows\[i\].Cells\[6\].Value); myCommand.Parameters.AddWithValue("@End", dataGridView1.Rows\[i\].Cells\[7\].Value); myCommand.Parameters.AddWithValue("@Remaining", dataGridView1.Rows\[i\].Cells\[8\].Value); myCommand.Parameters.AddWithValue("@Status", dataGridView1.Rows\[i\].Cells\[9\].Value); myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows\[i\].Cells\[10\].Value); myCommand.ExecuteNonQuery(); } connection.Close(); } -
I solved it by adding
myCommand.Parametes.Clear();
The final code:
private void btnSave_Click(object sender, EventArgs e)
{
update.PerformClick();
SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = @"Data Source=C:\Users\Dell\Desktop\DataGridView\PMinfo.sdf";
SqlCeCommand myCommand = new SqlCeCommand();
myCommand.Connection = connection;connection.Open(); myCommand.CommandText = "Delete From PMinfo"; myCommand.ExecuteNonQuery(); connection.Close(); myCommand.CommandText = @"INSERT into \[PMinfo\] (Number, CostCenter, pType, ServiceType, Receiver, WorkCenter, Start, \[End\], Remaining, Status, ReceiveDate) values(@Number, @CostCenter, @pType, @ServiceType, @Receiver, @WorkCenter, @Start, @End, @Remaining, @Status, @ReceiveDate)"; connection.Open(); for (int i = 0; i < dataGridView1.Rows.Count; i++) { myCommand.Parameters.Clear(); myCommand.Parameters.AddWithValue("@Number", dataGridView1.Rows\[i\].Cells\[0\].Value); myCommand.Parameters.AddWithValue("@CostCenter", dataGridView1.Rows\[i\].Cells\[1\].Value); myCommand.Parameters.AddWithValue("@pType", dataGridView1.Rows\[i\].Cells\[2\].Value); myCommand.Parameters.AddWithValue("@ServiceType", dataGridView1.Rows\[i\].Cells\[3\].Value); myCommand.Parameters.AddWithValue("@Receiver", dataGridView1.Rows\[i\].Cells\[4\].Value); myCommand.Parameters.AddWithValue("@WorkCenter", dataGridView1.Rows\[i\].Cells\[5\].Value); myCommand.Parameters.AddWithValue("@Start", dataGridView1.Rows\[i\].Cells\[6\].Value); myCommand.Parameters.AddWithValue("@End", dataGridView1.Rows\[i\].Cells\[7\].Value); myCommand.Parameters.AddWithValue("@Remaining", dataGridView1.Rows\[i\].Cells\[8\].Value); myCommand.Parameters.AddWithValue("@Status", dataGridView1.Rows\[i\].Cells\[9\].Value); myCommand.Parameters.AddWithValue("@ReceiveDate", dataGridView1.Rows\[i\].Cells\[10\].Value); myCommand.ExecuteNonQuery(); } connection.Close(); }That's one way to do it, but it's the least performant and increases heap fragmentation. Creating one set of parameter objects and reusing them is a much better method.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak
