CODEWITHSUNDEEP
c#databasevisual-studio-2010
EM10
EM10

Reputation: 815

C# Database adding to a table

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);
        }
    }

Upvotes: 0

Views: 192

Answers (4)

Alex Filipovici
Alex Filipovici

Reputation: 32541

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.

Upvotes: 4

EM10
EM10

Reputation: 815

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.

Upvotes: 0

WiiMaxx
WiiMaxx

Reputation: 5420

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);
            }
        }
    }

Upvotes: 0

rajansoft1
rajansoft1

Reputation: 1356

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

Upvotes: 0

Related Questions