SQL using Dreamweaver Ultradev

Is there a better way?

[Andrew Thomas]

IIm using dreamweaver, and connecting to a database with SQL select statements, the code generated looks like this for 2 SQL's

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/sql.asp" -->
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_sql_STRING
Recordset1.Source = "SELECT c.cat_name FROM type t, category c, cats_to_types ctt WHERE t.type_name LIKE 'Food' AND t.type_id = ctt.type_id AND ctt.cat_id = c.cat_id "
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
set selecttype = Server.CreateObject("ADODB.Recordset")
selecttype.ActiveConnection = MM_sql_STRING
selecttype.Source = "SELECT t.type_name FROM type as t "
selecttype.CursorType = 0
selecttype.CursorLocation = 2
selecttype.LockType = 3
selecttype.Open()
selecttype_numRows = 0
%>

Is there a more efficient way of doing this, and is all the code needed.

thanks

[topr8]

one thing you can do is actually write the queries into the actual database,

and call the query rather than a table with select statements

[Andrew Thomas]

Thanks,

Im using an Access database, how would i do this, and what code would i need.

[Lextech]

Good topic - I'm interested in this too.

[topr8]

using access, you can use the query builder to create queries directly in the database, you can use use the query building wizard available from the database window, Queries> New > simple query wizard

or you can just build them in design view, it is very easy once you get the hang of it, the access help gives some examples of what to do.

the idea is to build the query directly into the database so the database engine does the sorting rather than the server, you need to include all the parameters that you are using on the page but no others, thats the point of it.

in ultradev when you create the recordset chose the access query from the dropdown list of Tables, you still will need to do the WHERE statements as before.

this just gives you cleaner code, it also puts less pressure on the server, additionally if you are working with tables with complicated joins, the SQL statements on the page begin to get really long and complex, but if you built the query into the db its a snap.

regarding this section of code ... yes you need it all

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3

[4guysfromrolla.com ]

[txbakers]

You don't need all of the query bulding code in Dreamweaver Ultradev. The cursor types, etc. are mandatory, but you can hand code many of the SQL statements.

The Insert, Update, and Delete routines in DWU are especially bloated.

Essentially, an update statement looks like this:

update table set field = new value where field = XXX.

The code from Dreamweaver is so bloated. I usually yank it out and write my own.

The select statements are not as bad.

[Andrew Thomas]

Thanks for the help, ive just bought a book Beginning ASP Databases, only started reading it, but picking up a few good pointers. Getting to writing SQL in Access soon

thanks

[Andrew Thomas]

I was getting on fine, havent made any major changes but i now get

HTTP 500 - Internal server error
Internet Explorer

The services are still running, and DSN name etc is fine

Cant find the problem, any ideas?

[txbakers]

Just back out your changes, however minor, one by one until it works again.

It's hard to say what the error can be.

Also, have you tried restarting IIS?

[New_Alex]

Interesting topic people.

Is it any book that explains how MS access can be used for development of SQL databases??

Alex