Error in SQL Light Compact 4

Alex Dunlop

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

Victor Nijegorodov

Alex Dunlop wrote:

'There was an error parsing the query. [ Token line number = 1,Token line offset = 92,Token in error = End ]'

It seems that the parser doesn't like the field name "End". Try

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)";

Alex Dunlop

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.'

I have check marked null properties for this column in SQL server.

Richard Deeming

Quote:

dataGridView1.Rows[i].Cells[10].Value.ToString()

If the cell's Value is null, what do you think the result of calling the ToString method on the value will be? :) Remove those .ToString() calls, and pass the cell values directly to the parameters.

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);

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

OriginalGriff

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 call ToString (or any other property / method) on it. You could try

myCommand.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!

Dave Kreskowiak

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.

Asking questions is a skill CodeProject Forum Guidelines

Alex Dunlop

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

OriginalGriff

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

It adds a value to the end of the SqlParameterCollection.

OriginalGriff

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!

Alex Dunlop

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.

Richard Deeming

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

Alex Dunlop

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();
    }

Dave Kreskowiak

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