Please help me read this exception

  • Thread starter Thread starter Douglas Buchanan
  • Start date Start date
D

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" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:lkp01RefSource" />
<xs:field xpath="mstns:RefSourceID" />
</xs:unique>
</xs:element>
</xs:schema>
=== End ========================================
 
Douglas Buchanan said:
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);
Click to expand...

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,

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"))


David Browne said:
Douglas Buchanan said:
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);
Click to expand...

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
Click to expand...
 
Douglas Buchanan said:
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"))
Click to expand...

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
 
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 Browne said:
Douglas Buchanan said:
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"))
Click to expand...

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
Click to expand...
 
Back
Top