I have the three tables set up like you suggested.
A user first gets all the listed interstates for the state the user has in their profile.
My form is designed to show (for the moment) 33 checkboxes and it adapts dynamically to show the quantity of interstates for a particular state.
The user makes a selection and I save UserId followed by what ever interstates were selected.
Next time around the form gets displayed I display all the checkboxes for that particular state but this time I want to show only checkmarks on the boxes the user selected the last time.
For instance 17 interstates for a state, I want to show checkmarks in 1 3 4 7 11 and 15.
Here is the first column of my form. I have deleted columns 2,3, 4, and 5 ( 4 X 8 plus 1 makes 33) to keep things simple
I'm not convinced that you have set up the tables in the manner that I meant. I probably haven't described it that well. Ideally, the user should be able to select a state, and be presented with the list of interstates that belong to that state so that they
can select the one(s) they want. So the tables should look like this:
States [Contains all states]
StateID (e.g. AL)
State (e.g. Alabama)
Interstates [Contains all interstates]
InterState (e.g I-1, I-2 etc)
StatesInterstates [Bridging table contains insterstates by state]
StateID (FK on States.StateID)
InterState (FK on InterStates.InterState)
Contents of this table would be
AL I-10
AL I-20
AL I-22
AZ I-8
AZ I-10 etc
UserInterstates [contains the user's selection of interstates]
UserId (FK on the user table)
InterState (FK on InterStates.InterState)
User selects a state. You get all interstates that belong to that state and render then as checkboxes:
var interstates = db.Query("SELECT InterState From StateInterStates WHERE StateID = @0", StateID);
@foreach(var is in insterstates){
<tr><td><input type="checkbox" name="interstate" value="@is" />@is</td></tr>
}
User selects the interstate(s) that are close to him/her. All the checkboxes have the same name attribute value, so if they select multiple interstates, Request["interstate"] will contain a comma-separated string. You can convert that to an array using string.Split, and then loop through the array inserting a row per interstate into the UserInterStates table:
var interstates = Request["interstate"].Split(",");
foreach(var is in interstates){
db.Execute("INSERT INTO UserInterStates (UserId, Insterstate) VALUES (@0, @1)", userid, is);
}
If you want to allow the user to edit their selection, you get their previous selection from the database, and then check to see if any of the values they previously selected match the current one on the loop when you render the checkboxes:
var userSelection = db.Query("SELECT InsertState FROM UserInserState WHERE USerId = @0", userid);
@foreach(var is in insterstates){
<tr><td><input type="checkbox" name="interstate" value="@is" checked="@userSelection.Any(u => u.InsertState == is)" />@is</td></tr>
}
Redid all the tables so that they have the same names as you are using in your examples.
The code produces a number of checkboxes and "WebMatrix.Data.DynamicRecord" behind them.
I don't see the difference between your code and mine... but there must.
But more importantly, your example tables are a nightmare to enter. Do you have data entry elves working for you or is there a better method to enter data that I don't know about. Let's ignore that for now.
@{
var stateid = "MD";
var db = Database.Open("try1");
var interstates = db.Query("SELECT IntState FROM StatesIntStates WHERE StateId= @0", stateid);
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
</head>
<body>
<table>
@foreach(var istate in interstates) {
<tr><td><input type="checkbox" name="interstate" value="@istate" />@istate</td></tr>
}
</table>
</body>
</html>
The code produces a number of checkboxes and "WebMatrix.Data.DynamicRecord" behind them.
The code wasn't tested and was intended as illustrative only. There may be typos... In this case, I forgot to add the property to the DynamicRecord object:
your example tables are a nightmare to enter. Do you have data entry elves working for you or is there a better method to enter data that I don't know about
At least you only need to enter the data into the tables once.
If it was me, I'd google around for any structured data that already exists, and look at ways of importing that into my database. Structured data might be a csv file somehwere (people host the oddest collection of data on the Internet), or a web page that
contains the data that can be scraped fairly easily... so long as I was sure I wasn't breaching any copyright/terms and conditions, of course. Or I might simply create an admin page for myself and use that to enter the data.
'WebMatrix.Data.DynamicRecord' does not contain a definition for 'IntState'
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'WebMatrix.Data.DynamicRecord' does not contain a definition for 'IntState'
Source Error:
Line 27: <table>
Line 28: @foreach(var istate in interstates) {
Line 29: <tr><td><input type="checkbox" name="interstate" value="@istate.IntState" checked="@userSelection.Any(u => u.IntState = istate.IntState)" />@istate.IntState</td></tr> Line 30: }
Line 31: </table>
Source File: c:\Users\robert.AAT\Documents\My Web Sites\mthisRev100\findistates.cshtml Line:
29
Stack Trace:
[RuntimeBinderException: 'WebMatrix.Data.DynamicRecord' does not contain a definition for 'IntState']
CallSite.Target(Closure , CallSite , Object , Object ) +226
System.Dynamic.UpdateDelegates.UpdateAndExecute2(CallSite site, T0 arg0, T1 arg1) +685
ASP.<>c__DisplayClassc.<Execute>b__a(Object u) in c:\Users\robert.AAT\Documents\My Web Sites\mthisRev100\findistates.cshtml:29
System.Linq.Enumerable.Any(IEnumerable`1 source, Func`2 predicate) +146
ASP._Page_findistates_cshtml.Execute() in c:\Users\robert.AAT\Documents\My Web Sites\mthisRev100\findistates.cshtml:28
System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197
System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69
System.Web.WebPages.WebPage.ExecutePageHierarchy() +151
System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +76
System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +114
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929
The overload of Any that I used test to see if any elements in the sequence meet a condition. The condition in this case is that any of the user's selected interstates are the same as the current one being rendered as part of the checkbox.
I realize there were double equal signs there to begin with, but took one away when troubleshooting.
Sorry.
Next question....
The update of UserInterStates is now failing when it tries insert a record that already exists. And functionality needs to be added to remove a record that is no longer selected.
I am feeling pretty confident that I can fix that with a few if statements. Unless you have something up your sleeve to do that in one (or two) fell swoops.
While SQL CE supports EXISTS, it doesn't appear to support IF statements, so you can either run a SELECT statement to see if any rows match, or add a constraint to the two columns to ensure uniqueness (sounds like you have already done that) and then wrap
the INSERT in a try-catch block. If an exception is raised because of unique constraints being violated, ignore it.
wavemaster
Participant
1279 Points
1124 Posts
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 07, 2013 05:37 AM|LINK
I have the three tables set up like you suggested.
A user first gets all the listed interstates for the state the user has in their profile.
My form is designed to show (for the moment) 33 checkboxes and it adapts dynamically to show the quantity of interstates for a particular state.
The user makes a selection and I save UserId followed by what ever interstates were selected.
Next time around the form gets displayed I display all the checkboxes for that particular state but this time I want to show only checkmarks on the boxes the user selected the last time.
For instance 17 interstates for a state, I want to show checkmarks in 1 3 4 7 11 and 15.
Here is the first column of my form. I have deleted columns 2,3, 4, and 5 ( 4 X 8 plus 1 makes 33) to keep things simple
<table> <tr><td>@if (count>0) {<input type="checkbox" name="is1" checked="@show1" value="@is1" /> @is1<br>} </td> <tr><td>@if (count>1) {<input type="checkbox" name="is2" checked="@show2" value="@is2" /> @is2<br>} </td> <tr><td>@if (count>2) {<input type="checkbox" name="is3" checked="@show3" value="@is3" /> @is3<br>} </td> <tr><td>@if (count>3) {<input type="checkbox" name="is4" checked="@show4" value="@is4" /> @is4<br>} </td> <tr><td>@if (count>4) {<input type="checkbox" name="is5" checked="@show5" value="@is5" /> @is5<br>} </td> <tr><td>@if (count>5) {<input type="checkbox" name="is6" checked="@show6" value="@is6" /> @is6<br>} </td> <tr><td>@if (count>6) {<input type="checkbox" name="is7" checked="@show7" value="@is7" /> @is7<br>} </td> <tr><td>@if (count>7) {<input type="checkbox" name="is8" checked="@show8" value="@is8" /> @is8<br>} </td> </table>I am at a loss how to convert 1 3 4 7 11 and 15 into show1 show3 show4 show7 show 11 and show15 and set them to true.
Looking forward to your thoughts on this.
TIA
Robert
Mikesdotnett...
All-Star
154818 Points
19853 Posts
Moderator
MVP
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 07, 2013 08:20 PM|LINK
I'm not convinced that you have set up the tables in the manner that I meant. I probably haven't described it that well. Ideally, the user should be able to select a state, and be presented with the list of interstates that belong to that state so that they can select the one(s) they want. So the tables should look like this:
States [Contains all states]
StateID (e.g. AL)
State (e.g. Alabama)
Interstates [Contains all interstates]
InterState (e.g I-1, I-2 etc)
StatesInterstates [Bridging table contains insterstates by state]
StateID (FK on States.StateID)
InterState (FK on InterStates.InterState)
Contents of this table would be
AL I-10
AL I-20
AL I-22
AZ I-8
AZ I-10 etc
UserInterstates [contains the user's selection of interstates]
UserId (FK on the user table)
InterState (FK on InterStates.InterState)
User selects a state. You get all interstates that belong to that state and render then as checkboxes:
var interstates = db.Query("SELECT InterState From StateInterStates WHERE StateID = @0", StateID); @foreach(var is in insterstates){ <tr><td><input type="checkbox" name="interstate" value="@is" />@is</td></tr> }User selects the interstate(s) that are close to him/her. All the checkboxes have the same name attribute value, so if they select multiple interstates, Request["interstate"] will contain a comma-separated string. You can convert that to an array using string.Split, and then loop through the array inserting a row per interstate into the UserInterStates table:
var interstates = Request["interstate"].Split(","); foreach(var is in interstates){ db.Execute("INSERT INTO UserInterStates (UserId, Insterstate) VALUES (@0, @1)", userid, is); }If you want to allow the user to edit their selection, you get their previous selection from the database, and then check to see if any of the values they previously selected match the current one on the loop when you render the checkboxes:
var userSelection = db.Query("SELECT InsertState FROM UserInserState WHERE USerId = @0", userid); @foreach(var is in insterstates){ <tr><td><input type="checkbox" name="interstate" value="@is" checked="@userSelection.Any(u => u.InsertState == is)" />@is</td></tr> }Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
wavemaster
Participant
1279 Points
1124 Posts
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 08, 2013 03:17 AM|LINK
Redid all the tables so that they have the same names as you are using in your examples.
The code produces a number of checkboxes and "WebMatrix.Data.DynamicRecord" behind them.
I don't see the difference between your code and mine... but there must.
But more importantly, your example tables are a nightmare to enter. Do you have data entry elves working for you or is there a better method to enter data that I don't know about. Let's ignore that for now.
@{ var stateid = "MD"; var db = Database.Open("try1"); var interstates = db.Query("SELECT IntState FROM StatesIntStates WHERE StateId= @0", stateid); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title></title> </head> <body> <table> @foreach(var istate in interstates) { <tr><td><input type="checkbox" name="interstate" value="@istate" />@istate</td></tr> } </table> </body> </html>Mikesdotnett...
All-Star
154818 Points
19853 Posts
Moderator
MVP
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 08, 2013 06:40 AM|LINK
The code wasn't tested and was intended as illustrative only. There may be typos... In this case, I forgot to add the property to the DynamicRecord object:
<table> @foreach(var istate in interstates) { <tr><td><input type="checkbox" name="interstate" value="@istate.InState" />@istate.InState</td></tr> } </table>At least you only need to enter the data into the tables once.
If it was me, I'd google around for any structured data that already exists, and look at ways of importing that into my database. Structured data might be a csv file somehwere (people host the oddest collection of data on the Internet), or a web page that contains the data that can be scraped fairly easily... so long as I was sure I wasn't breaching any copyright/terms and conditions, of course. Or I might simply create an admin page for myself and use that to enter the data.
I don't have elves.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
wavemaster
Participant
1279 Points
1124 Posts
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 03:00 AM|LINK
I am getting an error "Webmatrix.Data.DynamicRecord does not contain a definition for IntState" on this section of the code: u.IntState = istate
Here is how far I got.
@{ var stateid = "MD"; int userid = 52; var db = Database.Open("try1"); var interstates = db.Query("SELECT IntState FROM StatesIntStates WHERE StateId= @0", stateid); var userSelection = db.Query("SELECT IntState FROM UserInterStates WHERE Userid = @0", userid); if (IsPost) { var selint = Request["interstate"].Split(','); foreach(var userint in selint) { db.Execute("INSERT INTO UserInterStates (UserId, IntState) VALUES (@0, @1)", userid, userint); } } } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title></title> </head> <body> <form method="post"> <table> @foreach(var istate in interstates) { <tr><td><input type="checkbox" name="interstate" value="@istate.IntState" checked="@userSelection.Any(u => u.IntState = istate)" />@istate.IntState</td></tr> } </table> <input type="submit" name="action" value="Save Settings" /> </form> </body> </html>Mikesdotnett...
All-Star
154818 Points
19853 Posts
Moderator
MVP
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 07:11 AM|LINK
That results from the same cause as previously:
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
wavemaster
Participant
1279 Points
1124 Posts
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 01:13 PM|LINK
Before the compiler hits "u.IntState = istate.IntState:
u = "I-70"
IntState = "I-68"
from the immediate window istate.IntState : "object does not contain a definition for 'IntState' accepting a first argument of type 'object'
interstates has 5 members
userSelection has 3 members (a subset of interstates)
The next step it fails as indicated below. I understand the .Any but could you explain what is in between the brackets?
'WebMatrix.Data.DynamicRecord' does not contain a definition for 'IntState'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'WebMatrix.Data.DynamicRecord' does not contain a definition for 'IntState'
Source Error:
Line 27: <table> Line 28: @foreach(var istate in interstates) { Line 29: <tr><td><input type="checkbox" name="interstate" value="@istate.IntState" checked="@userSelection.Any(u => u.IntState = istate.IntState)" />@istate.IntState</td></tr> Line 30: } Line 31: </table>Source File: c:\Users\robert.AAT\Documents\My Web Sites\mthisRev100\findistates.cshtml Line: 29
Stack Trace:
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.17929
Mikesdotnett...
All-Star
154818 Points
19853 Posts
Moderator
MVP
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 04:03 PM|LINK
That should be
@userSelection.Any(u => u.IntState == istate.IntState)
with 2x = signs
The overload of Any that I used test to see if any elements in the sequence meet a condition. The condition in this case is that any of the user's selected interstates are the same as the current one being rendered as part of the checkbox.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
wavemaster
Participant
1279 Points
1124 Posts
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 04:38 PM|LINK
So much for the helpful compiler messages.
I realize there were double equal signs there to begin with, but took one away when troubleshooting.
Sorry.
Next question....
The update of UserInterStates is now failing when it tries insert a record that already exists. And functionality needs to be added to remove a record that is no longer selected.
I am feeling pretty confident that I can fix that with a few if statements. Unless you have something up your sleeve to do that in one (or two) fell swoops.
Thanks either way, learned some new stuff.
Mikesdotnett...
All-Star
154818 Points
19853 Posts
Moderator
MVP
Re: need suggestions for code improvements (too many ifs) - unknown string lengths
Feb 09, 2013 08:05 PM|LINK
It depends on the database you are using. If you are using SQL Server, you can use NOT EXISTS in your SQL like in this answer: http://stackoverflow.com/questions/7743596/insert-into-a-table-if-not-exists-and-then-insert-duplicate-records-into-another
While SQL CE supports EXISTS, it doesn't appear to support IF statements, so you can either run a SELECT statement to see if any rows match, or add a constraint to the two columns to ensure uniqueness (sounds like you have already done that) and then wrap the INSERT in a try-catch block. If an exception is raised because of unique constraints being violated, ignore it.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter