Querying Microsoft SQL server 2005

alexgeek · September 24th, 2007, 01:44 PM

How do I do this with ASP.
I know how to do queries (SELECT * FROM whatever)
but how do I do this in ASP,
another words the equivalent of:

Code:


			
mysql_query("SELECT * FROM table_name") or die (mysql_error());

And how to setup Microsoft SQL Server Accounts to connect?
couldn't find any info on how to do this.

spinal007 · September 27th, 2007, 07:24 PM

the SQL doesn't change. what changes is how you connect to and retrieve the stuff from the database. The particular php code you have above connects to the local MySQL server (I believe).

In ASP, you will need to
a) create a recordset
b) connect to the database with a connection string
c) run your query
d) disconnect from the database

something like this:
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = " YOUR CONNECTION STRING HERE "
rs.Open " YOUR SQL HERE ", , adOpenForwardOnly, adLockReadOnly
Set rs.ActiveConnection = Nothing

That should get you started. Play with it and let me know how you get on...

Ps.: I typed this code straight in so forgive me if I make a mistake

alexgeek · September 27th, 2007, 07:44 PM

how do i find my connection string?
i don't know how to set up account
thanks for replying

spinal007 · September 28th, 2007, 04:12 AM

Alex, do you know anything about MSSQL or MySQL?

robbied72 · September 28th, 2007, 05:11 AM

Here's a MSSQL 2005 connection string -

MM_connDN_STRING = "Provider=MSDASQL; Driver={SQL Server}; Server=SERVERNAME_OR_IP_ADDRESS; Database=DATABASE_NAME; User ID=DATABASE_USERNAME; Password='PASSWORD';"

Are you using Dreamweaver or are you hand coding?

robbied72 · September 28th, 2007, 05:13 AM

if you are just learning ASP i would suggest cutting your teeth using MS Access and then change the DB and connection string when you know how MS SQL is setup with users and permissions.

spinal007 · September 28th, 2007, 05:23 AM

As Robbie suggested, if you're just starting off you're better off playing around with Access.
Once you've grasped the concepts of databases you can start using 'real' databases like MSSQL & MySQL.

Monie · September 28th, 2007, 05:44 AM

Maybe this will help.

ConnectionString

alexgeek · September 28th, 2007, 12:52 PM

No i can query mysql fine with PHP.
But i wanted to use an mssql database for use with PHP, ASP.NET and C#
that way i can have a multplatform program.
What i really need help with is setting up mssql accounts.
I don't know how to do it

spinal007 · September 28th, 2007, 01:03 PM

"setting up mssql accounts" doesn't really explain it well.
Do you mean setting up a hosting account with a hosting company that offers MS hosting and MS SQL databases?
- OR -
Do you mean installing MSSQL Server on your machine and creating databases?

Either way, you will need this:
Microsoft SQL Server: SQL Server 2005 Express Edition

alexgeek · September 28th, 2007, 01:09 PM

Quote:

Originally Posted by spinal007

"setting up mssql accounts" doesn't really explain it well.
Do you mean setting up a hosting account with a hosting company that offers MS hosting and MS SQL databases?
- OR -
Do you mean installing MSSQL Server on your machine and creating databases?

Either way, you will need this:
Microsoft SQL Server: SQL Server 2005 Express Edition

I've got that.
okay, you know in mySQL you need to use mysql_connect() with your host, username and password.
How do you do that with mssql?
I want to know how to connect to the mssql database which is on my server, via PHP.

spinal007 · September 29th, 2007, 09:04 AM

Google is your best friend.

alexgeek · September 29th, 2007, 09:09 AM

That's what I've been trying to do.
But i don't know how to find this out:

Quote:

Parameters

servername The MS SQL server. It can also include a port number. e.g. hostname,port.
username The username.
password The password.
new_link If a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. This parameter modifies this behavior and makes mssql_connect() always open a new link, even if mssql_connect() was called before with the same parameters.

spinal007 · September 29th, 2007, 10:03 AM

server name - "(local)"
username and password - depends on what you chose when you installed your server. The default may be username "sa" with an empty password.

If you can't work it out, get MSSQL 2005 Management Studio Express and create yourself a login. The program itself will use Windows authentication to access the database so you won't need username/password to use it.

alexgeek · September 29th, 2007, 10:28 PM

will try that and get back to you