SELECT * Happy!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey ya'll.

Is this SELECT * necessary? I am looking at some previously written code.
Help me understand please. Why do we SELECT * From a table if we are never
going to USE any of the data. The programmer only ever does AddNew so I was
just wondering If I could tune it better. A SELECT Statement is not needed
right? Or is it needed to get ALL existing records OUT OF THE WAY?

' OPEN THE LOAD LOG
lSQL = "SELECT * FROM LOADLOG"
set lrst = Server.CreateObject("ADODB.Recordset")
lrst.Open lSQL, con, adOpenStatic, adLockOptimistic, adCmdText

lrst.AddNew
lrst("SCRIPT") = "reset_ranks"
lrst("NOTE") = "RESET RANKS SCRIPT STARTING"
lrst.Update
 
You need a recordset that contains all of the fields, and is linked to the
table. However, if all you're doing is adding to the recordset, and never
looking at the values that already exist, you don't actually need the
recordset to come populated.

You could probably get away with:

lSQL = "SELECT * FROM LOADLOG WHERE False"
 
' OPEN THE LOAD LOG
lSQL = "SELECT * FROM LOADLOG"
set lrst = Server.CreateObject("ADODB.Recordset")
lrst.Open lSQL, con, adOpenStatic, adLockOptimistic, adCmdText
Click to expand...
Is this SELECT * necessary?
Click to expand...

Yes: you need a recordset to add records to. What you don't need is to
fetch all the records in the table (and, incidentally, lock them too).
You can be kinder on your network by specifying something like

lSQL = "SELECT Script, Note FROM LoadLog WHERE 0"

which reads an empty recordset and only holds the two fields.
I was just wondering If I could tune it better.
Click to expand...

Yes: you can use the thing like a database..! Try this:

lSQL = "INSERT INTO LoadLog (Script, Note) " & vbNewLine & _
"VALUES(""reset_ranks"", ""RESET RANKS SCRIPT STARTING"")"

' I am not an ADO expert, so take this with a pinch of help file
' I do remember that you need a Command object to carry out a
' command, rather than returning rows
'
Set cmd = New ADODB.Command
cmd.Connection = myConnection
cmd.Text = lSQL
cmd.Type = adCmdText
cmd.Open

Hope that helps


Tim F
 
Back
Top