ASP Connection to Ms SQL Database Problems

[efriese]

I'm having some problems connecting to a MS SQL database. I'm trying to use a DSN-less connection using ASP/ADO. Here's the code, minus the login info:

<%
db_server = "foo"
db_name = "foo"
db_username = "foo"
db_userpassword = "foo"
tablename = "foo"

var oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("Driver={SQL Server};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword)

strSql = "INSERT INTO customers (ID,first_name,last_name,address,city,state,zip,phone,cell,email,DOB,pickup_front,pickup_side,pickup_back,pickup_other,drop_front,drop_side,drop_back,drop_other,delivery_options,shirt_hangers,shirt_fold,starch_no,starch_light,starch_medium,starch_heavy,clean_instructions,cc_name,cc_type,cc_number,cc_exp) VALUES('" & first_name & "','" & last_name & "','" & address & "','" & city & "','" & state & "','" & zip & "','" & phone & "','" & cell & "','" & email & "','" & DOB & "','" & pickup_front & "','" & pickup_side & "','" & pickup_back & "','" & pickup_other & "','" & drop_front & "','" & drop_side & "','" & drop_back & "','" & drop_other & "','" & delivery_options & "','" & shirt_hangers & "','" & shirt_fold & "','" & starch_no & "','" & starch_light & "','" & starch_medium & "','" & starch_heavy & "','" & cleaning_instructions & "','" & cc_name & "','" & cc_type & "','" & cc_number & "','" & cc_exp & "')"

oConn.Execute(strSql)

oConn.Close
var oConn = Nothing
%>

Any help would be much appreciated!

 

[lemmin]

The type of error you are getting could be helpful. Can you not even connect?

I've never seen ADO before, but I've seen a script do this. I don't know if it is caps sensitive, but the way I've seen it, all of the identifiers were capital letters. You have them all capital except 'Driver'. I wouldn't think that would be it, but it's worth a try.

This is the way I would try it:

var oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DRIVER={SQL Server};SERVER=" & db_server & ";UID=" & db_username & ";PWD=" & db_userpassword & ";DATABASE=" & db_name

I'd say if that doesn't work then the problem is in the dbase information. Maybe those variables arent what you think they are. You didn't define them in the code you posted so if they arent defined in the code you are useing, it's always good to define them.

 

[Albino]

i dont know if this has anything to do with it, but in your INSERT INTO bit, a few variables have spaaces in, such as ph one, pickup_ back etc

 

[efriese]

I tried capitalizing DRIVER, and still didn't work. And I think the gaps in the code are from copy&paste because their not actually in my code.

Here's there error I'm getting:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This makes me believe it's a connection issue...the code I'm using is straight off GoDaddy's web site. I really appreciate everyone's help so far.

 

[lemmin]

I think that error comes up when it finds a difference in the version of the drivers between the host and client. If you use a DSN, it will allow to go across different versions.

The only other thing I can think of (although improbable) is that it reads it left to right and is actually trying to find the database before it knows the user and password. Might be a long shot, but try switching the DATABASE entry to the end. If that doesn't work, I'd try using a DSN.

Actually, I was thinking about how it is very possible that the variables arent what you think they are. have you tried doing some error tracing by just returning the text of those strings you input? If you are using a global.asa file there can be glitches with globally declared variables.