Answered DataTable.Select strange behavior, clarification needed

Hopworks

Member
Joined
Mar 31, 2011
Messages
10
Programming Experience
5-10
In advanced; Pardon any errors in my use of terminology concerning VB 2008 Express and the MySQqlDataAdapter.

I wrote a series of applications in VB 2008 that access a MySQL database and perform front end operations on that database. Everything works just fine... Read, Update, Add, Delete queries work just fine BUT...

I ran into a little snag that I can't figure out the cause of it. I am hoping someone can shed some light on the cause so I can better understand this issue and either code around it or improve my best practices for this kind of work.

The problem arose when tried a select method on a datatable filled inside a dataset. I used the select method to query a result of rows that matched a field. It's a vendor database where all but one vendor number is 5-7 digits. The one vendor has a 3 digit number. Anyway, for some reason, I do not return any datarows using that 3 digit vendor number, yet I have success when trying it with 5-7 digit numbers. When I tried the query on my MySql database using phpmyadmin, it works fine.

So to bandaid fix the problem, I changed the 3-digit vendor number to a five digit number, padding it with zeros, as in "619" to "00619" and then I returned datarows on the select method.

So my question is... are there limits to the select method? Like a minimum of characters needed to return a result? Is this a bug? Is there a parameter I'm missing somewhere?

I know you all like code to see so if someone responds, I'll post it. The setup code for the global connection, dataset, loading of the datatables, etc is rather extensive but I don't mind doing it. Just thought the question and scenario might trigger a quick answer.

My project is working fine with the bandaid fix, but I have this nagging urge to figure out why it didn't work before the fix so I can try to code to avoid this problem down the road.

Thank you so much for your time!

Hop
 
Last edited:
There are no specific limitations. We need to see what you're actually doing. We don't need to see anything about how you populate the DataTable; only how you call Select.
 
There are no specific limitations. We need to see what you're actually doing. We don't need to see anything about how you populate the DataTable; only how you call Select.

Very well.
VB.NET:
	Private Sub populate_contacts_list(ByVal vnum As String)
		Dim mydatatable As DataTable
		Dim mydatarows() As DataRow
		mydatatable = ds.Tables("dsd_contacts")
		mydatarows = mydatatable.Select("vnum = " & vnum)
		ListView2.Items.Clear()
		If mydatarows.Count > 0 Then
			For Each myrow In mydatarows
				Dim mylvi As New ListViewItem
				mylvi.Text = myrow.Item("first_name") & " " & myrow.Item("last_name")
				'
				mylvi.SubItems.Add(myrow.Item("office_phone"))
				mylvi.SubItems.Add(myrow.Item("position"))
				mylvi.Tag = myrow.Item("id")
				ListView2.Items.Add(mylvi)
			Next
		End If
		Dim mylvinew As New ListViewItem
		mylvinew.Text = "<NEW>"
		mylvinew.Tag = "NEW"
		ListView2.Items.Add(mylvinew)
It works on the dsd_contacts table for every other vendor number (as vnum) I use except "619". The other vnum column entries are 5-7 digits, like 9400032, or 40925. All are text. Again, I changed the vnum entry from 619 to 00619 in the database, and in the dsd_accounts database that references that vnum, and then it found it. Only difference is that it went from 3 characters to 5.

Thank you for your time!

Hop
 
If you are able to change the value in the database from 619 to 00619 then the data must be being stored as text rather than as a number, yet you are using a number in your Select filter. If the data is text then compare it to text, which requires single quotes.
 
If you are able to change the value in the database from 619 to 00619 then the data must be being stored as text rather than as a number, yet you are using a number in your Select filter. If the data is text then compare it to text, which requires single quotes.

I didn't know that. Thank you. Still, I wonder why the other 5-7 character queries worked then? It must be a MySql thing. I tried two queries on dsd_accounts AND dsd_contacts, both using text as vnum and using 619 and 40925 for vnum. I only had to put single quotes around 619. 40925 worked without them. They both could be numbers so still not sure why one works without single quotes and one doesn't.

Adding the single quotes fixed the issue and works after I changed the vnum back to '619' in both tables. I'll make sure I use the single quotes on all text data from now on. THANK YOU!
 
Back
Top