Form Data to SQL Database issues, help please!

[BlackReef]

Hello,

Note: The Server / DB that I am trying to connect to is local (we have the server in a server room here)

I am following this tutorial:

http://tinyurl.com/9c64t5k

Everything is in place, except I do not know what to put in the "data_source" and "Server.MapPath" fields in the 'form_ac.asp' file.

It seems like no matter what I put in there (I have tried several different connection strings), that I always get a '500 Internal Server' error as soon as I 'submit' the form. Its like something else is out of place, because it doesn't even seem to check the DB strings (just based on how quickly the 500 error shows up)

You can see the 'test' form I am working with here:

http://dealer.electrabike.com/registerTest/form.htm

And here is the file the form posts to (form_ac.asp):

<%
' Declaring variables
Dim first, last, account, email, state, comments, data_source, con, sql_insert

' A Function to check if some field entered by user is empty
Function ChkString(string)
	If string = "" Then string = " "
	ChkString = Replace(string, "'", "''")
End Function

' Receiving values from Form
first = ChkString(Request.Form("first"))
last = ChkString(Request.Form("last"))
dealer = ChkString(Request.Form("dealer"))
account = ChkString(Request.Form("account"))
email = ChkString(Request.Form("email"))
state = ChkString(Request.Form("state"))
phone_area = ChkString(Request.Form("phone_area"))
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ 
Server.MapPath("form.mdb")
sql_insert = "insert into users (first, last, dealer, account, email, state, phone_area) values ('" & _
	first & "', last & "','" & dealer & "', '" & account & "', '" & email & "', '" & state & "', '" & phone_area & "')"

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
%>

The bold is where I am having issues. No matter what connection string I try, I keep getting the 500 error in my browser.

I have created the Database in SQL Server Management Studio, and created the table. The screenshot is attached.

Do you guys see anything out of place? I have never done this before.

Any help would be greatly appreciated!

[Old Pedant]

Ummm...that connection string you are trying to use is for an *ACCESS* database.

You can't use the JET OLEDB driver to connect to SQL Server!

Look here:
http://www.connectionstrings.com/sql-server-2008

As for 500 errors, etc. If possible, reconfigure IIS to allow for *REAL* error messages to be sent to the browser. You can only do that if you have permissions to change IIS settings for ASP, though.

Also, your CheckString function is *BAD*.

You do *NOT* EVER want to convert a blank string into a space! You will ENORMOUSLY complicate your life when you start making queries on the DB.

Do this instead:

Function ChkString(string)
	ChkString = Replace( Trim(string) , "'", "''")
End Function

[Old Pedant]

By the by, this belongs in the ASP forum, not the MySQL forum.

It's not really database-related, per se. It's just an ASP connection issue.

[BlackReef]

Originally Posted by Old Pedant

Ummm...that connection string you are trying to use is for an *ACCESS* database.

You can't use the JET OLEDB driver to connect to SQL Server!

Look here:
http://www.connectionstrings.com/sql-server-2008

As for 500 errors, etc. If possible, reconfigure IIS to allow for *REAL* error messages to be sent to the browser. You can only do that if you have permissions to change IIS settings for ASP, though.

Also, your CheckString function is *BAD*.

You do *NOT* EVER want to convert a blank string into a space! You will ENORMOUSLY complicate your life when you start making queries on the DB.

Do this instead:

Function ChkString(string)
	ChkString = Replace( Trim(string) , "'", "''")
End Function

Thanks Old Pedant for the help,

Does this look a bit better?

<%
' Declaring variables
Dim first, last, account, email, state, comments, data_source, con, sql_insert

' A Function to check if some field entered by user is empty
Function ChkString(string)
	ChkString = Replace( Trim(string) , "'", "''")
End Function

' Receiving values from Form
first = ChkString(Request.Form("first"))
last = ChkString(Request.Form("last"))
dealer = ChkString(Request.Form("dealer"))
account = ChkString(Request.Form("account"))
email = ChkString(Request.Form("email"))
state = ChkString(Request.Form("state"))
phone_area = ChkString(Request.Form("phone_area"))
data_source = Server=IP ADDRESS;Database=DEALER_REQUEST;User Id=myUsername;Password=myPassword; 
Server.MapPath("form.mdb")
sql_insert = "insert into users (first, last, dealer, account, email, state, phone_area) values ('" & _
	first & "', last & "','" & dealer & "', '" & account & "', '" & email & "', '" & state & "', '" & phone_area & "')"

' Creating Connection Object and opening the database
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert

' Done. Close the connection
con.Close
Set con = Nothing
%>

- On the connection string, do I just use the local server IP for the 'server'?

And for the user/pass on the connection string - do I use the same credentials that I log into the server with? (through Remote Desktop Connection)

Also, I am confused about this line: Server.MapPath("form.mdb")

Thanks again

[Old Pedant]

You do *NOT* use Server.MapPath with SQL Server databases. One more time: That is *only* for Access.

You forgot the quote marks around your connection string.

data_source = "Server=IP ADDRESS;Database=DEALER_REQUEST;User Id=myUsername;Password=myPassword;"

Regarding username and password: I can't answer that. It depends on your system.

In general, the answer would be "no". Typically, the SQL Server username and password are independent of the machine username and password. But they could be.

Ask your system administrator if you don't know.