dynamic database editing, large contact list edits

Dev Shed Forums Sponsor:

September 2nd, 2003, 06:08 PM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 10 m 22 sec
Reputation Power: 6

dynamic database editing, large contact list edits

My task was to develope a contact list manager for some emails stuff we did. Rather than deal with the official company contact lists stuff, we just did it internally and with quite some ease. This code i'm just posting to maybe help show how to do dynamic database editing *EASILY*. Basically, we want to send out emails to users. And the database is set up with lets say i have an email called INFO (which i do), i need a to line, a from line, and a CC line for all the email contacts. My database has a column for fINFO, sINFO, ccINFO with a 1 in the persons name (each row is a person's email adddy) if they are to receive the email. This is easily displayed, if you need the code it's like 10 lines, but i can hook you up if you need it.
The beauty about this, is you can have a page where you can create a new email all through the webfront, you just have the user enter the name of the email, and then you can just add the s, f, cc in to columns. What's nice is if you send email with CDO, or another component, just called the function to send the mail the name the call it in the DB, and then we're really cooking with automation!
Next, is just manipulating request.form. It just grabs all the info and splits on & and =. I'm not good at explaining what i do, you'll just have to look at it and get it. But it's manipulating that object and completely autojmating the SQL line so you can edit the database anyway you want through the webfront and NEVER again deal with changing sql statements and the such.
I didn't show the datagrid display which shows the original complete db listing but that's a class and you can display your own info i'm sure. This is just some source code to get you on your way and hopefully get you thinking about automating things easily, well I at least think it's a cool way to have a centralized email, contactlist, and data center, hope this helps, peace

Code:


<!--#include file=credentials_check.asp-->
<%
       	dim Conn, RS, strSQL	
       	set Conn = server.CreateObject("ADODB.CONNECTION")
		Conn.Open "webcast", "webcast", "web"

	if request.querystring("go") = "yes" then
		'response.write request.form & "<br>"
		dim A,B,G
		A = split(request.form,"&")
		strSQL="update [rpc1183].[webcast].[dbo].contacts SET "
		for each item in A
			B = split(item,"=")
			strSQL = strSQL & B(0) & "='" & B(1) & "',"

		next
		G = split(strSQL,",submit")
		strSQL = G(0)
		strSQL = strSQL & " WHERE ID ='" & request.form("id") & "'"
		response.write strSQL
		Conn.execute(strSQL)
		response.redirect "contact_list.asp?message=yes"
	end if
%>
<html><head><title>Main Webcast Admin Page</title></head>

<body BACKGROUND="/bkgrdweb4.gif">	
	<div align="left">
	<!--#include file="links.asp"-->
	<!--#include file="TablePrintE.asp"-->
		<!-- ******************* BODY BEGIN ****************************-->
		  <table border="2" bordercolor="#000000" style="border-collapse: collapse" cellpadding="2" cellspacing="0">
          <tr><td align="center" colspan="2"><b><center><font Color="maroon" Size=6>KEY</font></center></b></td></tr>
          <tr><td><font Color="336699" Size=4>Prefix</font></td></tr>
          <tr><td><font Color="purple" Size=4>S</font></td><td><font Color="darkblue" Size=4>To: line in email</font></td></tr>
          <tr><td><font Color="purple" Size=4>F</font></td><td><font Color="darkblue" Size=4>From: line in email</font></td></tr>
          <tr><td><font Color="purple" Size=4>CC</font></td><td><font Color="darkblue" Size=4>Carbon Copy: (cc:) line in email</font></td></tr>
          <tr><td><font Color="336699" Size=4>PostFix</font></td></tr>
          <tr><td></td><td><font Color="darkblue" Size=4>Name of the cooresponding email</font></td></tr>
          <tr><td><font Color="336699" Size=4>Value</font></td></tr>
          <tr><td><font Color="purple" Size=4>1</font></td><td colspan="2"><font Color="darkblue" Size=4>Add them to List</font></td></tr>
          <tr><td><font Color="purple" Size=4>anything else</font></td><td colspan="2"><font Color="darkblue" Size=4>Not on list</font></td></tr>
          </table>
          <HR Size="2" Color=Black >

          <form name="myform" Method="POST"  form action="contact_edit.asp?go=yes">
<% 		
		strSQL = "select * from [rpc1183].[webcast].[dbo].contacts where id='" & request.querystring("id") & "'"
		'response.write strSQL & "<br>"
		set RS = Conn.Execute(strSQL)
		response.write TablePrintE(RS)
		conn.close
%>
	<br>
		<input type="submit" value="submit" name="submit">
	<br>	
	<br>
		</form>
		
		<!-- ******************* BODY END  *****************************-->
		<td height="19" width="120" valign="top">
      <p align="left">&nbsp;</td></tr>
		<tr><td height="19" width="150">
          <p align="left">footer</td></tr>
    </table>
    </div>
</body>
</html>

tableprintE code

Code:

<%
Function TablePrintE (objRS)

Dim strT                                 ' table html string
Dim fldF                                 ' current field object

While Not objRS.EOF                      ' now build the rows

	strT = "<TABLE BORDER=1>"   ' build the table header


	For Each fldF In objRS.Fields            ' each field as a table row name
		strT = strT & "<TR ALIGN=LEFT>"
		strT = strT & "<TD><B>" & fldF.Name & "&nbsp</B></TD>"
		strT = strT & "<TD><input type=""text"" name=""" & fldF.Name & """ value=""" & fldF.Value & """>&nbsp</TD>"
		strT = strT & "</TR>"
	Next
   
	objRS.MoveNext
Wend

strT = strT & "</TABLE>"
TablePrintE = strT                        ' and finally return the table

End Function
%>

Last edited by unatratnag : September 2nd, 2003 at 06:13 PM.

September 3rd, 2003, 09:31 AM

Vlince

Contributing User

Join Date: Jun 2003

Location: Canada, Quebec, Montreal

Posts: 410

Time spent in forums: < 1 sec
Reputation Power: 6

One things missing...

I don't see anywhere RS.Close and Set RS = nothing

Also...What I would add, but that's just me, is if I plan to reuse the function TablePrintE() more then once, then I'd look into doing this(adding validation):

------------------------------------------------------------------
Function TablePrintE (objRS)

Dim strT ' table html string
Dim fldF ' current field object

If IsObject(objRS) Then

objRS.MoveFirst

Else

Response.Write "the parameter is expecting an object!"
Response.End

End If

While Not objRS.EOF ' now build the rows
. . .
. .
.
------------------------------------------------------------------

Just to be on the safe side, the validation would be important and so is the MoveFirst, but again, like I said, only if you'd plan on reusing the function more then once...

Sincerely

Vlince

September 3rd, 2003, 09:37 AM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 10 m 22 sec
Reputation Power: 6

Quote:

conn.close

look harder next time

I don't like RS.movefirst, and I don't know why i'd ever display this table twice, this is to edit a single record, the actualy datagrid display which is a disconnected record set has that since you display that multiple times, but there's no way to come back to this page without reselecting a new or same key, in which case i query the DB, no need to hang onto the recordset so no need for RS.movefirst.

September 3rd, 2003, 09:42 AM

Vlince

Contributing User

Join Date: Jun 2003

Location: Canada, Quebec, Montreal

Posts: 410

Time spent in forums: < 1 sec
Reputation Power: 6

Ok I guess you're right, if your only going to use it once(the function that is) but conn.close is **NOT** the same as the RS object you've IMPLICITly created!

so I'll ask again

where is the RS.Close and Set RS = nothing

Now, I know you close the connection but I'm talking about the recordset but its no biggy

Sincerely

Vlince

September 3rd, 2003, 11:34 AM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 10 m 22 sec
Reputation Power: 6

aaaaahhhh, i just saw the .close and didn't read it really. Yeah, i never close record sets, i see no need to, but that's opinion. Record set objects are not persistant and destroyed once it's done interpreting server side code i believe.

but in anycase, just add rs.close or set = nothing for all those out there who like to do that *shrug*

Last edited by unatratnag : September 3rd, 2003 at 11:38 AM.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: