Beginner getting "Prepared statement...expects parameter...not supplied" error

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

This is my first crack at ASP.NET and ADO.NET. I am making a page
that will add/change/delete to SQL db using a DataGrid. I found a
sample in a book that does the exact same thing I want using the
Northwind db, but it doesn't work. The select, insert, and delete
commands work fine, but errors out if I uncomment the update code
which is the same format as the others. As soon as I call
DataAdapter.Fill it throws exception. I was under the impression that
this only executes the CommandText for the SqlCommand object set to
the SelectCommand property of the DataAdapter. Can anyone tell me why
this code won't work for the update command?

Here is the error text

Prepared statement '(@CustomerID nchar(5),@ContactName
nvarchar(30),@CompanyName nva' expects parameter @CustomerID, which
was not supplied"

Here's the code for Page_Load:

SqlConnection cnn = new SqlConnection(@"Data Source=<server>; Initial
Catalog=Northwind; Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{
//Create SELECT SqlCommand
SqlCommand cmdSelect = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "SELECT CustomerID, CompanyName, ContactName
FROM Customers";

//Create UPDATE SqlCommand
SqlCommand cmdUpdate = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "UPDATE Customers SET CompanyName =
@CompanyName, ContactName = @ContactName WHERE CustomerID =
@CustomerID";
cmdSelect.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdSelect.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdSelect.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdSelect.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create INSERT SqlCommand
SqlCommand cmdInsert = cnn.CreateCommand();
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = "INSERT INTO Customers (CustomerID,
CompanyName, ContactName) VALUES (@CustomerID, @CompanyName,
@ContactName)";
cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdInsert.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdInsert.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdInsert.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create DELETE SqlCommand
SqlCommand cmdDelete = cnn.CreateCommand();
cmdDelete.CommandType = CommandType.Text;
cmdDelete.CommandText = "DELETE FROM Customers WHERE CustomerID =
@CustomerID";
cmdDelete.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdDelete.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Set up SqlDataAdapter
da.SelectCommand = cmdSelect;
da.UpdateCommand = cmdUpdate;
da.InsertCommand = cmdInsert;
da.DeleteCommand = cmdDelete;
da.Fill(ds, "Customers");

//Fill grid on page load
if (!IsPostBack)
{
dgCustomers.DataSource = ds;
dgCustomers.DataMember = "Customers";
dgCustomers.DataBind();
}
}
 
Greg,

Looks like a typo. Did you mean to say

cmdSelect.CommandText = "UPDATE Customers SET CompanyName =
@CompanyName, ContactName = @ContactName WHERE CustomerID =
@CustomerID";

or should it be cmdUpdate.CommandText =...?

Neil

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Neil McKechnie
Microlink Associates Ltd
(e-mail address removed)
Greg said:
This is my first crack at ASP.NET and ADO.NET. I am making a page
that will add/change/delete to SQL db using a DataGrid. I found a
sample in a book that does the exact same thing I want using the
Northwind db, but it doesn't work. The select, insert, and delete
commands work fine, but errors out if I uncomment the update code
which is the same format as the others. As soon as I call
DataAdapter.Fill it throws exception. I was under the impression that
this only executes the CommandText for the SqlCommand object set to
the SelectCommand property of the DataAdapter. Can anyone tell me why
this code won't work for the update command?

Here is the error text

Prepared statement '(@CustomerID nchar(5),@ContactName
nvarchar(30),@CompanyName nva' expects parameter @CustomerID, which
was not supplied"

Here's the code for Page_Load:

SqlConnection cnn = new SqlConnection(@"Data Source=<server>; Initial
Catalog=Northwind; Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{
//Create SELECT SqlCommand
SqlCommand cmdSelect = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "SELECT CustomerID, CompanyName, ContactName
FROM Customers";

//Create UPDATE SqlCommand
SqlCommand cmdUpdate = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "UPDATE Customers SET CompanyName =
@CompanyName, ContactName = @ContactName WHERE CustomerID =
@CustomerID";
cmdSelect.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdSelect.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdSelect.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdSelect.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create INSERT SqlCommand
SqlCommand cmdInsert = cnn.CreateCommand();
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = "INSERT INTO Customers (CustomerID,
CompanyName, ContactName) VALUES (@CustomerID, @CompanyName,
@ContactName)";
cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdInsert.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdInsert.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdInsert.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create DELETE SqlCommand
SqlCommand cmdDelete = cnn.CreateCommand();
cmdDelete.CommandType = CommandType.Text;
cmdDelete.CommandText = "DELETE FROM Customers WHERE CustomerID =
@CustomerID";
cmdDelete.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdDelete.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Set up SqlDataAdapter
da.SelectCommand = cmdSelect;
da.UpdateCommand = cmdUpdate;
da.InsertCommand = cmdInsert;
da.DeleteCommand = cmdDelete;
da.Fill(ds, "Customers");

//Fill grid on page load
if (!IsPostBack)
{
dgCustomers.DataSource = ds;
dgCustomers.DataMember = "Customers";
dgCustomers.DataBind();
}
}
Click to expand...
 
My apologies - I should read closer before I post. The sample I gave
does work - it just had a typo. I still cannot get my implementation
to work though. I just modified the sample from the book to fit my
needs, but it gives me the ever-annoying "prepared statement expects
parameter" exception. Here is my adapted code:

SqlDataAdapter daCampsSearch = new SqlDataAdapter();
DataSet dsAlerts = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{

//Create a SqlCommand for SELECT
SqlCommand cmdCampsSearch = cnCampsSearch.CreateCommand();
cmdCampsSearch.CommandType = CommandType.Text;
cmdCampsSearch.CommandText = "SELECT ALERT_ID, STATUS FROM
ALERT_STATUS WHERE EMAIL_ADDR = 'email' AND STATUS = '" +
ddlStatus.SelectedItem.Value + "' ORDER BY ALERT_ID";

//Create a SqlCommand for UPDATE
SqlCommand cmdUpdateStatus = cnCampsSearch.CreateCommand();
cmdUpdateStatus.CommandType = CommandType.Text;
cmdUpdateStatus.CommandText = "UPDATE ALERT_STATUS SET STATUS =
@Status WHERE EMAIL_ADDR = @Email AND ALERT_ID = @AlertId";
cmdUpdateStatus.Parameters.Add("@Status", SqlDbType.Char, 1,
"ALERT_STATUS");
cmdUpdateStatus.Parameters.Add("@Email", SqlDbType.Char, 50,
"EMAIL_ADDR");
cmdUpdateStatus.Parameters.Add("@AlertId", SqlDbType.Int, 4,
"ALERT_ID");
cmdUpdateStatus.Parameters["@Email"].SourceVersion =
DataRowVersion.Original;
cmdUpdateStatus.Parameters["@AlertId"].SourceVersion =
DataRowVersion.Original;

//Create SqlDataAdapter
daCampsSearch.SelectCommand = cmdCampsSearch;
daCampsSearch.UpdateCommand = cmdUpdateStatus;


//Fill DataSet
daCampsSearch.Fill(dsAlerts, "ALERT_STATUS");

//Populate DataGrid with all open alerts on first load
if (!IsPostBack)
{
dgAlerts.DataSource = dsAlerts;
dgAlerts.DataMember = "ALERT_STATUS";
dgAlerts.DataBind();
}
}

private void dgAlerts_UpdateCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
RadioButtonList rbNewStatus =
(RadioButtonList)e.Item.Cells[2].FindControl("rbStatus");

//Create DataRow to edit data in DS
DataRow[] adrEdit = dsAlerts.Tables["ALERT_STATUS"].Select("ALERT_ID
= " + dgAlerts.DataKeys[e.Item.ItemIndex]);

//Put edited data in DataRow if data is different
if (adrEdit[0]["STATUS"].ToString() !=
rbNewStatus.SelectedItem.Value)
{
adrEdit[0]["STATUS"] = rbNewStatus.SelectedItem.Value;
daCampsSearch.Update(dsAlerts, "ALERT_STATUS");
}

//Turn off editing by reseting the EditItemIndex
((DataGrid)source).EditItemIndex = -1;
LoadData();
}




This is my first crack at ASP.NET and ADO.NET. I am making a page
that will add/change/delete to SQL db using a DataGrid. I found a
sample in a book that does the exact same thing I want using the
Northwind db, but it doesn't work. The select, insert, and delete
commands work fine, but errors out if I uncomment the update code
which is the same format as the others. As soon as I call
DataAdapter.Fill it throws exception. I was under the impression that
this only executes the CommandText for the SqlCommand object set to
the SelectCommand property of the DataAdapter. Can anyone tell me why
this code won't work for the update command?

Here is the error text

Prepared statement '(@CustomerID nchar(5),@ContactName
nvarchar(30),@CompanyName nva' expects parameter @CustomerID, which
was not supplied"

Here's the code for Page_Load:

SqlConnection cnn = new SqlConnection(@"Data Source=<server>; Initial
Catalog=Northwind; Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{
//Create SELECT SqlCommand
SqlCommand cmdSelect = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "SELECT CustomerID, CompanyName, ContactName
FROM Customers";

//Create UPDATE SqlCommand
SqlCommand cmdUpdate = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "UPDATE Customers SET CompanyName =
@CompanyName, ContactName = @ContactName WHERE CustomerID =
@CustomerID";
cmdSelect.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdSelect.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdSelect.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdSelect.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create INSERT SqlCommand
SqlCommand cmdInsert = cnn.CreateCommand();
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = "INSERT INTO Customers (CustomerID,
CompanyName, ContactName) VALUES (@CustomerID, @CompanyName,
@ContactName)";
cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdInsert.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdInsert.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdInsert.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create DELETE SqlCommand
SqlCommand cmdDelete = cnn.CreateCommand();
cmdDelete.CommandType = CommandType.Text;
cmdDelete.CommandText = "DELETE FROM Customers WHERE CustomerID =
@CustomerID";
cmdDelete.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdDelete.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Set up SqlDataAdapter
da.SelectCommand = cmdSelect;
da.UpdateCommand = cmdUpdate;
da.InsertCommand = cmdInsert;
da.DeleteCommand = cmdDelete;
da.Fill(ds, "Customers");

//Fill grid on page load
if (!IsPostBack)
{
dgCustomers.DataSource = ds;
dgCustomers.DataMember = "Customers";
dgCustomers.DataBind();
}
}
Click to expand...
 
As this is an SQL Server error, try running SQL Profiler so you can see
exactly what query is being sent to the database. This should shed some
light on the problem.

Neil

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Neil McKechnie
Microlink Associates Ltd
(e-mail address removed)
Greg said:
My apologies - I should read closer before I post. The sample I gave
does work - it just had a typo. I still cannot get my implementation
to work though. I just modified the sample from the book to fit my
needs, but it gives me the ever-annoying "prepared statement expects
parameter" exception. Here is my adapted code:

SqlDataAdapter daCampsSearch = new SqlDataAdapter();
DataSet dsAlerts = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{

//Create a SqlCommand for SELECT
SqlCommand cmdCampsSearch = cnCampsSearch.CreateCommand();
cmdCampsSearch.CommandType = CommandType.Text;
cmdCampsSearch.CommandText = "SELECT ALERT_ID, STATUS FROM
ALERT_STATUS WHERE EMAIL_ADDR = 'email' AND STATUS = '" +
ddlStatus.SelectedItem.Value + "' ORDER BY ALERT_ID";

//Create a SqlCommand for UPDATE
SqlCommand cmdUpdateStatus = cnCampsSearch.CreateCommand();
cmdUpdateStatus.CommandType = CommandType.Text;
cmdUpdateStatus.CommandText = "UPDATE ALERT_STATUS SET STATUS =
@Status WHERE EMAIL_ADDR = @Email AND ALERT_ID = @AlertId";
cmdUpdateStatus.Parameters.Add("@Status", SqlDbType.Char, 1,
"ALERT_STATUS");
cmdUpdateStatus.Parameters.Add("@Email", SqlDbType.Char, 50,
"EMAIL_ADDR");
cmdUpdateStatus.Parameters.Add("@AlertId", SqlDbType.Int, 4,
"ALERT_ID");
cmdUpdateStatus.Parameters["@Email"].SourceVersion =
DataRowVersion.Original;
cmdUpdateStatus.Parameters["@AlertId"].SourceVersion =
DataRowVersion.Original;

//Create SqlDataAdapter
daCampsSearch.SelectCommand = cmdCampsSearch;
daCampsSearch.UpdateCommand = cmdUpdateStatus;


//Fill DataSet
daCampsSearch.Fill(dsAlerts, "ALERT_STATUS");

//Populate DataGrid with all open alerts on first load
if (!IsPostBack)
{
dgAlerts.DataSource = dsAlerts;
dgAlerts.DataMember = "ALERT_STATUS";
dgAlerts.DataBind();
}
}

private void dgAlerts_UpdateCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
RadioButtonList rbNewStatus =
(RadioButtonList)e.Item.Cells[2].FindControl("rbStatus");

//Create DataRow to edit data in DS
DataRow[] adrEdit = dsAlerts.Tables["ALERT_STATUS"].Select("ALERT_ID
= " + dgAlerts.DataKeys[e.Item.ItemIndex]);

//Put edited data in DataRow if data is different
if (adrEdit[0]["STATUS"].ToString() !=
rbNewStatus.SelectedItem.Value)
{
adrEdit[0]["STATUS"] = rbNewStatus.SelectedItem.Value;
daCampsSearch.Update(dsAlerts, "ALERT_STATUS");
}

//Turn off editing by reseting the EditItemIndex
((DataGrid)source).EditItemIndex = -1;
LoadData();
}




(e-mail address removed) (Greg) wrote in message
This is my first crack at ASP.NET and ADO.NET. I am making a page
that will add/change/delete to SQL db using a DataGrid. I found a
sample in a book that does the exact same thing I want using the
Northwind db, but it doesn't work. The select, insert, and delete
commands work fine, but errors out if I uncomment the update code
which is the same format as the others. As soon as I call
DataAdapter.Fill it throws exception. I was under the impression that
this only executes the CommandText for the SqlCommand object set to
the SelectCommand property of the DataAdapter. Can anyone tell me why
this code won't work for the update command?

Here is the error text

Prepared statement '(@CustomerID nchar(5),@ContactName
nvarchar(30),@CompanyName nva' expects parameter @CustomerID, which
was not supplied"

Here's the code for Page_Load:

SqlConnection cnn = new SqlConnection(@"Data Source=<server>; Initial
Catalog=Northwind; Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{
//Create SELECT SqlCommand
SqlCommand cmdSelect = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "SELECT CustomerID, CompanyName, ContactName
FROM Customers";

//Create UPDATE SqlCommand
SqlCommand cmdUpdate = cnn.CreateCommand();
cmdSelect.CommandType = CommandType.Text;
cmdSelect.CommandText = "UPDATE Customers SET CompanyName =
@CompanyName, ContactName = @ContactName WHERE CustomerID =
@CustomerID";
cmdSelect.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdSelect.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdSelect.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdSelect.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create INSERT SqlCommand
SqlCommand cmdInsert = cnn.CreateCommand();
cmdInsert.CommandType = CommandType.Text;
cmdInsert.CommandText = "INSERT INTO Customers (CustomerID,
CompanyName, ContactName) VALUES (@CustomerID, @CompanyName,
@ContactName)";
cmdInsert.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdInsert.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName");
cmdInsert.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30,
"ContactName");
cmdInsert.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Create DELETE SqlCommand
SqlCommand cmdDelete = cnn.CreateCommand();
cmdDelete.CommandType = CommandType.Text;
cmdDelete.CommandText = "DELETE FROM Customers WHERE CustomerID =
@CustomerID";
cmdDelete.Parameters.Add("@CustomerID", SqlDbType.NChar, 5,
"CustomerID");
cmdDelete.Parameters["@CustomerID"].SourceVersion =
DataRowVersion.Original;

//Set up SqlDataAdapter
da.SelectCommand = cmdSelect;
da.UpdateCommand = cmdUpdate;
da.InsertCommand = cmdInsert;
da.DeleteCommand = cmdDelete;
da.Fill(ds, "Customers");

//Fill grid on page load
if (!IsPostBack)
{
dgCustomers.DataSource = ds;
dgCustomers.DataMember = "Customers";
dgCustomers.DataBind();
}
}
Click to expand...
Click to expand...
 
Back
Top