View Full Version : Requesting a simple example (asp - mysql)
I wanted to help a friend on a site he is making in which he wants to have a simple shopping cart / search function. I said o.k since I've spent the last wee while playing with php/coldfusion + mySQL here and there I should be able to offer some help but he can only use asp which I haven't touched and I want to learn it anyway so I thought I'd try.
Getting to the point, could anyone post a very simple example of how to connect to a mySQL database(on web host not local machine), select data from a table column and output it on the html page? that's all, simpler the better, 'bare essentials' so to speak. I've gone through a couple of basic tut's on syntax etc & I just thought this example would help me getting started.
mat
whammy
11-21-2002, 01:37 AM
I haven't used MySQL, but all databases use SQL (Structured Query Language), and there are very few minor differences... If I were you I'd just learn SQL, I'm sure there are plenty of references available if you google it.
You can't connect to a database on a remote webhost if I understand what you're getting at - you'll need to do it from the script, assuming the script is on the same LAN as the web server. :confused:
And using .asp, you won't be able to output anything to an .html page since they are static, you'd need to change the file extension to .asp if you want the page to be dynamic.
Unfortunately I can't provide you with any more bare essentials than that. I would take some tutorials on ASP. :(
If you run into any specific problems with ASP scripts, I'm sure everyone here will be glad to help you, though - including me!
victoria_1018
11-21-2002, 01:42 AM
You should start from the basic.
I will recommand you some good website where you can read up add these information.
http://www.w3schools.com
http://www.bravenet.com
http://hotwired.lycos.com/webmonkey/programming/asp/index.html
http://www.codefixer.com/tutorials/index.asp
hope these can help. :o
whammy
11-21-2002, 01:50 AM
Good links, Victoria. :)
..can't connect to a database on a remote webhost if I understand what you're getting at - you'll need to do it from the script, assuming the script is on the same LAN as the web server.
Yep, I meant is that The asp script will be tested on a web host as opposed to my local machine so I would appreciate if the extra bits needed for this in asp were in the example. *connection string :)
Here is what I would do if someone had asked me the same question as I am asking but about coldfusion:
------------------------------------------------------------------------------------
// Connecting to the mySQL database
<cfquery name="query_name" datasource="database_name" username="your_user" password="your_pass">
// Get data from the database table
SELECT firstName // The table column you'd like to select
FROM table_name // The table
WHERE firstName= '#variable#' // The criteria
</cfquery>
<html>
<head..
some html stuff here...
//Now down the page we can output the data we selected from the database
First names in the database are:<br />
<cfoutput query="query_name">#firstName#</cfouput>
</body>
</html>
------------------------------------------------------------------------------------
^Is it possible for anyone here to give me the equivalent type thing in asp?
And using .asp, you won't be able to output anything to an .html page since they are static,
Sorry, i should have said 'output in html format' :o :p
I know there are plenty of tutorials, I don't generally like tutorials, I have more luck if someone just codes up a very quick and simple example and i can go from there and play around.
mat,
dominicall
11-21-2002, 09:02 AM
Hi mat
There are a number of ways of creating recordsets in ASP/VBscript - I'd thoroughly recommend the 'Beginning ASP 3.0' from Wrox - will give you all the info you need.
If you're using MS SQL server then you can also use Stored Procedures which take a load off the web server and are much quicker - but that's a another story...
Anyhow, if all you need is the data for a table/listing then try this (make sure you include the adovbs.inc file):
Dim strConn
strConn = your connection string here
Dim cmdGetData, rsGetData, rsGetMoreData, rsAndEvenMoreData
Set cmdGetData = Server.CreateObject("ADODB.Command")
cmdGetData.ActiveConnection = strConnection
cmdGetData.CommandText = "SELECT firstname FROM names WHERE firstname = " & 'dave'
cmdGetData.CommandType = adCmdText
Set rsGetData = cmdGetData.Execute
..... now do something with your records
rsGetData.Close()
Set rsGetData = Nothing
.... and always close and destroy the server objects when you're done
Set cmdGetData = Nothing
You can also use the command object to pull multiple recordsets - i.e. all you need to do is redefine the CommandText and re-execute, as follows:cmdGetData.CommandText = "SELECT firstname FROM names WHERE firstname = " & 'dave'
cmdGetData.CommandType = adCmdText
Set rsGetData = cmdGetData.Execute
..... now do something with your records
rsGetData.Close()
Set rsGetData = Nothing
...get some more data
cmdGetData.CommandText = "SELECT surname FROM name WHERE surname = " & 'smith'
Set rsGetMoreData = cmdGetData.Execute
...do some stuff with the data
rsGetMoreData.Close()
Set rsGetMoreData = Nothing
...get even more data
cmdGetData.CommandText = "SELECT age FROM profiles WHERE age >= " & 30
Set rsAndEvenMoreData = cmdGetData.Execute
...do some stuff with the data
rsAndEvenMoreData.Close()
Set rsAndEvenMoreData = Nothing
... then close the command at the end of the page
Set cmdGetData = Nothing
Hope this helps. Hope there's no typos in the above but it gives you an idea.
Dominic :D
dominicall
11-21-2002, 09:45 AM
You're welcome :D
dominicall
11-21-2002, 09:53 AM
BTW - I just noticed your comment about local and remote connections...
Easiest way to deal with this is to use and include file with the connection details in there... as follows:<%
Dim strConnection
'local test connection
strConnection = Your local connection string
'remote live connection
'strConnection = Your remote live connection string
%>Just saves this file in your includes directory as connData.asp or some such name and when you do your includes on each page where you'll need the data connection just put the following include statement in:<!-- #include virtual="/includes/connData.asp" -->
In the code above the live connection is commented out for when you're testing. All you need to make sure you do is to change the commenting out when you upload it to the live server.
Using this technique will also save you a whole load of time, since whenever you need to instantiate the connection all you need to do is:cmdGetData.ActiveConnection = strConnection
You won't need to type it out every time and decreases margin of error when coding.
Once you know it works from the include file then you know it will work every time if you do it this way.
Dominic :D
whammy
11-22-2002, 12:17 AM
Yup, I even go a little further with includes, and if I know I'm using say, the same dropdown on several pages or whatever, I just include all of them as subroutines in a separate file:
'Dimension (Dim) all variables used in your subroutines
Sub MySubName()
'code here
End Sub
Sub MySubName2()
'code here
End Sub
Then after you have included the file at the top of the page, you can just say:
Call MySubName()
To do whatever code you put in the subroutine... whether it be a database connection, writing an HTML dropdown, etc.
This takes a LITTLE bit of extra work at first, but in the long run it saves you time since if you split your code up like this, it makes it easier to rearrange if changes need to be made.
:)
whammy
11-22-2002, 01:04 AM
Yeah, here's an example for validation (using functions, not subroutines, but it's the same idea):
http://www.solidscripts.com/downloads/regex.txt
I usually add functions that I use to this file and just include them in any page where I will need a few of those functions.
vBulletin® v3.8.2, Copyright ©2000-2012, Jelsoft Enterprises Ltd.