473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

VB.Net SQL Select reading every other record.

OK I've been staring at this code all day and still with everything I
have tried I cannot figure out two problems I am having. Once is why
the space limit for the directory I create in the code fails. Second,
why the data reader is reading every other record. Here is all of the
source code for my little application followed by the contents of the
log file that it dumps out:
================================================== =========================
Imports System
Imports System.Xml
Imports System.IO
Imports System.data.oledb
Imports System.Data.sqlclient

Public Class Form1
Inherits System.Windows.Forms.Form
Private logFileName As String = "AccountGeneration"
Private logFileExt As String = ".log"
Private logFile As TextWriter
Private timeStamp As String = Replace(DateString, "-", "") &
Replace(TimeString, ":", "")

Private newAccounts As Long

Private ref As SAGReference = New SAGReference
Private tree As String = ""
Private organization As String = ""
Private volText As String = ""
Private directoryName As String = ""

Private volume As String = ""
Private volDirectory As String = ""
Private directorySpace As Long = 100000

Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnGenerate.Click
generateAccounts()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
ref = New SAGReference
Dim currentLDAPServer As String = ref.SAG_CURRENT_LDAP_SERVER
tree = ref.SAG_CAS_TREE
organization = ref.SAG_CAS_ORGANIZATION
volText = ref.SAG_CAS_VOL_TEXT
directoryName = ref.SAG_CAS_DIRECTORY_NAME
End Sub

Private Sub generateAccounts()
'Dim SQL As String = "Select count(*) AS user_count from
User_Data_View"
Dim rowCount As Long = 0
Dim nullRecordCount = 0
Dim isNewAccount As Boolean
Dim createCount As Long = 0

Me.Cursor.Current() = System.Windows.Forms.Cursors.WaitCursor
Me.newAccounts = 0

Try
Dim appPath As String =
System.IO.Path.GetDirectoryName(System.Reflection. Assembly.GetExecutingAssembly().GetName().CodeBase )
appPath = appPath.Substring(0,
appPath.LastIndexOfAny("\"))
Me.logFile = File.CreateText("..\" + logFileName &
timeStamp & logFileExt)

Dim logString As String

Dim dsn As String =
"server=sandere;database=Student_Mirror;User ID=sa;Password=ma6ic4"
Dim myConn As New SqlConnection
Dim SQL As String = "SELECT count(*) AS Expr1 FROM
Student_Registered"
myConn.ConnectionString = dsn 'connString
myConn.Open() ' open the connection

' Lets get the total count first.
Dim cCommand As New SqlCommand(SQL, myConn)
cCommand.CommandText = SQL
cCommand.Connection = myConn
Dim myCntReader As SqlDataReader
myCntReader = cCommand.ExecuteReader()
If myCntReader.HasRows Then
While myCntReader.Read
rowCount = myCntReader("Expr1")
End While
End If
myConn.Close()

' Now lets get everything we need for each student.
SQL = "SELECT TOP 10 * FROM Student_Registered ORDER BY
username"
Dim myCommand As New SqlCommand(SQL, myConn)
'Dim myCommand As New SqlCommand("StudentsSelect", myConn)
'myCommand.CommandType = CommandType.StoredProcedure
Dim myReader As SqlDataReader
myConn.Open()
myReader = myCommand.ExecuteReader()

pbAccounts.Minimum = 0
pbAccounts.Maximum = rowCount
pbAccounts.Visible = True
rowCount = 0
nAccounts.Text = 0
nHomeDirs.Text = 0
spaceSet.Text = 0
trusteeSet.Text = 0

While myReader.Read
rowCount = rowCount + 1
logString = "Processing user:"
logRecord(processStudentAccount(myReader, logString))
stbStudents.Text = "Record " & rowCount & " of " &
pbAccounts.Maximum & " records processed."
End While
logRecord("Record " & rowCount & " of " &
pbAccounts.Maximum & " records processed.")
logRecord("======================================= ==================================")
logRecord("New Accounts: " + nAccounts.Text + " Home Dir
Created: " + nHomeDirs.Text + " Trustees Set: " + trusteeSet.Text)
myReader.Close()
Catch fillexception As System.Exception
MessageBox.Show(fillexception.Message)
Finally
Me.logFile.Flush()
Me.logFile.Close()
Me.Cursor.Current() = System.Windows.Forms.Cursors.Default
End Try

End Sub

Private Function processStudentAccount(ByVal inReader As
SqlDataReader, ByVal logString As String) As String
Dim studentAccountName As String
Dim studentCollege As String
If inReader.Read() Then
studentAccountName = CStr(inReader("username")).Trim(" ")
studentCollege = Me.DeNull(inReader("college"))
logString += studentAccountName
End If
Dim college As String = getCollegeName(studentCollege)
Dim isNewAccount As Boolean
Dim userEntry As NWDirLib.NWEntry
Dim studentEntry As NWDirLib.NWEntry
Dim studentEntryPath As String = "NDS:\\" & tree & "\" &
organization & "\STUDENT\" & college & "\" & studentAccountName
Dim homeDirPath As String = "NDS:\\" & tree & "\" &
organization & "\NWCLUSTER_STUDENT"

Try
studentEntry = doFindEntry(studentEntryPath)
userEntry = studentEntry
If IsNothing(userEntry) Then
isNewAccount = True
nAccounts.Text += 1
Else
isNewAccount = False
logString += createHomeDirectory(userEntry,
directoryName, homeDirPath, Me.directorySpace, college)
End If
Catch exception As System.Exception
Dim eMsg = exception.Message & " == " & exception.HelpLink
logString += "Unhandled exception '" + eMsg + "' while
processing."
End Try

Return logString
End Function

Private Function createHomeDirectory(ByRef inUser As
NWDirLib.NWEntry, ByVal inDirectoryName As String, ByVal
inUserFullName As String, ByVal inDirAmount As Long, ByVal college As
String) As String
Dim MKDir As NWVolAdmLib.NWEntryType
Dim Path As New NWDirLib.NWPath
Dim NewDirectory As NWVolAdmLib.NWEntry
Dim NewTrustee As NWVolAdmLib.NWTrustee 'Making a trustee
object
Dim shortName As String = inUser.ShortName
Dim logRec As String

MKDir = NWVolAdmLib.NWEntryType.NETWAREDIR 'Specifies that
this object is a directory
NWVolAdm1.FullName = getVolume()

Dim d1 As String = "\" & inDirectoryName & "\" & shortName
Try
NewDirectory = NWVolAdm1.FindEntry(NWVolAdm1.FullName &
d1)
logRec += " N"
Catch ex As Exception
Try
'Creating the directory
NewDirectory = NWVolAdm1.Entry.Entries.Add(d1, MKDir)
nHomeDirs.Text += 1
logRec += " Y"
Catch exp As Exception
logRec += " - " + exp.Message + "::" +
exp.InnerException.Message.ToString()
End Try
End Try
Dim t1 = "NDS:\\" & tree & "\" & organization & "\Student\" &
college & "\" & shortName
Try
'getting reference to trustee objects of directory
NewTrustee = NewDirectory.Trustees.Add(t1)
'setting rights
NewTrustee.EffectiveRights.Create = True
NewTrustee.EffectiveRights.Erase = True
NewTrustee.EffectiveRights.Write = True
NewTrustee.EffectiveRights.Modify = True
NewTrustee.EffectiveRights.Read = True
NewTrustee.EffectiveRights.Scan = True
trusteeSet.Text += 1
logRec += " Y"
Catch ex As Exception
logRec += " N"
End Try

'NewDirectory.Size = inDirAmount
Dim limit As NWVolAdmLib.NWSpaceLimit
'Dim NWVolAdm2 As NWVolAdm
'limit = NWVolAdm1.SpaceLimits.Add(t1, inDirAmount)
'NewDirectory.DontCompress = True
'NewDirectory.RenameInhibit = True
'NWVolAdm1.SpaceLimits(getVolume())
Dim spcLimit As NWVolAdmLib.NWSpaceLimit
Dim HomeDirVolume = getVolume()
NWVolAdm1.FullName = HomeDirVolume
t1 = getVolume() + "\Home\" + shortName
Try
spcLimit = NWVolAdm1.SpaceLimits.Add(t1,
CLng(inDirAmount))
spaceSet.Text += 1
logRec += " Y"
Catch ex As Exception
logRec += " N"
End Try

NewTrustee = Nothing
NewDirectory = Nothing
Return logRec
End Function

'################################################# ############################################
'PRIVATE functions for validation
'
'
'################################################# ############################################

Private Sub logRecord(ByVal inMsg As String)
Me.logFile.WriteLine(inMsg)
End Sub

Private Function doFindEntry(ByVal Path As String) As
NWDirLib.NWEntry
Try
Return NWDir1.FindEntry(Path)
Catch ex As Exception
If ex.Message <> "Directory entry not found" Then
Throw ex
Else
'MessageBox.Show(ex.Message)
End If
End Try
End Function

Private Function DeNull(ByVal ThisValue As System.Object) As
System.Object
Return IIf(ThisValue Is DBNull.Value, 0, ThisValue)
End Function

Private Function getVolume() As String
volume = "NDS:\\" & tree & "\" & organization & "\" & volText
Return volume
End Function

Private Function getVolDirectory() As String
volDirectory = volume & "\" & directoryName
Return volDirectory
End Function

Private Function getCollegeName(ByRef collnum As Integer)
Dim sList As XmlNodeList
sList = readFile("Colleges.xml")
For Each myNode As XmlNode In sList
Dim sCode As String = myNode.ChildNodes(0).Value
If sCode = collnum Then
Dim aNode As XmlNode = myNode.PreviousSibling
Dim rName As String = aNode.ChildNodes(0).Value
Return rName
End If
Next
Return "Non Matrics"
End Function

Private Function readFile(ByRef fname As String)
Dim myDoc As New XmlDocument
Try
myDoc.Load("..\Colleges.xml")
Dim root As XmlNode = myDoc.DocumentElement()
Dim selectList As XmlNodeList =
root.SelectNodes("//colleges/college/code")
Return selectList
Catch ex As Exception
Me.logFile.WriteLine(ex.Message)
MessageBox.Show("Error reading Colleges: " + ex.Message)
End Try

End Function

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnClose.Click
End
End Sub
End Class
================================================== ========================
Processing user:aardenaw N N N
Processing user:abadd N N N
Processing user:abatems N N N
Processing user:abbaticm N N N
Processing user:abbinala N N N
Record 5 of 43379 records processed.
New Accounts: 0 Home Dir Created: 0 Trustees Set: 0
================================================== ========================

The first time it ran the other functions (creating the home
directory, setting the trustee settings) all worked fine.

The following is what I get when I paste the exact SQL statement in to
Query Analyser and Enterprise Manager (MS SQL Server 2000).
================================================== ========================
aardemas 18
aardenaw 23
aaronjm 32
abadd 28
abankwek 15
abatems 20
abbasns 15
abbaticm 42
abbeysc 22
abbinala 34
================================================== =========================

As you can see every other name is being processed in the VB code so
for some reason my while read is only reading every other record.

Thanks
Nov 21 '05 #1
4 8341
Since we all may not be as intimately familiar with your data it is nice
that you posted all of your code ... but it helps a lot more and answers may
come faster if you can try to duplicate the issue using as little code as
possible and by using the sample databases that most everyone is familiar
with - the ones that come with the MS Tools, such as NWind or Biblio.

Trying to get it down to the real issue using those databases sometimes also
is a grerat help to you, since in trying to get to to just a simple example
you might come across the answer that was elluding you.

Would it be possible for you to shave the example code down a bit?

If not, then maybe setting a breakpoint and carefully watching every single
line of code execute in it's turn might point a finger somewhere.

Also... you might want to change the password on that database.

Robert Smith
Kirkland, WA
www.smithvoice.com
Nov 21 '05 #2
Thanks for the advice and I will try out a few things. Also the
database password is not that, I changed it to some gobly gook before
posting but thanks for the heads up.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #3
It appears that you are using MyReader.Read twice, once in generateAccounts
and then again in processStudentAccount

processStudentAccount is advancing it once, then passing the control back to
the reader loop in generateAccounts, which advances it again, causing every
other row to be skipped.

Take out the "If inReader.Read() Then" and matching "End If" in
processStudentAccount.
"Jason Kumpf" <jk****@yahoo.com> wrote in message
news:e%****************@tk2msftngp13.phx.gbl...
Thanks for the advice and I will try out a few things. Also the
database password is not that, I changed it to some gobly gook before
posting but thanks for the heads up.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #4
That took care of it! Great! Now the only problem is with the code
that I'm using to creat the home directory space limit. Here is a
snip of just that code:

Dim MKDir As NWVolAdmLib.NWEntryType
Dim Path As New NWDirLib.NWPath
Dim NewDirectory As NWVolAdmLib.NWEntry
Dim NewTrustee As NWVolAdmLib.NWTrustee 'Making a trustee
object
Dim shortName As String = inUser.ShortName
Dim logRec As String

MKDir = NWVolAdmLib.NWEntryType.NETWAREDIR 'Specifies that
this object is a directory
NWVolAdm1.FullName = getVolume()

Dim d1 As String = "\" & inDirectoryName & "\" & shortName
Try
NewDirectory = NWVolAdm1.FindEntry(NWVolAdm1.FullName &
d1)
logRec += " N"
Catch ex As Exception
Try
'Creating the directory
NewDirectory = NWVolAdm1.Entry.Entries.Add(d1, MKDir)
nHomeDirs.Text += 1
logRec += " Y"
Catch exp As Exception
logRec += " - " + exp.Message + "::" +
exp.InnerException.Message.ToString()
End Try
End Try
Dim t1 = "NDS:\\" & tree & "\" & organization & "\Student\" &
college & "\" & shortName
Try
'getting reference to trustee objects of directory
NewTrustee = NewDirectory.Trustees.Add(t1)
'setting rights
NewTrustee.EffectiveRights.Create = True
NewTrustee.EffectiveRights.Erase = True
NewTrustee.EffectiveRights.Write = True
NewTrustee.EffectiveRights.Modify = True
NewTrustee.EffectiveRights.Read = True
NewTrustee.EffectiveRights.Scan = True
trusteeSet.Text += 1
logRec += " Y"
Catch ex As Exception
logRec += " N"
End Try

'##########################################
'HERE IS WHERE I'm having trouble.
Dim spcLimit As NWVolAdmLib.NWSpaceLimit
Dim HomeDirVolume = getVolume()
NWVolAdm1.FullName = HomeDirVolume
t1 = getVolume() + "\Home\" + shortName
Try
'WHEN we hti this line we throw the exception
spcLimit = NWVolAdm1.SpaceLimits.Add(t1,
CLng(inDirAmount))
spaceSet.Text += 1
logRec += " Y"
Catch ex As Exception
logRec += " N"
End Try
'############################################
NewTrustee = Nothing
NewDirectory = Nothing
Return logRec
"Jim Hughes" <NO*********@Hotmail.com> wrote in message news:<ug**************@TK2MSFTNGP15.phx.gbl>...
It appears that you are using MyReader.Read twice, once in generateAccounts
and then again in processStudentAccount

processStudentAccount is advancing it once, then passing the control back to
the reader loop in generateAccounts, which advances it again, causing every
other row to be skipped.

Take out the "If inReader.Read() Then" and matching "End If" in
processStudentAccount.
"Jason Kumpf" <jk****@yahoo.com> wrote in message
news:e%****************@tk2msftngp13.phx.gbl...
Thanks for the advice and I will try out a few things. Also the
database password is not that, I changed it to some gobly gook before
posting but thanks for the heads up.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
9
by: Bob Bedford | last post by:
I've a form that use a combobox along with other fields. When the user submit the form, many tests are done. If any test fails, then I show the form again with previously entered values. My...
4
by: Daniel Tan | last post by:
Hi, how can i select every record from a subform that matched to my searching criteria ? i tried to use SQL select and "= '" & forms!xxx!xxx.form!field & '"" but it will only select first record...
6
by: KevinD | last post by:
assumption: I am new to C and old to COBOL I have been reading a lot (self teaching) but something is not sinking in with respect to reading a simple file - one record at a time. Using C, I am...
7
by: Peter Proost | last post by:
Hi, I'm creating an import module to read data from old textfiles, run some calculations on them and save them to sql server. I've figured out how to do a select on textfile using a schema.ini file...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
48
by: Jimmy | last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far: <% Dim oConn, oRS, randNum Randomize() randNum = (CInt(1000 * Rnd) + 1) *...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
5
by: Matthew Wells | last post by:
I have a listbox set to simple multi select. For this example, users only select one item at a time. I have command buttons on the form for First, Previous, Next, Last, New (record). The form...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.