No overwriting of database with asp

flashcdog · #1 (**permalink**) 04-27-04, 12:25

*** Sorry... the first post didn't go through correctly so I am posting again.***

What I have is a Flash front-end with and asp page acting as the connection to the database. I am passing some standard variables from the Flash form to the asp page and then on to the database.

Keep in mind ASP noob...

What I want to know is what do I need to add the the asp page to see if an email field for a record already exists and then send the variable back using the asp page. I want to keep users from overwriting their entries. Basically a single submission to the database per email. I know what to do with it in Flash once I receive it, but how do I get that?

Does any of that make sense?

Here is the code for the ASP page writing to the database from the Flash form.

Obviously any help would be appreciated.

<%

'Declare the variables
name = Request.Form("fname")
email = Request.Form("femail")
address1 = Request.Form("faddress1")
address2 = Request.Form("faddress2")
city = Request.Form("fcity")
state = Request.Form("fstate")
zipcode = Request.Form("fzipcode")

set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("tonytest.mdb")
conn.Open DSNtemp

'name = "true"
'email = "true"
'address1 = "true"
'address2 = "true"
'city = "true"
'state = "true"
'zipcode = "true"

SQLstmt = "INSERT INTO table1 (name,email,address1,address2,city,state,zipcode)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & name & "',"
SQLstmt = SQLstmt & "'" & email & "',"
SQLstmt = SQLstmt & "'" & address1 & "',"
SQLstmt = SQLstmt & "'" & address2 & "',"
SQLstmt = SQLstmt & "'" & city & "',"
SQLstmt = SQLstmt & "'" & state & "',"
SQLstmt = SQLstmt & "'" & zipcode & "'"
SQLstmt = SQLstmt & ")"

Set RS = conn.execute(SQLstmt)

Conn.Close

set conn = nothing

response.write("Database record created SUCCESSFUL!")
%>

Seppuku · #2 (**permalink**) 04-27-04, 13:21

Is there a reason you want to go through an ASP page to post/get data from a DB? Flash provides access to the DB directly.

The best ASP can can do to return the results of your query (whether they have an email address or not) is to Response.Write it out to the browser. How are you planning to handle the response (within Flash or just a HTML page)? This is extra work when Flash can do it all natively...

eduardob · #3 (**permalink**) 04-27-04, 13:36

where can I see the time when the register of a table was modified
ex: when you want to know the date when a register of a table was
modified you do a select of the table and can see it, but it doesn't show
the time. how can I see the time

flashcdog · #4 (**permalink**) 04-27-04, 14:58

Are you referring "Flash Remoting"?

I have always been under the impression that flash always requires a middleware of some sort... asp, php, cold fusion etc.

Is this something new with Flash MX 2004 Professional?

I am certainly no actionscript genius... so if you could expand on it I would really like to know.

Seppuku · #5 (**permalink**) 04-27-04, 15:59

Unfortunately I don't have my Flash '04 kPro book with me, nor am I a Flash expert, but I know that Flash can interface with DBs natively. You may want to Google for a tutorial related to such a setup.

flashcdog · #6 (**permalink**) 04-27-04, 17:27

Seppuku... I would never tell someone they are wrong

, especially since I consider myself a novice at all programming environments... including actionscript. but there is no "native" connection to databases with flash, UNLESS you are using Flash Remoting. Since flash runs client side there is no way for it to connect to a database. Perhaps what you have read is that using Flash Remoting it can connect... however this is a server side scripting version of actionscript.

New components in MX 2004 Pro do allow you to connect to "Web Services" like SOAP, but there again they are running server side.

Unless someone is willing to buy hosting that has "Flash Remoting" installed the other server side scripting is required.

Any suggestions on my original post.

Seppuku · #7 (**permalink**) 04-27-04, 17:59

Sorry.. I consider that native support - aka: you don't have to write your own ODBC gateway.

Go with XML. I have designed some Flash to interface with XML, and it's relatively easy. Create an XML object, connect to an ASP page and pass a URL param being the email addr you want to check. The ASP page will execute, using the URL param to query the DB, then write out some XML to yield the results of the query. This way you never even have to leave the Flash page.

Flash ActionScript Example:

Code:

var xmlData = new Object;
xmlObj = new XML();
xmlObj.ignoreWhite = true;
xmlObj.onLoad = function(success){
  if(success){
    processXMLData(xmlObj.firstChild);
  }
  else{
    // There was a problem getting the XML
  }
}
xmlObj.load("/testEmail.asp?Addr=" + txtEmailAddress.text);

// After you load it, you'll need to parse the XML
function processXMLData(xmlDoc_xml) {

  xmlData[xmlDoc_xml.nodeName] = xmlDoc_xml.firstChild.nodeValue;
  for (a in xmlDoc_xml.childNodes) {
		
    if(xmlDoc_xml.childNodes[a].hasChildNodes()){
      processXMLData(xmlDoc_xml.childNodes[a]);
    }
  }
}

if ( Number(xmlData.ErrorCode) == 0 )
    // The email address does not already exist.
else
    // The email address DOES already exist.

Then you would have some ASP:

Code:

<?xml version="1.0" standalone="no"?>
<%
'Do your SQL to determine if the email exists
'... insert connection code here...'
'... execute some SQL like:
'    SELECT Count(UserID) AS iTotalEmailAddrs FROM tblUsers WHERE EmailAddr LIKE '" & Trim(Request.QueryString("Addr")) & "'"

Response.Write "<ErrorCode>"
If CInt(RecordSet("iTotalEmailAddrs")) > 0 Then
  Response.Write "1" 'Duplicates Exist 
Else
  Response.Write "0" 'Duplicates Do Not Exist

End If
Response.Write "</ErrorCode>"
%>

The XML would come out looking like:

Code:

<?xml version="1.0" standalone="no"?>
<ErrorCode>0</ErrorCode>

You can get a lot more elaborate then this, but you should be able to get an idea from here...

flashcdog · #8 (**permalink**) 04-28-04, 08:38

WOW! Seppuku the Guru! Seriously... I have never gotten such a thorough response before.

Couple quick questions if I may... then I will leave you alone forever.

The flash code on creating the variable for the xml... can that code be executed on a button?

The flash code...

if ( Number(xmlData.ErrorCode) == 0 )
// The email address does not already exist.
else
// The email address DOES already exist.

Could it be modified to have the playhead move based on the results... for example:

if ( Number(xmlData.ErrorCode) == 0 )
gotoAndPlay("SomeFrameLabel");
// The email address does not already exist.
else
// The email address DOES already exist.
gotoAndPlay("SomeOtherFrameLabel");

The SQL query part... I am very very very new to ASP.

Could you break this down real quick:

'Do your SQL to determine if the email exists
'... insert connection code here...'
'... execute some SQL like:
' SELECT Count(UserID) AS iTotalEmailAddrs FROM tblUsers WHERE EmailAddr LIKE '" & Trim(Request.QueryString("Addr")) & "'"

tblUsers is the table I am connecting to?
"addr" is the field for the email?
(UserID) is the key field for the table tblUsers?

Seppuku · #9 (**permalink**) 04-28-04, 12:09

Bah.. I know it because I had to write it myself.. that whole thing is right out of one of my own apps

I haven't tried, but I'd say that all of that could be put in a button click action. I don't see why not. If not, put everything except the line

xmlObj.load("/testEmail.asp?Addr=" + txtEmailAddress.text);

in a separate frame, make sure it's global (meaning, not inside another function), and put the above line in the button click action event.

As for the SQL, you're right on... Addr isn't just the field, it's actually the "key" of the key/value pair in the QueryString (querystring being the parameters passed through a URL after a ?, such as mypage.asp?Key1=Value1). So by using Request.QueryString("Key1"), you would get the value "Value1". In our case, Request.QueryString("Addr") returns the email address that was passed from the Flash request. You can pass multiple key/value pairs by separating them with an & sign (Ex: mypage.asp?Addr=john.doe@website.com&ContentType=H TML).

As a side note, one issue/feature of Flash is that you can't access XML from a secondary site (supposedly a security feature). So if the SWF is loaded from website.com, you can't pull an XML (directly) from webpage.com. In my case this caused an issue since I was pulling stock data from a 3rd party. I had to create a separate PHP script on the same domain as the SWF to screenscrape the data from the source page which was on a separate domain. I picked PHP because I didn't need to install anything other then PHP to do it (you can download the PHP executibles for Windows/IIS). With ASP, I would've needed the MSXML COM object to be installed to do the same.

Don't worry about the questions.. I don't mind helping out when I can...

Seppuku · #10 (**permalink**) 04-28-04, 12:24

I forgot to mention about the gotoAndPlay part, you're right... it can be done that way. You may even move the whole block into the onLoad event:

Code:

xmlObj.onLoad = function(success){
  if(success){
    processXMLData(xmlObj.firstChild);

    if ( Number(xmlData.ErrorCode) == 0 )
      // The email address does not already exist.
      gotoAndPlay("SomeFrameLabel");
    else
      // The email address DOES already exist.
      gotoAndPlay("SomeOtherFrameLabel");
  }
  else{
    // There was a problem getting the XML
    gotoAndPlay("SomeErrorFrameLabel");
  }
}

flashcdog · #11 (**permalink**) 04-29-04, 09:57

I think that's the ticket there.

Giving that a try.

flashcdog · #12 (**permalink**) 04-29-04, 12:12

Would you mind some quick code checking.

First the asp... this is based on your code sample. SQL query string is where I am totally lost... even with your explanation.

In my database the user id is called "ID" it is the records key.
The table is called "table1".
The database is "testdb.mdb".

The SQL statement seems truncated somehow to me... I know you were just showing a sample. How to make that work?

Code:

<?xml version="1.0" standalone="no"?>
<%
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("testdb.mdb")
conn.Open DSNtemp

SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE " & Trim(Request.QueryString("email")) & "'"

Response.Write "<ErrorCode>"
If CInt(RecordSet("iTotalEmailAddrs")) > 0 Then
  Response.Write "1" 'Duplicates Exist 
Else
  Response.Write "0" 'Duplicates Do Not Exist

End If
Response.Write "</ErrorCode>"
%>

Now to the Flash...

Lets say that I have a button to continue to the next frame... on that frame I want to run the xmlobject code.

I want to use a various frame lables like before instead of onLoad could I go onEnterFrame like below?

This is all being executed on the same frame... is it correct?

Code:

var xmlData = new Object;
xmlObj = new XML();
xmlObj.ignoreWhite = true;
xmlObj.load("testemail.asp?email=" + txtEmailAddress.text);
xmlObj.onEnterFrame = function(success){
  if(success){
    processXMLData(xmlObj.firstChild);

    if ( Number(xmlData.ErrorCode) == 0 )
      // The email address does not already exist.
      gotoAndStop("emailno");
    else
      // The email address DOES already exist.
      gotoAndStop("emailyes");
  }
  else{
    // There was a problem getting the XML
    gotoAndStop("xmlerror");
  }
}

I know I am having a lot of questions.

Seppuku · #13 (**permalink**) 04-29-04, 12:25

Ok.. the SQL

Code:

SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE " & Trim(Request.QueryString("email")) & "'"

You're missing a single quote after LIKE. It should be:

Code:

SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE '" & Trim(Request.QueryString("email")) & "'"

So when the SQL execuites, it should be similar to:

Code:

SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE 'inbox@mailserver.com'

As for the ActionScript.. the onLoad for the XML object is not the Frame's onLoad. The XML's onLoad is executed when the XML document is loaded (after you call xmlObj.load()). Therefore, there is no onFrameEnter for the XML object. If you want the code to run on a specific event, then create that event (aka: onFrameEnter) and put your XML code in there instead of on the button. Or you could put all the code in the onFrameEnter except the xmlObj.load() function, which you could put in your button's onClick event.

flashcdog · #14 (**permalink**) 04-29-04, 13:28

Hmmm... everything after LIKE is acting like it is a comment/remark.

Should it be " ' " after like?

Code:

SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE "'" & Trim(Request.QueryString("email")) & "'"

Seppuku · #15 (**permalink**) 04-29-04, 13:54

Ok.. I see the issue... we're missing some code...

Try this:

Code:

Dim objRS
Dim sSQL
sSQL = "SELECT Count(ID) AS iTotalEmailAddrs FROM table1 WHERE EmailAddr LIKE '" & Trim(Request.QueryString("email")) & "'"

set objRS = server.createobject("adodb.recordset")
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("testdb.mdb")
set objRS = conn.Open sSQL, DSNtemp

Response.Write "<ErrorCode>"
If CInt(objRS("iTotalEmailAddrs")) > 0 Then
  Response.Write "1" 'Duplicates Exist 
Else
  Response.Write "0" 'Duplicates Do Not Exist

End If
Response.Write "</ErrorCode>"

objRS.Close
set objRS = Nothing

First I created a recordset object... when you execute conn.Open, it returns a recordset of the data, so you need a variable to store it.

Second, I created a variable to store the SQL.. you don't need it, but makes it easier to manage. You pass that variable into conn.Open( <SQL>, <Connection> ) and are returned the Recordset.

Then I edited the code to replace "Recordset(...)" with "objRS(...)", and then closed and destroyed the recordset.

Make sure you close and destroy the connection (conn) when you're finished as well.

Internet Services

Web Development

Digital Marketing

Consumer Tech