Unable to insert data to database in asp.net C#

Question

I'm new in C# programming, so I'll appreciate if anyone can help me. I know there are similar question but I still can't find the solution for my problem. I'm developing a mock system, where when user bought the product, the system will store all the transaction details. the problem is, I cannot insert the data into the database. Here's the code:

using (SqlConnection conn = new SqlConnection
    (ConfigurationManager.ConnectionStrings["database"].ConnectionString))
{
    string QueryA = "@Insert into TransDetails(AccountNumber,Amount,Provider" 
        + ",Mobile Number,TransNum,TransDate, Status) "
        + " Values (@AccountNumber,@Amount,@Provider,@Mobile Number," 
        + "@TransNum,@TransDate,@Status";

    using (SqlCommand cmd = new SqlCommand("InsertRecord", conn))
    {
        conn.Open();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = QueryA;
        cmd.Parameters.AddWithValue("@AccountNumber", acc.Text);
        cmd.Parameters.AddWithValue("@Amount", lblAmount.Text);
        cmd.Parameters.AddWithValue("@Provider", lblProvider.Text);
        cmd.Parameters.AddWithValue("@Mobile Number", lblNumber.Text);
        cmd.Parameters.AddWithValue("@TransNum", lblTrans.Text);
        cmd.Parameters.AddWithValue("@TransDate", lblDate.Text);
        cmd.Parameters.AddWithValue("@Status", status.Text);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch
        {
            lblMessage.Text = "Error";
        }
        finally
        {
            conn.Close();
        }
    }
}

and the stores procedures are as follows:

    ALTER PROCEDURE InsertRecord1

    @AccountNumber int,
    @Amount nchar(10),
    @Provider nchar(10),
    @MobileNumber int,
    @TransNum nchar(10),
    @TransDate date,
    @Status nchar(10)

    AS
    Insert into TransDetails(AccountNumber,Amount,Provider,MobileNumber,TransNum,TransDate,Status) 
    Values (@AccountNumber,@Amount,@Provider,@MobileNumber,@TransNum,@TransDate,@Status)

    return

Really appreciate any help. P/S: i dont know why the beginning of the stored procedures started with "alter".

Why are you creating a stored proc and then setting the command text to an insert statement. Do you want to call the stored proc or the insert statement? — Greg
– Greg, Commented Oct 5, 2012 at 3:10
Also, don't bother putting your SqlCommand in a using block. But, thank you for putting your connection in one. So many posts on here with no using and it makes it hard for me to sleep at night. — Lawrence Johnson
– Lawrence Johnson, Commented Oct 5, 2012 at 3:11
Please do not consume your exception. And when you say that you can't insert, do you see lblMessage say "Error" or does the code run fine but you don't see data in the DB? — Divi
– Divi, Commented Oct 5, 2012 at 4:12
the code can be run and the message error appear but no data beeing inserted in the database.. — AAA
– AAA, Commented Oct 5, 2012 at 7:01
for the stored procedures, I just try it because I've tried using only the command but still cannot get the data inserted into the database — AAA
– AAA, Commented Oct 5, 2012 at 7:02

dfosterh · Accepted Answer · 2012-10-05 03:10:06Z

1

I may be reading it wrong, but it looks like your stored procedure is not used at all. Try commenting out "cmd.CommandText = QueryA;" and substitute "cmd.CommandText = "InsertRecord1";" and change CommandType to StoredProcedure.

QueryA, by the way, is missing a paren at the end. However, the whole thing is unnecessary since you have a stored procedure that does the same thing and it's almost always preferable to use a stored procedure rather than embedded DML.

answered Oct 5, 2012 at 3:10

dfosterh

211 bronze badge

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

Comments

John Woo · Accepted Answer · 2012-10-05 03:18:32Z

1

You must escape Mobile Number while brackets

Insert into TransDetails(AccountNumber,Amount,Provider,[Mobile Number],...

and remove the space in your parameter

...,@MobileNumber,@TransNum,@TransDate,@Status

and change the paramname in your command parameter

cmd.Parameters.AddWithValue("@MobileNumber", lblNumber.Text);

but seeing your stored procedure, the column Mobile Number has no space between it. Is it a typo error in your query on QueryA? If it is, then remove the space on it (also on parameter name)

Insert into TransDetails(AccountNumber,Amount,Provider,MobileNumber,...

or

change your CommandType.Text to CommandType.StoredProcedure and remove this line,

cmd.CommandText = QueryA;

edited Oct 5, 2012 at 3:18

answered Oct 5, 2012 at 3:01

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Dave Zych · Accepted Answer · 2012-10-05 03:19:36Z

0

You're using the wrong overload of the SqlCommand constructor. According to MSDN:

new SqlCommand(string, SqlConnection) Initializes a new instance of the SqlCommand class with the text of the query and a SqlConnection.

What you need to do is either set your CommandType for the sql command to CommandType.StoredProcedure and not use QueryA, or initialize the sql command with QueryA and not make use of your stored procedure.

edited Oct 5, 2012 at 3:19

answered Oct 5, 2012 at 3:06

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

2 Comments

John Woo Over a year ago

no, he change the commandText back to QueryA, cmd.CommandText = QueryA; if he will change it, it will throw an exception stating that the procedure was not found.

AAA Over a year ago

I GOT IT!!!!! there are 2 things that I got mixed up... First: I missed the ")" at the end of the string definition. string QueryA = "Insert into TransDetails(AccountNumber,Amount,Provider"......@TransDate,@Status"; Second: I'm using the stored procedure here, using (SqlCommand cmd = new SqlCommand("InsertRecord", conn)) but using sqlcommand here, cmd.CommandText = QueryA; So, I just replace "InsertRecord" above with QueryA and add ")" at the string.. Thanks guys..If not for you guys, I will not see the error. Thanks for clearing it up..

Manoj De Mel · Accepted Answer · 2012-10-05 04:24:58Z

0

As you can see there is @ at the start of your SQL Statement.

Also you are not really using the Store Procedure.

answered Oct 5, 2012 at 4:24

Manoj De Mel

1,02310 silver badges17 bronze badges

1 Comment

AAA Over a year ago

I've remove it but still nothing..If I didn't include the stored procedure and just using the sqlcommand, the output is still the same..Can you show me the correct way to code the sqlcommand, incase if i did it wrongly..thanks

Manoj De Mel · Accepted Answer · 2012-10-06 03:18:11Z

You can Try this:

using (SqlConnection conn = new SqlConnection (ConfigurationManager.ConnectionStrings["database"].ConnectionString))
        {                
            conn.Open();
            SqlCommand cmd = new SqlCommand("InsertRecord1", conn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@AccountNumber", acc.Text);
            cmd.Parameters.AddWithValue("@Amount", lblAmount.Text);
            cmd.Parameters.AddWithValue("@Provider", lblProvider.Text);
            cmd.Parameters.AddWithValue("@Mobile Number", lblNumber.Text);
            cmd.Parameters.AddWithValue("@TransNum", lblTrans.Text);
            cmd.Parameters.AddWithValue("@TransDate", lblDate.Text);
            cmd.Parameters.AddWithValue("@Status", status.Text);

            try
            {                        
                cmd.ExecuteNonQuery();
            }
            catch
            {
                lblMessage.Text = "Error";
            }
            finally
            {
                conn.Close();
            }
        }

Tho I don't use SQL Commands, Adapters...etc. to access the data from the SQL Database. I prefer Microsoft Data Access ApplicationBlocks which is easy-to-use library provided by Microsoft to access data from SQL Server.

Download You can download it here http://download.microsoft.com/download/VisualStudioNET/daabref/RTM/NT5/EN-US/DataAccessApplicationBlock.msi

Introduction https://web.archive.org/web/20210304123854/https://www.4guysfromrolla.com/articles/062503-1.aspx

Collectives™ on Stack Overflow

Unable to insert data to database in asp.net C#

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related