Click here to Skip to main content
15,305,430 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
I have the grid I want to feel that grid from the sql server 2000. I want to add a 200 columns with a 8000 characters in a adodb recordset. I have done this by using the recordset but there is error of recordset to fetch data for every column with 8000 characters. I what a solution how to fetch a data from sql server 2000 with a column of varchar (8000) to the recordset


Code for this is

VB
Set recList = New ADODB.Recordset
    
    ' execute any SQL build statements
    If strSql3 & "" <> "" Then
        Dim lngResults As Long
        Call CmdExecuteSqlAsync(conn, Replace(strSql3, "___temp_table___", strTempTable), cmdCancel, _
            lblStatus, lngResults)
    End If
    
    recList.CursorLocation = adUseClient
    recList.CursorType = adOpenStatic
    recList.LockType = adLockBatchOptimistic

    recList.Properties("Initial Fetch Size") = 1
    recList.Properties("Background Fetch Size") = 1
    blnFetching = True
    strSQL = Replace(strSQL, "___temp_table___", strTempTable)
    recList.Open strSQL, conn, adOpenStatic, adLockBatchOptimistic, adAsyncFetch + adAsyncExecute
    lblStatus = Format(Now - dtmStart, "hh:mm:ss") & " elapsed, 0 record(s)"
    Do While blnFetching = True And cmdSearch.Enabled = False And recList.State > 1
        lblStatus = Format(Now - dtmStart, "hh:mm:ss") & Mid(lblStatus, InStr(lblStatus, " elapsed, "))
        Sleep 200
        SafeDoEvents
    Loop





//reclist is the object of adodb recordset
Posted
Updated 12-Sep-11 0:58am
v4
Comments
Herman<T>.Instance 12-Sep-11 6:50am
   
show your code!

SQL Server 2000 has a row size limit of 8060 bytes. So you cannot insert 200x8000 characters.
   
Comments
Nishant Jadhav 12-Sep-11 7:00am
   
It also happen same to sql server 2008
if i use the varchar(8000) as a datatype for the table column,
If I use nvarchar(max) as datatype then it working fine in sql server 2005 and 2008 but i wnat solution for sql server 2000
Simon_Whale 12-Sep-11 16:22pm
   
Max is a key word that is introduced with sql2005 for sql2000 you have to set the size to 8000
have a read of this article

SQL 2000 row sizes[^]

Even though you can use columns size upto 8000 characters in SQL 2000 you cant exceed 8k per row in size.
   
Comments
Nishant Jadhav 12-Sep-11 23:02pm
   
I have fetch the data from one column from table with the id is primary key. Every id has a 200 rows and each row is separate column in the grid. then what i do at this time. and the column of table has datatype varchar(8000)
Nishant Jadhav 12-Sep-11 23:02pm
   
I want the solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900