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



*** Sent via Developersdex http://www.developersdex.com ***

[test@test.com]

Re: Stored Procedure Problems


Always post the code, man!

On Tue, 11 Oct 2005 08:57:11 -0700, Thomas Beyerlein <tbxml@yahoo.co m>
wrote:
[color=blue]
>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
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***[/color]

[Thomas Beyerlein]

Re: Stored Procedure Problems


Public Function Insert_SomeThin g(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
connectionStrin g = "server=CS-78\TEST_DB,
1432;database=p ubs;User ID=;Password="

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

'set the SpParamXmlDoc XmlDocument property
xmlDoc.Load(App lication.Startu pPath &
"\StoredProcedu res.xml")
sqlServer.SpPar amXmlDoc = xmlDoc

Dim i As Integer

For i = 0 To Parameters.GetU pperBound(0)

'Add parameter name-values
'MsgBox(Paramet ers(i, 0))
'MsgBox(Paramet ers(i, 1))
params.Add(Para meters(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.ExecS p(SP, params)

Catch ex As Exception

'Display the exception message
errorMessage = ex.Message
If Not IsNothing(ex.In nerException) Then
errorMessage &= ex.InnerExcepti on.Message
End If

MsgBox(errorMes sage)


'/ Executes a stored procedure with or without parameters that
'/ does not return output values or a resultset.
'/ <param name="spName">T he name of the stored procedure to
execute.</param>
'/ <param name="paramValu es">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(s pName)

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

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


CREATE PROCEDURE usp_Insert_Proj ects_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_Projects Test
(UserName, Column_Name, PKey, tbl_Name, I_Value, N_Value, [Date], Why)
VALUES(@UserNam e, @Column_Name, @PKey, @tbl_Name, @I_Value, @N_Value,
GETDATE(), @Why)
GO



*** Sent via Developersdex http://www.developersdex.com ***