Question

Dynamic Insert with Field Check ... (A challenge)

Asked by: carrzkiss

Hello All;
(Confusing title.)

OK.
Download the file, and run the insert.asp?amt=2 (or any number of fields that you want)

In the Email and verfity email.
Type in Differernt Email address's.
This will cause it to show you an error page, letting you know that the address do not match.
Now, the record is inserted into the Database still, but with NotMatch in the [Status] Field.

In the Error/Correction page, it does not display the fields properly.
And it has the same amount of buttons as there are records.

I need it to show "1" Button no matter how many records.
(Like a Form suppose to do)

I think once you run it, you will understand it better then I can explain it to you.

Thanks All.
Carrzkiss

Multiple.zip
- 14 KB
Dynamic Fields Insert

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-07-04 at 22:59:20ID24544522
Topic: Active Server Pages (ASP)
Participating Experts: 1
Points: 500
Comments: 19

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: hieloPosted on 2009-07-05 at 11:29:56ID: 24780831

I did not try your script, but in dynamic.asp you have:
row=CInt(Request.Form("row"))
For n=1 to row

So in insert.asp
i=i-1

should be plus, NOT minus:
i=i+1

by: hieloPosted on 2009-07-05 at 11:32:23ID: 24780839

ignore that, I see it's outside the for construct.

by: hieloPosted on 2009-07-05 at 12:02:45ID: 24780956

I don't know why you would want to insert the records when the values do not match, but try this:

<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Dynamic.mdb") & ";"
objConn.Open
OrgID = request.Form("OrgID")
 
Set rsCount=CreateObject("ADODB.RecordSet")
sqlCount = "SELECT Count(Inserts.Status) AS CountOfStatus, Inserts.OrgID, Inserts.Status FROM Inserts GROUP BY Inserts.OrgID, Inserts.Status HAVING (((Inserts.OrgID)=11111) AND ((Inserts.Status)='NotMatch'))"
rsCount.open sqlCount, objConn,3,3
 
 
row=CInt(Request.Form("row"))
%>
                    	<form name="MyForm" action="insertAJAX.asp" method="post"> 
<%
For n=1 to row
	strMyFN = request.Form("FirstName_" & n)
	strMyLN = request.Form("LastName_" & n)
	strMyEmail = request.Form("Email_" & n)
	strMyCEmail = request.Form("ConfirmEmail_" & n)
 
	if strMyEmail <> strMyCEmail then
		response.Write"Emails Do Not Match: "
		response.Write""&strMyEmail&" - "&strMyCEmail&"<br /><br />"
		sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotMatch', "&OrgID&")"
		objConn.execute sqlMistake
'Next
 
		if not rsCount.eof then
			for i = 1 to rsCount("CountOfStatus")
				if i mod 1 = 0 then
				%><table style="font-size:9pt;">
     	                <tr>
          	           <td class="MyTD">Email</td>
               	      <td class="MyTD">Confirm Email</td>
                    	 <td class="MyTD">First Name</td>
	                     <td class="MyTD">Last Name</td>
     	                </tr>
          	          <tr>
               	     <td class="MyTD"><input type="text" value="<%=strMyFN%>" name="Email_<%=i%>" /></td>
                    	<td class="MyTD"><input type="text" value="<%=strMyLN%>" name="ConfirmEmail_<%=i%>" /></td>
	                    <td class="MyTD"><input type="text" value="<%=strMyEmail%>" name="FirstName_<%=i%>" /></td>
     	               <td class="MyTD"><input type="text" value="<%=strMyCEmail%>" name="LastName_<%=i%>" /><input type="hidden" value="NotActive" name="Status_<%=i%>"  /></td>
          	          </tr></table>
				<%
				end if
			next
			i=i-1
		end if
	end if
next
	%>
	 <input class="MyButton" name="" type="submit" value="Deactivate" />
	 <input type="hidden" value="11111" name="OrgID"  />
	 <input type="hidden" name="row" value="<%=i%>"/>
</form>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:

Select all Open in new window

by: carrzkissPosted on 2009-07-05 at 12:32:32ID: 24781043

Hello hielo
Hope you are doing well this 4th of July Weekend.

OK.
The i=i-1
Is used to Subtract the last number, that is why it is there.
-------
Save what DOES NOT MATCH.
OK. Let me explain this one to you a little bit.

This whole process looks at the field [Status]
If [NotMatch] then it will insert the record as [NotMatch]
Is the address's match it inserts as: [Status]=Matched
OK.
It looks through the records entered from there (Script not provided here as it works)
And sends out an email to all that are [Matched]
And the ones that are not Matched, will be sitting in limbo until they are Updated
From: [NotMatch]
to: [Matched]

I know that this may not be the way most would work this, but it is there incase
The person inserting the multiple records decides to leave and close out without
Finishing what s/he started.
Follow me?
This way once they come back, they can open the page up with the [NotMatch] Query
And complete the list.

That is why it needs to be inserted.
Hope that makes since to you, as it was an idea that came to me while I was trying to sleep.
And they are usuall my best ones, They better be, as I am loosing slept because of them :)

Going to test your code out now.
And thanks for the assistance so far.
Hope it works, as this is the last MAJOR addon to the site.

Carrzkiss

by: hieloPosted on 2009-07-05 at 13:13:00ID: 24781181

OK, if I understood correctly, you are trying to maintain values across sessions/visits. However, one of the issues not addressed in your post is that if I entered:
a@b.com and b@a.com

then I will see Not Matched and they will be inserted in the DB. If tomorrow I come back and enter those same values, then you db will have those values inserted again!

Not sure if this is acceptable, but if not, you need to query the db first to make sure they are not there already.

by: carrzkissPosted on 2009-07-05 at 13:44:50ID: 24781268

When you come back, you will be faced with the information that is already insterted into the database.
And also, I have code that (Not shown here)
That looks to see if the record exist, and if it does not, insert the record
If if does exist, then it lets you know that it is there, and to move on to the next one.

Thanks.
I had to tend to some issues, and now back.
So getting ready to test what you have here.

by: carrzkissPosted on 2009-07-05 at 19:04:56ID: 24782128

I need a fresh pair of eyes please.

If I put the form fields outside of
if i mod 1 = 0 then
It will show up, But will not count the fields.

If I put it inside (Like the code below) then their fields are not shown and the only thing that you get is a button.

Can you please have a look at it, and see if you can see where I have gone wrong?

-----------
The Insert of:
Good Emails
&
Bad Emails
Are working great, I just have to do the Update, as you can see in the form below.

Thanks Hielo.
Carrzkiss

<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Dynamic.mdb") & ";"
objConn.Open
OrgID = request.Form("OrgID")
 
Set objRs1=CreateObject("ADODB.RecordSet")
getSQL1 = "SELECT ID, Email, MyFN, MyLN, Status, OrgID FROM Inserts where Status='NotMatch' and OrgID="&OrgID&""
objRs1.Open getSQL1, objConn, 3,3 
 
Set rsCount=CreateObject("ADODB.RecordSet")
sqlCount = "SELECT Count(Inserts.Status) AS CountOfStatus, Inserts.OrgID, Inserts.Status FROM Inserts GROUP BY Inserts.OrgID, Inserts.Status HAVING (((Inserts.OrgID)=11111) AND ((Inserts.Status)='NotMatch'))"
rsCount.open sqlCount, objConn,3,3
 
 
row=CInt(Request.Form("row"))
%><table style="font-size:9pt;">
                    	<form name="MyForm" action="new.asp" method="post"> 
                        
     	                <tr>
          	           <td class="MyTD">Email</td>
               	      <td class="MyTD">Confirm Email</td>
                    	 <td class="MyTD">First Name</td>
	                     <td class="MyTD">Last Name</td>
     	                </tr>
          	          <tr>
<%
For n=1 to row
	strMyFN = request.Form("FirstName_" & n)
	strMyLN = request.Form("LastName_" & n)
	strMyEmail = request.Form("Email_" & n)
	strMyCEmail = request.Form("ConfirmEmail_" & n)
%>
 <%
 if strMyEmail = strMyCEmail then
 if not objRs1.eof then
 else 
  		sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotActive', "&OrgID&")"
		objConn.execute sqlMistake
		end if
elseif strMyEmail = strMyCEmail then		
if not objRs1.eof then
else
sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [Status]='"&strMyStatus&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
objConn.execute sqlOrgUpdate
end if		
 %>
 
 
<% 
	elseif strMyEmail <> strMyCEmail then
%>
<%
		if not objRs1.eof then
		else
		sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotMatch', "&OrgID&")"
		objConn.execute sqlMistake
'Next
%>
<%
		if not rsCount.eof then
			for i = 1 to rsCount("CountOfStatus")
				if i mod 1 = 0 then
				%>
 
<td class="MyTD"><input type="text" value="<%=strMyFN%>" name="Email_<%=i%>" /></td>
                    	<td class="MyTD"><input type="text" value="<%=strMyLN%>" name="ConfirmEmail_<%=i%>" /></td>
	                    <td class="MyTD"><input type="text" value="<%=strMyEmail%>" name="FirstName_<%=i%>" /></td>
     	               <td class="MyTD"><input type="text" value="<%=strMyCEmail%>" name="LastName_<%=i%>" />
                       <input type="hidden" value="NotActive" name="Status_<%=i%>"  /></td>
          	          </tr>
				<%
				end if
			next
			i=i-1
		end if
	end if
end if
next
	%>
     <%if objRs1("Status")="NotMatch" then%>
	 <input class="MyButton" name="" type="submit" value="Correct and Resend Through" style="height:25px" />
     <%else%>
     <input class="MyButton" name="" type="submit" value="Send Through" />
     <%end if%>
	 <input type="hidden" value="11111" name="OrgID"  />
	 <input type="hidden" name="row" value="<%=i%>"/>
</form></table>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:

Select all Open in new window

by: hieloPosted on 2009-07-05 at 20:51:42ID: 24782407

Indent your code. You should see TWO identical conditions on lines 36 and 42:

strMyEmail = strMyCEmail then

Thus, when strMyEmail EQUALS strMyCEmail , the elseif clause will not execute ever!

by: hieloPosted on 2009-07-05 at 21:15:31ID: 24782481

Below is probably close to what you are after. Read the comments in the code.

<%
' http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24544522.html
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Dynamic.mdb") & ";"
objConn.Open
OrgID = request.Form("OrgID")
 
Set objRs1=CreateObject("ADODB.RecordSet")
getSQL1 = "SELECT ID, Email, MyFN, MyLN, Status, OrgID FROM Inserts where Status='NotMatch' and OrgID="&OrgID&""
objRs1.Open getSQL1, objConn, 3,3 
 
Set rsCount=CreateObject("ADODB.RecordSet")
sqlCount = "SELECT Count(Inserts.Status) AS CountOfStatus, Inserts.OrgID, Inserts.Status FROM Inserts GROUP BY Inserts.OrgID, Inserts.Status HAVING (((Inserts.OrgID)=11111) AND ((Inserts.Status)='NotMatch'))"
rsCount.open sqlCount, objConn,3,3
 
 
row=CInt(Request.Form("row"))
%> <form name="MyForm" action="new.asp" method="post"> <table border=1 style="font-size:9pt;">
                       
                        
                        <tr>
                           	<td class="MyTD">Email</td>
                      		<td class="MyTD">Confirm Email</td>
                         	<td class="MyTD">First Name</td>
                             <td class="MyTD">Last Name</td>
                        </tr>
                          
<%
For n=1 to row
        strMyFN = request.Form("FirstName_" & n)
        strMyLN = request.Form("LastName_" & n)
        strMyEmail = request.Form("Email_" & n)
        strMyCEmail = request.Form("ConfirmEmail_" & n)
 
	if strMyEmail = strMyCEmail then
		if not objRs1.eof then
		else 
                sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotActive', "&OrgID&")"
                objConn.execute sqlMistake
		end if
'### this commnted "elseif" clause (lines 44-49) will never execute due to the if on line 36. I think what you need to do
'### is to move/execute the "Update" statement (currently on line 47) within the first "if" clause  (line 56) of your else clause (line 54) below
'	elseif strMyEmail = strMyCEmail then            
'		if not objRs1.eof then
'	else
'			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [Status]='"&strMyStatus&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
'			objConn.execute sqlOrgUpdate
'		end if
 
	'### no need to do an elseif here. When the if above (line 36) fails, then it is already implied
	'### that strMyEmail <> strMyCEmail. A simple else will suffice.
	'elseif strMyEmail <> strMyCEmail then
	else
		if not objRs1.eof then
			'### NOTE: you on the commented line above (line 47) you have ..., [Status]='"&strMyStatus&"', ...
			'but strMyStatus is not defined anywhere (that I can see). So when the statement below is executed, the value of
			'Status is set to an empty string. Not sure if this is what you intended
			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [Status]='"&strMyStatus&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
			objConn.execute sqlOrgUpdate
		else
			sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotMatch', "&OrgID&")"
			objConn.execute sqlMistake
 
			if not rsCount.eof then
				for i = 1 to rsCount("CountOfStatus")
					if i mod 1 = 0 then %>
					<tr>
					<td class="MyTD"><input type="text" value="<%=strMyFN%>" name="Email_<%=i%>" /></td>
					<td class="MyTD"><input type="text" value="<%=strMyLN%>" name="ConfirmEmail_<%=i%>" /></td>
					<td class="MyTD"><input type="text" value="<%=strMyEmail%>" name="FirstName_<%=i%>" /></td>
					<td class="MyTD"><input type="text" value="<%=strMyCEmail%>" name="LastName_<%=i%>" />
					<input type="hidden" value="NotActive" name="Status_<%=i%>"  /></td>
					</tr>
					<%
					end if
				next
				i=i-1
			end if
		end if
	end if
Next
'### there may be instances when objRs1 will NOT return any records, so you need to make
'### sure it is NOT at EOF
If Not objRs1.EOF Then
	if objRs1("Status")="NotMatch" then %>
         <input class="MyButton" name="" type="submit" value="Correct and Resend Through" style="height:25px" />
     <%else%>
     <input class="MyButton" name="" type="submit" value="Send Through" />
     <%end if
End If
%>
         <input type="hidden" value="11111" name="OrgID"  />
         <input type="hidden" name="row" value="<%=i%>"/>
</table></form>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:

Select all Open in new window

by: carrzkissPosted on 2009-07-05 at 21:38:40ID: 24782539

Had to do some changes in the code.
Works the same as it did before, still cannot get the Fields to show up

The fields that I am referring to are the ones that will show the
incorrect email address's so they can be edited.

Anyway.
Going to work on this a little longer this evening and see if I can figure out what is stopping it.

Have a good one Hielo.

Carrzkiss

by: hieloPosted on 2009-07-06 at 05:54:17ID: 24784660

>>If I put it inside (Like the code below) then their fields are not shown and the only thing that you get is a button.
I think that is because when you do the update, you are changing the status FROM 'NotMatch' to '', so when the query is done the next time around, no matches are found because the status is no longer 'NotMatch'

>>If I put the form fields outside of
if i mod 1 = 0 then
It will show up, But will not count the fields.
Don't know what you mean by "But will not count the fields.", but if you move it outside the outermost For...Next, I think it will do what you expect (I think).

<%
' http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24544522.html
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Dynamic.mdb") & ";"
objConn.Open
OrgID = request.Form("OrgID")
 
Set objRs1=CreateObject("ADODB.RecordSet")
getSQL1 = "SELECT ID, Email, MyFN, MyLN, Status, OrgID FROM Inserts where Status='NotMatch' and OrgID="&OrgID&""
objRs1.Open getSQL1, objConn, 3,3 
 
Set rsCount=CreateObject("ADODB.RecordSet")
sqlCount = "SELECT Count(Inserts.Status) AS CountOfStatus, Inserts.OrgID, Inserts.Status FROM Inserts GROUP BY Inserts.OrgID, Inserts.Status HAVING (((Inserts.OrgID)=11111) AND ((Inserts.Status)='NotMatch'))"
rsCount.open sqlCount, objConn,3,3
 
 
row=CInt(Request.Form("row"))
%> <form name="MyForm" action="new.asp" method="post"> <table border=1 style="font-size:9pt;">
                       
                        
                        <tr>
                           	<td class="MyTD">Email</td>
                      		<td class="MyTD">Confirm Email</td>
                         	<td class="MyTD">First Name</td>
                             <td class="MyTD">Last Name</td>
                        </tr>
                          
<%
For n=1 to row
        strMyFN = request.Form("FirstName_" & n)
        strMyLN = request.Form("LastName_" & n)
        strMyEmail = request.Form("Email_" & n)
        strMyCEmail = request.Form("ConfirmEmail_" & n)
 
	if strMyEmail = strMyCEmail then
		if not objRs1.eof then
		else 
                sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotActive', "&OrgID&")"
                objConn.execute sqlMistake
		end if
'### this commnted "elseif" clause (lines 44-49) will never execute due to the if on line 36. I think what you need to do
'### is to move/execute the "Update" statement (currently on line 47) within the first "if" clause  (line 56) of your else clause (line 54) below
'	elseif strMyEmail = strMyCEmail then            
'		if not objRs1.eof then
'	else
'			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [Status]='"&strMyStatus&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
'			objConn.execute sqlOrgUpdate
'		end if
 
	'### no need to do an elseif here. When the if above (line 36) fails, then it is already implied
	'### that strMyEmail <> strMyCEmail. A simple else will suffice.
	'elseif strMyEmail <> strMyCEmail then
	else
		if not objRs1.eof then
			'### NOTE: you on the commented line above (line 47) you have ..., [Status]='"&strMyStatus&"', ...
			'but strMyStatus is not defined anywhere (that I can see). So when the statement below is executed, the value of
			'Status is set to an empty string. Not sure if this is what you intended
'### IMPORTANT: On the else clause below you are setting status to 'NotMatch', but here you are changing that value to empty string, so on next query you will not get any results! Hence, I altered your original (commented) query below
'			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [Status]='"&strMyStatus&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
			objConn.execute sqlOrgUpdate
		else
			sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotMatch', "&OrgID&")"
			objConn.execute sqlMistake
		end if
 
 
	end if
Next
 
if not rsCount.eof then
	for i = 1 to rsCount("CountOfStatus")
		if i mod 1 = 0 then %>
			<tr>
			<td class="MyTD"><input type="text" value="<%=strMyFN%>" name="Email_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyLN%>" name="ConfirmEmail_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyEmail%>" name="FirstName_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyCEmail%>" name="LastName_<%=i%>" />
			<input type="hidden" value="NotActive" name="Status_<%=i%>"  /></td>
			</tr>
			<%
		end if
	next
	i=i-1
end if
 
'### there may be instances when objRs1 will NOT return any records, so you need to make
'### sure it is NOT at EOF
If Not objRs1.EOF Then
	if objRs1("Status")="NotMatch" then %>
         <input class="MyButton" name="" type="submit" value="Correct and Resend Through" style="height:25px" />
     <%else%>
     <input class="MyButton" name="" type="submit" value="Send Through" />
     <%end if
End If
%>
         <input type="hidden" value="11111" name="OrgID"  />
         <input type="hidden" name="row" value="<%=i%>"/>
</table></form>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:

Select all Open in new window

by: carrzkissPosted on 2009-07-06 at 11:45:21ID: 24788011

OK.
Got it fixed, had to move the NEXT to the lowest part of the statement.
Above the button. Now it displays the information.
BUT
It is duplicating it,
2 records are inserted as
Status='NotMatch'
1 record is inserted for
Status='NotActive'

It reads the Fields correctly, BUT duplicates them.
It was doing it Triple, and I fixed that, now it is duplicating and I cannot seem to find where
It is happening at.

But, this is a working code, and records to do.
And they do count
ConfirmEmail_<%=i%>
ConfirmEmail_1 exc......
So that is taken care of.
Just need some fresh eyes to help me get rid of the duplicating.

Thanks again Hielo.
Carrzkiss

<%
' http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_24544522.html
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("Dynamic.mdb") & ";"
objConn.Open
OrgID = request.Form("OrgID")
 
Set objRs1=CreateObject("ADODB.RecordSet")
getSQL1 = "SELECT ID, Email, MyFN, MyLN, Status, OrgID FROM Inserts where Status='NotMatch' and OrgID="&OrgID&""
objRs1.Open getSQL1, objConn, 3,3 
 
Set rsCount=CreateObject("ADODB.RecordSet")
sqlCount = "SELECT Count(Inserts.Status) AS CountOfStatus, Inserts.OrgID, Inserts.Status FROM Inserts GROUP BY Inserts.OrgID, Inserts.Status HAVING (((Inserts.OrgID)=11111) AND ((Inserts.Status)='NotMatch'))"
rsCount.open sqlCount, objConn,3,3
 
 
row=CInt(Request.Form("row"))
%> <form name="MyForm" action="new.asp" method="post"> <table border=1 style="font-size:9pt;">
                       
                        
                        <tr>
                           	<td class="MyTD">Email</td>
                      		<td class="MyTD">Confirm Email</td>
                         	<td class="MyTD">First Name</td>
                             <td class="MyTD">Last Name</td>
                        </tr>
                          
<%
For n=1 to row
        strMyFN = request.Form("FirstName_" & n)
        strMyLN = request.Form("LastName_" & n)
        strMyEmail = request.Form("Email_" & n)
        strMyCEmail = request.Form("ConfirmEmail_" & n)
 
	if strMyEmail = strMyCEmail then
		if not objRs1.eof then
		else 
                sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotActive', "&OrgID&")"
                objConn.execute sqlMistake
		end if
	else
		if not objRs1.eof then
			sqlOrgUpdate = "Update Inserts Set [MyFN]='"&strMyFN&"', [MyLN]='"&strMyLN&"', [Email]='"&strMyEmail&"', [OrgID]="&OrgID&" where Email='"&strMyEmail&"'"
			objConn.execute sqlOrgUpdate
		else
			sqlMistake = "Insert into Inserts (MyFN, MyLN, Email, Status, OrgID)Values('"&strMyFN&"','"&strMyLN&"','"&strMyEmail&"','NotMatch', "&OrgID&")"
			objConn.execute sqlMistake
		end if
 
if not rsCount.eof then
	for i = 1 to rsCount("CountOfStatus")
		if i mod 1 = 0 then %>
			<tr>
			<td class="MyTD"><input type="text" value="<%=strMyFN%>" name="Email_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyLN%>" name="ConfirmEmail_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyEmail%>" name="FirstName_<%=i%>" /></td>
			<td class="MyTD"><input type="text" value="<%=strMyCEmail%>" name="LastName_<%=i%>" />
			<input type="hidden" value="NotActive" name="Status_<%=i%>"  /></td>
			</tr>
			<%
		end if
	next
	i=i-1
end if
end if
Next 
 
If Not objRs1.EOF Then
	if objRs1("Status")="NotMatch" then %>
         <input class="MyButton" name="" type="submit" value="Correct and Resend Through" style="height:25px" />
     <%else%>
     <input class="MyButton" name="" type="submit" value="Send Through" />
     <%end if
End If
%>
         <input type="hidden" value="11111" name="OrgID"  />
         <input type="hidden" name="row" value="<%=i%>"/>
</table></form>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:

Select all Open in new window

by: carrzkissPosted on 2009-07-07 at 14:18:01ID: 24798586

It looks like I might have to ditch this idea.
And just hope that people enter the information correctly the first time.

------
The last code that I posted above will duplicate a single records.
IF ONLY there is 2 records matching the Status='NotMatch'

BUT IF
1 Record is: Status='NotMatch'
1 Record is: Status='NotActive'
THEN nothing is shown.

BUT IF
2 Record is: Status='NotMatch'
1 Record is: Status='NotActive'

It will Duplicate the 'NotMatch' Records.

This is getting weird.
And I have ran out of idea's.
So, until it can be resolved, I am going to temporaily remove it from the site.
And add it back in when it is fixed.

If you have any idea's please send them along.

Carrzkiss

by: hieloPosted on 2009-07-07 at 17:29:58ID: 24799720

The explanation you posted above (ID:24781043) is relevant to what your initial implementation. Basically, it's "technical". What I would like is for you to give a description of your application. What is your ultimate goal?

I find the "Deactivate" button very confusing because when I do "insert.asp?amt=2" and the email addresses do not match, then you shouldn't be allowed to "Deactivate" something that shouldn't exist to begin with. To clarify, the way I understand this, a record with "NotMatch" is like saying "Doesn't exists yet" - or at least that is what makes sense to me.

To aggravate matters, your other status is "NotActive", so how can you "Deactivate" something that is not active? Then again, I am thinking of deactivating an account, not sure what "Deactivate" means to you.

Again, what is the purpose of your application? Don't explain it to me in technical terms (since clearly the technical implementation is not working out for you). Imagine I am NOT a programmer, and explain to me what you are trying to do.

by: carrzkissPosted on 2009-07-07 at 17:53:41ID: 24799811

<"Deactivate"> was mis-named for this example here.
So pay that no never mind.

Lets say you have

5 sets of Fields

FName, LName, Email

OK.
Now, when you submit all 5 fields in inserts into the database.
BUT, what if you mis-type one of the email address's?

SO.
We do a <Form> Field set like this

FName, LName, Email, VerifyEmail

Now.

if strEmail <> strVerifyEmail then
Do not match
Else
Insert the records.

---------------
So, it is identical as what I have in the demo when run.
If the emails match insert them with
match -----> Status="NotActive"
Dont match -----> Status='NotMatch'

---------
Pretty straight forward, as I thought my body of this post was.

Insert Multiple Records with the exception OF.... Being able to change the information when it is wrong.

I do not know how else to explain, it is as it is.
==========================================================
>>(since clearly the technical implementation is not working out for you).
What exactly do you mean by this?
I explained it to you, none of the information supplied here is in any form of "technical"
Maybe a little "Over- Explained" But nothing that I have written here is Technical.
So, I am a little taken back on that remark.
==========================================================

If you still do not understand it, let me know, and we can close this one out.
As I have already removed it from the project, and on doing a simple
1 email
Form.

Let me know.
Carrzkiss

by: hieloPosted on 2009-07-07 at 20:01:55ID: 24800227

Is this an account management application or an account creation application?

Consider this: if you type insert.asp?amt=1, one row of fields will be generated (and the user intends to enter test@company.com), but let's say that there were 3 previous entries in the db, one of which is test@company.com. Of course, you don't know ahead of time that the user intended to enter test@company.com, so should insert.asp first find out what is already "NotMatch" in the db AND additionally supply 1 empty row (due to amt=1) so that user may fix all previous "bad entries" and additionally enter any new one?

by: carrzkissPosted on 2009-07-07 at 20:21:52ID: 24800296

OK.
amt=1 (or) whatever.
That is a payment system, once the link draws back to the page.
We read the String, and we create the number of rows needed according to the Query.
So
amt=1
Then enter 1 Set of Form Fields
amt=50
Then create a Form with 50-sets of fields, to create 50 New records.
And these records get inserted.

Now.
This program it is "Highly Unlikely" that anyone will be entering a duplicate email address.
There are several reason's to that fact, BUT, in case, it need to check if the record exist
And then move on to the next, and display
These records Exist, and let the user know.
(Link to open question for the IF Record Exist in Multiple Insert
http://www.experts-exchange.com/Q_24551756.html (These are handled differenlt))

Let me know if you need to know anything else.
The other link is a TOP PRIORITY, so if you could assist on that one, that would be create.
This issue for this post, can be added in later on, but the link above needs to be tended to
As soon as humanly (or computerly) possible.

Carrzkiss

by: carrzkissPosted on 2009-07-16 at 18:47:12ID: 24875388

I have gone a different route on this issue.
I am going to Award Hielo points for the simple fact,
That he tried to assist in this issue.

Have a good one.
Carrzkiss

by: carrzkissPosted on 2009-07-16 at 18:48:45ID: 31599874

Thanks for your assist Hielo.

have a good one.
Carrzkiss

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

Dynamic Insert with Field Check ... (A challenge)

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Active Server Pages (ASP)

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press