StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POThe Link Between Webform Combobox Data and the Database (SQL Server & ASP.NET)
text
Body
copied!<p>The title, while long, pretty much says it all.</p> <p>What I have is a master table with a bunch of supporting table relations through foreign keys. For a few of the foreign tables, upon attempting to insert a record into the master table where one of the foreign keys doesn't exist, the data would be passed to the foreign table to create the record first, thereby making the constraint valid and passing the key to the created record back to the insert procedure of the master table.</p> <p>This data comes from a form in String form, but naturally the foreign key will be an int. The process would look something like this:</p> <pre><code>-- ASP.NET Web Form -- Requestor Name: _____________ (combobox) Request: _____________ (dropdownlist) Date: _____________ (datepicker) </code></pre> <p>This is a super simplified version, but assume we have a master table with the above data, where both names are foreign keys to a <code>People</code> table. The <strong>name fields are comboboxes</strong> with a populated list of names linking to People. However, if I wanted to enter a person who didn't yet exist in the People table, the procedure should first create the Person, then use the ID from that new record as the foreign key in the Master table containing columns for the above.</p> <p>I'm using SQL Server and ASP.NET with VB.NET codebehind. I've been scratching my head over this one for awhile, how to pass data (in different forms such as a foreign key or string) between the web server and DB server, as well as where to validate / transform the data.</p> <p><strong>It seems the entered name will be passed as an ID if the foreign key exists, and a String if not.</strong></p> <p>This is my most perplexing problem so far, and no idea where else to look. I've read up on <a href="http://www.4guysfromrolla.com/" rel="nofollow">Scott Mitchell's</a> site and others.</p> <p><strong>MY SOLUTION (?)</strong></p> <blockquote> <p>The best I can come up with is to pass the user input from the user as a string and convert it to int in the T-SQL procedure. If the value was selected from the drop down, it should match precisely with a valid foreign key. If it doesn't match, then create a new Person and return a foreign key. Is this best practice?</p> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload