General Question - Newbie [RESOLVED]

[Bahatmut]

Forgive the post if this is the wrong forum. I am new to .NET studio. I was self learning VB6, but had a major comp crash, and now only have .NET studio. My question is this. I am trying to complete a very very simple Access Database project. I was able in VB6 to use the .seek and .find to read in and compare a single field from each record in a single table and test it for what I needed. It appears that in .NET Microsoft has gone out of their way to ensure this is no longer possible. Does anyone know how I might be able to accomplish this. The VB6-VB.NET upgrader DOES NOT work properly and fails to return anythign but gibberish when I try and upgrade the old code, even though all I had was a ery outdated backup copy. I do not need data grids, or massive table displays, I am merely tryign to test if a value is present in any of the table's records.

Resolved - Thank you for the help. I had to keep witht he OleDbDataReader setup, as since I'm still learning, changing designs only confuses me more simply. I thank you for your input and prodding in a direction I needed.

 

[jmcilhinney]

Sounds like you need the services of an OleDbDataReader. It provides forward only, read only access to the result set of a select query.

 

[Bahatmut]

Update on Situation

Ok, thanks for the nudge, ti helped otu tons...now I've hit another wall taht 12 hours of trygin to pick at it hasn't fixed. Here is the situation, we are trying to find OPEN (unused) numbers in a list of member numbers (IE there are number 1,2,3,6,7 so 4 and 5 are open) The Access DB stores these as STRING data. Now I figured otu string comparing and such, but the thing is this, I need to be able to test the test value against EVERY entry in the dataset, since the original program that created the table is a tad off....below is the code of what I have so far.


Dim RecordPos AsInteger = 0

Dim Counter AsInteger = 1

Dim TestValue AsString

Dim FoundNumber AsBoolean = False

Dim TV2 AsString

Dim mySelectQuery AsString = "SELECT Member_Number FROM Customers"

Dim myConnString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\<path\filename\password removed for security>.mdb;User Id=admin;Password=;"

Dim myConnection AsNew OleDbConnection(myConnString)

Dim myCommand AsNew OleDbCommand(mySelectQuery, myConnection)

myConnection.Open()

Dim myReader As OleDbDataReader

For Counter = 1 To 99999

'Counter is the number we are testing

TestValue = CStr(Counter)

Label4.Text = TestValue

myReader = myCommand.ExecuteReader()

' Always call Read before accessing data.

While myReader.Read()

TV2 = myReader.GetString(0)

If StrComp(TestValue, TV2, CompareMethod.Text) Then

FoundNumber = False

Else

FoundNumber = True

EndIf

EndWhile

' always call Close when done reading.

myReader.Close()

If FoundNumber = TrueThen

TextBox1.Text = TestValue

Counter = 99999

EndIf

Next

' Close the connection when done with it.

myConnection.Close()

EndSub



Hopefully this is a solvable problem. Basically I need to find a way to trap and return only a number that DOESN'T exist in the table already.

 

[jmcilhinney]

If your values were stored as numbers I would use an OleDbDataReader because you could order them and then, as you read them one by one you could be sure that any number that was skipped was definitely available. As they are strings, I'd be inclined to use an OleDbDataAdapter to retrieve your data into a table first. I think that would make matters easier. Here's my solution:

		[color=Blue]Dim[/color] myAdapter [color=Blue]As New[/color] OleDbDataAdapter("SELECT Member_Number FROM Customers", myConnection)

		[color=Green]'If Member_Number is the primary key for the table, this line will allow you to use Find() later.
		'Although it won't hurt, this line is not required if Member_Number is not the primary key.[/color]
		myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

		[color=Blue]Dim[/color] myTable [color=Blue]As New[/color] DataTable

		[color=Green]'Retrieve the data from the database.[/color]
		myAdapter.Fill(myTable)

		[color=Green]'If Member_Number is not the primary key for the table, you need to include this line to be able to use Find().
		'Although it won't hurt, this line is not required if Member_Number is the primary key.[/color]
		myTable.PrimaryKey = [color=Blue]New[/color] DataColumn() {myTable.Columns("Member_Number")}

		[color=Green]'Set this to be either the maximum number in use if you know it
		'or a number that will definitely be greater than the maximum number if you don't.[/color]
		[color=Blue]Dim[/color] maxMemberNumber [color=Blue]As Integer[/color]

		[color=Green]'The avialable member numbers will be stored here.[/color]
		[color=Blue]Dim[/color] availableMemberNumbers [color=Blue]As New[/color] ArrayList

		[color=Blue]For[/color] i [color=Blue]As Integer[/color] = 1 [color=Blue]To[/color] maxMemberNumber
		    [color=Green]'Check whether a row exists with the current number as primary key.[/color]
			[color=Blue]If[/color] myTable.Rows.Find(i.ToString()) [color=Blue]Is Nothing Then[/color]
			    [color=Green]'The current number is not in use so add it to the list.[/color]
				availableMemberNumbers.Add(i)
			[color=Blue]End If[/color]
		[color=Blue]Next[/color]

This assumes that all member numbers are unique, which I suspect they are. If they are not, the line that sets the primary key of the table would fail. We would require a slight variation in that case. Let me know if that is the case.

 

[DavidT_macktool]

Bout the same as JM's

If the value is the primary key of the record and an integer

Read in the dataset

Dim count As Integer = Me.BindingContext(DsTemp1, "Temp").Count
Dim tblTemp As DataTable = DsTemp1.Tables("Temp")

For i = 0 To count
If tblTemp.Rows.Contains(i) Then
' do something
Else
' do something
End If
Next

Don't know if it works with strings, mabey someone could help convert this to strings

 

[TechGnome]

jmcilhinney -- have on prob w/ your solution: For i As Integer = 1 To maxMemberNumber -- I see where maxMemberNumber was declared, but not set.... And I'm not sure what it should be set to.

Here's my solution....
[vbcode]

Dim Counter AsInteger = 1
Dim FoundNumber AsBoolean = False
Dim TV2 AsString
Dim mySelectQuery AsString = "SELECT Member_Number FROM Customers"
Dim myConnString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\<path\filename\password removed for security>.mdb;User Id=admin;Password=;"
Dim myConnection AsNew OleDbConnection(myConnString)
Dim myCommand AsNew OleDbCommand(mySelectQuery, myConnection)

myConnection.Open()

Dim myReader As OleDbDataReader
'As long as we have record AND we haven't found our hole yet.... loop
While myReader.Read() And (Not FoundNumber)
TV2 = myReader.GetString(0) 'Get the Field Value
If TV2 <> Counter.ToString Then 'Compare the Counter string to the Field Value
'If they don't match, we found our hole
FoundNumber = True
Else
'They do match, so we need to increment the counter
Counter +=1
End IF

End While

' Close the connection when done with it.
myConnection.Close()

TextBox1.Text = Counter.ToString

End Sub
[/vbcode]

Smaller, tighter, and probably more effective.

Tg

 

[jmcilhinney]

Edit: Directed at TechGnome rather than Bahatmut.

There is a comment above the declaration of maxMemberNumber in my code that tells you how to set it. My code will find every number that is available. If you just want to find the first number that is available then you can modify it. The flaw with your method comes from the order the numbers will be returned in, as I mention in my post. Because they are strings, they will not be returned in numerical order (1,2,3,4,...) but in alphabetical order (1,10,100,1000...). That means that your method will return 2 as the first available number because the second row returned will contain the string "10" and not the string "2". That is why I proposed getting all rows first.

Edit:
By the way, not including the comments my code has twelve lines, one of which can be removed as per the comments, and yours has twenty. Even accounting for creating the connection and populating the text box, I'd still say that makes my code smaller than yours. The one thing I would change is to use DavidT_macktool's method of checking whether a key is present, i.e. change

[color=Blue]If[/color] myTable.Rows.Find(i.ToString()) [color=Blue]Is Nothing Then[/color]

to

[color=Blue]If Not[/color] myTable.Rows.Contains(i.ToString()) [color=Blue]Then[/color]

 

[Bahatmut]

Reply to input and final solution.

Thanks for all your input, below is the actuall code that does seem to work. The problem with using hte maxmembercount is that although there are only 7492 records in the members table, the actuall value of thier ID numbers ranges from 1 to 59000 at the time I got a copy of the table for development purposes. It turns out I had to use a dual logic check to ensure that I got back only numbers that truly are not in the table already. I know it might be ugly, but step 1 is to get it working, step 2 is to add the harder part (finding multiple open numbers) and then step 3 is optimazation and such.

Code for the loop, better internal remarks forthcoming (and it works too):

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim RecordPos As Integer = 0
Dim Counter As Integer = 1
Dim TestValue As String
Dim FoundNumber As Boolean = False
Dim TV2 As String
Dim mySelectQuery As String = "SELECT Member_Number FROM Customers"
Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\<name/path removed for security>.mdb;User Id=admin;Password=;"
Dim myConnection As New OleDbConnection(myConnString)
Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDbDataReader
Dim result As Boolean
For Counter = 1 To 99999
'Counter is the number we are testing
TestValue = CStr(Counter)
Label4.Text = TestValue
myReader = myCommand.ExecuteReader()
' Always call Read before accessing data.
While myReader.Read()
TV2 = myReader.GetString(0)
result = StrComp(TestValue, TV2, CompareMethod.Text)
If result = False Then
FoundNumber = True
Exit While
End If
End While
' always call Close when done reading.
myReader.Close()
If FoundNumber = True And result = True Then
TextBox1.Text = TestValue
Counter = 99999
End If
Next
' Close the connection when done with it.
myConnection.Close()
End Sub


Like I said, it's probaly messy, mostly used to MUF, not VB, but ti does do the job it has to do.

 

[jmcilhinney]

Bahamut, you are new and probably aren't aware of their existence, but please use

 tags when posting code. It makes it much more readable. Also indenting helps a lot. You can cut and paste your code straight from the IDE.  I'm glad you found a solution.

 

[jmcilhinney]

I've had another look at your code, Bahamut, and I now realise why it works but it is very inefficient. Let's say your table has 1000 rows and every number up to 999 is taken, then 1000 is available, then the last row uses 1001. Your code will open a reader, check the first row and find "1", then close the reader. Then it will open another reader, check rows until it finds "2" which means it checks rows containing "1", "10", "100", "101", "102", etc. until it comes across "2", then it will close the reader. Next it will open another reader, check rows until it finds "3",... and so on. This is incredibly slow and extremely inefficient. To find the next number in this example you would have to open and close 999 DataReaders and read the same rows over and over many, many times. It seems that you want to find only the next available number rather than all available numbers. Let me rewrite my code for that purpose. I'm going to assume that Member_Number is the primary key for your table.

	[color=Blue]Private Sub[/color] Button1_Click([color=Blue]ByVal[/color] sender [color=Blue]As[/color] System.Object, [color=Blue]ByVal[/color] e [color=Blue]As[/color] System.EventArgs) [color=Blue]Handles[/color] Button1.Click
		[color=Blue]Dim[/color] myConnection [color=Blue]As New[/color] OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\<name/path removed for security>.mdb;User Id=admin;Password=;")
		[color=Blue]Dim[/color] myAdapter [color=Blue]As New[/color] OleDbDataAdapter("SELECT Member_Number FROM Customers", myConnection)

		[color=Green]'Retrieve primary key info along with data.
 		'This way primary key can be used to find specific rows.[/color]
		myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

		[color=Blue]Dim[/color] myTable [color=Blue]As New[/color] DataTable

		[color=Green]'Retrieve the data from the database.[/color]
		myAdapter.Fill(myTable)

		[color=Green]'Start searching from 1.[/color]
		[color=Blue]Dim[/color] nextMemberNumber [color=Blue]As Integer[/color] = 1

		[color=Green]'Keep incrementing the member number until one is not found in the table.[/color]
		[color=Blue]While[/color] myTable.Rows.Contains(nextMemberNumber.ToString())
			nextMemberNumber += 1
		[color=Blue]End While[/color]

		[color=Blue]Me[/color].TextBox1.Text = nextMemberNumber
	[color=Blue]End Sub[/color]

I guarantee you that this is a better solution. Also, you may or may not prefer to move the bulk of this code into a function, perhaps called GetNextMemberNumber, and use it like this:

[color=Blue]Me[/color].TextBox1.Text = [color=Blue]Me[/color].GetNextMemberNumber()

It can then be used in other places as well, if required.

 

[jmcilhinney]

I thought I would add something else that I had considered before but neglected to write. If you use my original method, or something similar, to get all the available numbers then you only have to do this once each time the program is run. Then, each time one of the available numbers is used, you remove it from the list. That way, you don't have to go back to the database every time and do exactly the same thing every time. In programming terms, database access is considered to be expensive and should be kept to a minimum.

 

[jmcilhinney]

Don't mind me. I've just decided to add something else. Bahatmut, I've reread your post #8 more carefully and I notice that you say you have to look for duplicate numbers. Does this mean that Member_Number is not the primary key for the table? If that is the case, then the last block of code I posted will not work. It would only take a slight variation to get it to work without a primary key, though. The whole process would be slower, as searching without a unique key is slower, but I can guarantee that it will still be much faster than the method you said you are using. It would only take another slight variation to use the same method to find duplicates.

 

[Bahatmut]

Thansk for the input

Yes, most of the clunkieness does come from the fact that the tables I am trying to access have no direct primary key, an issue I have taken up with the software's writer several times. I know that opening a new reader each test number is slow, but given that we have open numbers all over the numeric landscape it is needed to be sure, as this is for a business environment. Eventually I do hope to be able to compact and optimize the code as my working knowledge of VB.NET grows. If I had primary keys I know it woudl be faster, heck if the actuall field was Integers instead of strings it would be faster. This is a stopgap program until such time as the Point Of Sale software adds in a feature similar, or the owner gets so fed up with the whole thing that he farms out to a private group to code an entire POS from scratch for us. I also need it to reopen the connection each time the main 'find' button is pressed as the owner has a habit of leaving all the programs running, and if I didn't re-open the connection, then it's possible that the loop would miss a newly added entry if it used old cached data. Resource usage is a concern, and I do hope to address it eventually to try and make the whole thing faster, but I get excellent performance on my measly 1.4 gHz and the store's machiens are all a minimum of 2.4 gHz, so for now, performance is secondary to just getting it working. The other issue is the POS software has no internal open number checking, it just goes to the last record in the table, and increments the number by one, wether or not there are open numbers before it.

 

[jmcilhinney]

Like I said, it only takes a small adjustment to make my code work without a primary key.

	[color=Blue]Private Sub[/color] Button1_Click([color=Blue]ByVal[/color] sender [color=Blue]As[/color] System.Object, [color=Blue]ByVal[/color] e [color=Blue]As[/color] System.EventArgs) [color=Blue]Handles[/color] Button1.Click
		[color=Blue]Dim[/color] myConnection [color=Blue]As New[/color] OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\<name/path removed for security>.mdb;User Id=admin;Password=;")
		[color=Blue]Dim[/color] myAdapter [color=Blue]As New[/color] OleDbDataAdapter("SELECT Member_Number FROM Customers", myConnection)
		[color=Blue]Dim[/color] myTable [color=Blue]As New[/color] DataTable

		[color=Green]'Retrieve the data from the database.[/color]
		myAdapter.Fill(myTable)

		[color=Green]'Start searching from 1.[/color]
		[color=Blue]Dim[/color] nextMemberNumber [color=Blue]As Integer[/color] = 1

		[color=Green]'Keep incrementing the member number until one is not found in the table.[/color]
		[color=Blue]While[/color] myTable.Select([color=Blue]String[/color].Format("Member_Number = '{0}'", nextMemberNumber)).Length > 0
			nextMemberNumber += 1
		[color=Blue]End While[/color]

		[color=Blue]Me[/color].TextBox1.Text = nextMemberNumber
	[color=Blue]End Sub[/color]

This code only opens a connection once and will always give you the lowest number available.

 

[Bahatmut]

Ok.

Ok, I'll try and keep it in mind, but as I am learning, I need to understand how the code works, so for now, it stays clunky. It's just how I learn, if I don't know exactly how it works, it makes really difficult to fix. But I will keep the other solutions in mind for as my skill level increases.