Click here to Skip to main content
11,436,179 members (62,565 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hey guys,

I finished my comic book database project, or so I thought. When I go to upload the files and make a new SQL 2005 DB, I'm having some trouble connecting to it from my index.asp page.

I'm getting this error:

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

I'm using their suggested/given connection string:

"workstation id=ComicsDB.mssql.somee.com;packet size=4096;user id=[myusernamehere];pwd=[mypasswordhere];data source=ComicsDB.mssql.somee.com;persist security info=False;initial catalog=ComicsDB"

Here's initial connector code I'm trying to use:

<%
strSQL = "Select * from Info ORDER BY Title ASC;" 'create SQL string

SET DbObj = Server.CreateObject("ADODB.Connection") 'set up the ADO connection
DbObj.Open = "data source=ComicsDB.mssql.somee.com;packet size=4096;user id=[myusernamehere];pwd=[mypasswordhere];persist security info=False;initial catalog=ComicsDB" 
SET oRs = DbObj.Execute(strSQL) 'Execute the SQL statement

%>

What I'm also confused about, was when I contacted their support about it, he said I was using an ODBC connector, which isn't supported on somee.com and he said to try using an ADO or ADO.NET connection instead. But I thought this was an ADO connection.....???

I did try another connection method, which 'should' be an ADO connection:

<%
strSQL = "Select * from Info ORDER BY Title ASC;" 'create SQL string (where clauses etc should be added according to what you would like displayed)

Dim DbObj as ADODB.Connection
Set DbObj=New ADODB.Connection
Let DbObj.ConnectionString= "ODBC;DSN=" & ComicsDB.mssql.somee.com & ";UID=" & 
[myusernamehere] & ";PWD=" & [mypasswordhere]
DbObj.Open
SET oRs = DbObj.Execute(strSQL) 'Execute the SQL statement
%>

But when I use that code, I always get this error:

"Microsoft VBScript compilation error '800a0401'

Expected end of statement

/index.asp, line 10

Dim DbObj as ADODB.Connection"
----------^

Can anyone help me clear this sql connection up?

Thanks in advance!


Jerome
Posted 10-Jul-12 15:55pm
Edited 10-Jul-12 16:14pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The connection strings you are using requires DSN, try using different options for different connection options to SQL 2005 from the following link:

http://www.connectionstrings.com/sql-server-2005[^]

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
  Permalink  
Comments
jump_ace at 11-Jul-12 22:52pm
   
Awesome, got it working. Thanks buddy!


Jerome
Arthurit at 22-Sep-12 10:56am
   
Same problem here! Wouldn't you mind sharing the solution you found?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Here's what I am currently using for somee.com and it works well Smile | :)

Dim DbObj
Set DbObj = Server.CreateObject("ADODB.Connection")
DbObj.Open = "Driver={SQL Server};" &_
"packet size=4096;" &_
"Server=[DB Name].mssql.somee.com;" &_
"Database=[DB Name];" &_
"Uid=[Username];" &_
"Pwd=[Password];" &_
"security info=False;" &_
"initial catalog=[DB Name]"
<pre>
 
Just fill in your data without the [ ]'s and you should be set!  HTH.

 
Jerome
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

It turns out the problem was in the driver specification. The solution I found that actually works for me is:

Dim DbObj
Set DbObj = Server.CreateObject("ADODB.Connection")
DbObj.Open "Driver={SQL Server Native Client 10.0};" & _
  "packet size=4096;" & _
  "Server=[DB].mssql.somee.com;" & _
  "Database=[DB];" & _
  "Uid=[UserName];" & _
  "Pwd=[Password];" & _
  "security info=False;" & _
  "initial catalog=[DB]"

Thanks for your help!
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Thanks Jerome. I see that you are using a DSN-less connection string and so I used the following code (I've substituted all the corresponding [ ]'s):
Dim DbObj
Set DbObj = Server.CreateObject("ADODB.Connection")
DbObj.Open "Driver={SQL Server};" & _
  "packet size=4096;" & _
  "Server=[DB].mssql.somee.com;" & _
  "Database=[DB];" & _
  "Uid=[UserName];" & _
  "Pwd=[Password];" & _
  "security info=False;" & _
  "initial catalog=[DB]"
I thought that everything was OK but unfortunately I got the following error

error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

Any suggestions on what is going on?
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

I see you are missing a '=' after DbObj.Open Maybe that is it? Otherwise it looks fine to me.


Jerome
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.150428.2 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100