Problem in .Net programming

28 Aug 2006 1:48 AM

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 & " " & strProc & " "

            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

28 Aug 2006 2:41 AM

David Browne

Show quote Hide quote

"Win" <a**@aaa.com> wrote in message
news:%23jZjPNkyGHA.1936@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 & " " & strProc & " "
> 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

28 Aug 2006 4:20 AM

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

Show quoteHide quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:#ONa2tkyGHA.476@TK2MSFTNGP06.phx.gbl...
>
> "Win" <a**@aaa.com> wrote in message
> news:%23jZjPNkyGHA.1936@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 & " " & strProc & " "
> > If vntPara.GetLowerBound(0) >= 0 Then
> > For intCount = vntPara.GetLowerBound(0) To
> > vntPara.GetUpperBound(0)
> > strError = strError &

CStr(vntPara.GetValue(intCount))
Show quoteHide quote

> > &
> > ", "
> >                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
>
>

28 Aug 2006 2:22 PM

David Browne

"Win" <a**@aaa.com> wrote in message
news:ergizhlyGHA.2300@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

28 Aug 2006 4:59 AM

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

Show quoteHide quote

"Win" <a**@aaa.com> schreef in bericht
news:%23jZjPNkyGHA.1936@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 & " " & strProc & " "
> 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
>
>