How to loop till the last or null row.

Discussion in 'ASP .Net' started by bthumber, Aug 24, 2009.

  1. bthumber

    bthumber Guest

    My application creates hostnames in sequence. Users can delete hostnames. Too
    late for soft deletes. After the app creates a hostname it checks to see if
    the created name and the one in the database are the same. If so, increment
    the count and check again. But, it only checks that record, I need to kept
    checking and incrementing till I hit a null row. How do you do that? Here is
    my code below.


    private void InsertRecord(string host, DateTime dateadded, int requestID,
    string smc, string serialNum, string description, string firstname, string
    lastname)
    {
    string dbHost = lblHostname.Text;
    dateadded = DateTime.Now;

    if (Session["Inserted"] == null)
    {
    string sql = "SELECT * FROM HostName WHERE Host='" + dbHost + "'";
    using (SqlConnection cn = new
    SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
    {
    cn.Open();
    SqlCommand cmd = new SqlCommand(sql, cn);
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows)
    {
    while (dr.Read())
    {
    lblInDb.Text = Convert.ToString(dr["Host"]);
    }
    }
    else
    {
    lblInDb.Text = "";
    }
    Session["inDb"] = lblInDb.Text;
    }

    int count = Convert.ToInt32(Session["hostCount"]);

    while (lblHostname.Text.Equals(lblInDb.Text)) // while equal
    {
    if (lblHostname.Text.Equals(lblInDb.Text))
    {
    count ++;

    lblSubStr.Text = Convert.ToString(count);

    if ((count >= 0) && (count <= 98)) // This was 99
    {
    SequenceNumbers sn = new SequenceNumbers();
    lblSubStr.Text = sn.GetNumberSequence(count);
    }
    else if ((count >= 0) && (count <= 233)) // was ((count
    >= 100) && (count <= 233))

    {
    if (count > 99)
    {
    count = count - 100;
    count++;

    AlphaNumberSeq ans = new AlphaNumberSeq();
    lblSubStr.Text = ans.GetAlphaNumberSeq(count);
    }
    }
    else if (count >= 333) // was 234
    {
    count = count - 334;
    count++;

    AlphaAlphaSeq aas = new AlphaAlphaSeq();
    lblSubStr.Text = aas.IntToChar(count);
    }
    IncrementedHostSequence();

    host = lblHostname.Text;
    lastname = Session["lastname"].ToString();
    firstname = Session["firstname"].ToString();

    if (Session["nPage"] != null)
    {
    smc = Session["NSMC"].ToString();
    serialNum = Session["Serial"].ToString();
    if (Session["Ndescript"] == null)
    {
    description = "N/A";
    }
    else
    {
    description = Session["Ndescript"].ToString();
    }
    }
    if (Session["sPage"] != null)
    {
    smc = Session["sSCen"].ToString();
    serialNum = Session["sSerialNum"].ToString();
    if (Session["serdescript"] == null)
    {
    description = "N/A";
    }
    else
    {
    description = Session["serdescript"].ToString();
    }
    }
    if (Session["sdPage"] != null)
    {
    smc = Session["sdsmc1"].ToString();
    serialNum = Session["sdSerialNumber"].ToString();
    if (Session["storDescript"] == null)
    {
    description = "N/A";
    }
    else
    {
    description = Session["storDescript"].ToString();
    }
    }
    if (Session["wsPage"] != null)
    {
    if (Session["workDescript"] == null)
    {
    description = "N/A";
    }
    else
    {
    description = Session["workDescript"].ToString();
    }
    smc = Session["wsSrvce"].ToString();
    }

    SqlConnection conn = new
    SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
    SqlCommand cmd = new SqlCommand("INSERT INTO [HostName]
    ([Host], [DateAdded], [HostNameRequestID], [SMCContact], [DeviceSerialNum],
    [Description], [UserFirstName], [UserLastName]) VALUES (@Host, @DateAdded,
    @HostNameRequestID, @SMCContact, @DeviceSerialNum, @Description,
    @UserFirstName, @UserLastName)", conn);
    cmd.Parameters.AddWithValue("Host", host);
    cmd.Parameters.AddWithValue("DateAdded", dateadded);
    cmd.Parameters.AddWithValue("HostNameRequestID",
    requestID);
    cmd.Parameters.AddWithValue("SMCContact", smc);
    cmd.Parameters.AddWithValue("DeviceSerialNum", serialNum);
    cmd.Parameters.AddWithValue("Description", description);
    cmd.Parameters.AddWithValue("UserFirstName", firstname);
    cmd.Parameters.AddWithValue("UserLastName", lastname);

    try
    {
    conn.Open();
    cmd.ExecuteNonQuery();
    }
    catch (Exception err)
    {
    lblMsgErr.Visible = true;
    lblMsgErr.Text += "<br><b> Possible duplicate IP
    address </b> " + err.Message;
    }
    finally
    {
    if (conn != null) { conn.Close(); }
    }
    }
    } // End of first loop while equal.
     
    bthumber, Aug 24, 2009
    #1
    1. Advertising

  2. bthumber

    bthumber Guest

    Alexey,

    Thanks for your help. From the part that is unreadable...SequenceNumbers is
    a class count the number of records from 01 - 98. the hostname buffal is
    concatenated to number to form buffal01. The next one AlphaNumberSeq a class
    the creates alphabets and numbers like buffala1 the a1 is concatenated onto
    buffal. The last one AlphaAlphaSeq returns aa to zz ending using base 26.
    The other part there are 233 possible conbinations of AlphaNumber but if the
    count is over 98 I need to reset count to zero example: numbers sequence. If
    the last number is buffal99 the next record will be buffala1,
    buffala2...buffalb1 thur buffalz9.


    "Alexey Smirnov" wrote:

    > On Aug 24, 11:33 pm, bthumber <>
    > wrote:
    >
    > > {
    > > string dbHost = lblHostname.Text;

    >
    > [1]
    >
    >
    > > dateadded = DateTime.Now;
    > >
    > > if (Session["Inserted"] == null)
    > > {
    > > string sql = "SELECT * FROM HostName WHERE Host='" + dbHost + "'";

    >
    > 1) Read about SQL Injections.
    > 2) Do not use * when you need just one column (Host)
    >
    >
    > > using (SqlConnection cn = new
    > > SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]))
    > > {
    > > cn.Open();
    > > SqlCommand cmd = new SqlCommand(sql, cn);
    > > SqlDataReader dr = cmd.ExecuteReader();
    > > if (dr.HasRows)
    > > {
    > > while (dr.Read())
    > > {
    > > lblInDb.Text = Convert.ToString(dr["Host"]);

    >
    > What's this? You loop through all values and set the lblInDb.Text in
    > the cycle. If SQL returned more than one row it will set Text into
    > last value. If you need just one last value, you should change your
    > SQL query to
    >
    > SELECT TOP 1 Host FROM HostName WHERE Host=... ORDER BY ...
    >
    > In this case you would not need to have loop and SqlDataReader. You
    > could use ExecuteScalar to get your value.
    >
    > Like this:
    >
    > SqlCommand cmd = new SqlCommand(sql, cn);
    > lblInDb.Text = cmd.ExecuteScalar();
    >
    >
    > > }
    > > }
    > > else
    > > {
    > > lblInDb.Text = "";
    > > }
    > > Session["inDb"] = lblInDb.Text;
    > > }

    >
    > Is host from database equal to value from lblHostname.Text?
    >
    >
    > >
    > > int count = Convert.ToInt32(Session["hostCount"]);
    > >
    > > while (lblHostname.Text.Equals(lblInDb.Text)) // while equal
    > > {

    >
    > If lblInDb.Text was empty string and database has no empty hosts you
    > would have a loop forever:
    >
    > while ("".Equals(""))
    >
    >
    > > if (lblHostname.Text.Equals(lblInDb.Text))
    > > {

    >
    > Why to check it once again?
    >
    >
    > > count ++;
    > >
    > > lblSubStr.Text = Convert.ToString(count);
    > >
    > > if ((count >= 0) && (count <= 98)) // This was 99

    >
    > It cannot be 0 because of count ++;
    >
    > The rest of your code is unreadable. Please explain in details what
    > should be done there. Please provide some examples of user input,
    > database data and a desired output.
    >
    > > {
    > > SequenceNumbers sn = new SequenceNumbers();
    > > lblSubStr.Text = sn.GetNumberSequence(count);
    > > }
    > > else if ((count >= 0) && (count <= 233)) // was ((count>= 100) && (count <= 233))
    > >
    > > {
    > > if (count > 99)
    > > {
    > > count = count - 100;
    > > count++;
    > >
    > > AlphaNumberSeq ans = new AlphaNumberSeq();
    > > lblSubStr.Text = ans.GetAlphaNumberSeq(count);
    > > }
    > > }
    > > else if (count >= 333) // was 234
    > > {
    > > count = count - 334;
    > > count++;
    > >
    > > AlphaAlphaSeq aas = new AlphaAlphaSeq();
    > > lblSubStr.Text = aas.IntToChar(count);
    > > }
    > > IncrementedHostSequence();
    > >
    > > host = lblHostname.Text;
    > > lastname = Session["lastname"].ToString();
    > > firstname = Session["firstname"].ToString();
    > >
    > > if (Session["nPage"] != null)
    > > {
    > > smc = Session["NSMC"].ToString();
    > > serialNum = Session["Serial"].ToString();
    > > if (Session["Ndescript"] == null)
    > > {
    > > description = "N/A";
    > > }
    > > else
    > > {
    > > description = Session["Ndescript"].ToString();
    > > }
    > > }
    > > if (Session["sPage"] != null)
    > > {
    > > smc = Session["sSCen"].ToString();
    > > serialNum = Session["sSerialNum"].ToString();
    > > if (Session["serdescript"] == null)
    > > {
    > > description = "N/A";
    > > }
    > > else
    > > {
    > > description = Session["serdescript"].ToString();
    > > }
    > > }
    > > if (Session["sdPage"] != null)
    > > {
    > > smc = Session["sdsmc1"].ToString();
    > > serialNum = Session["sdSerialNumber"].ToString();
    > > if (Session["storDescript"] == null)
    > > {
    > > description = "N/A";
    > > }
    > > else
    > > {
    > > description = Session["storDescript"].ToString();
    > > }
    > > }
    > > if (Session["wsPage"] != null)
    > > {
    > > if (Session["workDescript"] == null)
    > > {
    > > description = "N/A";
    > > }
    > > else
    > > {
    > > description = Session["workDescript"].ToString();
    > > }
    > > smc = Session["wsSrvce"].ToString();
    > > }
    > >
    > > SqlConnection conn = new
    > > SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
    > > SqlCommand cmd = new SqlCommand("INSERT INTO [HostName]
    > > ([Host], [DateAdded], [HostNameRequestID], [SMCContact], [DeviceSerialNum],
    > > [Description], [UserFirstName], [UserLastName]) VALUES (@Host, @DateAdded,
    > > @HostNameRequestID, @SMCContact, @DeviceSerialNum, @Description,
    > > @UserFirstName, @UserLastName)", conn);
    > > cmd.Parameters.AddWithValue("Host", host);
    > > cmd.Parameters.AddWithValue("DateAdded", dateadded);
    > > cmd.Parameters.AddWithValue("HostNameRequestID",
    > > requestID);
    > > cmd.Parameters.AddWithValue("SMCContact", smc);
    > > cmd.Parameters.AddWithValue("DeviceSerialNum", serialNum);
    > > cmd.Parameters.AddWithValue("Description", description);
    > > cmd.Parameters.AddWithValue("UserFirstName", firstname);
    > > cmd.Parameters.AddWithValue("UserLastName", lastname);
    > >
    > > try
    > > {
    > > conn.Open();
    > > cmd.ExecuteNonQuery();
    > > }
    > > catch (Exception err)
    > > {
    > > lblMsgErr.Visible = true;
    > > lblMsgErr.Text += "<br><b> Possible duplicate IP
    > > address </b> " + err.Message;
    > > }
    > > finally
    > > {
    > > if (conn != null) { conn.Close(); }
    > > }
    > > }
    > > } // End of first loop while equal
     
    bthumber, Aug 25, 2009
    #2
    1. Advertising

  3. On Aug 25, 10:25 pm, bthumber <>
    wrote:
    > Alexey,
    >
    > Thanks for your help. From the part that is unreadable...SequenceNumbers is
    > a class count the number of records from 01 - 98. the hostname buffal is
    > concatenated to number to form buffal01. The next one AlphaNumberSeq a class
    > the creates alphabets and numbers like buffala1 the a1 is concatenated onto
    > buffal. The last one AlphaAlphaSeq returns aa to zz ending using base 26.  
    > The other part there are 233 possible conbinations of AlphaNumber but if the
    > count is over 98 I need to reset count to zero example: numbers sequence. If
    > the last number is buffal99 the next record will be buffala1,
    > buffala2...buffalb1 thur buffalz9.
    >


    Ok, I suppose that the algorithm is working as expected.

    Back to your original question. "It only checks that record" because
    you do call database only once. To make all simple, you need to
    rearrange the code

    void InsertRecord(...)
    {

    host = typed_value;

    while (true)
    {
    if hostExists(host) {
    host = getNext(host);
    } else {
    break;
    }

    if (host == null)
    break;
    }

    addHost(host );

    }

    where you can move the part with calculation algorithm to getNext(),
    hostExists() would be responsible for checking it in the database, and
    addHost would finally add new record.

    getNext would return null in the case when all possible combination
    were used

    Looks more clear, isn't it?

    The disadvantage of this approach is that in some cases you would call
    your database many times (when user typed "buffal" and you have
    already "buffal01"..."buffalz9" in the database). You can avoid it by
    either taking all "buffal%" from the database into a recordset or by
    moving all logic to the database as a stored procedure. If you don't
    want to change a lot, take first approach

    void InsertRecord(...)
    {

    host = typed_value;
    sql = "SELECT host FROM HostName WHERE host LIKE '" + host + "%' ORDER
    BY host"; // should be buffal%

    SqlDataReader dr = objCommand.ExecuteReader(...);

    while (dr.Read())
    {
    if (host == dr["host"])
    {
    host = getNext(host);
    } else {
    break;
    }

    if (host == null)
    break;
    }

    addHost(host );

    }
     
    Alexey Smirnov, Aug 26, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. M. Duijkers
    Replies:
    2
    Views:
    492
    Joe Smith
    Dec 12, 2004
  2. Replies:
    5
    Views:
    27,233
    Mike Schilling
    Mar 29, 2006
  3. Michel Rouzic
    Replies:
    6
    Views:
    806
    A. Sinan Unur
    Dec 8, 2005
  4. D
    Replies:
    0
    Views:
    236
  5. Isaac Won
    Replies:
    9
    Views:
    419
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page