Click here to Skip to main content
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 14:55pm
Edited 10-Jul-12 15: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 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  
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  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 338
1 _Amy 290
2 Maciej Los 260
3 Manfred R. Bihy 200
4 OriginalGriff 195
0 OriginalGriff 7,445
1 Sergey Alexandrovich Kryukov 6,347
2 Maciej Los 3,799
3 Peter Leow 3,558
4 CHill60 2,702


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 13 Dec 2012
Copyright © CodeProject, 1999-2014
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