Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Apr 2010
    Posts
    220
    Thanks
    116
    Thanked 1 Time in 1 Post

    Exclamation Form Data to SQL Database issues, help please!

    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):

    Code:
    <%
    ' 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!

    Attached Thumbnails Attached Thumbnails Form Data to SQL Database issues, help please!-image11.jpg  

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    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:
    Code:
    Function ChkString(string)
    	ChkString = Replace( Trim(string) , "'", "''")
    End Function
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    BlackReef (10-24-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    BlackReef (10-24-2012)

  • #4
    Regular Coder
    Join Date
    Apr 2010
    Posts
    220
    Thanks
    116
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    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:
    Code:
    Function ChkString(string)
    	ChkString = Replace( Trim(string) , "'", "''")
    End Function
    Thanks Old Pedant for the help,

    Does this look a bit better?

    Code:
    <%
    ' 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

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,603
    Thanks
    80
    Thanked 4,500 Times in 4,464 Posts
    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.
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •