Problem in .Net programming

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Win
    #1

    Problem in .Net programming

    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("A DODB.COMMAND")
    Set objRS = CreateObject("A DODB.RECORDSET" )

    With objCmd
    .CommandType = adCmdStoredProc
    .CommandText = strSPName
    .CommandTimeout = 0
    .ActiveConnecti on = mobjConn
    .Parameters.Ref resh
    If .Parameters.Cou nt 0 Then
    For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
    .Parameters(int Count + 1) = vntPara(intCoun t)
    Next
    End If
    Set objRS = .Execute()

    If .Parameters.Cou nt 0 Then
    For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
    vntPara(intCoun t) = .Parameters(int Count + 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(strP roc, msqlConn)
    sqlCmd.CommandT ype = CommandType.Sto redProcedure

    If vntPara.GetUppe rBound(0) >= 0 Then
    For intCount = vntPara.GetLowe rBound(0) To
    vntPara.GetUppe rBound(0)
    sqlCmd.Paramete rs.Add(vntPara. GetValue(intCou nt))
    Next
    End If

    msqlConn.Open()
    sqlReader = sqlCmd.ExecuteR eader

    If vntPara.GetUppe rBound(0) >= 0 Then
    For intCount = vntPara.GetLowe rBound(0) To
    vntPara.GetUppe rBound(0)
    vntPara.SetValu e(sqlCmd.Parame ters.Item(intCo unt).Value,
    intCount)
    Next
    End If

    Catch ex As Exception
    strError = ex.Message & "<BR>" & strProc & "<BR>"
    If vntPara.GetLowe rBound(0) >= 0 Then
    For intCount = vntPara.GetLowe rBound(0) To
    vntPara.GetUppe rBound(0)
    strError = strError & CStr(vntPara.Ge tValue(intCount )) &
    ", "
    Next
    strError = Mid(strError, 1, Len(Trim(strErr or)) - 1)
    End If
    End Try
    Return sqlReader
    msqlConn.Close( )

    End Function


    Tags: None
    • David Browne
      #2
      Re: Problem in .Net programming


      "Win" <aaa@aaa.comwro te in message
      news:%23jZjPNky GHA.1936@TK2MSF TNGP04.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("A DODB.COMMAND")
      Set objRS = CreateObject("A DODB.RECORDSET" )
      >
      With objCmd
      .CommandType = adCmdStoredProc
      .CommandText = strSPName
      .CommandTimeout = 0
      .ActiveConnecti on = mobjConn
      .Parameters.Ref resh
      If .Parameters.Cou nt 0 Then
      For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
      .Parameters(int Count + 1) = vntPara(intCoun t)
      Next
      End If
      Set objRS = .Execute()
      >
      If .Parameters.Cou nt 0 Then
      For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
      vntPara(intCoun t) = .Parameters(int Count + 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(strP roc, msqlConn)
      sqlCmd.CommandT ype = CommandType.Sto redProcedure
      >
      If vntPara.GetUppe rBound(0) >= 0 Then
      For intCount = vntPara.GetLowe rBound(0) To
      vntPara.GetUppe rBound(0)
      sqlCmd.Paramete rs.Add(vntPara. GetValue(intCou nt))
      Next
      End If
      >
      msqlConn.Open()
      sqlReader = sqlCmd.ExecuteR eader
      >
      If vntPara.GetUppe rBound(0) >= 0 Then
      For intCount = vntPara.GetLowe rBound(0) To
      vntPara.GetUppe rBound(0)
      >
      vntPara.SetValu e(sqlCmd.Parame ters.Item(intCo unt).Value,
      intCount)
      Next
      End If
      >
      Catch ex As Exception
      strError = ex.Message & "<BR>" & strProc & "<BR>"
      If vntPara.GetLowe rBound(0) >= 0 Then
      For intCount = vntPara.GetLowe rBound(0) To
      vntPara.GetUppe rBound(0)
      strError = strError & CStr(vntPara.Ge tValue(intCount ))
      &
      ", "
      Next
      strError = Mid(strError, 1, Len(Trim(strErr or)) - 1)
      End If
      End Try
      Return sqlReader
      msqlConn.Close( )
      >
      End Function
      >
      >
      First off, in your .NET code there is no equivilent of

      Parameters.Refr esh

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

      David


        Comment

        • Win
          #3
          Re: Problem in .Net programming

          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" <davidbaxterbro wne no potted meat@hotmail.co mwrote in
          message news:#ONa2tkyGH A.476@TK2MSFTNG P06.phx.gbl...
          >
          "Win" <aaa@aaa.comwro te in message
          news:%23jZjPNky GHA.1936@TK2MSF TNGP04.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("A DODB.COMMAND")
          Set objRS = CreateObject("A DODB.RECORDSET" )

          With objCmd
          .CommandType = adCmdStoredProc
          .CommandText = strSPName
          .CommandTimeout = 0
          .ActiveConnecti on = mobjConn
          .Parameters.Ref resh
          If .Parameters.Cou nt 0 Then
          For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
          .Parameters(int Count + 1) = vntPara(intCoun t)
          Next
          End If
          Set objRS = .Execute()

          If .Parameters.Cou nt 0 Then
          For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
          vntPara(intCoun t) = .Parameters(int Count + 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(strP roc, msqlConn)
          sqlCmd.CommandT ype = CommandType.Sto redProcedure

          If vntPara.GetUppe rBound(0) >= 0 Then
          For intCount = vntPara.GetLowe rBound(0) To
          vntPara.GetUppe rBound(0)
          sqlCmd.Paramete rs.Add(vntPara. GetValue(intCou nt))
          Next
          End If

          msqlConn.Open()
          sqlReader = sqlCmd.ExecuteR eader

          If vntPara.GetUppe rBound(0) >= 0 Then
          For intCount = vntPara.GetLowe rBound(0) To
          vntPara.GetUppe rBound(0)

          vntPara.SetValu e(sqlCmd.Parame ters.Item(intCo unt).Value,
          intCount)
          Next
          End If

          Catch ex As Exception
          strError = ex.Message & "<BR>" & strProc & "<BR>"
          If vntPara.GetLowe rBound(0) >= 0 Then
          For intCount = vntPara.GetLowe rBound(0) To
          vntPara.GetUppe rBound(0)
          strError = strError &
          CStr(vntPara.Ge tValue(intCount ))
          &
          ", "
          Next
          strError = Mid(strError, 1, Len(Trim(strErr or)) - 1)
          End If
          End Try
          Return sqlReader
          msqlConn.Close( )

          End Function
          >
          First off, in your .NET code there is no equivilent of
          >
          Parameters.Refr esh
          >
          Which fills in the stored procedure parameters by querying the server. In
          .NET the SqlCommandBuild er has equivilent functionality. In general you
          should hard-code your stored procedure parameters into your application
          code, but that's another issue.
          >
          David
          >
          >

            Comment

            • Cor Ligthert [MVP]
              #4
              Re: Problem in .Net programming

              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" <aaa@aaa.comsch reef in bericht
              news:%23jZjPNky GHA.1936@TK2MSF TNGP04.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("A DODB.COMMAND")
              Set objRS = CreateObject("A DODB.RECORDSET" )
              >
              With objCmd
              .CommandType = adCmdStoredProc
              .CommandText = strSPName
              .CommandTimeout = 0
              .ActiveConnecti on = mobjConn
              .Parameters.Ref resh
              If .Parameters.Cou nt 0 Then
              For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
              .Parameters(int Count + 1) = vntPara(intCoun t)
              Next
              End If
              Set objRS = .Execute()
              >
              If .Parameters.Cou nt 0 Then
              For intCount = LBound(vntPara, 1) To UBound(vntPara, 1)
              vntPara(intCoun t) = .Parameters(int Count + 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(strP roc, msqlConn)
              sqlCmd.CommandT ype = CommandType.Sto redProcedure
              >
              If vntPara.GetUppe rBound(0) >= 0 Then
              For intCount = vntPara.GetLowe rBound(0) To
              vntPara.GetUppe rBound(0)
              sqlCmd.Paramete rs.Add(vntPara. GetValue(intCou nt))
              Next
              End If
              >
              msqlConn.Open()
              sqlReader = sqlCmd.ExecuteR eader
              >
              If vntPara.GetUppe rBound(0) >= 0 Then
              For intCount = vntPara.GetLowe rBound(0) To
              vntPara.GetUppe rBound(0)
              >
              vntPara.SetValu e(sqlCmd.Parame ters.Item(intCo unt).Value,
              intCount)
              Next
              End If
              >
              Catch ex As Exception
              strError = ex.Message & "<BR>" & strProc & "<BR>"
              If vntPara.GetLowe rBound(0) >= 0 Then
              For intCount = vntPara.GetLowe rBound(0) To
              vntPara.GetUppe rBound(0)
              strError = strError & CStr(vntPara.Ge tValue(intCount ))
              &
              ", "
              Next
              strError = Mid(strError, 1, Len(Trim(strErr or)) - 1)
              End If
              End Try
              Return sqlReader
              msqlConn.Close( )
              >
              End Function
              >
              >

                Comment

                • David Browne
                  #5
                  Re: Problem in .Net programming


                  "Win" <aaa@aaa.comwro te in message
                  news:ergizhlyGH A.2300@TK2MSFTN GP05.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


                    Comment

                    Previous template Next
                    Working...