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
-
-
%>
-
-
7 2091
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
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
statement outside of the
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
-
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
-
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?
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?
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Eric Linders |
last post by:
Hi,
I'm trying to figure out the most efficient method for taking the
first character in a string (which will be a number), and use
it as a variable to check to see if the other numbers in the string
match that first number. I'm using this code for form validation of a
telephone number.
Previous records from the past few months show that when someone is
just messing around on one of our forms (to waste our time), they type
|
by: Stefan Richter |
last post by:
Hi, after coding for days on stupid form validations -
Like:
strings (min / max length), numbers(min / max value), money(min / max
value), postcodes(min / max value), telefon numbers,
email adresses and so on.
I thought it might be a better way to programm an automated, dynamic
form validation that works for all kinds of fields, shows the
necessary error messages and highlights the coresponding form fields.
|
by: Darren |
last post by:
I have a form that has 10 fields on it. I have made all of them "Required".
I also am using vb if statements to decide whether or not each field should
be on the page. I am using the vb to compare values in my database and if a
certain field equals something then the field is shown on the form. When
the form is displayed in the browser, it could any increment of the 10
fields on it. My problem is that unless all fields are being shown...
|
by: Hosh |
last post by:
I have a form on a webpage and want to use JavaScript validation for the
form fields.
I have searched the web for form validation scripts and have come up with
scripts that only validate individual fields, such as an "Email Validation
Script" or a "Phone Validation Script".
Is it ok to put all these scripts on page as they are or should they be
joined in some way together to be one script?
I'm a total JavaScript newbie and am completely...
|
by: julie.siebel |
last post by:
Hello all!
As embarrassing as it is to admit this, I've been designing db driven
websites using javascript and vbscript for about 6-7 years now, and I
am *horrible* at form validation.
To be honest I usually hire someone to do it for me, grab predone
scripts and kind of hack out the parts that I need, or just do very
minimal validation (e.g. this is numeric, this is alpha-numeric, etc.)
| |
by: Chris |
last post by:
Hi,
I have a form for uploading documents and inserting the data into a mysql
db. I would like to validate the form. I have tried a couple of Javascript
form validation functions, but it appears that the data goes straight to the
processing page, rather than the javascript seeing if data is missing and
popping up an alert. I thought it may be because much of the form is
populated with data from the db (lists, etc.), but when I leave...
|
by: Rik |
last post by:
Hello guys,
now that I'm that I'm working on my first major 'open' forms (with
uncontrolled users I mean, not a secure backend-interface), I'd like to add
a lot of possibilities to check wether certain fields match certain
criteria, and inform the user in different ways when the data is wrong
(offcourse, this will be checked on posting the data again, but that's
something I've got a lot of experience with).
Now, offcourse it's...
|
by: dirk |
last post by:
Hi,
Being new coding in phpI have a question concerning server side form
validation.
In a php script I check if a form is correctly filled in. Now I want
that the
page containing the forms is represented agian so that the user can
fill in a
correct value. The other forms that a correctly filled in should be
|
by: rubelpasha |
last post by:
Hi all,
I want to learn about server side form validation.I have the following source code. But i dont know the meaning of $_SERVER. When i ran this code it shows an error. The error says multiple choice.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>New HTML...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |