Solved

Error in inserting record

Posted on 2008-06-15
8
205 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_
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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

13 Experts available now in Live!

Get 1:1 Help Now