CODEWITHSUNDEEP
c#asp.netsql-insert
AAA
AAA

Reputation: 921

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

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".

Upvotes: 1

Views: 2450

Answers (5)

Manoj De Mel
Manoj De Mel

Reputation: 1005

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

Upvotes: 0

Manoj De Mel
Manoj De Mel

Reputation: 1005

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

Also you are not really using the Store Procedure.

Upvotes: 0

Dave Zych
Dave Zych

Reputation: 21897

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.

Upvotes: 0

John Woo
John Woo

Reputation: 263853

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;

Upvotes: 1

dfosterh
dfosterh

Reputation: 21

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.

Upvotes: 1

Related Questions