C# Discussion Boards - CodeProject

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

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

OriginalGriff

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.

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

OriginalGriff

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

OriginalGriff

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

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

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

22-Nov-20 22:40

Hi Richard,

The half-blind man salutes the eagle-eyed man Smile | :)

I was aware of the fact that ElementAt is an Extension method of IEnumerable; that's the reason I listed the inheritance chain for SortedSet at the top of my post. Just stupidity for me not to spot my syntax error.

I'm also aware it's an expensive call.

A QA question today [^] got me interested in exploring some of the Sorted/Ordered data structures in terms of their overhead and utility ... when duplicate keys are required. They have real constraints/costs when various patterns of usage are needed: inserts, deletes, etc.

My goal, as ever, in my compulsive quest to exit this incarnation with better understanding of the glory of C# and .NET ... is to gain heuristic knowledge: which is the better tool to use to construct a data structure, given context and usage. My one good eye sees you as exemplary in that regard ... go ahead and blush OMG | :OMG:

cheers, 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

Richard Deeming22-Nov-20 22:58

need some guidance and advice to build a questionnaire system similar to Google forms in vb.net or c# and sql server

22-Nov-20 22:58

There's some basic performance data in the Microsoft docs for a few collection types:
Collections and Data Structures | Microsoft Docs[^]
Sorted Collection Types | Microsoft Docs[^]

But there are so many different collection classes available these days, even if you restrict yourself to the BCL, that it would be difficult to find a source to compare the performance of all of them. Smile | :)

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

albasheer210019-Nov-20 18:09

albasheer2100

19-Nov-20 18:09

I need some guidance and advice to build a questionnaire system similar to Google forms that include two parts,

the first part is a desktop system with vb.net or c# and separate SQL server database (it must be a desktop system because it will be part of a vb.net old system, The new project will be called from within the main interface of the old project )

and the second part is a web system, and the two systems are linked to one SQL server database through a local network and a local server

the desktop system builds multiple questionnaires forms and each questionnaire is associated with a specific user to answer these questions through the web system via the username that will be assigned to him by the desktop system... Here, I would like to note that each user has a different questionnaire from the other...

The desktop system will also use the answers to build a dashboard has charts and report based on the analysis of the answers

My questions ...

1- What is the best way to design (questionnaire builder) if I want it to work and similar to Google Forms method and user interface of building questions if each user has its own questionnaire that contains an unlimited set of questions, the new question is added through the Add button and each question is specified by the admin It determines the nature of the answer and the answer and contains a tool to choose a specific classification from a group of classifications that are added to the system, and each question contains in addition to the answers a field of notes and evaluation from 1 to 5, and after building this questionnaire for a specific user it is permanently saved and linked with this user and there is a possibility to edit these questions on This questionnaire for the future.

Is the User Control method the best way to build a question-building interface, or is there a better way and how?

2- What are your tips and directions that help me to build a better design for such a system?

Greetings

[REPOST] need some guidance and advice to build a questionnaire system similar to Google forms in vb.net or c# and sql server

Richard Deeming19-Nov-20 21:45