Hi everyone, I'm in need of a bit of guidance. I'm working on a little weather app and I'm got it all pretty much done. The app gets the local weather by zip code which the user inputs into a textbox. What I would like to do is have the app check the submitted zip code against a list of valid zip codes. Now, I have the list of zip codes and there are honestly more than I thought(approx. 43,180). I tried to hard code a string array with the zip codes but that caused visual studio to get too sluggish to use. Does anyone have any ideas on how I could check the users zip against the list of zip codes, or could you point me in the right direction? Thanks for any and all help.
I have an application using an ADODB connection to a DBase database (simple and easy to distribute). I captured the SQL string sent to the connection object using Quickwatch. I pasted the SQL statement into an SQL tool. I executed the SQL and it returned no rows which is correct. However when the same SQL string is passed to the ADODB connection it returns 189 rows.
Here is the code the initializes the ADODB connection:
' Define DBase connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DataStore;Extended Properties=DBASE III"
ConnDBF = New ADODB.Connection
' Open DBase database connection
OK = ConnDBF.State
' Initialize recordset
rstGRK = New ADODB.Recordset
rstGRK.CursorType = ADODB.CursorTypeEnum.adOpenStatic
Here is the SQL string being passed:
' Get result set
' Get number of hits
ResultCnt = rstGRK.RecordCount
Here is the SQL string:
FROM BIENBVS SRC,
WHERE SRC.BookNo = BKL.BookNumber
AND (SRC.VerseText1 LIKE '%frank jones%'
OR SRC.VerseText2 LIKE '%frank jones%' )
ORDER BY 2, 3, 4</pre>
The correct results is NO ROWS because the names begin with upper case. The ADODB connection returns 189 rows which is incorrect.
Does anybody have any ideas what could be going on and how to force the connection to be case sensitive?
The database is actually DBase (good reason to use, but it is a long explanation).
I converted the VB app to use OdbcConnection & OdbcDataReader methods and the ODBC DSN created with driver - "Microsoft dBase Driver (*.dbf)" . The results are the same - the query in VB still refuses to be case-sensitive.
If I put a breakpoint in the code and capture the SQL string and paste the string verbatim into WinSQL (an SQL tool using the same exact ODBC DSN), and execute the query, no results are returned.
The query behaves differently in VB that in an SQL tool using the exact same driver and ODBC definition.
Do not know why it works differently in VB than in WinSQL !!!!!
I played around with this bit and was perplexed by your problem with case-insensitivity as I was not seeing it. That was until I noticed that you included an "Order By" clause. At least on my end, it is the "Order By" that makes it case-insensitive. Weird!
I only used a single table, so I don't know if that will effect anything, but it may worth a try to drop the "Order By" clause and see what happens.
If that resolves the issue, you could load a DataTable with your query results and apply a sort on the DefaultView to achieve the needed sort order.
I tried removing the "ORDER BY" clause, but the results were the same.
However, after running the query from within VB, I ran the same exact query in WinSQL and it returned multiple rows. Then I ran it a second time and it returned 0 rows. Running it a 3rd and 4th time still returned 0 rows.
I returned to VB and ran the query again. Then I reran it in WinSQL and the first time the query returned many rows and the 2nd, 3rd, 4th, ..... runs returned 0 rows. This behavior is repeatable.
That tells me that the VB app is setting some unknown property that is causing the query to be case-insensitive.
I am going to turn SQL tracing on and see if I can find a difference in the SQL.LOG created from VB and the SQL.LOG created by a good run in WinSQL.
Don't you just love it when the application/objects help you by doing something you did not ask it/them to do !!!!!
Based on a suggestion from someone on another forum, and mega frustration, I decided to abandon the current ODBC DSN. I found and installed a Visual FoxPro driver and created a new DSN using that driver.