Flash MX ASP SQL and MS Access nightmare

[Kevlarsjal]

Im having a nightmare with some script and cant tell if it is my actionscript or asp letting me down.
I am new to coding and would really appreciate any suggestions you have.

Basically i have created a simple flash file which speaks to an ms access database via a tidy little asp file.
It works fine and has no problems so i decided to grow on this success by adding a second element to add a user to the database.

On to the code...

I should also mention that Flash MX 2004 kicks up the following in the output pane
Error opening URL "http://localhost/newuser.asp"
Error Creating User

I have attached the developer files because I cant get the formatting to stay with the code in here.

Action Script
// first we create the objects
login_lv = new LoadVars();
//now we create the function that will happen when this object recieves data
login_lv.onLoad = function() {
//check to see if the users info is correct
if (this.userInfo == "true") {
gotoAndStop("Success");
userName.text = login_txt.text;
} else {
//if the users info was not correct, trace an error, and clean the text fields
trace("Invalid data");
login_txt.text = "";
password_txt.text = "";
}
};
//the function for the login button
_root.authenticate_btn.onPress = function() {
// an error will occur if the user didn't enter a login name
if (login_txt.text.length<1) {
trace("please provide a login name");
// an error will occur if the password is not the right length
} else if (password_txt.text.length<5) {
trace("invalid password");
// and clean out the password
password_txt.text = "";
} else {
// this is if everything is fine to send
// we will store the user information as properties of the LoadVars object
login_lv.username = login_txt.text;
login_lv.password = password_txt.text;
// now we send the data, and wait to recieve something back
login_lv.sendAndLoad("http://localhost/login.asp", login_lv, "POST");
}
};
newlogin_lv = new LoadVars();
newlogin_lv.onLoad = function() {
//check to see if the users info is created
if (this.newuserinfo == "true") {
trace("New User Created");
userName.text = login_txt.text;
} else {
//if the users info was not created, trace an error, and clean the text fields
trace("Error Creating User");
login_txt.text = "";
password_txt.text = "";
}
};
//the function for the new user button
_root.newuser_btn.onPress = function() {
// an error will occur if the user didn't enter a login name
if (login_txt.text.length<1) {
trace("enter a new login name");
// an error will occur if the password is not the right length
} else if (password_txt.text.length<1) {
trace("enter a new password");
// and clean out the password
password_txt.text = "";
} else {
// this is if everything is fine to send
// we will store the user information as properties of the LoadVars object
newlogin_lv.username = login_txt.text;
newlogin_lv.password = password_txt.text;
// now we send the data, and wait to recieve something back
newlogin_lv.sendAndLoad("http://localhost/newuser.asp", newlogin_lv, "POST");
}
};
stop();

ASP Code

<%@Language=Vbscript%>
<%Option Explicit%>
<%
'''''''First, get the stuff from flash
Dim newinusername, newinpassword
newinusername = Trim(Request("username"))
newinpassword = Trim(Request("password"))

'''''''Now, create the connection object
Dim myConnection
Set myConnection=Server.CreateObject("ADODB.Connection ")
myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("login.mdb")
myConnection.Open

'''''''Here is our SQL statement when we open the Recordset
Dim loginSQL
''loginSQL= "INSERT INTO loginTable (userName,password) "
''loginSQL=loginSQL & " Values('" & newinusername & "', '" & newinpassword & "') "
loginSQL = "INSERT INTO loginTable (userName, password) VALUES ('"& newinusername &"', '"& newinpassword &"')"
'''''''Create, and open the Recordset
Dim myRS
Set myRS=Server.CreateObject("ADODB.Recordset")
myRS.Open loginSQL, myConnection

Dim newusermessage
newusermessage="newuserinfo=true"
'''''''Clean up everything
myRS.Close
Set myRS=Nothing
myConnection.Close
Set myConnection=Nothing

'''''''Now send the data back to flash
Response.Write(newusermessage)
%>

[scudsucker]

It seems to me you ASP is at fault, from those error messages: try this instead.

ASP Code

<%@Language=Vbscript%>
<%Option Explicit%>
<%
'''''''First, get the stuff from flash
Dim newinusername, newinpassword
newinusername = Trim(Request("username"))
newinpassword = Trim(Request("password"))

'''''''Now, create the connection object
Dim myConnection
Set myConnection=Server.CreateObject("ADODB.Connection")
myConnection.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("login.mdb")
myConnection.Open

'''''''Here is our SQL statement when we open the Recordset
Dim loginSQL

loginSQL = "INSERT INTO loginTable (userName, password) VALUES ('"& newinusername & "', '" & newinpassword & "')"
'' Recordset is not necessary with INSERT statements
myConnection.Execute(loginSQL)
''


newusermessage="newuserinfo=true"
'''''''Clean up everything

myConnection.Close
Set myConnection=Nothing

'''''''Now send the data back to flash
Response.Write(newusermessage)
%>

Note that you can test this code by going to your ASP page directly in your browser, and adding the values as a querystring:
http://localhost/newuser.asp?newinusername =TestName&newinpassword=TestPassword

If you get any errors, then post again with them and I'll have another look.

[Kevlarsjal]

Thanks for your time, unfortunately the problem persists.

Exactly the same error occurs in Flash, and entering the info into the browser directly results in :

Operation must use an updateable query.
/newuser.asp, line 20

Some reading suggests this is an access rights issue on the database but my localhost settings all appear correct.

[scudsucker]

Wierdly, I am getting the same error, no matter what I try- and I do these things for a living as an ASP developer.

I'll give it another go, on our server instead og my local IIS.

[scudsucker]

When I tried it on the live server- a copy/paste method, of course it worked. I hate MS Access!

So, you have to try to get write permission on the database in IIS.

Here's an up=dated ASP page for you, with error messages if the username/password combo already exists:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit %>
<%
'''''''First, get the stuff from flash
Dim newinusername, newinpassword
newinusername = Trim(Request("username"))
newinpassword = Trim(Request("password"))

'''''''Now, create the connection object
Dim myConnection
Set myConnection=Server.CreateObject("ADODB.Connection")
Dim sConn : sConn="DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("login.mdb")
myConnection.Open sConn
Dim rs
Set rs=Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection=myConnection
	
Dim newusermessage : newusermessage="&newuserinfo=false"

'''''''Here is our SQL statement when we open the Recordset
If(newinusername<>"" And newinpassword<>"") Then
	
	Dim loginSQL
	loginSQL = "SELECT userName, password FROM loginTable WHERE username= '" & newinusername & "' AND password='" & newinpassword & "'"

	rs.Open loginSQL,myConnection,2,3,1
	If (rs.EOF or rs.BOF) then
		'' we can safely add the user
		rs.AddNew
		rs("username")=newinusername
		rs("password")=newinpassword
		rs.Update
		newusermessage="&newuserinfo=true"
		Else
		' The user already exists
		newusermessage=newusermessage & "&error=UserExistsAlready"
		End If

	'''''''Clean up everything
	rs.Close
	Set rs=Nothing
	myConnection.Close
	Set myConnection=Nothing
	Else
	' Either name or password didnt get through.
	newusermessage=newusermessage & "&error=NoNameOrPassword"
	End If
'''''''Now send the data back to flash
Response.Write(newusermessage)
%>

[Kevlarsjal]

Thank you very much for your time.

I will have to try and find some decent webhosting which supports this kind of skullduggery and test it.

[ShadowOfSilence]

The problem is that your local machine account does not have write privilages to your DB. i.e. on that file it needs to have write permissions for the account : MachineName\iusr_MachineName
give that account write permissions to your database file or the folder that it resides in.

[daerion]

where is insert that code (actionscript)??
is it in the botton? or when the movie runs??