C# Database adding to a table

Question

I am using a .mdf database in Visual Studio 2010. When I add information to my table I get error. I don't get any problem when I add the first four rows. But when I add the fifth row I get error.

Here is the error:

SqlException was unhandled

What can the problem be?

        dataAccess.AddQuestion("Category1", "Question1?", "1");
        dataAccess.AddQuestion("Category2", "Question2?", "2");
        dataAccess.AddQuestion("Category3", "Question3?", "3");
        dataAccess.AddQuestion("Category4", "Question4?", "4");
        dataAccess.AddQuestion("Category5", "Question5?", "5");

I get the error when I add the question number five.

Here is the method for how I add the information to the table in the database.

    public void AddQuestion(string title, string question, string answer)
    {

        sqlConnection = new SqlConnection(connectionString);

        sqlCommand = new SqlCommand("INSERT INTO QuestionTable VALUES(@Title, @Question, @Answer)", sqlConnection);

        try
        {
            sqlConnection.Open();
            sqlCommand.Parameters.Add(new SqlParameter("@Title", title));
            sqlCommand.Parameters.Add(new SqlParameter("@Question", question));
            sqlCommand.Parameters.Add(new SqlParameter("@Answer", answer));
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }
        catch (Exception ex)
        {
            throw(ex);
        }
    }

have u caught an exception with your? catch (Exception ex) — Roar
– Roar, Commented May 29, 2013 at 10:38
This it the error I get: SqlException was unhandled. Yes I caught the exception with catch (Exception ex) — EM10
– EM10, Commented May 29, 2013 at 10:40

Alex Filipovici · Accepted Answer · 2013-05-29 11:11:44Z

4

Is there a reason not to use method scoped variables for the SQL objects? Try to use this:

public void AddQuestion(string title, string question, string answer)
{

    using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO QuestionTable VALUES(@Title, @Question, @Answer)", sqlConnection))
    {
        try
        {
            sqlConnection.Open();
            sqlCommand.Parameters.Add(new SqlParameter("@Title", title));
            sqlCommand.Parameters.Add(new SqlParameter("@Question", question));
            sqlCommand.Parameters.Add(new SqlParameter("@Answer", answer));
            sqlCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine(ex); 
            throw; 
        }
    }
}

If you consider moving to SQL Server 2008 or newer, you might also look into Table-Valued Parameters.

edited May 29, 2013 at 11:11

answered May 29, 2013 at 10:41

Alex Filipovici

32.8k7 gold badges60 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Filipovici Over a year ago

@SonerGönül, read this article, What are the limitations of MS Access?: Microsoft doesn't even publish hard numbers; they just use off-hand references such as "ten or fewer" and never truly define what they mean by "high-concurrency."

03Usr Over a year ago

@SonerGönül I think this is relevant because including the using statements might have prevented the problem (i.e not disposing the command and connection objects) hence solve his problem.

Eoin Campbell Over a year ago

You're hiding your exception stack trace by the way you are rethrowing. change the catch block to catch(Exception ex) { System.Diagnotstics.Debug.WriteLine(ex); throw; }

rajansoft1 · Accepted Answer · 2013-05-29 10:41:16Z

0

Why don't you use multiple insert statement to insert multiple rows at once as their is always a limit on the no of connection to database per app pool so either you must not close the connection or use multiple insert at once

answered May 29, 2013 at 10:41

rajansoft1

1,3562 gold badges18 silver badges38 bronze badges

Comments

WiiMaxx · Accepted Answer · 2013-05-29 10:52:37Z

try the following code maybe you need to lock()

    private static readonly object Locker = new object();
    public void AddQuestion(string title, string question, string answer)
    {

        lock (Locker)
        {
            try
            {
                sqlConnection = new SqlConnection("");

                sqlCommand = new SqlCommand("INSERT INTO QuestionTable VALUES(@Title, @Question, @Answer)", sqlConnection);

                sqlConnection.Open();
                sqlCommand.Parameters.Add(new SqlParameter("@Title", title));
                sqlCommand.Parameters.Add(new SqlParameter("@Question", question));
                sqlCommand.Parameters.Add(new SqlParameter("@Answer", answer));
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("That is the Error:" ex.ToString()); // post this Text if it doesn't work
                throw (ex);
            }
        }
    }

EM10 · Accepted Answer · 2013-05-29 11:10:22Z

0

I have found the problem (error)! In the database I was using nvarchar(50), but I hade one string which was 52 characters. I got the error because of that.

answered May 29, 2013 at 11:10

EM10

8157 gold badges13 silver badges24 bronze badges

Collectives™ on Stack Overflow

C# Database adding to a table

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related