Frontpage SQL Database with 1and1 (Full Version)

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



Message

Mav44 -> Frontpage SQL Database with 1and1 (10/4/2006 11:22:33)

I bit the bullet and upgraded my 1and1 package to include a SQL server. 1and1 supplied me with a host name, database name, user name, and password.

This looked like a no brainier for adding to Frontpage.

I went to tools>Site Settings>Database>Add
I gave this database a name and selected the radio button for network connection to a database
I hit the browse button and left the first box at SQL Server, under server name I added the 1and1 info "mssxxx.1and1.com", under the database name I added the 1and1 info "dbxxxxxxxxx"

I then went back and hit the Advanced button and added my user name under name "dboxxxxxxxxx", and password "xxxxxxxx".

I hit OK twice to exit and the database had been added to the list. Lastly, I hit verify and received;

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

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

I wanted to know if anyone has any experience using 1and1 and SQL databases or if anyone has any suggestions to get me back on track.

Thanks,




Spooky -> RE: Frontpage SQL Database with 1and1 (10/4/2006 23:45:13)

Does 1&1 have any specific connection recommendations in their FAQ? eg, DSN?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/5/2006 1:27:48)

Hi Spooky,

I have been through the 1and1 FAQ info and I found two items related to my problem.

1.

<html>
<title>Database query using ASP</title>
<body bgcolor="FFFFFF">
<h2>Query table <b>Products</b> with ASP</h2>
<%
Set dbaseConn = Server.CreateObject("ADODB.Connection")
dbaseConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\db\products.mdb") & ";"
SQLQuery = "SELECT * FROM PRODUCTS"
Set RS = dbaseConn.Execute(SQLQuery)
%>
<%
Do While Not RS.EOF
%>
<%=RS("name")%>, <%=RS("description")%>, <%=RS("price")%> DM
<p>
<%
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
dbaseConn.Close
Set dbaseConn = Nothing
%>
</body>
</html>

2.

Following is an example asp script to connect to the database and query a table.

<html>
<title>Queries from the MS-SQL database with ASP</title>
<body bgcolor="FFFFFF">
<h2>Query from table <b>products</b> with ASP</h2>

<%

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=mssqxxx.1and1.com;UID=dboxxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxxx"

'This code block will create a recordset
Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "select * from products"
rs.open SQL, conn

'will iterate to display the records got from the database
While Not rs.EOF
response.write(rs("id") & " " & rs("price"))
rs.MoveNext
Wend

'closes the connection
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing

%>
</body>
</html>

I am new to this but I know I need to get Frontage to add this connection to my database list to fully utilize the Frontpage forms. I am wondering if I need to specify a folder or file when I am entering the info in Frontpage>Tools>Database>Add to get Frontpage to see and verify my database?

Thanks as always,

Mav



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/5/2006 12:57:34)

And Now The Rest of The Story!

After several days of trying and talking to the worst techs in the industry at 1and1 they admitted that for "security reasons" I would be unable to use this feature of Frontpage with a 1and1 SQL database. This kind of kills my whole reason in upgrading my hosting package in the first place. They stated the only way to access my database was with the script above.

I am new at ASP and I really need the assistance of the wizards in Frontpage to help with my forms and database. I guess my task now is to find a new hosting company or SQL server that will support Frontage addition of databases and allow me to easily add my databases.

If anyone knows of any affordable solutions please recommend.

Thanks!



Spooky -> RE: Frontpage SQL Database with 1and1 (10/5/2006 14:44:03)

I dont think all is totally lost.
If you open your global.asa file, locate the application that controls the connection

It will look like :
Application ("yourdb_connectionstring") = "{SQL Server.....blah blah}"

Change it to :

Application ("yourdb_connectionstring") = "PROVIDER=SQLOLEDB;DATA SOURCE=mssqxxx.1and1.com;UID=dboxxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxxx"

Then try the DRW again?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/5/2006 15:16:20)

Spooky, Thanks for hanging in with me. You can tell this is driving me crazy.

I changed my golbal.asa file to read--
**********************************************

'--Project Data Connection
Application("dbxxxxxxxx_ConnectionString") = "PROVIDER=SQLOLEDB;DATA SOURCE=mssxxxx.1and1.com;UID=dboxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxx"
Application("db180107579_ConnectionTimeout") = 15
Application("db180107579_CommandTimeout") = 30
Application("db180107579_CursorLocation") = 3
Application("db180107579_RuntimeUserName") = "dboxxxxxxxx"
Application("db180107579_RuntimePassword") = "xxxxxxxx"
'--
**********************************************
I reloaded everthing just to be sure and ran the DRW. The respone I received is--

**********************************************
-------------------------------------------------------
Server error: Unable to connect to a database from the Web server using the connection string 'PROVIDER=SQLOLEDB;DATA SOURCE=mssxxxx.1and1.com;UID=xxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxx'.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

Source: Microsoft OLE DB Provider for SQL Server
Number: -2147467259 (0x80004005)

************************************************

Thanks again for your help and let me know of any other ideas I am willing to try anything.

Brett



Spooky -> RE: Frontpage SQL Database with 1and1 (10/5/2006 15:36:29)

Is the host able to set up a DSN connection?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/5/2006 16:54:06)

Spooky,

No luck with the DSN number. I have spent an hour on the line with 1and1 and traded several emails. All the tranied monkeys can do is direct me to the two scripts in my post #2.

I have emailed back that these are not a solution for my problem and we will see if I ever get a response.

Mav



Spooky -> RE: Frontpage SQL Database with 1and1 (10/5/2006 20:16:59)

If you use their exact code (and correct UID/Password etc) do you get the same error?

Change the table name (in bold) to the same as your table.
You do have a database with data in it? 

<html>
<title>Queries from the MS-SQL database with ASP</title>
<body bgcolor="FFFFFF">
<h2>Query from table <b>products</b> with ASP</h2>

<%

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=mssqxxx.1and1.com;UID=dboxxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxxx"

'This code block will create a recordset
Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "select * from products"
rs.open SQL, conn

While Not rs.EOF
response.write(rs(0) & "<br> "
rs.MoveNext
Wend

'closes the connection
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing

%>
</body>
</html>



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 1:14:49)

Spooky it appears to work if I use their code and replace the table with a table I have uploaded. See Below


Page Shows
******************************************


Query from table products with ASP


******************************************

It looks like the only way to access my databse is if I keep to 1and1's script.

Mav44



Spooky -> RE: Frontpage SQL Database with 1and1 (10/6/2006 2:25:12)

But it shows nothing other than that? Does your table have any data?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 10:06:56)

Spooky,

The first time I ran your modified script I got something like

expected ")"
line 18


I went in and added the ")" to close that line. Now all I get is the page h2 statement, "Query from table products with ASP" . There is about 25 lines of data on the table I selected. I am completely a novice but doesn't this mean that the page is working but the script is not requesting any data from my table?



Spooky -> RE: Frontpage SQL Database with 1and1 (10/6/2006 13:14:08)

It shows that its connecting as there is no error - it should also show data though!
Whats the SQL = "select * from products" look like now?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 14:10:47)

Sorry Spooky, I must of had a typo in there. I changed that line to:

SQL = "select * from Cal_Calendars"

When I go to that page I now get

***********************************************


Query from table products with ASP
blue
#FF0000
#0000FF
#00FFFF
#008000
FF9900

************************************************

So it looks like I am able to receive the data from the table but I cannot add the database to FrontPage. It's funny, when I import the mycalendar.mdb database to the 1and1 SQL Server it only pulls in the tables and not the database package. Is there no longer a database name but only a server?



Spooky -> RE: Frontpage SQL Database with 1and1 (10/6/2006 16:21:19)

So, for your database - the first column of information *does* contain that data thats being displayed?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 17:01:20)

Spooky,

Yes that is my first column data.

Mav



Spooky -> RE: Frontpage SQL Database with 1and1 (10/6/2006 17:45:47)

The connection sting you used for this code is exactly the same as we ried in the global.asa file?
The bold being the only different part 

Application("dbxxxxxxxx_ConnectionString") = "PROVIDER=SQLOLEDB;DATA SOURCE=mssxxxx.1and1.com;UID=dboxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxx"


Can you change your test code to use this : 

Set conn = Server.CreateObject("ADODB.Connection")
conn.open Application("dbxxxxxxxx_ConnectionString")


How does that work?



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 18:26:11)

Spooky, this is spooky.

I used the conn.open Application("dbxxxxxxxx_ConnectionString") on my test page and I got the same results as when I use;

conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=mssqxxx.1and1.com;UID=dboxxxxxxxxx;PWD=xxxxxxxx;DATABASE=dbxxxxxxxxx"


I found this so strange that I set up another test page to verify my results and got the same thing. I went back to the DRW and it still will not verify my results. I set up a new page and select insert>database >results and my dbxxxxxx is in the drop down menu but when I go to the second screen "record source" I get the same results as the verify add screen;

**********************************************************************

Server error: Unable to connect to a database from the Web server using the connection string 'PROVIDER=SQLOLEDB;DATA SOURCE=mssxx.1and1.com;UID=********;PWD=********;DATABASE=dbxxxxxxxx;'.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

Source: Microsoft OLE DB Provider for SQL Server
Number: -2147467259 (0x80004005)

**********************************************************************

It's amazing to me that the connection string in the global.asa file will work with a script page but will not verify..



Spooky -> RE: Frontpage SQL Database with 1and1 (10/6/2006 19:33:44)

The only other thing I can suggest trying, is to create a DRW using an access database then change the connection in the DRW to the application name we used above. All being equal, it *should* still work



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/6/2006 20:43:03)

No that did not work. I set up the new database, added and verified it in the DRW, made the change to the global.asa. When I went to add results to a test page I got the same error when I selected the new database.

It is my best guess that 1and1 has some kind of block on their SQL databases being accessed by anything other than the script that they have established. I think I have reached the end of the line....


Anyway if anyone ever reads this and knows of a hosting company that is real Frontpage friendly, affordable and allows, for databases to easily be added and accessed from the DRW I would love the name of 1 or 2.

Spooky thanks for all of your help and suggestions! Your patience with us beginners is really appreciated![:D]



Mav44 -> RE: Frontpage SQL Database with 1and1 (10/7/2006 20:21:32)

IT WORKS!!!!!!!!!!!!!

I can now verify the pages on my PC and use the Database Wizard feature to build the pages!

First, I need to apologize to 1and1 as the problem was on my local PC. They have been guilty in the past of many things but this one was my own doing.

Second, I want to apologize to Spooky for being such a hack and taking up his time.

You see you need to be live and online to use the Database Wizard from a SQL server. You need to open http://www.mysite.com and not try to use the wizards while working with your files from your local hard drive. I only hope these posts will help another beginner learn through my mistake.

NOTE- Spooky has suggested that it is always best to Develop your Pages on the Server and not the local PC.

Spooky, thanks again for all your help and patience, your the best[:D]





Spooky -> RE: Frontpage SQL Database with 1and1 (10/7/2006 21:10:37)

Glad we got it sortred !
quote:

"that it is always best to Develop your Pages on the Server"

Just to qualify that, in your instance, accessing the live SQL database I would do it on the server.
Ideally youd have a local copy of the SQL server with test data and develop it all off line.
Personally Im fine with both



biddyman -> RE: Frontpage SQL Database with 1and1 (10/16/2006 19:12:40)

This has helped me out a lot!!!! Thanks you two!

Now, how would I import this data into Excel? As in a live connection to SQL.



xlmdxlmd -> RE: Frontpage SQL Database with 1and1 (10/17/2006 18:41:56)

bad or best?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1099854