How to loop till the last or null row.

[bthumber]

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]

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.

{
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

 

[Alexey Smirnov]

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 );

}