Solved

Error in inserting record

Posted on 2008-06-15
8
207 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 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

20 Experts available now in Live!

Get 1:1 Help Now