SQL data reader

what is wrong with the follwoing code?  I am trying to get the newId based the sql command in the follwoing loop. But I get the newId only once.means loop is not working. How can repeat the loop till end of the data table count?

      SqlCommand cmd = new SqlCommand("select isnull(Max(SubString(Id,4,Len(Id)-3))+1,1000) as newId from myTable", cn);
        SqlDataReader Reader;
int newid;

if (!blnMatch)
                    {
                                cn.Open();                                        
                    foreach (DataRow objDR in objDT.Rows)
                                            {
         Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    if (Reader.Read())
                      {
                      newId = maxReader.GetInt32(maxReader.GetOrdinal("newId "));
                       }
                                                  Reader.Close();
                           
                                                cmdPart.Parameters["@newid"].Value = newId;
                                                cmdPart.Parameters["@userid"].Value = User.Identity.Name;
                                                cmdPart.Parameters["@date"].Value = DateTime.Now;
   
                                                try
                                                {
                                                    cn.Open();
                                                    cmd2.ExecuteNonQuery();
                                                 }
                                                 catch
{
}

pls help.

ayha
LVL 7
ayha1999Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Change
  if (Reader.Read())
to
While (Reader.Read())

This will loop
0
 
deightonCommented:
not sure what you need to achieve

    SqlCommand cmd = new SqlCommand("select isnull(Max(SubString(Id,4,Len(Id)-3))+1,1000) as newId from myTable", cn);
        SqlDataReader Reader;
int newid;

if (!blnMatch)
                    {
                                cn.Open();                                        
                    foreach (DataRow objDR in objDT.Rows)
                                            {
         Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (Reader.Read())
                      {
                      newId = maxReader.GetInt32(maxReader.GetOrdinal("newId "));

                                                  Reader.Close();
                           
                                                cmdPart.Parameters["@newid"].Value = newId;
                                                cmdPart.Parameters["@userid"].Value = User.Identity.Name;
                                                cmdPart.Parameters["@date"].Value = DateTime.Now;
   
                                                try
                                                {
                                                    cn.Open();
                                                    cmd2.ExecuteNonQuery();
                                                 }
                                                 catch
{
}
}

Open in new window

0
 
dwkorConnect With a Mentor Commented:
Your select returns only 1 row because of Max function.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ayha1999Author Commented:
Now an error generated.

System.IndexOutOfRangeException: maxNumber

at line:

at newId = int.Parse(maxReader["maxNumber"].ToString());

What I am trying to achive is I have a datable. I want insert all rows to a table. while inserting I want to generate a unique id (max no. from the table) let's says the first number is null. the it creates 1000 and inserts then in the next loop the max numbers becomes 1001 and so on. Here the loop never works.

ayha
0
 
jdavistxCommented:
I'm still not entirely sure what you mean, or what exactly you want to do.

When you create your MSSQL table, you could specify an identity column that is set to auto-increment.  So, as you add new records, the ID for each record is unique and automatically generated.  All you do is insert the other records in the row, and each subsequently added row will get a new ID value.

http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx 
0
 
Rajkumar GsSoftware EngineerCommented:
Please post the complete code
Raj
0
 
ayha1999Author Commented:
Now the problem is with the select command.

thanks for the help.

ayha
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.