Solved

Error in inserting record

Posted on 2008-06-15
8
211 Views
Last Modified: 2013-12-17
I have code as listed below. This code is working perfectly if i use MS Access (oleDbConnection) but it giving me error when i convert to use sql server 2005 express edition.
protected void btnAdd_Click(object sender, EventArgs e)
    {
 
        SqlConnection conNwind;
        SqlCommand cmdSelect, cmdSelectID;
        SqlDataReader dtrCustomer;
 
 
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionNWind"].ConnectionString;
        conNwind = new SqlConnection(connStr);
 
 
        conNwind.Open();
 
        string select = "Select * from Customers where CustomerID=@ID";
        cmdSelectID = new SqlCommand(select, conNwind);
 
        cmdSelectID.Parameters.AddWithValue("@ID", txtId.Text);
        dtrCustomer = cmdSelectID.ExecuteReader();
 
 
        if (dtrCustomer.Read())
        {
            Label1.Text = "ID already exist!";
 
        }
 
        else
        {
            string strInsert = "Insert into Customers(CustomerID,Companyname,Contactname,Phone) values(@customerId,@companyname,@contactname,@phone)";
 
            cmdSelect = new SqlCommand(strInsert, conNwind);
 
            cmdSelect.Parameters.AddWithValue("@customerId", txtId.Text);
            cmdSelect.Parameters.AddWithValue("@companyname", txtCompanyName.Text);
            cmdSelect.Parameters.AddWithValue("@contactname", txtContactName.Text);
            cmdSelect.Parameters.AddWithValue("@phone", txtPhone.Text);
 
            int intAdd = cmdSelect.ExecuteNonQuery();
 
            if (intAdd != 0)
                Label1.Text = "Record successfully added into database!";
 
            else
                Label1.Text = "Record not added into database!";
        }
 
        dtrCustomer.Close();
        conNwind.Close();
 
    }

Open in new window

0
Comment
Question by:nisha_
[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
8 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 21791138
Where do you get an error and what error do you get when you change it to sql server 2005 express edition?
0
 

Author Comment

by:nisha_
ID: 21791143
Error message : There is already an open DataReader associated with this Command which must be closed first.
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 250 total points
ID: 21791219
Where do you get that error ? Can you try this.
protected void btnAdd_Click(object sender, EventArgs e)
    {
 
        SqlConnection conNwind;
        SqlCommand cmdSelect, cmdSelectID;
        SqlDataReader dtrCustomer;
 
 
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionNWind"].ConnectionString;
        conNwind = new SqlConnection(connStr);
 
 
        conNwind.Open();
 
        string select = "Select * from Customers where CustomerID=@ID";
        cmdSelectID = new SqlCommand(select, conNwind);
 
        cmdSelectID.Parameters.AddWithValue("@ID", txtId.Text);
        dtrCustomer = cmdSelectID.ExecuteReader();
 
 
        if (dtrCustomer.Read())
        {
            Label1.Text = "ID already exist!";
            dtrCustomer.Close();
        }
 
        else
        {
            dtrCustomer.Close();
            string strInsert = "Insert into Customers(CustomerID,Companyname,Contactname,Phone) values(@customerId,@companyname,@contactname,@phone)";
 
            cmdSelect = new SqlCommand(strInsert, conNwind);
 
            cmdSelect.Parameters.AddWithValue("@customerId", txtId.Text);
            cmdSelect.Parameters.AddWithValue("@companyname", txtCompanyName.Text);
            cmdSelect.Parameters.AddWithValue("@contactname", txtContactName.Text);
            cmdSelect.Parameters.AddWithValue("@phone", txtPhone.Text);
 
            int intAdd = cmdSelect.ExecuteNonQuery();
 
            if (intAdd != 0)
                Label1.Text = "Record successfully added into database!";
 
            else
                Label1.Text = "Record not added into database!";
        }
         
        conNwind.Close();
 
    }

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:AUmidh
ID: 21791221
make a try of the following
add this statement dtrCustomer.Close();
before the following.
string strInsert = "Insert into Customers(CustomerID,Companyname,Contactname,Phone) values(@customerId,@companyname,@contactname,@phone)";

I am not sure about this but may be.


0
 
LVL 19

Expert Comment

by:elimesika
ID: 21791226
Try
     SqlConnection conNwind;
        SqlCommand cmdSelect, cmdSelectID;
        SqlDataReader dtrCustomer;
 
 
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionNWind"].ConnectionString;
        conNwind = new SqlConnection(connStr);
 
 
        conNwind.Open();
 
        string select = "Select * from Customers where CustomerID=@ID";
        cmdSelectID = new SqlCommand(select, conNwind);
 
        cmdSelectID.Parameters.AddWithValue("@ID", txtId.Text);
        dtrCustomer = cmdSelectID.ExecuteReader();
 
 
        if (dtrCustomer.Read())
        {
            Label1.Text = "ID already exist!";
            dtrCustomer.Close();
            conNwind.Close();
 	    return;
        }
 
        else
        {
            dtrCustomer.Close();
            string strInsert = "Insert into Customers(CustomerID,Companyname,Contactname,Phone) values(@customerId,@companyname,@contactname,@phone)";
 
            cmdSelect = new SqlCommand(strInsert, conNwind);
 
            cmdSelect.Parameters.AddWithValue("@customerId", txtId.Text);
            cmdSelect.Parameters.AddWithValue("@companyname", txtCompanyName.Text);
            cmdSelect.Parameters.AddWithValue("@contactname", txtContactName.Text);
            cmdSelect.Parameters.AddWithValue("@phone", txtPhone.Text);
 
            int intAdd = cmdSelect.ExecuteNonQuery();
 
            if (intAdd != 0)
                Label1.Text = "Record successfully added into database!";
 
            else
                Label1.Text = "Record not added into database!";
                
            conNwind.Close();
        }
 

Open in new window

0
 

Author Comment

by:nisha_
ID: 21791255
putting dtrCustomer.Read() before insert statement make the program work. But why i should do like that since my insert statement not using data reader.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21791298
A datareader opens a transaction. As long as it's not commited (or closed), the connection will be exclusive used by your datareader.
0
 

Author Comment

by:nisha_
ID: 21791307
Then how it can work in MS Access even i closed the datareader after insert statement?
0

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.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

751 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