Ask a Question

Data with Longvarchar datatype is not retreived to recordset

Archana_72
Contributor

Data with Longvarchar datatype is not retreived to recordset

Hi,

 

I am using ADO connection to connect to database (ASA or SQL Server).

Everything was working until recently column with  datatype LongVarchar was added.

 

Now data initially retrieved to record set but immediately is it cleared from recordset.

Can anyone let me know whats wrong in below script?

 

 

Set Conn = ADO.CreateADOCommand
  Conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=<Datasource name>"
  Conn.CommandText = sQry
  Set RS = Conn.Execute
  Dim objExcel
  Dim objWorkbook1
 Set objExcel = CreateObject("Excel.Application")
 Set objWorkbook1= objExcel.Workbooks.Open(<Excel Path>)
  objExcel.visible =true
  Set objWorksheet1= objWorkbook1.Worksheets(<Sheet Name>)
  fldCount = rs.Fields.Count
    For iCol = 1 To fldCount
        objWorksheet1.Cells(iRownumber, iCol).Value = rs.Fields(iCol - 1).Name
    Next
  objWorksheet1.Cells(iRownumber+1, 1).CopyFromRecordset RS  
 
  RS.Close
  Set RS = Nothing
  Set Conn = Nothing
  Call objWorkbook1.Save
  Call objWorkbook1.Close
  Set objExcel=nothing

 

3 REPLIES 3
Marsha_R
Community Hero

As you can see, Excel doesn't always play well with others.  

 

Are you copying from the database to Excel in order to use the data for testing?  I would use a .csv file instead of Excel.  There's no hidden functionality to worry about then.  Your data is just there.


Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame

Hi,

 

I have even tried out in csv and had same problem.

 

recordset is getting cleared once we start accessing/ looping through data.

Where exactly in the code do you see that that recordset is gone?  Does it happen after once through the loop or some other time? 

 

Are you sure that the recordset is even there in the first place?  Have you tried outputting the values to make sure?


Marsha_R
[Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
cancel
Showing results for 
Search instead for 
Did you mean: