Multi tool use

Populate a list view in asp.net winform by using stored procedure with parameters through a button click

In my Winforms application, I have to do search by using three parameters. I retrieve from the database by using stored procedure. But an exception is thrown:

Procedure or function 'sp_searchProduct' expects parameter '@categoryId', which was not supplied

Kindly help me.

This is my code:

private void button2_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-U1OP1S9SQLEXPRESS;Initial Catalog=PaintStores;Integrated Security=True");

SqlCommand command = new SqlCommand("sp_searchProduct", con);
command.Parameters.AddWithValue("@categoryId", comboBox1.SelectedValue.ToString());
command.Parameters.AddWithValue("@typeId", comboBox2.SelectedValue.ToString());
command.Parameters.AddWithValue("@productId", comboBox3.SelectedValue.ToString());

SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
da.Fill(dataTable);

for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow drow = dataTable.Rows[i];

if (drow.RowState != DataRowState.Deleted)
{
ListViewItem lvi = new ListViewItem(drow["ProductId"].ToString());
lvi.SubItems.Add(drow["ProductName"].ToString());
lvi.SubItems.Add(drow["TypeName"].ToString());
lvi.SubItems.Add(drow["Quantity"].ToString());
lvi.SubItems.Add(drow["Price"].ToString());
lvi.SubItems.Add(drow["Stock"].ToString());

listView1.Items.Add(lvi);
}
}

con.Close();
}


This is my stored procedure:

ALTER PROCEDURE [dbo].[sp_searchProduct]
@categoryId INT,
@typeId INT,
@productId INT
AS
IF (@categoryId = 0)
BEGIN
SET @categoryId = NULL
END

IF (@typeId = 0)
BEGIN
SET @typeId = NULL
END

IF (@productId = 0)
BEGIN
SET @productId = NULL
END
BEGIN
SET NOCOUNT ON;

SELECT p.ProductId, p.ProductName, p.Quantity, p.Price, p.Stock
FROM tblProductNew p
INNER JOIN tblTypes t ON t.TypeId = p.TypeId
WHERE p.ProductId = ISNULL(@productId, p.ProductId)
AND p.TypeId = ISNULL(@typeId, p.TypeId)
AND t.MainCategoryId = ISNULL(@categoryId, t.MainCategoryId)
END


sp_

sp_

3 Answers
3

Make sure you add this to your code before calling da.Fill(dataTable);

da.Fill(dataTable);

command.CommandType = CommandType.StoredProcedure;


Your final code should look something like this..

SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-U1OP1S9SQLEXPRESS;Initial Catalog=PaintStores;Integrated Security=True");
SqlCommand command = new SqlCommand("sp_searchProduct", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@categoryId",comboBox1.SelectedValue.ToString());


There are two mistakes. One is you should specify command type. Second is, SP expects categoryId as int and you're converting it into string.

SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-U1OP1S9SQLEXPRESS;Initial Catalog=PaintStores;Integrated Security=True");

SqlCommand command = new SqlCommand("sp_searchProduct", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@categoryId", comboBox1.SelectedValue);
command.Parameters.AddWithValue("@typeId", comboBox2.SelectedValue);
command.Parameters.AddWithValue("@productId", comboBox3.SelectedValue);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
da.Fill(dataTable);


The mistake is I missed to clear the earlier results in the list view. I use the line listView1.Items.Clear(); before I insert the queried results to the list view. Now the error gone.

listView1.Items.Clear();

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.