SQL & ASP Help

[deano270]

Trying to execute an SQL statement with ASP for a super basic registration system, but the INSERT INTO statement is throwing an error.
Essentially, I need to take input from the web form (variables email and password) and insert it into an SQL database.
I'm having trouble trying to get my head around what's wrong, especially with the mindfuck of quotation marks. Any help or pointers as to what might be wrong would be super helpful.

set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open("C:\Users\George Dean\cadetdetailsdb.mdb")
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open "Select * FROM cadetDetails WHERE Email = '" & email & "'", conn
    if rs.EOF = true then
      sql="INSERT INTO Users (Email,Password,Role) VALUES ('" & email & "','" & password & "','guest')"
      conn.Execute(sql)

Thanks

[Nexzer]

sql="INSERT INTO Users (Email,Password,Role) VALUES ('" & email & "','" & password & "','guest')"

try that

[deano270]

Did you actually change anything? Looks exactly the same.
Tried it anyway and no luck
[t]http://puu.sh/6PUYx.png[/t]

[dexon]

Have your tried to echo (or write, idk) your 'sql' string to see how the string looks like ?
That might help you to find the error.

[deano270]

Had a go at that and the output was as follows. (

INSERT INTO Users (Email, Password, Role) VALUES ('[e-mail]','passw0rd','guest')

It's still throwing syntax error which is confusing the hell out of me.
Looking here [url=http://www.w3schools.com/sql/sql_insert.asp]http://www.w3schools.com/sql/sql_insert.asp[/url] it seems to be correctly structured.

[inaba]

[QUOTE=deano270;43856640]Had a go at that and the output was as follows. (

INSERT INTO Users (Email, Password, Role) VALUES ('[e-mail]','passw0rd','guest')

It's still throwing syntax error which is confusing the hell out of me.
Looking here [url=http://www.w3schools.com/sql/sql_insert.asp]http://www.w3schools.com/sql/sql_insert.asp[/url] it seems to be correctly structured.[/QUOTE]

I (with limited ASP experience) wonder if it's because you are using a reserved keyword (password)

[Fulcrum]

You might as well start with good practice and use a prepared statement or stored procedure. Otherwise you'll need to put a fair deal of work into sanitising the data. (stop hackers)

[ZuenTheMan]

[QUOTE=deano270;43847915]Trying to execute an SQL statement with ASP for a super basic registration system, but the INSERT INTO statement is throwing an error.
Essentially, I need to take input from the web form (variables email and password) and insert it into an SQL database.
I'm having trouble trying to get my head around what's wrong, especially with the mindfuck of quotation marks. Any help or pointers as to what might be wrong would be super helpful.

set conn = Server.CreateObject("ADODB.Connection")
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open("C:\Users\George Dean\cadetdetailsdb.mdb")
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open "Select * FROM cadetDetails WHERE Email = '" & email & "'", conn
    if rs.EOF = true then
      sql="INSERT INTO Users (Email,Password,Role) VALUES ('" & email & "','" & password & "','guest')"
      conn.Execute(sql)

Thanks[/QUOTE]

Trying to execute this directly into the SQL server and still throwing out a Syntax error means that you have to make sure what database are you using.

Here are a few basic questions and tips to perhaps help you:
- Is it SQL or MySQL?
- Does the SQL account have access to using Insert commands?
- Are you connected to the correct database?
- Make sure the Table names are correct
- To avoid using reserved keywords in a database - make sure the table names are lowercase and perhaps add a prefix/suffix to your table names.
- Try executing the command in the database and make sure it works befor executing it in ASP

I hope it helped but if not, could you show the exact syntax error that it is showing when trying to execute the SQL command. :)

[EvacX]

[QUOTE=gokiyono;43859187]I (with limited ASP experience) wonder if it's because you are using a reserved keyword (password)[/QUOTE]

Surround table and column names with ` and values with '.

[Phrozen99]

This is what I use when I connect to my database

        SqlConnectionStringBuilder csb;

        public void ConnectToDatabase()
        {
            csb = new SqlConnectionStringBuilder();
            csb.DataSource = Encryption.Decrypt(ConfigurationManager.AppSettings["dbServer"], "****");
            csb.InitialCatalog = "****";
            csb.Encrypt = true;
            csb.TrustServerCertificate = false;
            csb.UserID = Encryption.Decrypt(ConfigurationManager.AppSettings["dbUser"], "****");
            csb.Password = Encryption.Decrypt(ConfigurationManager.AppSettings["dbPassword"], "****");
        }

            ConnectToDatabase();
            using (SqlConnection conn = new SqlConnection(csb.ToString()))
            {
                DateTime date = DateTime.Now;
                string site = "****";
                using (SqlCommand command = conn.CreateCommand())
                {
                    conn.Open();
                    command.CommandText = "INSERT INTO log(logIP, logSite, logDate) " +
                        "VALUES (@ip, @site, @date)";
                    command.Parameters.Add("ip", OracleType.NVarChar).Value = ip;
                    command.Parameters.Add("site", OracleType.NVarChar).Value = site;
                    command.Parameters.Add("date", OracleType.DateTime).Value = date;
                    command.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

[SteveS]

[QUOTE=Phrozen99;43983997]This is what I use when I connect to my database
[/QUOTE]

That looks like .NET to me, for whatever reason the OP is using Classic ASP which hasn't been updated in 14 years and hasn't been relevant for over a decade.

[deano270]

[QUOTE=SteveUK;43989832]That looks like .NET to me, for whatever reason the OP is using Classic ASP which hasn't been updated in 14 years and hasn't been relevant for over a decade.[/QUOTE]

This.

I was mindlessly following some tutorial from W3schools but came to my senses. Now using WebMatrix 3, Razor, and SQL compact DBs it's working successfully with this code:

if (ErrorMessage=="")
   {
       var SQLINSERT = "INSERT INTO UsersDB (FirstName, SurName, Emails, Password, Role) VALUES (@0, @1, @2, @3, 'user')";
       var db = Database.Open("users");
       db.Execute(SQLINSERT, FirstName, SurName, UserEmail, UserPassword);
   }

Thanks for the help though!

[editline]21st February 2014[/editline]

I think the SQL statement syntax was fine, the problem was the DB wasn't accepting queries.
Probably better off using Razor anyway

[SteveS]

If you're actually doing ASP.NET now, I'd recommend Visual Studio Express over WebMatrix. I haven't tried ASP.NET Web Pages but WebMatrix is locked to using that flavour of ASP.NET, for bigger applications being able to use MVC would probably be better.

[deano270]

I have got VSE, but I'm only doing a small project for school so WebMatrix keeps it fairly simple. Definitely keen to use VSE for bigger projects in the future though.