April 19, 2003 at 9:03 am
I am trying to write a stored proc that will insert up to 5 values for the same field.
For instance, I have a table called Witness table.
According to our specs, whenever an incident occurs, there could be 0 to 5 witnesses on the scene of the incident.
My question is, how can I construct the insert statement so that the same field can be used to capture all the witnesses at the scene of the incident.
Here is my table structure:
Witness table:
witnessId int pk, identity,
witnessName varchar(50),
WitnessAddress varchar(50),
WitnessCity varchar(50),
WitnessState varchar(50),
WitnessZip varchar(50),
WitnessPhone varchar(50)
for instance, rather than coding it this way:
insert into witness (witnessName, witnessAddress, WitnessCity,witnessState,witnessZip)
values('Name1','Address1','City1','State','Zip')
insert into witness (witnessName, witnessAddress, WitnessCity,witnessState,witnessZip)
values('Name2','Address2','City2','State2','Zip2')
up to 5 records,
Is there a way to make it may be using
I have other tables to insert but this is the one that gives me the problem.
Thanks in advance
April 19, 2003 at 9:36 am
I dont see anything wrong with that. One way to avoid multiple calls to the server is to define 5 sets of parameters for your proc, then you test each set to determine if there is really data that needs to be inserted. You could avoid this by passing in the data as XML, using OpenXML as the source for the insert statement.
I question the merits of declaring that there can be a max of 5 witnesses. If you're saving the data row based (as it appears you are), you can support zero to unlimited, so why put the artificial limit in place?
Andy
April 19, 2003 at 10:59 am
Andy, thanks for the response.
I agree with with you that no limits should be set on the number of witnesses, though that was the client's spec, I think I can change that.
Second, I am not good with xml. Asp, yes but not xml.
Thirdly, I when I started this project, I did it differently.
I did it by asking the user to select how many witnesses were on scene.
Depending on the user's choice, an input box or more would then open up allowing the user to enter the witness information.
The reason I am dumping this code is I am having all kinds of trouble displaying the results of my input.
That is why I am trying to change the code to something more manageable.
April 19, 2003 at 11:56 am
Well..lot's of options. Without seeing all the code, hard to give a really great recommendation. I'll add a couple more comments, maybe other readers can add to it.
XML is pretty easy for this type operation, provided you have SQL2K. You would just save all your witnesses into an XML document (a string) something like this:
<XML>
<WITNESS>
<NAME>Witness1</NAME>
<ADDR>101 Main St, Sometown, FL</ADDR>
</WITNESS>
<WITNESS>
<NAME>Witness2</NAME>
<ADDR>201 Main St, Sometown, FL</ADDR>
</WITNESS>
</XML>
Then look at how OpenXML works, not bad as long as your XML doc is simple. Beyond that, maybe you don't have the right metaphor? Instead of asking how many, why not present it as a grid - users pretty much instinctively understand how grids work, give them a way to add a row, etc. If you bind to ADO, the recordset could generate the update code for you. If you prefer not to bind, then you still have to move the data back to the server yourself.
Andy
April 19, 2003 at 2:05 pm
Thanks a lot again, Andy!
I am posting a link to my staging area.
This link provides a bit of a visual clue to what I am looking to accomplish:
http://www26.brinkster.com/simflex/kenig/accident_Report.asp
If you scroll down, you will see "WITNESSES / OCCUPANTS" section.
Unless I am misunderstanding your question, I do have the interface.
I am just having problem coming up with the code that will insert the witness records, should there be more than one witness for a given incident/accident.
April 19, 2003 at 5:03 pm
I still think simple works. When they post the page, you test each set of witnesses, if not blank you do the insert. Worst case, you do five inserts. Most times probably less. Inserts are fast, so no really performance concerns. I'd do an insert proc to make as much of it reusable as possible, put a sub in your code that you pass the params and have it execute the proc.
Andy
April 19, 2003 at 5:40 pm
Man, for a site owner, you are very generous and kind.
I will try the 5 inserts first.
Like I said earlier, my primary objective is to be able to write the code that will display the records; that's why I abandoned my original code and that is why I will try the 5 inserts.
I will post the result later, good or bad.
Thanks again for your time and suggestions, Andy!
April 21, 2003 at 4:57 am
Another variant to what Andy has discussed is you could look and see how many witnesses have been entered and then create a loop inside your application. This way you would have one INSERT statement (or stored-procedure) and have the ability to cut it off at your pre-determined max.
Also, if you are concerned about properly displaying the information you have the incident # and the witness # so you can pull the data back and ORDER BY the witness #.
just another spin on it.
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 21, 2003 at 11:41 am
simflex,
On a completely different subject, nice looking form, although the validation is a little awkward. Validating and Reporting each field one at a time is a pain. Try building up a string of error messages and just display the final message containing all the fields that are wrong, and positioning at the first field.
function FrontPage_Form1_Validator(theForm)
{
var s_Error_Msg = "";
var b_Error_Encountered = false;
var s_Error_Field = "";
if({Condition})
{
s_Error_Message += "Please enter the Drivers Name\n";
b_Error_Encountered = true;
s_Error_Field = "DriverName";
}
if({Condition})
{
s_Error_Message += "Please enter the License Plate Number\n";
b_Error_Encountered = true;
s_Error_Field = "LicensePlateNumber";
}
Blah....Blah....
if(b_Error_Encountered)
{
alert(s_Error_Field);
theForm(s_Error_Field).focus();
}
return !b_Error_Encountered
}
Personally, I change the coloration of the field and the label as a means of highlighting the problem fields. It provides more feedback and makes the form more usable.
Also, when you think about either displaying all the data or the form, use multiple recordsets and have some code to check in your ASP which Recordset you are on. For example :-
Execute a stored procedure which contains the multiple SELECTS from each of your tables, assuming a parameter of @l_Parm_Incident_Id :-
SELECT 1 AS Recorset_Id, *
FROM table1
WHERE IncidentId = @l_Parm_Incident_Id
SELECT 2 AS Recorset_Id, *
FROM table2
WHERE IncidentId = @l_Parm_Incident_Id
SELECT 3 AS Recorset_Id, *
FROM table3
WHERE IncidentId = @l_Parm_Incident_Id
And so on....
Then in your ASP Code you can interpret the Recordset_Id field to determine which set of data you are processing.
(ASP Code follows)
Select Case rst_Data("Recordset_Id")
Case 1
*** Process Recordset 1 Data
Case 2
*** Process Recordset 2 Data
Case 3
*** Process Recordset 3 Data
End Select
Using this technique you will be able to redisplay the original form data or just the data that was entered with very few access to the database, even though the data is spread over several tables.
Hope I am not teaching you to suck eggs.
SWJS
Edited by - swjs on 04/21/2003 11:45:03 AM
April 22, 2003 at 5:47 am
hi whoteegan, and swjs!
I appreciate all the input.
Right now, I am more concerned about getting the form to store data for all those fields on the database.
I am close to completing that part of the task.
I think a bigger challenge will be to come with a way to display the records on form similar to what you guys saw.
Again, the pain here will be displaying multple records for witnesses/occupants/injured persons.
The user wants to see data displayed in the same format as the input form.
So there is got to be a way to code an asp to pull records from the database and display them to look like the form you guys just saw.
April 22, 2003 at 5:49 am
I hate to say this Sim but...
You are on your own now. I don't know ASP at all. If I was to build it I would probably research outputting data as XML and populating on your site.
Good Luck!
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 22, 2003 at 10:48 am
Only had time for a quick peek. I would name each control in the WITNESSES / PASSENGERS block the same (eg WitnessOrPassengerName,Witness,Passenger etc)
IE will make them an array. When you output the blocks you can do so with a loop depending on how many retrieved from db and when you process them on the return you can reference them by occurance. This will enable you to have a variable number of inputs and outputs.
Far away is close at hand in the images of elsewhere.
Anon.
April 23, 2003 at 5:42 am
hi David, again!
If you have time, can you give me an example of what you just said, especially as it relates to displaying the witness/passenger records?
Thanks as always.
April 23, 2003 at 5:49 am
I forgot to mention that I do have the insert statement to work right now.
I used a crude (spagetti) method of inserting records but it seems to work.
What I would love to get help on is how to come up with the code that retrieves records from the db and populates the forms that you guys have seen so far.
The client wants to use the same form for inserting records into the database as well as retrieving records from the db.
So given what I have put out so far, if anyone would be kind enough to help with the select .... from code I would greatly appreciate it.
I don't mind doing this with stored proc and then use asp to invoke the stored proc as long as it can capture witnesses/passengers records.
April 23, 2003 at 6:02 am
I would probably create something like the following:
SELECT WitnessFirstName, WitnessSecondName
FROM WitnessTable
ORDER BY WitnessId
This way it returns the data in the proper order for you to populate back into your form.
It would also be easy to do a record count inside your app (I hope) to determine how many you got and loop that many times
Good Luck
AJ Ahrens
SQL DBA
Revenue Assurance Management - AT&T
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply