Classic ASP and SQL Server Express 2005

Question

Below is my simple asp code that I am using to insert some data to the SQL Server database. It gives the below error. Could someone please have a look and let me know the error with this code?

<html>
    <head>
        <title>Address Book Insertion</title>
    </head>
    <body>
<%

    set connection=Server.CreateObject("ADODB.Connection")
    connection.Open "Provider=SQLOLEDB;Server=charithj-pc;Integrated Security=SSPI;Initial Catalog=AddressDB;"

    cmd =       "INSERT INTO AddressBook (FirstName, Surname, Address) VALUES ('"
    cmd = cmd & Request("FirstName") & "','"
    cmd = cmd & Request("Surname")   & "','" 
    cmd = cmd & Request("Address")   & "')"

    Response.Write(cmd)

    on error resume next
    connection.Execute cmd
    if err <> 0 then
        Response.Write("Insertion failed")
    else 
        Response.Write("Insertion successful")
    end if
    connection.close
%>
    </body>
</html>

The website cannot display the page

HTTP 500

Most likely causes:

•The website is under maintenance.

•The website has a programming error.

Are you sure your connection string is correct? The server name doesn't look like a SQL Server Express instance name. — Oded, Apr 24 '11 at 09:08
i suggest to remove on error resume next this could help finding the problem faster. — NickD, Apr 24 '11 at 09:19
Anyway, why don't use SQL Server Express 2008 R2? 2005 is obsolete — abatishchev, Apr 25 '11 at 07:07

score 3 · Accepted Answer · answered Apr 24 '11 at 09:10

3

go in your browser, i assume it's internet explorer and disable "show friendly http errors". this helps you to see the real error message.

btw. your code allows sql injection. use sql parameters or replace some characters like single quote etc.

answered Apr 24 '11 at 09:10

NickD

2,672
7
37
58

+1 for sql injection - concatening strings with input from user and executing them as commands is so BAD – stalker Apr 24 '11 at 09:14
another point, i assume the application pool from the iis has no access rights to the database. the default it probably network service. – NickD Apr 24 '11 at 09:37

David Hall · Answer 2 · 2011-04-24T09:52:03.933

2

The points made about turning off friendly HTTP errors, removing the on error resume and taking care of the SQL injection are all correct and you should do them before we go too far on guessing what is wrong, but one thing does stand out in your VB Script code (that is the language embedded in ASP Classing in the <% %>).

That is your use of Response.Write().

What this does is allow the code to return output to the browser. When you have the line:

Response.Write(cmd)

You are sending the string representation of your cmd object back to the browser. That could easily break the server side creation of the page.

Two more general pieces of advice to get you working better:

For all but the most trivial pages it is usually better to remove the code from the ASP page and put it into a stand alone libary that the ASP page then calls. This is certainly advisable to data access code.
While testing, your VB script code does not need to be run out of the ASP page, you can copy the script out into a text file. Rename that file .vbs and you should be able to run the VB script by double clicking on that. I've always found this much more convenient when working through a bug.

edited Apr 24 '11 at 09:52

answered Apr 24 '11 at 09:30

David Hall

32,624
10
90
127

I thaught the same, that you need a command object, but you can use the connection object directly. He has not defined option explicit, so the cmd is a simple string. – NickD Apr 24 '11 at 09:33
@Snoopy And given that, doing response.write should not cause a problem. I guess we wait to see what the real error is :) – David Hall Apr 24 '11 at 09:39
i am pretty sure the application pool user is not associated with the database :-) – NickD Apr 24 '11 at 09:43
This is the error now I get. Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database "AddressDB" requested by the login. The login failed. /AddressBook/Insert.asp, line 9 – CharithJ Apr 25 '11 at 05:01

score 1 · Answer 3 · edited Apr 25 '11 at 08:49

1

This is the error now I get. Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database "AddressDB" requested by the login. The login failed. /AddressBook/Insert.asp, line 9 –

Either try to use sql authentication, see connection string is here: www.connectionstrings.com/ or change your security settings:

http://msdn.microsoft.com/en-us/library/2xzyzb0f.aspx

edited Apr 25 '11 at 08:49

David Hall

32,624
10
90
127

answered Apr 25 '11 at 06:58

NickD

2,672
7
37
58

Classic ASP and SQL Server Express 2005

3 Answers3

Linked