Need HELP with RecordSet!!

j_mact
01-13-2004, 07:55 AM
All,
Please forgive me if this posted elsewhere, but I have been looking around the site, and have not found an answer. Here's the situation: We are using SQL Server 2000, and are using VB to query the data and return a recordset. We then take this data and store it in a text file. For some reason, my app freezes halfway through this process. We had this code in a Stored Procedure, but was experiencing the same problem. It is only supposed to be returning 13-15 rows MAX but always stops around 6 or 7, so I am perplexed as to what the problem could be. I am posting the code below. If anyone can see what we are doing wrong PLEASE HELP!

Dim bDate As String
Dim edate As String
Dim strSql As String
Dim MyDb As Connection
Set MyDb = New Connection
Dim rcdSet As ADODB.Recordset
Dim nCnt As Integer
Dim outStr As String

MyDb.CommandTimeout = 360
MyDb.CursorLocation = adUseClient
MyDb.Open "PROVIDER=MSDASQL;dsn=sqltest;uid=user;pwd=passwd;database=sisbug;"

bDate = BeginDate.Text
edate = EndDate.Text

strSql = "select studentid,lastname,firstname,gender,homephone,ssn,birthdate," & _
"grade,ethnicity,entrywithcode,enrollchgdate,homeroom from students " & _
"where schoolid = " + "'" + curruser + "'" + " and enrolled = 1 and enrollchgdate" & _
" >= " + "'" + bDate + "'" + " and enrollchgdate <= " + "'" + edate + "'"

Set rcdSet = New Recordset

rcdSet.Open strSql, MyDb, adOpenForwardOnly, adLockReadOnly, adCmdText
'MyDb.Close

rcdSet.MoveFirst

While Not rcdSet.EOF

strStudent = rcdSet("studentid")
strLName = rcdSet("lastname")
strFName = rcdSet("firstname")
strGend = rcdSet("gender")
strHomePh = rcdSet("homephone")
strSsn = rcdSet("ssn")
strBDate = rcdSet("birthdate")
strGrade = rcdSet("grade")
strEthn = rcdSet("ethnicity")
strEntrWCode = rcdSet("entrywithcode")
strEnrChgDte = rcdSet("enrollchgdate")
strHomeRm = rcdSet("homeroom")

If Len(strStudent) < 9 Then
While Len(strStudent) < 9
strStudent = "0" + strStudent
Wend
End If

'Gender Field
If strGend = "M" Then
strGend = "0"
Else
strGend = "1"
End If

'must manipulate date field so Sims will read it correctly...
strYear = Year(strBDate)
strMonth = Month(strBDate)
strDay = Day(strBDate)
'must test to see if month and day need a "0" in front
If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If

If Len(strDay) = 1 Then
strDay = "0" & strDay
End If

'creating string for date....
strBDate = strYear & strMonth & strDay


'must do same for enroll date....

strYear = Year(strEnrChgDte)
strMonth = Month(strEnrChgDte)
strDay = Day(strEnrChgDte)
'must test to see if month and day need a "0" in front
If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If

If Len(strDay) = 1 Then
strDay = "0" & strDay
End If

'creating string for date....
strEnrChgDte = strYear & strMonth & strDay


'need to pad out first and last name fieds
strLName = RTrim(strLName)
strFName = RTrim(strFName)

'reset counter
nCnt = 0
nCnt = Len(strLName)
If nCnt < 18 Then
strLName = strLName + Space(18 - nCnt)
End If

nCnt = 0
nCnt = Len(strFName)
If nCnt < 14 Then
strFName = strFName & Space(14 - nCnt)
End If

'will go away, but for testing purposes, must feed something into entrywithcode...
If IsNull(strEntrWCode) Then
strEntrWCode = "E9"
End If

'must test to see if homeroom is available. If not, we will hard code to "TRAN"

If IsNull(strHomeRm) Then
strHomeRm = "TRAN"
End If

nCnt = Len(strHomeRm)
If nCnt < 4 Then
strHomeRm = strHomeRm + Space(14 - nCnt)
End If


outStr = "V91" & strStudent & strLName & strFName & strGend & LTrim(RTrim(strHomePh)) & _
strSsn & " " & strBDate & strGrade + strHomeRm & " " & "1" & _
strEnrChgDte & strEntrWCode

strExec = "cmd.exe /c echo " + outStr + ">> c:\jwshare\sims." + curruser + ""
dShell = Shell(strExec, vbHide)

rcdSet.MoveNext
Wend
MyDb.Close
rcdSet.Close

MsgBox "Import File Created"

j_mact
01-13-2004, 08:18 AM
Found the problem. Was not the recordset. A small while loop was not working! :chuckle:

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum