Ok, I have two tables: (1)"ZIPS" which contains zipcodes,states,and counties for everywhere in the U.S. and (2) "UserCriteria" which contains username, state, county, rank, and a few other criteria.
In the UserCriteria table, I can have one user (mtaylor) representing many states..let's say 10. Each state is assigned a Rank based on how well that user knows that state (typically 1 - 3...3 being 'they do not understand it at all').
So let's say mtaylor's first 8 states have a Rank of 1, and his 9th and 10th state has a Rank of 2....well I want to let the table know that the rest of the 40 states that mtaylor does not have should have a rank of 3 ('meaning he should not do these states')
I am clueless on how to do this.
As of now, I have in the Usercriteria table, in the State column, I have the word "REST" (meaning rest of the states) and the Rank given to "REST" is 3....but how would i go about finding out what the 'rest of the states' are?
My SQL strings are very rough, but i have this so far...
Expand|Select|Wrap|Line Numbers
- Function RestOfStates(ByVal _user As String)
- Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
- Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
- Try
- Dim queryString As String
- queryString = "SELECT DISTINCT State from Zips"
- Dim dbCommand As OleDbCommand = New OleDbCommand
- dbCommand.CommandText = queryString
- dbCommand.Connection = dbConnection
- Dim queryString2 As String
- queryString2 = "SELECT UserName, State, Rank from UserCriteria WHERE State = 'REST' AND Username = '" & _user & "'"
- Catch ex As Exception
- Console.WriteLine(ex.Message)
- myLogger.Log(ex.Message)
- Finally
- dbConnection.Close()
- End Try
- End Function