Click here to Skip to main content
13,093,184 members (80,673 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Need advice on the structure of students table..interest on enrolled column(datatype) for sql server 2005. I also need advice on the use of the datatype for which my function is declared. My enrolled column currently has bit datatype. Am getting the error above what could be the problem.

Public Function GetEnrolled() As Boolean
If Rs.State = adStateOpen Then Rs.Close
sql = " SELECT Students.Enrolled From Students WHERE Students.sID='" & vOldIDNumber & "' and students.yrlevel='" & voidYearlevel & "' and students.scourse='" & voidCourse & "' and semester='" & voidSem & "'"
   Rs.Open sql, RSconn
   If Not Rs.EOF Then
   GetEnrolled = Rs(0).Value
   End If
End Function
Posted 24-Apr-13 5:35am
CHill60 24-Apr-13 12:00pm
What is actually in Rs(0) ... I think it might be Null which can't be mapped to a boolean ... I usually use a char(1) not null column for bools (defaulting to N) but you can get around the problem here without changing the database by wrapping the assignment to GetEnrolled in an if-statement
Aleu Philip 24-Apr-13 12:08pm
R(0) is actually null going to try your suggestion
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Steps to do:
1) create and open connection
2) create and open Recordset object
3) while recordset not EOF, read values

Dim oConn As ADODB.Connection
Dim sConn As String 
Dim oRst As ADODB.Recordset
Dim sSQL As String
sConn = "Your connection"
Set oConn = New ADODB.Connection
oConn.Open sConn
Set oRst = New ADODB.Recordset
sSQL = "your query"
rstEmployees.Open sSQL, oConn, adOpenStatic, adLockOptimistic, adCmdTable
Do While Not oRst.EOF
    MsgBox oRst.Fields(0).Value

See more:
Open Method (ADO recordset)[^]
Open and Close Methods Example (VB)[^]
MoveFirst, MoveLast, MoveNext, and MovePrevious Methods Example (VB)[^]
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Use this
GetEnrolled = IIf(IsNull(Rs(0).Value), "", Rs(0).Value)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.170813.1 | Last Updated 10 May 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100