Solved

SQL data reader

Posted on 2011-02-14
7
534 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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now