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

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();
}
}
 
N

Neil McKechnie

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

Greg

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

Neil McKechnie

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

beginner question about page_load 1
Trouble Deleteing records in DataBase - SQL Server 7 - ASP.NET? 4
OLEDB: UpdateCommand fails 3
Datasource question 1
Problem with Update Adapter 5
C#, Stored Procedure and Parameter 1
Trouble with Dataset 3
syntax problem (vb) data connection 4

Top