ASP & Connection to MS SQL database (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Charlesb -> ASP & Connection to MS SQL database (5/17/2004 17:37:12)

Hello,

I am trying to create a dsnless connection to Microsoft SQL server on a different subnet that does not use the default SQL port.

If a copy my code to the same subnet that the SQL server is on - the code works just fine. But if it's running on the other subnet (which is where my web server is located) I get the following error msg:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

I have tried using the ip address of the sql server and the fully qualified name in the connection string with no success.

My connection string -
strConnect = "DRIVER={SQL Server}; Server=servername\sqlInstance; UID=username; PWD=password; Languages=us_english;Database=DatabaseName"

I would have assumed that I would need to specify the port in the connection string - but I haven't found the syntax for doing that and the code works just fine when it is on same subnet as the sql server.

Thank you,
Charley



BeTheBall -> RE: ASP & Connection to MS SQL database (5/17/2004 23:09:21)

Have you tried OLEDB? Something like:


<%
set cnn = server.createobject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "
%>



Spooky -> RE: ASP & Connection to MS SQL database (5/18/2004 2:39:15)

Can you use the IP address of the server?



Charlesb -> RE: ASP & Connection to MS SQL database (5/18/2004 15:24:51)

thank you both for the suggestions - but no luck

Using oledb I get:
Microsoft OLE DB Provider for SQL Server error '80004005'

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.


Using sqlserverip address I get
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.


Any other suggestions?

Thank you,
Charley



BeTheBall -> RE: ASP & Connection to MS SQL database (5/18/2004 15:27:32)

Have you read this MS article?

http://support.microsoft.com/?kbid=328306



Charlesb -> RE: ASP & Connection to MS SQL database (5/18/2004 19:27:17)

I hadn't seen it. I had done a search on my error msg - must have missed it somehow.

The information in it got me going down the right track - I had messed up some settings in the sql client network utility. All is working now.

Thank you very much.
Charley



phaithow -> RE: ASP & Connection to MS SQL database (10/7/2004 4:10:58)

I'm also face this problem. Here is my HTML Code. Could you please advise me on the mistake point?

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>

<body>

<% DIM conntemp, mySQL, myDSN

'myDSN = " DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" fpdb/spooky_login2000.mdb" )
'myDSN would be specific to your environment
'myDSN = "DRIVER={SQL Server}; Server=Webserver\sqlInstance; UID=sa; PWD=kui9213; Languages=us_english;Database=Dhnarath2"
myDSN = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.123.111;UID=sa;PWD=kui9213;DATABASE=Dhnarath2"

set conntemp= server.createobject("adodb.connection" )
conntemp.open myDSN


FOR i = 1 to 11

IF Request("rate" & i) > "" THEN

mySQL = " INSERT INTO PO (PO_ID, Product_ID, Quantity,Unit,SP,Invoice_Number) VALUES"
mySQL = mySQL & " (" & Request("PO_ID") & ", '" & Request("Product" & i) & "','" & Request("Quantity" & i) & "','" & Request("Unit" & i) & "'," & Request("SP" & i) & ", " & Request("Invoice_Number" & i) "
Response.write(mySQL)
conntemp.execute(mySQL)

END IF

NEXT

conntemp.close
set conntemp=nothing

%>


</body>

</html>
[:D] 
<html> 

<head> 
<meta http-equiv="Content-Language" content="en-us"> 
<meta name="GENERATOR" content="Microsoft FrontPage 6.0"> 
<meta name="ProgId" content="FrontPage.Editor.Document"> 
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> 
<title>New Page 1</title> 
</head> 

<body> 

<% DIM conntemp, mySQL, myDSN 

'myDSN = " DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" fpdb/spooky_login2000.mdb" ) 
'myDSN would be specific to your environment 
'myDSN = "DRIVER={SQL Server}; Server=Webserver\sqlInstance; UID=sa; PWD=kui9213; Languages=us_english;Database=Dhnarath2" 
myDSN = "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.123.111;UID=sa;PWD=kui9213;DATABASE=Dhnarath2"

set conntemp= server.createobject("adodb.connection" ) 
conntemp.open myDSN 


FOR i = 1 to 11 

IF Request("rate" & i) > "" THEN 

mySQL = " INSERT INTO PO (PO_ID, Product_ID, Quantity,Unit,SP,Invoice_Number) VALUES" 
mySQL = mySQL & " (" & Request("PO_ID") & ", '" & Request("Product" & i) & "','" & Request("Quantity" & i) & "','" & Request("Unit" & i) & "'," & Request("SP" & i) & ", " & Request("Invoice_Number" & i) " 
Response.write(mySQL) 
conntemp.execute(mySQL) 

END IF 

NEXT 

conntemp.close 
set conntemp=nothing 

%> 


</body> 

</html>



rdouglass -> RE: ASP & Connection to MS SQL database (10/7/2004 8:19:08)

quote:

,'" & Request("Quantity" & i) & "',


Is Quantity a number field in the DB? Then remove the apostrophes like:

," & Request("Quantity" & i) & ",

At least thats one thing I see.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.0625