You need to pass the method parameters to the query as query parameter, and use those parameters to insert the data, rather than using hard-coded values.
Since you're passing in the category ID, the first part of your query is irrelevant. You don't need to insert a new category and retrieve its ID, since you already have the category ID you want to use.
Wrap your
SqlConnection
and
SqlCommand
objects in a
using
block to make sure they're cleaned up properly in all cases.
Don't catch the base
Exception
type; only catch the exceptions you can handle. In this case, you should only be catching
SqlException
. You should also be logging or displaying the details of the exception, so that you know what went wrong when your method returns
false
.
Something like this should work:
public bool InsertProduct(string productName, int categoryId, string companyName, string price, int qty)
{
const string Query = @"INSERT INTO tblProduct (ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
VALUES (@ProductName, @CategoryID, @ProductCompany, @ProductPrice, @ProductQty);";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["InvoiceDB"].ConnectionString))
using (SqlCommand command = new SqlCommand(Query, connection))
{
command.Parameters.AddWithValue("@ProductName", productName);
command.Parameters.AddWithValue("@CategoryID", categoryId);
command.Parameters.AddWithValue("@ProductCompany", companyName);
command.Parameters.AddWithValue("@ProductPrice", price);
command.Parameters.AddWithValue("@ProductQty", qty);
try
{
connection.Open();
command.ExecuteNonQuery();
return true;
}
catch (SqlException ex)
{
return false;
}
}
}