Click here to Skip to main content
15,867,594 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Anybody who would help me solve this problem?
Please guide me to the right way.

Following is a common DB function which is called from all data access functions.
All SQL commands are stored procedures, and problem is at line
VB
aryOutputParam.Add(sqlParam)


VB
Public Function ActionQuery(ByVal cmdText As String, ByRef errorCode As Integer, _
		Optional ByVal sqlParams As List(Of SqlParameter) = Nothing, _
		Optional ByRef ds As DataSet = Nothing) As ArrayList
	Dim aryOutputParamValues As New ArrayList

	Dim aryOutputParam As New ArrayList
	Dim command As SqlCommand = New SqlCommand
	Dim sqlParamReturn As SqlParameter = Nothing

	errorCode = clsDBHelper.ErrorCode.ERRORCODE_SUCCESS

	Try
		With command
			.Connection = conn
			.CommandText = cmdText
			.CommandTimeout = 6000

			If Not sqlParams Is Nothing AndAlso sqlParams.Count > 0 Then
				For Each sqlParam As SqlParameter In sqlParams
					.Parameters.Add(sqlParam)
					If sqlParam.Direction = ParameterDirection.Output Then
						aryOutputParam.Add(sqlParam)
					ElseIf sqlParam.Direction = ParameterDirection.ReturnValue Then
						sqlParamReturn = sqlParam
					End If
				Next
			End If

			If ds Is Nothing Then
				.ExecuteNonQuery()
			Else
				Dim da As SqlDataAdapter = New SqlDataAdapter()
				da.SelectCommand = command
				da.Fill(ds)
			End If
			If sqlParamReturn IsNot Nothing Then
				errorCode = sqlParamReturn.Value
			End If

			For Each sqlParam As SqlParameter In aryOutputParam
				aryOutputParamValues.Add(sqlParam.Value)
			Next
		End With
	Catch ex As Exception
		ErrorHandler.Handle(ex)
	End Try

	Return aryOutputParamValues
End Function


Thanks for your time and consideration.

What I have tried:

I've also tried
VB
.Parameters.AddWithValue(sqlParam.ParameterName, sqlParam.Value)
in place of
VB
.Parameters.Add(sqlParam)
Posted
Updated 10-Jun-18 22:18pm
Comments
CHill60 11-Jun-18 3:21am    
The best place to start would be the support forum for the software you have having problems with!Support and Services | Micro Focus[^]
Patrice T 11-Jun-18 4:00am    
Show code of stored procedure.

1 solution

The problem may be that you are passing the command text string into the method as a string - so the actual text passed to SQL as a command could be anything, regardless of how careful the method itself is with using parameters.
It's OK if you call it like this:
ActionQuery("SELECT * FROM MyTable WHERE ID = @ID", ...

But you could equally well do this:
ActionQuery(TextBox1.Text, ...
And that is wide open to SQL injection. I don't think you can solve this problem and keep the current method structure.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900