ASP - multiple SQL insertion & form validation

[sbryguy]

Greetings,

I'm pretty new at ASP/SQL so if this seems like a no brainer, please enlighten me.

I have a form that is being used to track volunteer activities for employees in my company. The form has multiple input fields with the same id in case the employee has volunteered for multiple organizations.

First of all what's the beset practice out there for handling this scenario?
Currently I'm running a FOR loop which is creating a new record for every organization the employee has entered. Should I be doing this? Or, should I just have the form create one record with comma delineated values?

Secondly, if the user doesn't fill out all the files available, the script will still run the insert statement using the blank fields so what I end up with are empty records. Is there a way I can check the fields before I sent them? Any help is much appreciated as I have no direction in which to run.

Here's the ASP code:

<%
dim txt
Dim tempv
tempR = Request.Form("frmVolOrg1")
tempv = Request.Form("frmVolName") 
If tempv <> "" THEN
Dim con, sql_insert, data_source
data_source = "Driver={SQL Server};Server=SOMESERVER;Database=SOMEdb;Uid=someUSR;Pwd=somePWD"
For w = 1 TO Request.Form("frmVolOrg1").Count AND Request.Form("frmVolHours1").Count AND Request.Form("frmOrgContact1").Count AND Request.Form("frmOrgPhone1").Count
sql_insert = "insert into tbl_main (volName, volTitle, volDepartment, volPhone, volMonth, volYear, volOrg, volHours,  volContact, volConPhone) values " & _
	"('"&Request.Form("frmVolName") & "','" & _
Request.Form("frmVolTitle") & "','" & _
Request.Form("frmVolDept") & "','" & _
Request.Form("frmVolPhone") & "','" & _
Request.Form("frmVolMonth") & "','" & _
Request.Form("frmVolYear") & "','" & _
Request.Form("frmVolOrg1").Item(w) & "','" & _
Request.Form("frmVolHours1").Item(w) & "','" & _
Request.Form("frmOrgContact1").Item(w) & "','" & _
Request.Form("frmOrgPhone1").Item(w)&"')"
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
con.Close
Next
Response.Redirect("allResults.asp")
Set con = Nothing
End If

%>

[DrBunchman]

Hi sbryguy,

Welcome to Bytes!

It sounds like you're structuring your database correctly with one record for each organisation an employee has entered but if you're not sure about this then feel free to print your tables here. Can I make a suggestion with regards to your insert code?

At the moment you are creating a SQL string, opening your connection, executing the sql & closing the connection in each cycle of your loop.

It would be quicker to build a single sql string by adding a bit each time you go round your loop and then executing it once at the end. This saves your page the task of opening & closing the connection and executing the sql each time it goes round. An example is below:

<%
con.open data_source
sql_insert = ""
For w = 0 To 2
	 sql_insert = sql_insert & " INSERT INTO Table(Column1, Column2) "
	 sql_insert = sql_insert & " VALUES('" & Array1(w) & "', '" & Array(w) & "') "
Next
con.Execute sql_insert
%>

This will generate a sql string that looks something like the following which you execute just once at the end:

 INSERT INTO Table(Column1, Column2) 
VALUES('hello', 'world')
INSERT INTO Table(Column1, Column2)
VALUES('this is a ', 'test')
INSERT INTO Table(Column1, Column2)
VALUES('i love', 'bytes.com')

To answer the second part of your question you will need to validate your data before you do your insert. You can do this using javascript as the user submits the form or you can do it before you insert it using vbscript. Something like the following just after your For statement:

 
If Request.Form("Value1") <> "" And Request.Form("Value2") <> "" And <etc etc> Then
	 <write your sql insert code here>
End If

If you'd rather use a javascript solution (where you can get the page to fire a message box telling the user they've forgotten to fill out a certain field) then let me know and I'll show you how it's done.

Let me know how you get on,

Hope this helps,

Dr B

[sbryguy]

validation?

Hi Dr B,

Thanks for the detailed response. I worked my insert statment around but I can't seem to get any validation to work. The FOR loop seems to run not matter what IF statement I put in there creating three enteries when only one is needed. Can you point me in a direction for dealing with validation?

For the life of me I can't seem to figure out how to only include fields that are filled out.

PS I tried to move my

con.Execute

statement outside of the

NEXT

statement but then I was only writing the last indexed item to the DB.

If Request.Form("Submit") = "Submit" Then
For w = 1 to Request.Form("frmVolOrgM").Count AND Request.Form("frmVolHoursM").Count AND Request.Form("frmOrgContactM").Count AND Request.Form("frmOrgPhoneM").Count
sql_insert = "insert into tbl_main (volName, volTitle, volDepartment, volPhone, volMonth, volYear, volOrg, volHours, volContact, volConPhone) values "
sql_insert = sql_insert &	"('"&Request.Form("frmVolName") & "','" & _
Request.Form("frmVolTitle") & "','" & _
Request.Form("frmVolDept") & "','" & _
Request.Form("frmVolPhone") & "','" & _
Request.Form("frmVolMonth") & "','" & _
Request.Form("frmVolYear") & "','" & _
Request.Form("frmVolOrgM").Item(w) & "','" & _
Request.Form("frmVolHoursM").Item(w) & "','" & _
Request.Form("frmOrgContactM").Item(w) & "','" & _
Request.Form("frmOrgPhoneM").Item(w)&"')"
con.Execute sql_insert
Next
con.Close
Set con = Nothing
End If

[sbryguy]

This is where I'm at with this; I have a chunk of IF statements that basically sets a variable to plug into the FOR loop. There has to be a more dynamic way of determining how many times to execute the INSERT statement. If anyone can help me optimize this chunk of code your help is much appreciated.

con.Open data_source
r = Request.Form("frmVolOrgM").Count AND Request.Form("frmVolHoursM").Count AND Request.Form("frmOrgContactM").Count AND Request.Form("frmOrgPhoneM").Count
IF Request.Form("Submit") = "Submit" Then
	IF Request.Form("frmVolOrgM").Item(2) = "" AND Request.Form("frmVolOrgM").Item(3) = "" Then
		r = 1 
		Else If Request.Form("frmVolOrgM").Item(2) <> "" AND Request.Form("frmVolOrgM").Item(3) = "" Then
		r = 2
		Else If Request.Form("frmVolOrgM").Item(2) = "" AND Request.Form("frmVolOrgM").Item(3) <> "" Then
		r = 2
		Else If Request.Form("frmVolOrgM").Item(2) <> "" AND Request.Form("frmVolOrgM").Item(3) <> "" Then
		End If
		End If
		End If
	End If
For w = 1 to r
sql_insert = "insert into tbl_main (volName,volTitle,volDepartment,volPhone,volMonth,volYear,volOrg,volHours,volContact,volConPhone) values "
sql_insert = sql_insert &	"('"&Request.Form("frmVolName") & "','" & _
Request.Form("frmVolTitle") & "','" & _
Request.Form("frmVolDept") & "','" & _
Request.Form("frmVolPhone") & "','" & _
Request.Form("frmVolMonth") & "','" & _
Request.Form("frmVolYear") & "','" & _
Request.Form("frmVolOrgM").Item(w) & "','" & _
Request.Form("frmVolHoursM").Item(w) & "','" & _
Request.Form("frmOrgContactM").Item(w) & "','" & _
Request.Form("frmOrgPhoneM").Item(w)&"')"
con.Execute sql_insert
Next
con.Close
Set con = Nothing
End If

[DrBunchman]

Rather than doing all those tests before hand you could do them inside the loop. Each time you start the loop you could test if the item has any data; if so run the insert and if not just skip over it.

Does that make sense to you?

[sbryguy]

It does make sense but when I tried to check for an empty value within the for loop it continued to execute insert for the empty columns, I couldn't get any validation to work. Should I be using CASE statements instead of IFs?

[DrBunchman]

Can you print your code so I can take a look? It may be that your logic isn't quite right in your If statements. Something like the following should work:

 
If Request("a") <> "" And Request("b") <> "" And Request("c") Then
...do the insert... 
End If

Dr B

[sbryguy]

Hi Dr B,

I apologize for taking so long; i had a much needed vacation :)

I was able to get my logic right; had a little syntax error in my logic. Thanks for all your help once I get the chunk of code cleaned up i'll post it up. Again thanks for your help