Problem inserting database value of numeric type into Excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • veer
    New Member
    • Jul 2007
    • 198

    Problem inserting database value of numeric type into Excel

    Hello expert.
    I made a program in which the data from an SQL table is transferred to an Excel sheet.
    When the data type of a field is Integer it works fine, but when the data type of a field is "numeric" it produces the error "APPLICATIO N ERROR OR OBJECT DEFINED ERROR" but I can't change the data type of the table from Numeric to Integer.
    Please provide some idea.

    Thanks in advance.
    Last edited by Killer42; Dec 17 '07, 07:06 AM.
  • QVeen72
    Recognized Expert Top Contributor
    • Oct 2006
    • 1445

    #2
    Hi.

    I'm not sure if the error has got to do anything with the data type of the Field.
    Did you check if the field is Null?
    Before populating an Excel cell check for Not Null.

    Regards,
    Veena.
    Last edited by Killer42; Dec 17 '07, 07:07 AM.

    Comment

    • veer
      New Member
      • Jul 2007
      • 198

      #3
      Hello expert.
      I am not properly understanding how I can check the Excel cell for not null.
      Please provide some method.
      Thanks.


      Originally posted by QVeen72
      I'm not sure if the error has got to do anything with the data type of the Field.
      Did you check if the Field is Null?
      Before populating the Excel cell check for Not Null.
      Last edited by Killer42; Dec 17 '07, 07:08 AM.

      Comment

      • Killer42
        Recognized Expert Expert
        • Oct 2006
        • 8429

        #4
        Originally posted by veer
        I am not properly understanding how I can check the Excel cell for not null.
        Veena meant to check that the value is not null, before you copy it to the cell in Excel.

        Can you tell us what value is in the field, that you are trying to copy to Excel? It might help if you showed us the code that does the copy, too.

        Comment

        • veer
          New Member
          • Jul 2007
          • 198

          #5
          Hello expert.
          I'm accessing two columns i,e entry and record. Both contain integer value
          e,g entry=777and record=78.
          The code for copying the records from table to Excel is:

          [CODE=vb]xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)

          Function Transpose(v As Variant) As Variant
          Dim Xupper, Yupper, x, y As Long
          Dim temparray As Variant
          Xupper = UBound(v, 2)
          Yupper = UBound(v, 1)
          ReDim temparray(Xuppe r, Yupper)

          For x = 0 To Xupper
          For y = 0 To Yupper
          temparray(x, y) = v(y, x)
          Next y
          Next x

          Transpose = temparray
          End Function[/CODE]

          Is it helpful or I send the whole program code?




          Originally posted by Killer42
          Veena meant to check that the value is not null, before you copy it to the cell in Excel.

          Can you tell us what value is in the field, that you are trying to copy to Excel? It might help if you showed us the code that does the copy, too.
          Last edited by Killer42; Dec 17 '07, 07:56 AM. Reason: Added CODE=vb tag

          Comment

          • QVeen72
            Recognized Expert Top Contributor
            • Oct 2006
            • 1445

            #6
            Hi Veer,

            So in which line you are getting the error (for Numeric as you said)..?
            What you are trying to do with the above code..?
            Say, if the value is > 64k, and you are trying to Loop through all the Rows, you may get error. For Excel, 64k is Max Limit for Rows/columns..(per Sheet)


            Regards
            Veena

            Comment

            • veer
              New Member
              • Jul 2007
              • 198

              #7
              Hi Veena,
              when i execute it works fine through the function and function return the value and insert into excel sheet
              xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)
              this is the line where i am getting error


              Originally posted by QVeen72
              Hi Veer,

              So in which line you are getting the error (for Numeric as you said)..?

              Regards
              Veena

              Comment

              • Killer42
                Recognized Expert Expert
                • Oct 2006
                • 8429

                #8
                So you're saying that if either field (record or entry) is of type Numeric, this code produces an error. And if they're both type Integer it doesn't. Correct?

                However, none of this code refers to either of these fields, which seems surprising. Perhaps you should show us the rest of the code.
                Last edited by Killer42; Dec 18 '07, 12:41 AM.

                Comment

                • QVeen72
                  Recognized Expert Top Contributor
                  • Oct 2006
                  • 1445

                  #9
                  Hi,

                  So when you get the error, can you tell what are the Values of all the Variables in this line :

                  xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)


                  Regards
                  Veena

                  Comment

                  • veer
                    New Member
                    • Jul 2007
                    • 198

                    #10
                    Hello expert.
                    Here is my code.
                    [CODE=vb]Dim con As ADODB.Connectio n
                    Dim rs As ADODB.Recordset
                    Dim squery As String
                    Dim xlApp As Object
                    Dim xlWb As Object
                    Dim xlWs As Object
                    Dim reccount As Variant
                    Dim recarray As Variant
                    Dim fldcount As Variant
                    Dim i As Long
                    i = 1
                    Dim j As Integer
                    Dim a, k, s As Variant
                    Set con = New ADODB.Connectio n
                    Set rs = New ADODB.Recordset

                    con.Open "Driver={SQ L Server};Server= " & listserver & "; Database=shrmgm tDb;Uid=sa;Pwd= " & txtpassword & ";"
                    squery = "select * from output"
                    rs.Open squery, con, adOpenDynamic, adLockBatchOpti mistic, adCmdText

                    Set xlApp = CreateObject("E xcel.Applicatio n")
                    Set xlWb = xlApp.Workbooks .Open("c:\var.x ls")
                    Set xlWs = xlWb.Worksheets ("sheet1")

                    xlApp.Visible = True
                    fldcount = rs.Fields.Count
                    recarray = rs.GetRows
                    reccount = UBound(recarray , 2) + 1
                    xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)
                    xlWb.SaveAs ("c:\var.xls ")
                    rs.Close
                    con.Close
                    Set rs = Nothing
                    Set con = Nothing

                    Set xlWs = Nothing
                    Set xlWb = Nothing
                    Set xlApp = Nothing
                    Exit Sub

                    Function Transpose(v As Variant) As Variant
                    Dim Xupper, Yupper, x, y As Long
                    Dim temparray As Variant
                    Xupper = UBound(v, 2)
                    Yupper = UBound(v, 1)
                    ReDim temparray(Xuppe r, Yupper)

                    For x = 0 To Xupper
                    For y = 0 To Yupper
                    temparray(x, y) = v(y, x)

                    Next y
                    Next x

                    Transpose = temparray
                    End Function[/CODE]



                    Originally posted by Killer42
                    So you're saying that if either field (record or entry) is of type Numeric, this code produces an error. And if they're both type Integer it doesn't. Correct?

                    However, none of this code refers to either of these fields, which seem surprising. Perhaps you should show us the rest of the code.
                    Last edited by Killer42; Dec 18 '07, 12:42 AM.

                    Comment

                    • veer
                      New Member
                      • Jul 2007
                      • 198

                      #11
                      Hi,
                      I am sending you the whole code

                      [CODE=vb]Dim con As ADODB.Connectio n
                      Dim rs As ADODB.Recordset
                      Dim squery As String
                      Dim xlApp As Object
                      Dim xlWb As Object
                      Dim xlWs As Object
                      Dim reccount As Variant
                      Dim recarray As Variant
                      Dim fldcount As Variant
                      Dim i As Long
                      i = 1
                      Dim j As Integer
                      Dim a, k, s As Variant
                      Set con = New ADODB.Connectio n
                      Set rs = New ADODB.Recordset

                      con.Open "Driver={SQ L Server};Server= " & listserver & "; Database=shrmgm tDb;Uid=sa;Pwd= " & txtpassword & ";"
                      squery = "select * from output"
                      rs.Open squery, con, adOpenDynamic, adLockBatchOpti mistic, adCmdText

                      Set xlApp = CreateObject("E xcel.Applicatio n")
                      Set xlWb = xlApp.Workbooks .Open("c:\var.x ls")
                      Set xlWs = xlWb.Worksheets ("sheet1")

                      xlApp.Visible = True
                      fldcount = rs.Fields.Count
                      recarray = rs.GetRows
                      reccount = UBound(recarray , 2) + 1
                      xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)
                      xlWb.SaveAs ("c:\var.xls ")
                      rs.Close
                      con.Close
                      Set rs = Nothing
                      Set con = Nothing

                      Set xlWs = Nothing
                      Set xlWb = Nothing
                      Set xlApp = Nothing
                      Exit Sub

                      Function Transpose(v As Variant) As Variant
                      Dim Xupper, Yupper, x, y As Long
                      Dim temparray As Variant
                      Xupper = UBound(v, 2)
                      Yupper = UBound(v, 1)
                      ReDim temparray(Xuppe r, Yupper)

                      For x = 0 To Xupper
                      For y = 0 To Yupper
                      temparray(x, y) = v(y, x)

                      Next y
                      Next x

                      Transpose = temparray
                      End Function[/CODE]



                      Originally posted by QVeen72
                      Hi,

                      So when you get the error, can you tell what are the Values of all the Variables in this line :

                      xlWs.Cells(i, 10).Resize(recc ount, fldcount) = Transpose(recar ray)


                      Regards
                      Veena
                      Last edited by Killer42; Dec 18 '07, 01:05 AM. Reason: Added CODE=vb tag

                      Comment

                      • QVeen72
                        Recognized Expert Top Contributor
                        • Oct 2006
                        • 1445

                        #12
                        Hello,

                        I did ask you for values of variables (i, reccount, fldcount, recarray)
                        at the time of error

                        Regards
                        Veena

                        Comment

                        • veer
                          New Member
                          • Jul 2007
                          • 198

                          #13
                          Hello
                          i=2
                          reccount=12
                          fldcount=2
                          recarray= both row and columns






                          Originally posted by QVeen72
                          Hello,

                          I did ask you for values of variables (i, reccount, fldcount, recarray)
                          at the time of error

                          Regards
                          Veena

                          Comment

                          • QVeen72
                            Recognized Expert Top Contributor
                            • Oct 2006
                            • 1445

                            #14
                            HI,

                            How many rows and columns you have for recarrays...?

                            Comment

                            • veer
                              New Member
                              • Jul 2007
                              • 198

                              #15
                              12 rows and 2 columns


                              Originally posted by QVeen72
                              HI,

                              How many rows and columns you have for recarrays...?

                              Comment

                              Working...