Inserting multipe values dynamically and faster

Want to insert values extracted from another database into another database that has a totally different format? Try this tutorial and you might be surprised how easy it is. If it doesn't work for you, trust me, you'd get the idea.

Alright, I was facing a dillema of extracting e-mail addresses from a column in a particular database and inserting those email addresses into a mailinglist database for the registered users so they wouldn't have to do it themselves. I wanted a quick and easy way to just run a page with a stored procedure or something of that sort that will insert these email addresses easily so I decided to mess around with DWMX and came up with an easy way to do it.

TakeOut.mdb >> There is a column with email addresses in here and we want to take it out and move it somewhere else

PutIn.mdb >> We will put the email addresses here automatically

To do in MS Access:

Assuming the name of the initial column with email addresses is called "email-address" in the TakeOut.mdb file, right click on the column and select copy. (Refer to the image on the left)

To do in a notepad or Textpad:

Paste the copied values on a blank txt file. It will look something like

email1@me.com
email2@tree.org
email3@what.net
.
.
emaillast@last.com

Do whatever you can to have the values in this format.

"email1@me.com", "email2@tree.org", "email3@what.net", . . . . "emaillast@last.com"

so we can easily set the email addresses as values of an array we will be using later.

To do in DWMX

Open DWMX and create a new ASP + VBScript page. This page will be used to insert all the values we want to insert.
Click on the Server Behaviors tab and select "Command"
Type the command name and select your connection.
Select the Type as "Insert" for obvious reasons.
Double Click on the Database Items, then highlight the column we wish to insert the email addresses into and Click on the "COLUMN" Button
Click on OK
(Refer to the image)

If you change the view to the Code View, you should have something like this in there

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>


<%

set cmdInsertemail = Server.CreateObject("ADODB.Command")
cmdInsertemail.ActiveConnection = MM_newsletter_STRING
cmdInsertemail.CommandText = "INSERT INTO users (email) VALUES ( ) "
cmdInsertemail.CommandType = 1
cmdInsertemail.CommandTimeout = 0
cmdInsertemail.Prepared = true
cmdInsertemail.Execute()

%>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
</body>
</html>

We now need to change the code and mess around with it.

Insert this code right after ""

<%
Dim vals
Dim count
Dim i
vals = array ()
%>

Here is what we are trying to do:

Use the "For. . .Next" Statement to insert every value in the array into the database with the command function. Plain and simple.

Remember the email addresses we copied and changed in Notepad that looks something like "email1@me.com"?

If you have not copied it, you should do that now and paste it inside the code above to make it look like this

1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
2. 

<%
Dim vals
Dim cnt
Dim i
vals = Array ("email1@me.com", "email2@tree.org", "email3@what.net","emaillast@last.com")
cnt = Ubound(vals) - Lbound(vals) + 1 'This is to count. You don't need this if you know the number of elements in the array
%>

' --------- THIS IS THE MAIN CODE THAT CALLS THE FUNCTION TO INSERT THE VALUES ------------
<%
For i = 0 to 151 ' replace 151 with cnt if you don't know the number of elements in the array
getin(vals(i))
Response.Write ("Inserted " & vals(i) & "<br>")
Next
%>

'------------THIS IS THE MAIN FUNCTION BEING CALLED TO INSERT VALUES ----------
<%

Function getin(what) ' a function that inserts the emails automatically

Dim nwCmdText
nwCmdText = "INSERT INTO users (email) VALUES ('" & what & "')" 'this is dynamic based on the value of what

set rsinsert = Server.CreateObject("ADODB.Command")
rsinsert.ActiveConnection = MM_newsletter_STRING
rsinsert.CommandType = 1
rsinsert.CommandTimeout = 0
rsinsert.Prepared = true
rsinsert.CommandText = nwCmdText
rsinsert.Execute()

End Function

%>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
</body>
</html>

Save and call the file from your browser. It should work.

Alright, time to decipher the code and explain how it works

We use the "For . . . Next" Statement to call a function we call getin()
The function receives an arguement we call "what" - The argument is dynamic
Copy and paste the command we created in the function
We changed the commandText and made it dynamic based on the argument received by the function.

Email me if it doesn't work

Comments

Scattered

April 28, 2003 by lynnette Browne

I understood it, but, what about those who are at the beginning of learning dnyamic development? they won't get it. Idea is good, but not well presented, therefore the resulting low rating from readers.

You must me logged in to write a comment.