C# Discussion Boards - CodeProject

Re: Is my program idea possible?

28-Nov-20 8:22

Dioxazine wrote:
If you came to me for guitar lessons I too could slice you to ribbons for all that you don't know.

Yes, because I know nothing.

Dioxazine wrote:
But that is a very poor way of teaching guitar.

And so is what you are doing.

Suppose I came to you for help with my guitar playing, and told you that I was learning on my own by guessing what to do and wondering why I can't play as fast as others - but the forks and spoons I jam under the frets to select the notes to strum take me too long to pull out and move?

Clearly, I don't know enough to play the guitar because I've spent so long guessing what to do instead of sitting down and actually learning the right way!

And that's where you are with development. You've found code that does some of what you want, but you don't understand how it works - or even exactly what it is doing - so you can't change it to do something else yourself. And for the same reasons as me with my guitar spoons - they work for me, but not very well - I need to be taken back to the beginning and learn how to play in a structured way that introduces chords and so on in a logical progression.

Seriously, even as a hobby this is a complicated subject and it really benefits from learning the basics thoroughly before you try to rush ahead to "more interesting" projects.

"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!

Liktor Janos11-Dec-20 7:42

Liktor Janos

11-Dec-20 7:42

Why do you need stream to load picture into viewer?
Just imagine this:
You(stream) are necessary to play(load) a song(picture) at your guitar(viewer).

Re: Is my program idea possible?

Gerry Schmitz28-Nov-20 7:51

Gerry Schmitz

28-Nov-20 7:51

You should just use a "picture viewer". Something like MS Photo. It illustrates concepts such as files and folders.

It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food

Error in SQL Light Compact 4

Alex Dunlop23-Nov-20 3:47

23-Nov-20 3:47

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:

<pre> 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 ]'

What is wrong with my code?

Victor Nijegorodov23-Nov-20 3:56

Victor Nijegorodov

23-Nov-20 3:56

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 Dunlop23-Nov-20 4:04

23-Nov-20 4:04

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.

Richard Deeming23-Nov-20 4:18

Richard Deeming

23-Nov-20 4:18

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? Smile | :)

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

OriginalGriff23-Nov-20 4:29

23-Nov-20 4:29

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.

Alex Dunlop23-Nov-20 7:38

23-Nov-20 7:38

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

I reviewed the loop but I couldn't find out the reason.

OriginalGriff23-Nov-20 7:53

23-Nov-20 7:53

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?

Alex Dunlop23-Nov-20 8:10

23-Nov-20 8:10

It adds a value to the end of the SqlParameterCollection.

OriginalGriff23-Nov-20 8:19

23-Nov-20 8:19

And the error message is telling you "The SqlCeParameter with this name is already contained by this SqlCeParameterCollection"

Put the two together, and ...

Alex Dunlop23-Nov-20 8:28

23-Nov-20 8:28

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.

modified 23-Nov-20 14:54pm.

Richard Deeming23-Nov-20 17:43

Richard Deeming

23-Nov-20 17:43

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 Dunlop24-Nov-20 6:06

24-Nov-20 6:06

I solved it by adding

<pre lang="c#">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 Kreskowiak24-Nov-20 8:52

Dave Kreskowiak

24-Nov-20 8:52

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

modified 25-Nov-20 0:59am.

interesting issue with SortedSet and ElementAt

Dave Kreskowiak23-Nov-20 5:58

Dave Kreskowiak

23-Nov-20 5:58

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
Google: C# How to debug code
Seriously, go read these articles.

Dave Kreskowiak

BillWoodruff22-Nov-20 18:59

22-Nov-20 18:59

SortedSet implements:

[System.Serializable]
public class SortedSet<T> : System.Collections.Generic.ICollection<T>, System.Collections.Generic.IEnumerable<T>, System.Collections.Generic.IReadOnlyCollection<T>, System.Collections.Generic.ISet<T>, System.Collections.ICollection, System.Runtime.Serialization.IDeserializationCallback, System.Runtime.Serialization.ISerializable

There are numerous examples of using ElementAt on SortedSets on the web, like this [^], however, I find using it on a SortedSet<KeyValuePair<string, string>>

if (SortedNames != null)
{
    for (int i = 0; i < SortedNames.Count; i++)
    {
        KeyValuePair<string, string> item = SortedNames.ElementAt[i];
        Console.WriteLine(item);
    }
}

creates a compile error: "CS0021 Cannot apply indexing with [] to an expression of type 'method group" ... I can't locate any matching error report for this and SortedSet.

fyi: I am using a custom IComparer<KeyValuePair<string, string>> for sorting, but, that's working as expected.

Hypothesis: something about using KeyValuePair results in this error.

Ideas ?

thanks, Bill

«One day it will have to be officially admitted that what we have christened reality is an even greater illusion than the world of dreams.» Salvador Dali

modified 23-Nov-20 1:47am.

Sandeep Mewara22-Nov-20 20:06

Sandeep Mewara

22-Nov-20 20:06

Hey Bill!

For using KeyValuePair in SortedSet, you need to define IComparable for it. Until it's not there, SortedSet would not be able to work on the datatype (as inherently it is supposed to sort)

To see what I say, comment your ElementAt line and run the code. You will get a runtime error as "At least one object must implement IComparable."
UPDATE: Below sample works fine with ElementAt. Where is the gap?

Once we have a defined sorted data, we can access with ElementAt. I put up a quick example:

class SortedSetDemo
{
    static void Main(string[] args)
    {
        var words = new string[]
            {"the", "quick", "brown", "fox", "jumps",
             "over", "the", "lazy", "dog"};

        // Create a sorted set.
        var wordSet2 = new SortedSet<KeyValuePair<int, string>>(new KVPKeyComparer<int, string>());
        int x = 0;
        foreach (string word in words)
        {
            x++;
            wordSet2.Add(new KeyValuePair<int, string>(x, word));
        }

        // List the members of the sorted set.
        Console.WriteLine("Set items in sorted order:");

        // Access an item at a specified index (position).
        for (int j = 0; j < wordSet2.Count; j++)
        {
            var item = wordSet2.ElementAt(j);
            Console.WriteLine(item);
        }
    }

    public class KVPKeyComparer<TKey, TValue> : IComparer<KeyValuePair<TKey, TValue>> where TKey : IComparable
    {
        public int Compare(KeyValuePair<TKey, TValue> x, KeyValuePair<TKey, TValue> y)
        {
            if(x.Key == null)
            {
                if(y.Key == null)
                    return 0;
                return -1;
            }

            if(y.Key == null)
                return 1;

            return x.Key.CompareTo(y.Key);
        }
    }
}

Outputs:

[1, the]
[2, quick]
[3, brown]
[4, fox]
[5, jumps]
[6, over]
[7, the]
[8, lazy]
[9, dog]

Latest CodeProject post:
How to solve Word Ladder Problem?
Linear Time Partition – A Three Way Split

To read all my blog posts, visit: Learn by Insight...

modified 23-Nov-20 4:44am.

BillWoodruff22-Nov-20 22:19

22-Nov-20 22:19

Hi Sandeep,

Did you not see my statement:

Quote:
fyi: I am using a custom IComparer<keyvaluepair<string, string="">> for sorting, but, that's working as expected.

«One day it will have to be officially admitted that what we have christened reality is an even greater illusion than the world of dreams.» Salvador Dali

Sandeep Mewara22-Nov-20 22:38

Sandeep Mewara

22-Nov-20 22:38

Sure I missed it. Apologies.

Now, If you are using, you should not get the error. Sample I shared above is working fine for ElementAt, to which your query was related to. Is that of some help?

UPDATE:
Chuck it. It's the square and circular brackets difference between yours and my code. You already got that, so Thumbs Up | :thumbsup:

Latest CodeProject post:
How to solve Word Ladder Problem?
Linear Time Partition – A Three Way Split

To read all my blog posts, visit: Learn by Insight...

modified 23-Nov-20 4:47am.

BillWoodruff22-Nov-20 22:46

22-Nov-20 22:46

Hi Sandeep-ji, कोई बात नहीं Smile | :)

See Richard Deeming's post for the correct diagnosis.

thanks, Bill

«One day it will have to be officially admitted that what we have christened reality is an even greater illusion than the world of dreams.» Salvador Dali

Sandeep Mewara22-Nov-20 22:51

Sandeep Mewara

22-Nov-20 22:51

Latest CodeProject post:
How to solve Word Ladder Problem?
Linear Time Partition – A Three Way Split

To read all my blog posts, visit: Learn by Insight...

Richard Deeming22-Nov-20 22:15

Richard Deeming

22-Nov-20 22:15

SortedSet<T>[^] doesn't have a property called ElementAt.

The ElementAt[^] method is an extension method defined for all IEnumerable<T> instances. You'll need round parentheses to call it, rather than square brackets, and you'll need a using System.Linq; declaration at the top of your code file.

But in your example, it would be better to simply use a foreach loop instead. The ElementAt method has to create an enumerator and enumerate i elements of the set for each iteration of your for loop, whereas a foreach loop would only create a single enumerator and enumerate the set once.

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

BillWoodruff22-Nov-20 22:40