Solved

SQL data reader

Posted on 2011-02-14
7
538 Views
Last Modified: 2012-08-14
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
Comment
Question by:ayha1999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 200 total points
ID: 34889771
Change
  if (Reader.Read())
to
While (Reader.Read())

This will loop
0
 
LVL 18

Expert Comment

by:deighton
ID: 34889834
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
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 50 total points
ID: 34889919
Your select returns only 1 row because of Max function.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Author Comment

by:ayha1999
ID: 34889966
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
 
LVL 7

Expert Comment

by:jdavistx
ID: 34890045
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34890050
Please post the complete code
Raj
0
 
LVL 7

Author Comment

by:ayha1999
ID: 34890080
Now the problem is with the select command.

thanks for the help.

ayha
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question