Problem in .Net programming

  • Thread starter Thread starter Win
  • Start date Start date
W

Win

Dear All,

I am going to change the coding from ASP & VB6 to ASP.Net and VB.Net.
However, there is no data retrieved after I changed the coding.
Is there anything wrong?

Thanks

================================================
VB6 coding

Public Function myRS(ByRef vntErr As Variant, ByRef vntErrDesc As Variant, _
ByVal strSPName As String, ParamArray vntPara()
As Variant) As ADODB.Recordset
On Error GoTo ErrorHandler

Dim intCount As Integer
Dim objCmd As Object
Dim objRS As Object

Set objCmd = CreateObject("ADODB.COMMAND")
Set objRS = CreateObject("ADODB.RECORDSET")

With objCmd
.CommandType = adCmdStoredProc
.CommandText = strSPName
.CommandTimeout = 0
.ActiveConnection = mobjConn
.Parameters.Refresh
If .Parameters.Count > 0 Then
For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
.Parameters(intCount + 1) = vntPara(intCount)
Next
End If
Set objRS = .Execute()

If .Parameters.Count > 0 Then
For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
vntPara(intCount) = .Parameters(intCount + 1)
Next
End If

End With

Set myRS = objRS

ProcExit:
Set objRS = Nothing
Set objCmd = Nothing
Exit Function

ErrorHandler:
vntErr = Err.Number
vntErrDesc = Err.Description
Resume ProcExit

End Function
================================================

..Net coding
================================================

Public Function myRS(ByRef strError As String, ByVal strProc As String,
_
ByVal ParamArray vntPara() As VariantType)
As SqlDataReader
Dim sqlReader As SqlDataReader
Dim intCount As Integer
Try
Dim sqlCmd As New SqlCommand(strProc, msqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

If vntPara.GetUpperBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)
sqlCmd.Parameters.Add(vntPara.GetValue(intCount))
Next
End If

msqlConn.Open()
sqlReader = sqlCmd.ExecuteReader

If vntPara.GetUpperBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)
vntPara.SetValue(sqlCmd.Parameters.Item(intCount).Value,
intCount)
Next
End If

Catch ex As Exception
strError = ex.Message & "<BR>" & strProc & "<BR>"
If vntPara.GetLowerBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)
strError = strError & CStr(vntPara.GetValue(intCount)) &
", "
Next
strError = Mid(strError, 1, Len(Trim(strError)) - 1)
End If
End Try
Return sqlReader
msqlConn.Close()

End Function
 
Win said:
Dear All,

I am going to change the coding from ASP & VB6 to ASP.Net and VB.Net.
However, there is no data retrieved after I changed the coding.
Is there anything wrong?

Thanks

================================================
VB6 coding

Public Function myRS(ByRef vntErr As Variant, ByRef vntErrDesc As Variant,
_
ByVal strSPName As String, ParamArray vntPara()
As Variant) As ADODB.Recordset
On Error GoTo ErrorHandler

Dim intCount As Integer
Dim objCmd As Object
Dim objRS As Object

Set objCmd = CreateObject("ADODB.COMMAND")
Set objRS = CreateObject("ADODB.RECORDSET")

With objCmd
.CommandType = adCmdStoredProc
.CommandText = strSPName
.CommandTimeout = 0
.ActiveConnection = mobjConn
.Parameters.Refresh
If .Parameters.Count > 0 Then
For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
.Parameters(intCount + 1) = vntPara(intCount)
Next
End If
Set objRS = .Execute()

If .Parameters.Count > 0 Then
For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
vntPara(intCount) = .Parameters(intCount + 1)
Next
End If

End With

Set myRS = objRS

ProcExit:
Set objRS = Nothing
Set objCmd = Nothing
Exit Function

ErrorHandler:
vntErr = Err.Number
vntErrDesc = Err.Description
Resume ProcExit

End Function
================================================

.Net coding
================================================

Public Function myRS(ByRef strError As String, ByVal strProc As String,
_
ByVal ParamArray vntPara() As VariantType)
As SqlDataReader
Dim sqlReader As SqlDataReader
Dim intCount As Integer
Try
Dim sqlCmd As New SqlCommand(strProc, msqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

If vntPara.GetUpperBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)
sqlCmd.Parameters.Add(vntPara.GetValue(intCount))
Next
End If

msqlConn.Open()
sqlReader = sqlCmd.ExecuteReader

If vntPara.GetUpperBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)

vntPara.SetValue(sqlCmd.Parameters.Item(intCount).Value,
intCount)
Next
End If

Catch ex As Exception
strError = ex.Message & "<BR>" & strProc & "<BR>"
If vntPara.GetLowerBound(0) >= 0 Then
For intCount = vntPara.GetLowerBound(0) To
vntPara.GetUpperBound(0)
strError = strError & CStr(vntPara.GetValue(intCount))
&
", "
Next
strError = Mid(strError, 1, Len(Trim(strError)) - 1)
End If
End Try
Return sqlReader
msqlConn.Close()

End Function
Click to expand...

First off, in your .NET code there is no equivilent of

Parameters.Refresh

Which fills in the stored procedure parameters by querying the server. In
..NET the SqlCommandBuilder has equivilent functionality. In general you
should hard-code your stored procedure parameters into your application
code, but that's another issue.

David
 
Thanks a lot.

Could you please explain "In general you should hard-code your stored
procedure parameters into your application code, but that's another issue."
 
Win,

In my idea are you in your original code returning a recordset, but what is
it that you think that you are returning in the new code?

That can be used if you bind it to an ASPNET DataGrid by the way, but I am
in doubt that it is your intention.

Cor
 
Win said:
Thanks a lot.

Could you please explain "In general you should hard-code your stored
procedure parameters into your application code, but that's another
issue."
Click to expand...

Sure. A stored procedure is just a function. Discovering the procedure
parameters at runtime is basically "late binding". It's just bad form
because it's expensive and makes your code harder to read and debug.

David
 
Back
Top