Problem in .Net programming

[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

[David Browne]

"Win" <aa*@aaa.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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

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

[Win]

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."

"David Browne" <davidbaxterbrowne no potted me**@hotmail.comwrote in
message news:#O*************@TK2MSFTNGP06.phx.gbl...

>
"Win" <aa*@aaa.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

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

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

[Cor Ligthert [MVP]]

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" <aa*@aaa.comschreef in bericht
news:%2****************@TK2MSFTNGP04.phx.gbl...

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

[David Browne]

"Win" <aa*@aaa.comwrote in message
news:er**************@TK2MSFTNGP05.phx.gbl...

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."

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