Statement Revisitied

The insert statement that we originally used to insert data into our database is the most elementary version. We are going to look at a more efficient and faster method of inserting data into our database. We are using a SQL statement. SQL is short for Structured Query Language. This is the language used with most databases. SQL will have minor syntax differences depending on the database you are using. For now, we are using an Access 2000 database. Our insert statement takes the form of:

SQL = "INSERT INTO tablename (fieldname1, fieldname2) VALUES (value1, value2)"
objConn.execute SQL

In our example, we will still be opening our recordset, because we want to first make sure that no duplicates exist of the data we are trying to insert. But after looping through our recordset and not finding any duplicates, we will execute our SQL statement.

In our first tutorial on inserting data into our database, open your insertuser.asp page. Locate the portion that says:

objRS.AddNew
objRS.Fields("username") = username
objRS.Fields("password") = password
objRS.Update

We want to replace this with our SQL statement. Notice that we are inserting the values from our username and password variables by enclosing them in single, then double quotes and finally the ampersand. This is important and is usually the cause of syntax errors. If you run into sytax errors in your INSERT statement, this is the first place to check. Make sure that you only use single quotes around string data. Any data that is numeric will only use double quotes.

SQL = "INSERT INTO users(username,password) VALUES ('"&username&"','"&password&"')"

This is the same SQL statement, but we are going to pretend that password is numeric data

SQL = "INSERT INTO users(username,password) VALUES ('"&username&"', "&password&")"

Finally, we execute our SQL statement by using the execute method of our connection object.

objConn.Execute SQL

This is a faster, more efficient way to add new records to your table. It is also the most preferred way. So once you feel confident using the old AddNew method, give this method a try. If you run into any problems, remember to post your errors or questions on our messageboard. See ya next time!

~Geoff Swartz