|
 |
ado_dotnet thread: problem with sqlparameter error parameter was not supplied
Message #1 by "Keith Thorne" <keith@k...> on Thu, 13 Mar 2003 07:53:04
|
|
This is my first time trying to use vs.net and the data adapter. I think
I'm close but I keep getting this error message when I try to use the
adapter update and nothing I do seems to be accepted by the sproc.
Here is the error...
"Procedure 'insert_person' expects parameter '@f...', which was not
supplied."
Below are my sproc, the adapter and insert commands as generated by
vs.net designer, the data set schema for PersonDS, and my button click
handler that adds a row to the dataset and then calls the adapter update
resulting in the error message.
Any help would be greatly appricated!
-- my stored precedure with one parameter
CREATE Procedure insert_person
( @firstName char(20) )
As
INSERT person VALUES (@firstName ,
"last" ,
"middle" ,
"title" ,
121 ,
"email" ,
"phone",
"street1" ,
"street2" ,
"city" ,
"ST" ,
"112233" ,
"USA" )
return
GO
-- end stored procedure
// snipet from vs.net generated adapter and sqlcommand with parameter
this.sqlDataAdapter1.InsertCommand = this.insertPerson2;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "person", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("personID", "personID"),
new System.Data.Common.DataColumnMapping("fName", "fName"),
new System.Data.Common.DataColumnMapping("lName", "lName"),
new System.Data.Common.DataColumnMapping("mName", "mName"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping
("companyID", "companyID"),
new System.Data.Common.DataColumnMapping("email", "email"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("street1", "street1"),
new System.Data.Common.DataColumnMapping("street2", "street2"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping
("country", "country")})});
this.insertPerson2.CommandText = "[insert_person]";
this.insertPerson2.CommandType = System.Data.CommandType.StoredProcedure;
this.insertPerson2.Connection = this.sqlConnection1;
this.insertPerson2.Parameters.Add(new System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
// end of vs.net generated snipet
// dataset schema for PersonDS as generated by vs.net designer
fName,
lName,
mName,
Title,
companyID,
email,
phone,
street1,
street2,
city,
state,
zip,
country
// end of dataset schema as generated by designer
// my button click handler to add a row to the dataset then use the
// adapter to update the database
private void submitPerson_Click(object sender, System.EventArgs e)
{
dsPerson = (jobHunt.PersonDS) Session["personsDS"];
DataTable tblFolks = dsPerson.Tables["person"];
DataRow newPerson = tblFolks.NewRow();
newPerson["fName"] = tbFirst.Text;
newPerson["lName"] = tbLast.Text;
newPerson["mName"] = tbMiddle.Text;
newPerson["Title"] = tbTitle.Text;
newPerson["companyID"] = 121;
newPerson["email"] = tbEmail.Text;
newPerson["phone"] = tbPhone.Text;
newPerson["street1"] = tbStreet1.Text;
newPerson["street2"] = tbStreet2.Text;
newPerson["city"] = tbCity.Text;
newPerson["state"] = tbState.Text;
newPerson["zip"] = tbZip.Text;
newPerson["country"] = tbCountry.Text;
tblFolks.Rows.Add(newPerson);
DataGrid1.DataBind();
Session["personsDS"] = dsPerson;
sqlDataAdapter1.Update(dsPerson, "person");
//end button click handler
I think I've included all the necessary info. Thanks again for any help.
Message #2 by "Brian Smith" <bsmith@l...> on Thu, 13 Mar 2003 08:45:18 -0000
|
|
Your Parameter is defined wrongly (wrong type. But more importantly with
an included pair of quotes - perhaps you typed them into the Property? I
tend to let the DataAdapter configuration seeek out the parameters. In
your case it wouldn't match firstName because the column is fName. In
general, it makes sense to keep all sproc parameters with the exact same
spelling as the underlying columns.
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
Should be
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.Char, 20, "fName"));
Brian
-----Original Message-----
From: Keith Thorne [mailto:keith@k...]
Sent: Thu, 13 Mar 2003 07:53
To: ADO.NET
Subject: [ado_dotnet] problem with sqlparameter error parameter was not
supplied
This is my first time trying to use vs.net and the data adapter. I think
I'm close but I keep getting this error message when I try to use the
adapter update and nothing I do seems to be accepted by the sproc.
Here is the error...
"Procedure 'insert_person' expects parameter '@f...', which was not
supplied."
Below are my sproc, the adapter and insert commands as generated by
vs.net designer, the data set schema for PersonDS, and my button click
handler that adds a row to the dataset and then calls the adapter update
resulting in the error message.
Any help would be greatly appricated!
-- my stored precedure with one parameter
CREATE Procedure insert_person
( @firstName char(20) )
As
INSERT person VALUES (@firstName ,
"last" ,
"middle" ,
"title" ,
121 ,
"email" ,
"phone",
"street1" ,
"street2" ,
"city" ,
"ST" ,
"112233" ,
"USA" )
return
GO
-- end stored procedure
// snipet from vs.net generated adapter and sqlcommand with parameter
this.sqlDataAdapter1.InsertCommand = this.insertPerson2;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "person", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("personID",
"personID"),
new System.Data.Common.DataColumnMapping("fName", "fName"),
new System.Data.Common.DataColumnMapping("lName", "lName"),
new System.Data.Common.DataColumnMapping("mName", "mName"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping
("companyID", "companyID"),
new System.Data.Common.DataColumnMapping("email", "email"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("street1", "street1"),
new System.Data.Common.DataColumnMapping("street2", "street2"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping
("country", "country")})});
this.insertPerson2.CommandText = "[insert_person]";
this.insertPerson2.CommandType
System.Data.CommandType.StoredProcedure;
this.insertPerson2.Connection = this.sqlConnection1;
this.insertPerson2.Parameters.Add(new System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
// end of vs.net generated snipet
// dataset schema for PersonDS as generated by vs.net designer
fName,
lName,
mName,
Title,
companyID,
email,
phone,
street1,
street2,
city,
state,
zip,
country
// end of dataset schema as generated by designer
// my button click handler to add a row to the dataset then use the //
adapter to update the database private void submitPerson_Click(object
sender, System.EventArgs e) {
dsPerson = (jobHunt.PersonDS) Session["personsDS"];
DataTable tblFolks = dsPerson.Tables["person"];
DataRow newPerson = tblFolks.NewRow();
newPerson["fName"] = tbFirst.Text;
newPerson["lName"] = tbLast.Text;
newPerson["mName"] = tbMiddle.Text;
newPerson["Title"] = tbTitle.Text;
newPerson["companyID"] = 121;
newPerson["email"] = tbEmail.Text;
newPerson["phone"] = tbPhone.Text;
newPerson["street1"] = tbStreet1.Text;
newPerson["street2"] = tbStreet2.Text;
newPerson["city"] = tbCity.Text;
newPerson["state"] = tbState.Text;
newPerson["zip"] = tbZip.Text;
newPerson["country"] = tbCountry.Text;
tblFolks.Rows.Add(newPerson);
DataGrid1.DataBind();
Session["personsDS"] = dsPerson;
sqlDataAdapter1.Update(dsPerson, "person");
//end button click handler
I think I've included all the necessary info. Thanks again for any help.
=== Fast Track ADO.NET with C# is a concise introduction to the
concepts, techniques, and libraries that you will need in order to start
using ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
Message #3 by "Keith Thorne" <keith@k...> on Thu, 13 Mar 2003 16:24:58
|
|
Man! thanks so much. I can't tell you how I have wracked over this,
simple as it is.
One question though, how do you let designer "seek" out the parameters?
I hate it when learning a new development environment, you get stuck on
such trivial things - but it's happen more than once.
Thanks tons again! -Keith
> Your Parameter is defined wrongly (wrong type. But more importantly with
an included pair of quotes - perhaps you typed them into the Property? I
tend to let the DataAdapter configuration seeek out the parameters. In
your case it wouldn't match firstName because the column is fName. In
general, it makes sense to keep all sproc parameters with the exact same
spelling as the underlying columns.
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
Should be
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.Char, 20, "fName"));
Brian
-----Original Message-----
From: Keith Thorne [mailto:keith@k...]
Sent: Thu, 13 Mar 2003 07:53
To: ADO.NET
Subject: [ado_dotnet] problem with sqlparameter error parameter was not
supplied
This is my first time trying to use vs.net and the data adapter. I think
I'm close but I keep getting this error message when I try to use the
adapter update and nothing I do seems to be accepted by the sproc.
Here is the error...
"Procedure 'insert_person' expects parameter '@f...', which was not
supplied."
Below are my sproc, the adapter and insert commands as generated by
vs.net designer, the data set schema for PersonDS, and my button click
handler that adds a row to the dataset and then calls the adapter update
resulting in the error message.
Any help would be greatly appricated!
-- my stored precedure with one parameter
CREATE Procedure insert_person
( @firstName char(20) )
As
INSERT person VALUES (@firstName ,
"last" ,
"middle" ,
"title" ,
121 ,
"email" ,
"phone",
"street1" ,
"street2" ,
"city" ,
"ST" ,
"112233" ,
"USA" )
return
GO
-- end stored procedure
// snipet from vs.net generated adapter and sqlcommand with parameter
this.sqlDataAdapter1.InsertCommand = this.insertPerson2;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "person", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("personID",
"personID"),
new System.Data.Common.DataColumnMapping("fName", "fName"),
new System.Data.Common.DataColumnMapping("lName", "lName"),
new System.Data.Common.DataColumnMapping("mName", "mName"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping
("companyID", "companyID"),
new System.Data.Common.DataColumnMapping("email", "email"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("street1", "street1"),
new System.Data.Common.DataColumnMapping("street2", "street2"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping
("country", "country")})});
this.insertPerson2.CommandText = "[insert_person]";
this.insertPerson2.CommandType
System.Data.CommandType.StoredProcedure;
this.insertPerson2.Connection = this.sqlConnection1;
this.insertPerson2.Parameters.Add(new System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
// end of vs.net generated snipet
// dataset schema for PersonDS as generated by vs.net designer
fName,
lName,
mName,
Title,
companyID,
email,
phone,
street1,
street2,
city,
state,
zip,
country
// end of dataset schema as generated by designer
// my button click handler to add a row to the dataset then use the //
adapter to update the database private void submitPerson_Click(object
sender, System.EventArgs e) {
dsPerson = (jobHunt.PersonDS) Session["personsDS"];
DataTable tblFolks = dsPerson.Tables["person"];
DataRow newPerson = tblFolks.NewRow();
newPerson["fName"] = tbFirst.Text;
newPerson["lName"] = tbLast.Text;
newPerson["mName"] = tbMiddle.Text;
newPerson["Title"] = tbTitle.Text;
newPerson["companyID"] = 121;
newPerson["email"] = tbEmail.Text;
newPerson["phone"] = tbPhone.Text;
newPerson["street1"] = tbStreet1.Text;
newPerson["street2"] = tbStreet2.Text;
newPerson["city"] = tbCity.Text;
newPerson["state"] = tbState.Text;
newPerson["zip"] = tbZip.Text;
newPerson["country"] = tbCountry.Text;
tblFolks.Rows.Add(newPerson);
DataGrid1.DataBind();
Session["personsDS"] = dsPerson;
sqlDataAdapter1.Update(dsPerson, "person");
//end button click handler
I think I've included all the necessary info. Thanks again for any help.
=== Fast Track ADO.NET with C# is a concise introduction to the
concepts, techniques, and libraries that you will need in order to start
using ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
Message #4 by "Brian Smith" <bsmith@l...> on Thu, 13 Mar 2003 16:27:28 -0000
|
|
>One question though, how do you let designer "seek" out the
parameters?<
1. Establish a connection to your database in Server Explorer
2. Create a new Component Class - this way it gets a Designer
3. Drag a DataAdapter onto the Designer
4. It'll now prompt you to identify the connection, and then allow you
to choose the Existing Stored Procedures you have created for SELECT,
INSERT etc.
5. The designer then automatically generates the code to create all the
commands, parameters etc.
At any time, you can redo step 4 by choosing Configure Adapter from the
DataAdapter context menu. It generates slightly less efficient code,
because it all goes in the class constructor and InitializeComponent,
but you can always cut-and-paste it into another class if you want...
brian
-----Original Message-----
From: Keith Thorne [mailto:keith@k...]
Sent: Thu, 13 Mar 2003 16:25
To: ADO.NET
Subject: [ado_dotnet] RE: problem with sqlparameter error parameter was
not supplied
Man! thanks so much. I can't tell you how I have wracked over this,
simple as it is.
One question though, how do you let designer "seek" out the parameters?
I hate it when learning a new development environment, you get stuck on
such trivial things - but it's happen more than once.
Thanks tons again! -Keith
> Your Parameter is defined wrongly (wrong type. But more importantly
> with
an included pair of quotes - perhaps you typed them into the Property? I
tend to let the DataAdapter configuration seeek out the parameters. In
your case it wouldn't match firstName because the column is fName. In
general, it makes sense to keep all sproc parameters with the exact same
spelling as the underlying columns.
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
Should be
System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.Char, 20, "fName"));
Brian
-----Original Message-----
From: Keith Thorne [mailto:keith@k...]
Sent: Thu, 13 Mar 2003 07:53
To: ADO.NET
Subject: [ado_dotnet] problem with sqlparameter error parameter was not
supplied
This is my first time trying to use vs.net and the data adapter. I think
I'm close but I keep getting this error message when I try to use the
adapter update and nothing I do seems to be accepted by the sproc.
Here is the error...
"Procedure 'insert_person' expects parameter '@f...', which was not
supplied."
Below are my sproc, the adapter and insert commands as generated by
vs.net designer, the data set schema for PersonDS, and my button click
handler that adds a row to the dataset and then calls the adapter update
resulting in the error message.
Any help would be greatly appricated!
-- my stored precedure with one parameter
CREATE Procedure insert_person
( @firstName char(20) )
As
INSERT person VALUES (@firstName ,
"last" ,
"middle" ,
"title" ,
121 ,
"email" ,
"phone",
"street1" ,
"street2" ,
"city" ,
"ST" ,
"112233" ,
"USA" )
return
GO
-- end stored procedure
// snipet from vs.net generated adapter and sqlcommand with parameter
this.sqlDataAdapter1.InsertCommand = this.insertPerson2;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "person", new
System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("personID",
"personID"),
new System.Data.Common.DataColumnMapping("fName", "fName"),
new System.Data.Common.DataColumnMapping("lName", "lName"),
new System.Data.Common.DataColumnMapping("mName", "mName"),
new System.Data.Common.DataColumnMapping("Title", "Title"),
new System.Data.Common.DataColumnMapping
("companyID", "companyID"),
new System.Data.Common.DataColumnMapping("email", "email"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("street1", "street1"),
new System.Data.Common.DataColumnMapping("street2", "street2"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping
("country", "country")})});
this.insertPerson2.CommandText = "[insert_person]";
this.insertPerson2.CommandType
System.Data.CommandType.StoredProcedure;
this.insertPerson2.Connection = this.sqlConnection1;
this.insertPerson2.Parameters.Add(new System.Data.SqlClient.SqlParameter
("@firstName", System.Data.SqlDbType.NVarChar, 0, "\"fName\""));
// end of vs.net generated snipet
// dataset schema for PersonDS as generated by vs.net designer
fName,
lName,
mName,
Title,
companyID,
email,
phone,
street1,
street2,
city,
state,
zip,
country
// end of dataset schema as generated by designer
// my button click handler to add a row to the dataset then use the //
adapter to update the database private void submitPerson_Click(object
sender, System.EventArgs e) {
dsPerson = (jobHunt.PersonDS) Session["personsDS"];
DataTable tblFolks = dsPerson.Tables["person"];
DataRow newPerson = tblFolks.NewRow();
newPerson["fName"] = tbFirst.Text;
newPerson["lName"] = tbLast.Text;
newPerson["mName"] = tbMiddle.Text;
newPerson["Title"] = tbTitle.Text;
newPerson["companyID"] = 121;
newPerson["email"] = tbEmail.Text;
newPerson["phone"] = tbPhone.Text;
newPerson["street1"] = tbStreet1.Text;
newPerson["street2"] = tbStreet2.Text;
newPerson["city"] = tbCity.Text;
newPerson["state"] = tbState.Text;
newPerson["zip"] = tbZip.Text;
newPerson["country"] = tbCountry.Text;
tblFolks.Rows.Add(newPerson);
DataGrid1.DataBind();
Session["personsDS"] = dsPerson;
sqlDataAdapter1.Update(dsPerson, "person");
//end button click handler
I think I've included all the necessary info. Thanks again for any help.
=== Fast Track ADO.NET with C# is a concise introduction to the
concepts, techniques, and libraries that you will need in order to start
using ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
|
|
|
View Cart
