Cannot keep the value when I do the calculation

Question

0.00/5 (No votes)

See more:

My ReservationID like this R00001 but when auto generate the ReservationID, the value 0 cannot keep in the variable. How to solve it ?

C#

public void GenerateReservationID()
  {
      string sql = "SELECT * FROM Reservation";
      string cs = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True";
      string s = "";

      SqlConnection con = new SqlConnection(cs);
      SqlCommand cmd = new SqlCommand(sql, con);

      con.Open();
      SqlDataReader dr = cmd.ExecuteReader();
      while (dr.Read())
      {
          s = Convert.ToString(dr["ReservationID"]);
      }
      string sub1 = s.Substring(0,1);
      string sub2 = Convert.ToString(Convert.ToInt16(s.Substring(1)) + 1);
      dr.Close();
      con.Close();

      lblRservationID.Text = sub1 + sub2;
  }

[edit]Code block added - OriginalGriff[/edit]

Posted 20-Dec-13 21:13pm

Ceil Phantomhive

Updated 20-Dec-13 21:22pm

OriginalGriff

v2

Add a Solution

Comments

♥…ЯҠ…♥ 21-Dec-13 3:30am

what do you get in sub1 and sub2?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2013-12-20T21:35:00

OK, there are a couple of problems here:
1) You read all the rows, and only use the last value returned (because the loop throws away all the other values) - but you don't specify an order, so SQL can happily return the rows in any order it sees fit. They will not necessarily be returned in the order they were entered.
In addition, it is a bad idea to return data you don't need - it can slow things down considerably, and waste a lot of memory / SQL server time / bandwidth. Use SELECT only with the data you actually want.
Consider returning only the row you are interested in:

SQL

SELECT MAX(ReservationID) FROM Reservation

Will return only the current highest number, which is what I suspect you are interested in.

2) When you convert a string to an integer, you don't "lose" zeros - but the normal method to convert an integer back to a string does not insert leading zeros - so that is the effect you get. To an integer, 00000001 is the same as 1 because leading zeros are completely irrelevant.
Try using ToString with a format to specify the leading zeros instead, or String.Format:

C#

string sub1 = s.Substring(0,1);
int reserveNp = Convert.ToInt32(s.Substring(1)) + 1;
lblReservationID.Text = string.Format("{0}{1:00000}", sub1, reserveNo);

3) There is another problem though: Since you are using SQL there is a good chance that this will be used in a multi user environment, where the idea you are using here will not work: if two users go for a reservation number at the same time, there is a very, very good chance that they will both get the same value.
Where are you creating this reservation ID, and why aren't you keeping the value from there?