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

• Greg: 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?
• Lawrence Johnson: 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.
• Divi: 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?
• AAA: the code can be run and the message error appear but no data beeing inserted in the database..
• AAA: 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

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

Answer 2

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;

Answer 3

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.

• John Woo: 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: 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..

Answer 4

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

Also you are not really using the Store Procedure.

• AAA: 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

Answer 5

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