Click here to Skip to main content
15,031,941 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I bring the stock code and name information from the sql database into the datagridview I use in the program I wrote. As I stated in the example below, when adding a new inventory card, I want it to give +1 more than the largest code in the line I have selected, but in my project it gives +1 more than the largest record, can it give the highest value according to the inventory code on the line I choose, is it possible to do this?
Code | Details
0001 | Pencil
0002 | Paper
0003 | Rubber
tk22 | Battery
tk23 | Glass
................

If I select the line with the code 0002 in the list above, when I add a new stock card, the code 0004 should come, or if I choose tk22, it should come tk24, but the program gives me the code tk24 no matter what I choose.

What I have tried:

C#
conn.Open();
SqlCommand comm = new SqlCommand("SELECT max(STOCKID) from STOCKS WHERE STOCKID=@STOCKID ", conn);
comm.Parameters.Add("@STOCKID", SqlDbType.VarChar).Value = Convert.ToString(dgvStock.SelectedRows[0].Cells[1].Value);
string StockID = Convert.ToString(comm.ExecuteScalar());
conn.Close();
txtbirimID.Text = StockID;
Posted
Updated 13-Jan-21 23:33pm
v2
Comments
Maciej Los 14-Jan-21 6:02am
   
I'm pretty sure i've seen this question earlier...

1 solution

That's because you are using a string based ID code, and expecting comparisons to work on the numeric bits.

They don't. String based comparisons work by comparing two strings character by character from the left had side until they find the first pair of different characters.
The whole comparison is then based on the difference between that pair of characters, no other characters in the strings are even looked at.

So if you have strings containing the numeric values "1", "2, "10", "19, "20", and "100" the sort order will be:
1
10
19
100
2
20
Not the numeric sequence you would expect.

And the character comparison uses the ASCII (for VARCHAR columns) or Unicode (for NVARCHAR columns) character set where '0' is less than 'A', which is less than 'a', and so on.

You would need to work out exactly what "sort oder" your various code values should have, establish the rules, and implement them manually in order for your current design to work. That isn't going to be at all easy, given that string handling in SQL is pretty basic.

Me? I wouldn't pregenerate codes, and I'd have a consistent system code code values which could be automated probably via a Computed Column.
   
Comments
Osmantanca 14-Jan-21 5:13am
   
Thank you for your explanation, first of all, I do the necessary conversions for the string field and I can only find the maximum value of the numeric values in the field, I have no problem here. What I want is to find the highest value according to the structure of the code in the line I choose in the Datagrid. So I want to be able to find the largest numerical value according to the code content.
OriginalGriff 14-Jan-21 5:29am
   
That's not what your code is doing: or even close. Your code retrieves all rows where the code matches exactly and returns the max value of the ID from that. Since all the rows retrieved will by definition have the same ID you will always get the the single ID value back.

And your sample data shows only one row per id ...

I suspect you need to go back to yoru design and rethink it somewhat - I'm not at all sure what you expect this DB to be able to do, but it doesn't sound like you have thought it through too carefully!
Osmantanca 14-Jan-21 5:44am
   
I think we can solve this problem with SQL query. If we consider my problem program independent, let's assume that there is a loaded SQL table. In the content of this table, there are columns with different features. I want to find the greatest value of one of these columns according to the data in the field. Notice that I say the biggest value according to the data content, not the biggest value of the column. We can sort by content using Group by or order, and since it does this, it should be able to find the highest value according to the content, do you think it's wrong?
OriginalGriff 14-Jan-21 6:04am
   
Stop thinking about implementation, and think about exactly what you are trying to do.
Expand the sample data you gave us to include all the columns, and provide sample data of exactly what you want to get returned from the input sample data.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900