Intermittent SP error - "has too many arguments"

[dbuchanan]

Hello,

I have an audit table into which I insert information about the use of
the application. This works sometimes and other times fails. I cannot
find any reason for it failing. It is always given the information.

Here are the errors I receive;

---------------------------
Exception Message: Procedure or function usp_UseAudit_ins has too many
arguments specified.
---------------------------

---------------------------
Exception Source: .Net SqlClient Data Provider
---------------------------

---------------------------
Exception StackTrace: at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable) at CLIP.UseageAudit.InsertAuditRecord(String selection,
String version)
---------------------------
The call to the method
\\
InsertAuditRecord("ManageJobs", _version)
//
The Method
\\
Friend Sub InsertAuditRecord(ByVal selection As String, ByVal version
As String)

Call DAL.InsertUseAudit()

Dim row As DataRow = _dataSet1.Tables("tblUseAudit").NewRow

row("pkUseAuditId") = Guid.NewGuid.ToString
row("ClipUser") = SystemInformation.UserName.ToString
row("version") = version
row("DateTime") = Now
row("Selection") = selection

_dataSet1.Tables("tblUseAudit").Rows.Add(row)

Dim dsDataChanges As New CLIP.dsTables
dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables)
If (Not (dsDataChanges) Is Nothing) Then

DAL.daUseAudit.Update(dsDataChanges, "tblUseAudit")

_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()
//

The DataAccess (DAL)
\\
Public Sub InsertUseAudit()

With cmdUseAudit_Ins
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_UseAudit_ins"
.Connection = sqlConn
With cmdUseAudit_Ins.Parameters

' No return required - Does not work even with this incuded
'.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int,
'4, ParameterDirection.ReturnValue, False, CType(0, Byte),
'CType(0, Byte), "", DataRowVersion.Current, Nothing))

.Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36,
"pkUseAuditId"))
.Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50,
"ClipUser"))
.Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version"))
.Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4,
"DateTime"))
.Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50,
"Selection"))
End With
End With
End Sub
//

Parts of the DataSet
\\
Friend tblUseAudit As New DataTable

Me.tblUseAudit = Me.Tables.Add("tblUseAudit")

' tblUseAudit
Dim pkUseAuditId As DataColumn =
Me.tblUseAudit.Columns.Add("pkUseAuditId", GetType(String))
pkUseAuditId.MaxLength = 36
pkUseAuditId.AllowDBNull = False
Dim ClipUser As DataColumn = Me.tblUseAudit.Columns.Add("ClipUser",
GetType(String))
ClipUser.MaxLength = 50
ClipUser.AllowDBNull = False
Dim version As DataColumn = Me.tblUseAudit.Columns.Add("version",
GetType(String))
version.MaxLength = 50
version.AllowDBNull = False
Dim DateTime As DataColumn = Me.tblUseAudit.Columns.Add("DateTime",
GetType(DateTime))
DateTime.AllowDBNull = False
Dim Selection As DataColumn = Me.tblUseAudit.Columns.Add("Selection",
GetType(String))
Selection.MaxLength = 50
Selection.AllowDBNull = False

pkUseAuditId.ReadOnly = True

tblUseAudit.PrimaryKey = New DataColumn()
{tblUseAudit.Columns("pkUseAuditId")}
The Table
\\
CREATE TABLE [tblUseAudit] (
[pkUseAuditId] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ClipUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DateTime] [smalldatetime] NOT NULL ,
[Selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
//
The Stored Procedure
\\
CREATE PROCEDURE dbo.usp_UseAudit_ins(
@pkUseAuditId varchar(36),
@ClipUser varchar(50),
@version varchar(50),
@DateTime smalldatetime,
@Selection varchar(50)
) AS
SET NOCOUNT OFF;
INSERT INTO tblUseAudit(
pkUseAuditId,
ClipUser,
version,
DateTime,
Selection
) VALUES (
@pkUseAuditId,
@ClipUser,
@version,
@DateTime,
@Selection
)

/** no return required - doesn't work even with this included.
;
SELECT
pkUseAuditId,
ClipUser,
version,
DateTime,
Selection
FROM tblUseAudit
WHERE
(pkUseAuditId = @pkUseAuditId)
**/

GO
//

What could cause this error?

Thank you,
dbuchanan

[Cowboy (Gregory A. Beamer) - MVP]

I would turn on profiler until you get a hit on the query that breaks. You
can then see the parameters sent. I do not, at my cursory glance, see
anything that points to a reason for failure.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
"dbuchanan" wrote:

Hello,

I have an audit table into which I insert information about the use of
the application. This works sometimes and other times fails. I cannot
find any reason for it failing. It is always given the information.

Here are the errors I receive;

---------------------------
Exception Message: Procedure or function usp_UseAudit_ins has too many
arguments specified.
---------------------------

---------------------------
Exception Source: .Net SqlClient Data Provider
---------------------------

---------------------------
Exception StackTrace: at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable) at CLIP.UseageAudit.InsertAuditRecord(String selection,
String version)
---------------------------
The call to the method
\\
InsertAuditRecord("ManageJobs", _version)
//
The Method
\\
Friend Sub InsertAuditRecord(ByVal selection As String, ByVal version
As String)

Call DAL.InsertUseAudit()

Dim row As DataRow = _dataSet1.Tables("tblUseAudit").NewRow

row("pkUseAuditId") = Guid.NewGuid.ToString
row("ClipUser") = SystemInformation.UserName.ToString
row("version") = version
row("DateTime") = Now
row("Selection") = selection

_dataSet1.Tables("tblUseAudit").Rows.Add(row)

Dim dsDataChanges As New CLIP.dsTables
dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables)
If (Not (dsDataChanges) Is Nothing) Then

DAL.daUseAudit.Update(dsDataChanges, "tblUseAudit")

_dataSet1.Merge(dsDataChanges)
_dataSet1.AcceptChanges()
//

The DataAccess (DAL)
\\
Public Sub InsertUseAudit()

With cmdUseAudit_Ins
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_UseAudit_ins"
.Connection = sqlConn
With cmdUseAudit_Ins.Parameters

' No return required - Does not work even with this incuded
'.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int,
'4, ParameterDirection.ReturnValue, False, CType(0, Byte),
'CType(0, Byte), "", DataRowVersion.Current, Nothing))

.Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36,
"pkUseAuditId"))
.Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50,
"ClipUser"))
.Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version"))
.Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4,
"DateTime"))
.Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50,
"Selection"))
End With
End With
End Sub
//

Parts of the DataSet
\\
Friend tblUseAudit As New DataTable

Me.tblUseAudit = Me.Tables.Add("tblUseAudit")

' tblUseAudit
Dim pkUseAuditId As DataColumn =
Me.tblUseAudit.Columns.Add("pkUseAuditId", GetType(String))
pkUseAuditId.MaxLength = 36
pkUseAuditId.AllowDBNull = False
Dim ClipUser As DataColumn = Me.tblUseAudit.Columns.Add("ClipUser",
GetType(String))
ClipUser.MaxLength = 50
ClipUser.AllowDBNull = False
Dim version As DataColumn = Me.tblUseAudit.Columns.Add("version",
GetType(String))
version.MaxLength = 50
version.AllowDBNull = False
Dim DateTime As DataColumn = Me.tblUseAudit.Columns.Add("DateTime",
GetType(DateTime))
DateTime.AllowDBNull = False
Dim Selection As DataColumn = Me.tblUseAudit.Columns.Add("Selection",
GetType(String))
Selection.MaxLength = 50
Selection.AllowDBNull = False

pkUseAuditId.ReadOnly = True

tblUseAudit.PrimaryKey = New DataColumn()
{tblUseAudit.Columns("pkUseAuditId")}
The Table
\\
CREATE TABLE [tblUseAudit] (
[pkUseAuditId] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ClipUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DateTime] [smalldatetime] NOT NULL ,
[Selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
//
The Stored Procedure
\\
CREATE PROCEDURE dbo.usp_UseAudit_ins(
@pkUseAuditId varchar(36),
@ClipUser varchar(50),
@version varchar(50),
@DateTime smalldatetime,
@Selection varchar(50)
) AS
SET NOCOUNT OFF;
INSERT INTO tblUseAudit(
pkUseAuditId,
ClipUser,
version,
DateTime,
Selection
) VALUES (
@pkUseAuditId,
@ClipUser,
@version,
@DateTime,
@Selection
)

/** no return required - doesn't work even with this included.
;
SELECT
pkUseAuditId,
ClipUser,
version,
DateTime,
Selection
FROM tblUseAudit
WHERE
(pkUseAuditId = @pkUseAuditId)
**/

GO
//

What could cause this error?

Thank you,
dbuchanan

[dbuchanan]

Hello Gregory,

Thank you for your reply

This is a trace of when it works;
\\
exec usp_UseAudit_ins
@pkUseAuditId = '929bc078-f8c9-4ac9-ace8-930f4a5661e9',
@ClipUser = 'dbuchanan',
@version = '1.1.4.16949',
@DateTime = 'Oct 26 2005 11:27AM',
@Selection = 'ManageJobs'
//

This is a trace of when it does not work;
\\
exec usp_UseAudit_ins
@pkUseAuditId = '47975d17-a234-45d2-a3a3-c78ed87e42ca',
@ClipUser = 'dbuchanan',
@version = '1.1.4.16949',
@DateTime = 'Oct 26 2005 11:27AM',
@Selection = 'Close',
@pkUseAuditId = '47975d17-a234-45d2-a3a3-c78ed87e42ca',
@ClipUser = 'dbuchanan',
@version = '1.1.4.16949',
@DateTime = 'Oct 26 2005 11:27AM',
@Selection = 'Close'
//

It executes the stored procedure with the row items inserted twice!
What's up?

I stepped through the code in debug and nothing ran twice. It's a
mystery to me! It runs differently with the same code!

Where do I go from here?

dbuchanan

[Chris Dunaway]

Comments/Questions inline:

dbuchanan wrote:

//

The DataAccess (DAL)
\\
Public Sub InsertUseAudit()

With cmdUseAudit_Ins

where is cmdUseAudio_Ins defined?

.CommandType = CommandType.StoredProcedure
.CommandText = "usp_UseAudit_ins"
.Connection = sqlConn
With cmdUseAudit_Ins.Parameters

' No return required - Does not work even with this incuded
'.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int,
'4, ParameterDirection.ReturnValue, False, CType(0, Byte),
'CType(0, Byte), "", DataRowVersion.Current, Nothing))

.Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36,
"pkUseAuditId"))
.Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50,
"ClipUser"))
.Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version"))
.Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4,
"DateTime"))
.Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50,
"Selection"))
End With
End With
End Sub
'It seems that every time you call InsertUseAudit you add 'new'
parameters to the parameters collection. Perhaps you are adding
duplicate parameters when you call this method the second time? You
might try calling the .clear method of the parameters collection before
adding 'new' parameters.
//

Parts of the DataSet
\\
Friend tblUseAudit As New DataTable
It appears you are creating a NEW datatable here...

Me.tblUseAudit = Me.Tables.Add("tblUseAudit")

....but then throwing it away and assigning the table from the Tables
collection. Are these two variables the same?

[dbuchanan]

Chris,

Thank you very much. I needed to add a .Clear() to the parameters

dbuchanan