Intermittent SP error - "has too many arguments"

D

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
 
G

Guest

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 said:
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
Click to expand...
 
D

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
 
C

Chris Dunaway

Comments/Questions inline:
//

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

With cmdUseAudit_Ins
Click to expand...


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

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

It appears you are creating a NEW datatable here...
Me.tblUseAudit = Me.Tables.Add("tblUseAudit")
Click to expand...

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

dbuchanan

Chris,

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

dbuchanan
 

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

How to insert a record with a stored procecure 2
Too Many Arguments Specified Error 3
How to update datagrid when it displays lookup table values 3
Error: Failed to convert parameter value from a String to a Byte[]. (Need Help Urgently) 0
Why am I getting ForeignKeyConstraint requires child key value error? 12
HELP "has too many arguments specified" error 3
Datareader to Datatable gets stuck... 3
Inserting Date info into Sql Server 1

Top