Stored Procedure Problems

[Thomas Beyerlein]

I am building a UI, I am using stored procedures for executing SQL
commands on the server. When I ran a insert stored procedure it returned
me a error message that it could not find a parameter that I sent it, it
was sent but in the building of the parameter the first and second
parameters places were switched after the second parameter is loaded.
Has anyone herd of this and is there a way around it or a fix. If
looking at the code would help let me know and I will post it.

Tom

 

[test]

Always post the code, man!

 

[Thomas Beyerlein]

Public Function Insert_SomeThing(ByVal Parameters(,) As String,
ByVal SP As String)

Try
'set the SQL manager provider connection string
'need to get connection string from CTOS_Interface
'hard coded for now
connectionString = "server=CS-78\TEST_DB,
1432;database=pubs;User ID=;Password="

'Call the SqlServer wrapper constructor and
'pass the db connection string
sqlServer = New SqlServer(connectionString)

'set the SpParamXmlDoc XmlDocument property
xmlDoc.Load(Application.StartupPath &
"\StoredProcedures.xml")
sqlServer.SpParamXmlDoc = xmlDoc

Dim i As Integer

For i = 0 To Parameters.GetUpperBound(0)

'Add parameter name-values
'MsgBox(Parameters(i, 0))
'MsgBox(Parameters(i, 1))
params.Add(Parameters(i, 0), Parameters(i, 1))
Next
'This is what is being passed in Parameters
'("@username", "Tom")
'("@Column_Name", "column")
'("@PKey", "PK")
'("@tbl_Name", "table")
'("@I_Value", "oldValue")
'("@N_Value", "newValue")
'("@Why", "Why")

'Execute the sp
sqlServer.ExecSp(SP, params)

Catch ex As Exception

'Display the exception message
errorMessage = ex.Message
If Not IsNothing(ex.InnerException) Then
errorMessage &= ex.InnerException.Message
End If

MsgBox(errorMessage)


'/ Executes a stored procedure with or without parameters that
'/ does not return output values or a resultset.
'/ <param name="spName">The name of the stored procedure to
execute.</param>
'/ <param name="paramValues">A name-value pair of stored
procedure parameter
'/ name(s) and value(s).</param>
Public Sub ExecSp(ByVal spName As String, ByVal paramValues As
IDictionary)
Dim command As SqlCommand = Nothing
Try
' Get the initialized SqlCommand instance
command = GetSqlCommand(spName)

' Set the parameter values for the SqlCommand
SetParameterValues(command, paramValues)

' Run the stored procedure
RunSp(command)
Catch e As Exception
LogError(e)
Throw New Exception(ExceptionMsg, e)
Finally
' Close and release resources
DisposeCommand(command)
End Try
End Sub


CREATE PROCEDURE usp_Insert_Projects_Audit
(
@username VarChar (50),
@Column_Name VarChar (50),
@PKey VarChar (50),
@tbl_Name VarChar (50),
@I_Value VarChar (50),
@N_Value VarChar (50),
@Why VarChar (50)
)
AS
INSERT INTO tbl_AT_ProjectsTest
(UserName, Column_Name, PKey, tbl_Name, I_Value, N_Value, [Date], Why)
VALUES(@username, @Column_Name, @PKey, @tbl_Name, @I_Value, @N_Value,
GETDATE(), @Why)
GO