Reputation: 921
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
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
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
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
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
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