Please help me read this exception

[Douglas Buchanan]

Where is the problem?

Find Below
•Exception
•Stored procedure
•Table Definition
•XML Dataset

=== The Exception ================================
System.Data.SqlClient.SqlException: Procedure or function F1Ins has
too many arguments specified.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at Test_lkp01RefSource.Form1.Button2_Click(Object sender, EventArgs e)
in C:\Documents and Settings\DBuchanan\My Documents\Visual Studio
Projects\Test_lkp01RefSource\Form1.vb:line 167
An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in Test_lkp01RefSource.exe

Additional information: System error.
=== End ========================================

=== The Stored procedure ==========================
CREATE PROCEDURE [dbo].[F1Ins]
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide) VALUES (@RefSource,
@ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @@IDENTITY);

GO
=== End ========================================

=== The Table Definition ============================
CREATE TABLE [dbo].[lkp01RefSource] (
[RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ord] [tinyint] NOT NULL ,
[hide] [bit] NOT NULL
) ON [PRIMARY]
GO
=== End ========================================

=== The XML Dataset ==============================
<?xml version="1.0" standalone="yes"?>
<xs:schema id="Dsf1" targetNamespace="http://www.tempuri.org/Dsf1.xsd"
xmlns:mstns="http://www.tempuri.org/Dsf1.xsd"
xmlns="http://www.tempuri.org/Dsf1.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="Dsf1" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="lkp01RefSource">
<xs:complexType>
<xs:sequence>
<xs:element name="RefSourceID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:unsignedByte" />
<xs:element name="RefSource" type="xs:string" />
<xs:element name="ord" type="xs:unsignedByte" default="0"/>
<xs:element name="hide" type="xs:boolean" default="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:lkp01RefSource" />
<xs:field xpath="mstns:RefSourceID" />
</xs:unique>
</xs:element>
</xs:schema>
=== End ========================================

 

[David Browne]

Where is the problem?

Find Below
.Exception
.Stored procedure
.Table Definition
.XML Dataset

=== The Exception ================================
System.Data.SqlClient.SqlException: Procedure or function F1Ins has
too many arguments specified.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at Test_lkp01RefSource.Form1.Button2_Click(Object sender, EventArgs e)
in C:\Documents and Settings\DBuchanan\My Documents\Visual Studio
Projects\Test_lkp01RefSource\Form1.vb:line 167
An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in Test_lkp01RefSource.exe

Additional information: System error.
=== End ========================================

=== The Stored procedure ==========================
CREATE PROCEDURE [dbo].[F1Ins]
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide) VALUES (@RefSource,
@ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @@IDENTITY);

The problem is in the SqlCommand.Parameters. This error suggests that the
SqlCommand has more than three parameters attached. Look there, and post
that code if you're still stuck.

David

 

[Douglas Buchanan]

David,

Thank you for your reply.

The SqlCommand (insert) and its parameters were auto-generated by the
DACW.
(Note: I am working to learn coding without DACW - this is a step
along the way)

Here is the insert command and its Parameters:
(the stored procedure is in the original post)

'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "[F1Ins]"
Me.SqlInsertCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSourceID",
System.Data.SqlDbType.TinyInt, 1, "RefSourceID"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSource",
System.Data.SqlDbType.VarChar, 25, "RefSource"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ord",
System.Data.SqlDbType.TinyInt, 1, "ord"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@hide", System.Data.SqlDbType.Bit,
1, "hide"))

Where is the problem?

Find Below
.Exception
.Stored procedure
.Table Definition
.XML Dataset

=== The Exception ================================
System.Data.SqlClient.SqlException: Procedure or function F1Ins has
too many arguments specified.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at Test_lkp01RefSource.Form1.Button2_Click(Object sender, EventArgs e)
in C:\Documents and Settings\DBuchanan\My Documents\Visual Studio
Projects\Test_lkp01RefSource\Form1.vb:line 167
An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in Test_lkp01RefSource.exe

Additional information: System error.
=== End ========================================

=== The Stored procedure ==========================
CREATE PROCEDURE [dbo].[F1Ins]
(
@RefSource varchar(25),
@ord tinyint,
@hide bit
)
AS
SET NOCOUNT OFF;
INSERT INTO lkp01RefSource(RefSource, ord, hide) VALUES (@RefSource,
@ord, @hide);
SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE
(RefSourceID = @@IDENTITY);

The problem is in the SqlCommand.Parameters. This error suggests that the
SqlCommand has more than three parameters attached. Look there, and post
that code if you're still stuck.

David

 

[David Browne]

David,

Thank you for your reply.

The SqlCommand (insert) and its parameters were auto-generated by the
DACW.
(Note: I am working to learn coding without DACW - this is a step
along the way)

Here is the insert command and its Parameters:
(the stored procedure is in the original post)

'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "[F1Ins]"
Me.SqlInsertCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSourceID",
System.Data.SqlDbType.TinyInt, 1, "RefSourceID"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSource",
System.Data.SqlDbType.VarChar, 25, "RefSource"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ord",
System.Data.SqlDbType.TinyInt, 1, "ord"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@hide", System.Data.SqlDbType.Bit,
1, "hide"))

Here's your problem:

System.Data.SqlClient.SqlParameter("@RefSourceID",
System.Data.SqlDbType.TinyInt, 1, "RefSourceID"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSource",
System.Data.SqlDbType.VarChar, 25, "RefSource"))

It's added two parameters, @RefSourceID and @RefSource, but your stored
procedure only accepts @RefSource.

David

 

[Douglas Buchanan]

Dave,

Thank you so much.

So... the things that I heard about DACW making mistakes.... I guess
it's more mistake prone than I realized. You have to know what you
want and keep DACW on a short leash. Right?

Doug

David,

Thank you for your reply.

The SqlCommand (insert) and its parameters were auto-generated by the
DACW.
(Note: I am working to learn coding without DACW - this is a step
along the way)

Here is the insert command and its Parameters:
(the stored procedure is in the original post)

'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "[F1Ins]"
Me.SqlInsertCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSourceID",
System.Data.SqlDbType.TinyInt, 1, "RefSourceID"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSource",
System.Data.SqlDbType.VarChar, 25, "RefSource"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ord",
System.Data.SqlDbType.TinyInt, 1, "ord"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@hide", System.Data.SqlDbType.Bit,
1, "hide"))

Here's your problem:

System.Data.SqlClient.SqlParameter("@RefSourceID",
System.Data.SqlDbType.TinyInt, 1, "RefSourceID"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RefSource",
System.Data.SqlDbType.VarChar, 25, "RefSource"))

It's added two parameters, @RefSourceID and @RefSource, but your stored
procedure only accepts @RefSource.

David