Solved

SQL data reader

Posted on 2011-02-14
7
539 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
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…

738 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