469,270 Members | 1,793 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

ASP - multiple SQL insertion & form validation

12
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%
  3. dim txt
  4. Dim tempv
  5. tempR = Request.Form("frmVolOrg1")
  6. tempv = Request.Form("frmVolName") 
  7. If tempv <> "" THEN
  8. Dim con, sql_insert, data_source
  9. data_source = "Driver={SQL Server};Server=SOMESERVER;Database=SOMEdb;Uid=someUSR;Pwd=somePWD"
  10. For w = 1 TO Request.Form("frmVolOrg1").Count AND Request.Form("frmVolHours1").Count AND Request.Form("frmOrgContact1").Count AND Request.Form("frmOrgPhone1").Count
  11. sql_insert = "insert into tbl_main (volName, volTitle, volDepartment, volPhone, volMonth, volYear, volOrg, volHours,  volContact, volConPhone) values " & _
  12.     "('"&Request.Form("frmVolName") & "','" & _
  13. Request.Form("frmVolTitle") & "','" & _
  14. Request.Form("frmVolDept") & "','" & _
  15. Request.Form("frmVolPhone") & "','" & _
  16. Request.Form("frmVolMonth") & "','" & _
  17. Request.Form("frmVolYear") & "','" & _
  18. Request.Form("frmVolOrg1").Item(w) & "','" & _
  19. Request.Form("frmVolHours1").Item(w) & "','" & _
  20. Request.Form("frmOrgContact1").Item(w) & "','" & _
  21. Request.Form("frmOrgPhone1").Item(w)&"')"
  22. Set con = Server.CreateObject("ADODB.Connection")
  23. con.Open data_source
  24. con.Execute sql_insert
  25. con.Close
  26. Next
  27. Response.Redirect("allResults.asp")
  28. Set con = Nothing
  29. End If
  30.  
  31. %>
  32.  
  33.  
Apr 10 '08 #1
7 1853
DrBunchman
979 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. con.open data_source
  3. sql_insert = ""
  4. For w = 0 To 2
  5.      sql_insert = sql_insert & " INSERT INTO Table(Column1, Column2) "
  6.      sql_insert = sql_insert & " VALUES('" & Array1(w) & "', '" & Array(w) & "') "
  7. Next
  8. con.Execute sql_insert
  9. %>
  10.  
This will generate a sql string that looks something like the following which you execute just once at the end:

Expand|Select|Wrap|Line Numbers
  1.  INSERT INTO Table(Column1, Column2) 
  2. VALUES('hello', 'world')
  3. INSERT INTO Table(Column1, Column2)
  4. VALUES('this is a ', 'test')
  5. INSERT INTO Table(Column1, Column2)
  6. VALUES('i love', 'bytes.com')
  7.  
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. If Request.Form("Value1") <> "" And Request.Form("Value2") <> "" And <etc etc> Then
  3.      <write your sql insert code here>
  4. 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
Apr 11 '08 #2
sbryguy
12
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
Expand|Select|Wrap|Line Numbers
  1. con.Execute
statement outside of the
Expand|Select|Wrap|Line Numbers
  1. NEXT
statement but then I was only writing the last indexed item to the DB.

Expand|Select|Wrap|Line Numbers
  1. If Request.Form("Submit") = "Submit" Then
  2. For w = 1 to Request.Form("frmVolOrgM").Count AND Request.Form("frmVolHoursM").Count AND Request.Form("frmOrgContactM").Count AND Request.Form("frmOrgPhoneM").Count
  3. sql_insert = "insert into tbl_main (volName, volTitle, volDepartment, volPhone, volMonth, volYear, volOrg, volHours, volContact, volConPhone) values "
  4. sql_insert = sql_insert &    "('"&Request.Form("frmVolName") & "','" & _
  5. Request.Form("frmVolTitle") & "','" & _
  6. Request.Form("frmVolDept") & "','" & _
  7. Request.Form("frmVolPhone") & "','" & _
  8. Request.Form("frmVolMonth") & "','" & _
  9. Request.Form("frmVolYear") & "','" & _
  10. Request.Form("frmVolOrgM").Item(w) & "','" & _
  11. Request.Form("frmVolHoursM").Item(w) & "','" & _
  12. Request.Form("frmOrgContactM").Item(w) & "','" & _
  13. Request.Form("frmOrgPhoneM").Item(w)&"')"
  14. con.Execute sql_insert
  15. Next
  16. con.Close
  17. Set con = Nothing
  18. End If
  19.  
Apr 14 '08 #3
sbryguy
12
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.

Expand|Select|Wrap|Line Numbers
  1. con.Open data_source
  2. r = Request.Form("frmVolOrgM").Count AND Request.Form("frmVolHoursM").Count AND Request.Form("frmOrgContactM").Count AND Request.Form("frmOrgPhoneM").Count
  3. IF Request.Form("Submit") = "Submit" Then
  4.     IF Request.Form("frmVolOrgM").Item(2) = "" AND Request.Form("frmVolOrgM").Item(3) = "" Then
  5.         r = 1 
  6.         Else If Request.Form("frmVolOrgM").Item(2) <> "" AND Request.Form("frmVolOrgM").Item(3) = "" Then
  7.         r = 2
  8.         Else If Request.Form("frmVolOrgM").Item(2) = "" AND Request.Form("frmVolOrgM").Item(3) <> "" Then
  9.         r = 2
  10.         Else If Request.Form("frmVolOrgM").Item(2) <> "" AND Request.Form("frmVolOrgM").Item(3) <> "" Then
  11.         End If
  12.         End If
  13.         End If
  14.     End If
  15. For w = 1 to r
  16. sql_insert = "insert into tbl_main (volName,volTitle,volDepartment,volPhone,volMonth,volYear,volOrg,volHours,volContact,volConPhone) values "
  17. sql_insert = sql_insert &    "('"&Request.Form("frmVolName") & "','" & _
  18. Request.Form("frmVolTitle") & "','" & _
  19. Request.Form("frmVolDept") & "','" & _
  20. Request.Form("frmVolPhone") & "','" & _
  21. Request.Form("frmVolMonth") & "','" & _
  22. Request.Form("frmVolYear") & "','" & _
  23. Request.Form("frmVolOrgM").Item(w) & "','" & _
  24. Request.Form("frmVolHoursM").Item(w) & "','" & _
  25. Request.Form("frmOrgContactM").Item(w) & "','" & _
  26. Request.Form("frmOrgPhoneM").Item(w)&"')"
  27. con.Execute sql_insert
  28. Next
  29. con.Close
  30. Set con = Nothing
  31. End If
  32.  
Apr 14 '08 #4
DrBunchman
979 Expert 512MB
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?
Apr 15 '08 #5
sbryguy
12
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?
Apr 16 '08 #6
DrBunchman
979 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. If Request("a") <> "" And Request("b") <> "" And Request("c") Then
  3. ...do the insert... 
  4. End If
  5.  
Dr B
Apr 17 '08 #7
sbryguy
12
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
Jun 24 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by Stefan Richter | last post: by
6 posts views Thread by Darren | last post: by
16 posts views Thread by Hosh | last post: by
9 posts views Thread by julie.siebel | last post: by
27 posts views Thread by Chris | last post: by
11 posts views Thread by Rik | last post: by
5 posts views Thread by rubelpasha | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.