• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

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
0
ayha1999
Asked:
ayha1999
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
Change
  if (Reader.Read())
to
While (Reader.Read())

This will loop
0
 
deightonprogCommented:
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
 
dwkorCommented:
Your select returns only 1 row because of Max function.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now