[RESOLVED] connection between SQL Server 2. and dreamweaver??

[edith]

hello

I did a conenction to SQL server and dw but now i want to read the data and bring it to the form (ASP).

How can i present the data, make modifications or deleting and saving it in my DB.???

[wengang]

Hi Edith
How did you connect SQL Server to Dreamweaver?
You're working in Dreamweaver making ASP pages, right?
You just need to do a connectionstring and create a ADODB.connection that uses this string. Then open the connection on any page you want to use the db

Here is a typical SQLServer conn string:
connstring="Driver={SQL Server};Server=200.200.200.200;Database=mydatabasename;UID=myuserid;PWD=mypassword; OLE DB Services=-1;"
'200..... is your IP address where the SQLSErver is located
'now build a connection and open it:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring

Now you can return a recordset with ADODB recordset object:

SET rs=CreateObject("ADODB.Recordset") 'create recordset object
sql="select Username, Password from tblUsers" 'design a query for the recordset
rs.Open sql,conn,1,2 'execute the query and return recordset

'now to display:
do until rs.eof
response.write rs("username") & "<br>"
rs.movenext
loop

[edith]

Hi,
Thanks for answering. Yes i'm working with dreamweaver making ASP pages. I was told to use this to connect DW to SQL Server: <% ConnectToDB() %> in this code was this instruction : <% Set Cnn = server.creatobject ("ADODB.Connection")
cnn.open "PROVIDER = SQLOLEDB;DATA
SOURCE = sqlservername; UID=username; PWD=password;
DATABASE=database" %>

You will notice that i'm no expert, so i was wondering what this meant:
OLE DB Services=-1;"

And also i would like to know where can i find my server's IP address?

Other than that i'm ready to run this.

Thaks again,
Edith

[edith]

hi,

I just did what you told me and still, nothing happens.
I'll send you what i did, if you can help me?
Please let me know, ok.

Edith

[Hack]

You have the db code between VBScript tags, right?

<SCRIPT LANGUAGE="VBScript">
db code
</Script>

[wengang]

OK. where is your server? you should know the server's address. Do you have a network server, or a website? If you have a website, I assume you have set up a sql server database with your web host. In that case they should tell you the IP address, username and password for the database.

Put those information into your string and you will be okay.

Also, I don't know why you say connect DW to SQL Server. Dreamweaver is just a text editor for writing web pages. What you are connecting is the ASP page who carries this code to your database. That has to happen at the server of the code pages, where the ASP page is being built.

So the real question here is: do you have a Sql Server database set up already and where is it?

[edith]

Ok,
I know where my Ip Address is, just found out, i have a network server... i also know the username and password.

I did put the info in the string like this:

<script language="vbscript">

<% connstring="Driver={SQL Server};Server=200.200.200.200;Database=databasename;UID=username;PWD=password; OLE DB Services=-1;" %>

<% Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring %>

<% SET rs=CreateObject("ADODB.Recordset") 'create recordset object
oRs.Source = "Skills"
oRs.ActiveConnection = "Skills"
oRs.Options = adCmdTable = adCmdTable
oRs.open
sql="select skill-num From Skills" 'design a query for the recordset
rs.Open "Skills" 'sql,conn,1,2 'execute the query and return recordset %>

and so on
still nothing happens :-(

Yes that's what i meant by connecting ASP code to my DB, in my pages.
I do have the Sql Server database and is in here somewhere...;-)
just need a little time time to find it,
Edith

[wengang]

change this:
SET rs=CreateObject("ADODB.Recordset") 'create recordset object
oRs.Source = "Skills"
oRs.ActiveConnection = "Skills"
oRs.Options = adCmdTable = adCmdTable
oRs.open
sql="select skill-num From Skills" 'design a query for the recordset
rs.Open "Skills" 'sql,conn,1,2 'execute the query and return recordset

to just this:
SET rs=CreateObject("ADODB.Recordset") 'create recordset object
sql="select skill-num From Skills" 'design a query for the recordset
rs.Open sql,conn,1,2 'execute the query and return recordset

and I think nothing happens here because you only opened a recordset you didn't tell it to do anything.

add this code:
<%
if rs.eof then
response.write "NO RECORDS"
else
do until rs.eof
response.write "value: " & rs("skill-num") & "<br>"
rs.movenext
loop
end if
%>
And now run your ASP page. It is impossible for nothing to happen.
At least you will get some kind of error.
Tell me what the error is (if you get error) that will help to solve the problem

[edith]

This is what happens when i press F12:

C:\Documents and Settings\varelave\My Documents\EV\Co-pla\Unnamed Site 2\TMP64f1vjoe8m.htm

And the page is completly blank.

[wengang]

did you save this page as htm or asp?

looks like you are viewing it as a temp page, you didn't send it to your server?

Have you set up IIS on your PC to execute ASP pages (if you are doing this locally)

OK. Make a new page, take out all codes.
Just write :

<html>
<body>
Test
</body>
</html>

and run that page. You should see the word test. If you don't then you are not set up correctly. You should either be sending these pages to a server, or running them on a PC with IIS set up. Also, this page should be saved as .ASP.

OK let me know.

[edith]

Then i went to the create site, choose a doc type, testing server, creat a recordset.
in the setup instructions.... it already had a checkmark on the first two, so i tried to do the testeing server part and in the: server model i put VBScript; in access - local/network; Testing server folder - C:\Documents and Settings\varelave\My Documents\EV\Co-pla\Unnamed Site 2\Connections\; URL Prefix - http://localhost/


ooohh i don't know i'm doing a mess
I have to go now, but i'll write again on monday,
Thank you for the help, i appreciated,
Edith

[edith]

ok, before i go i'll do what you just send

[wengang]

well, I guess it is noon in Puerto Rico. But it is midnight in beijing.
I think what you need to do is set up IIS (if you haven't). You will need your Windows install disc. Go to Control Panel > Add Programs> Windows components>
Then in the list, you will see Internet Information Services (IIS). Make sure it is checked and click OK. Then insert your Windows CD and let it install.

Next time you look at Control Panel, Administrative Tools, you will have an INternet Information Services Icon. Open that up, navigate to Default website in the left pane. Then right click and check properties. Take a look at that and set up as you see fit. Usually, I use 127.0.0.1 as the website address. There are a lot of settings. Just look over them. Most important is the directory where the default site is located. Usually it is on your C: drive at C:\inetpub\wwwroot

That is where you should put your ASP files.

Then you can execute them in IE by browsing to
http://127.0.0.1/pagename.asp

and it will run

I will check back in the morning if you had any problem with it.
Good luck.

[edith]

ok, this is what i get now:

http://localhost/TMP714gdjoff0.asp

and the page is empty, but in the Desing mode i can see the word Test

[edith]

ok, bye

[edith]

Hello!
Wengang,

I donwloaded the IIS V 5.1

Now what??
How do I "execute them in IE by browsing "? What's IE??

Please be there....,
Edith

[wengang]

Hi.
IE = Internet Explorer

Have you installed IIS?

[edith]

Hello,

Yes it took long cause my PC didn't wanted to ready it so i had to ask for help, and one of the tech here re-installed it.

Ok what's the next step??

Thank you again,
Edith

[wengang]

go to your Control Panel
click on ADMISTRATIVE TOOLS
click on Internet Information Services

You will see the IIS interface.
on the left menu, navigate to DEFAULT WEBSITE.
Right click this and open PROPERTIES.

Set up your website.
Very basically, choose a folder on your PC where you will put your website. The default is C:\inetpub\wwwroot
but you can change it to whatever, e.g. c:\myweb or whatever.
under IP address, you can use 127.0.0.1, port 80

Those are the basic settings. There are many, many more but that is enough to get your website displayed in IE. So click on APPLY and OK to close this box.

To make sure your IIS is pointed on the right place, right click on DEFAULT WEBSITE again and then click on REFRESH. Look at the right side of the IIS interface, and you should see your ASP files listed. If not, repeat the steps I mentioned.

To see some page in your root directory, for example page1.asp, you can open IE (or whatever browser) and enter the address:
http://127.0.0.1/page1.asp

You can test the connection by making a test page:

<html><body>TEST</body></html>

save as .htm or .asp file and try to find it in IE under localhost 127.0.0.1

Let me know what happens.

[edith]

Ok it's done.

Now when i press F12 the webpage opens and shows the word "Test" in this localhost:

C:\Inetpub\wwwroot\TMP35qvvjvp8c.htm

That last thing is weird thou, and i don't remember putting it anywhere, is it normal?
"TMP35qvvjvp8c.htm"

I saved it as "test.asp"

[edith]

Nope now is this: C:\Inetpub\wwwroot\TMP3bmc7jvpfy.htm

Is it suppose to change???

[wengang]

OK. first step. Stop using F12!

You have set up an IIS in your PC. You don't need F12.. That is a preview function for people who have no IIS. Dreamweaver built your ASP page to HTML page.

OK, to see your page, like I said, go to IE
and visit the address:
http://127.0.0.1/test.asp
and you can see the word TEST (I hope)

[wengang]

OK. sorry, it is late in Beijing. I have to go. Let me know your results. And I will reply in the morning.

[edith]

Ok!! :-)

Yes, the word TEST is visible.
I put the wrong IP address. ;-( that's why i didn't see it.

And now??

[edith]

oooooooohhh,ok
Thank you very much,

Edith

[wengang]

OK.
now try your pages with SQL Server connection and see if you can see everything.

[edith]

Ok.


<html>
<!-- #include virtual ="/_scripts/esdc-lib.asp" -->
<% ConnectToDB() %>
<body>
Test
</body>
<% DisconnectFromDB() %>
</html>

Error Type:
Active Server Pages, ASP 0126 (0x80004005)
The include file '/_scripts/esdc-lib.asp' was not found.
/test.asp, line 2


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)

Page:
GET /test.asp

Time:
Wednesday, July 20, 2005, 8:15:41 AM


Why is this happening? Is it the virtual stuff??

[edith]

No i don't see a thing.

It's the code, this is a mess :-(.....

<html>
<!-- #include virtual ="/_scripts/esdc-lib.asp" -->
<% ConnectToDB() %>

<% connstring="Driver={SQL Server};Server=127.0.0.1;Database=copla_bi3dc-stage;UID=;PWD=; OLE DB Services=-1;" %>

<% Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring %>

Test

<% SET rs=CreateObject("ADODB.Recordset") 'create recordset object
sql="select skill-name, Password from Skills" 'design a query for the recordset
rs.Open sql,conn,1,2 'execute the query and return recordset

'now to display:
do until rs.eof
response.write rs("skill-name") & "<br>"
rs.movenext
loop

<% DisconnectFromDB() %>
</html>

and then this page error shows:
Error Type:
Microsoft VBScript compilation (0x800A0400)
Expected statement
/test.asp, line 22
<% DisconnectFromDB() %>

What is it expecting?

[wengang]

OK.
first, delete this line:
<!-- #include virtual ="/_scripts/esdc-lib.asp" -->
it means to include another page's code on this page. It seems your IIS cannot find that page.
Anyway, you don't need it just for testing your SQL Server connection.
So delete that.

OK. For a test, you can put those functions on a new ASP page, named "conn.asp"

In that file, put this code:
<%
connstring="Driver={SQL Server};Server=200.200.200.200;Database=mydatabasename;UID=myuserid;PWD=mypassword; OLE DB Services=-1;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring
%>

Nothing else, okay. Just this code.

Save that file in the SAME folder with TEST.ASP together.

Now, change your TEST.ASP page code to look like this:

<!--#include File="conn.asp"-->
'the above line puts the database connection code on this page
<html><body>TEST</body></html>

Just put the above 3 lines into TEST.ASP (and nothing else).
Now go back to your IE and browse http://127.0.0.1/test.asp

If you have no error, you will see the word TEST, and you ARE connected to your SQL Server DB
otherwise, you will get an error. So in that case, tell me the error.
If no error, let me know and you can go to the next step

[edith]

Ok.

I redid both pages: conn.asp

<html>
<%
connstring="Driver={SQL Server};Server=200.200.200.200;Database=mydatabasename;UID=myuserid;PWD=mypassword; OLE DB Services=-1;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring
%>
</html>

And test.asp:

<html>
<% connstring="Driver={SQL Server};Server=127.0.0.1;Database=copla_bi3dc-stage;UID=;PWD=; OLE DB Services=-1;" %>
<% Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connstring %>
</html>

This is the error i get:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
/test.asp, line 7

I don't get it, it does exits, does it?

[edith]

ok my mistake, this is test.asp:

<!--#include file="file:///C|/Inetpub/wwwroot/EV/conn.asp"-->
'the above line puts the database connection code on this page
<html>
<body>
TEST
</body>
</html>

And this is the new error:

Error Type:
Active Server Pages, ASP 0126 (0x80004005)
The include file 'file:///C|/Inetpub/wwwroot/EV/conn.asp' was not found.
/test.asp, line 2

Sorry

[edith]

Why is it denying me ?

conn.Open connstring

[edith]

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
/conn.asp, line 5

error when i open conn.asp

[wengang]

the line I gave you should not be changed:
<!--#include File="conn.asp"-->

This is correct.

The error you got is because IIS cannot find any SQL Server database there.
Is your SQL Server database on your same computer? If not, you should not list server as 127.0.0.1. If the SQL Server database is on your same machine, is that the right database name? And did you include the right password and username?

In SQL Server, you have two ways to log in, one is windows login and the other is SQL Server login. Here it should be SQL Server login (set in SQL Server) . This is only a question if the database is on your same machine.

So, we are back to the old question, where is your SQL Server database? Your machine, local network PC, or internet server somewhere else?

[edith]

Yes they told me it is and i can access it too.
Through SQL Server Enterprise Manager, i cans see the server, the databases, i also know the username and password.

and i change the instruction you just gave me.

Still....

[edith]

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'copla_bi3dc-stage'. Login fails.
/conn.asp, line 5

[edith]

Yeaahhhh!!
i did it!!!!!
I mean we did it.

it worked i can see the word
TEST

[edith]

I went to the DB, and i saw that i wrote _ instead of a -
Blah
Anyway what's the next step?

[wengang]

OK. that is good.

do you want to try to display some data?

Just build a recordset:

change the word TEST in your test.asp to:

<%
SET rs=CreateObject("ADODB.Recordset") 'create recordset object
sql="select skill-num From Skills" 'design a query for the recordset
rs.Open sql,conn,1,2 'execute the query and return recordset
if rs.eof then
response.write "NO RECORDS"
else
do until rs.eof
response.write "value: " & rs("skill-num") & "<br>"
rs.movenext
loop
end if
%>

[edith]

So, if i want to bring the data i have in the servers table, and i want to be able to add, delete and modify....
Can i do this :

<p>Description:
<textarea name="comp-desc" cols="45" rows="2" id="comp-desc"></textarea>
</p>
<p>Process Owner:
<select name="proc-owner" id="proc-owner">
<option>users fron bd</option>
</select>

And inside those boxes is the data from the tables or whatever.