Ways of writing to a db.

[Morgoth]

Ok, for about a year now, I have been always been using the SQL statements and such to write to a db.

Example:

<%
Set oConn = Server.CreateObject("ADODB.Connection")
StrConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db.mdb") & ";"
oConn.open StrConn
SQL = "INSERT INTO table (Info) VALUES ('" & StrInfp & "')"
Set oRS = oConn.Execute(SQL)


I know there are other methods then this. Would anyone be able to tell me? Am I making sence?



This also beings me to another question:
I am not too sure how ADODB.Connection or ADODB.RecordSet are different. Does it matter which one I use? Even at the slightest bit? Will using either one for scripts give me errors depending on the script I use? And are there other ADODB._____ things?



Thank you for the answers you will give me... This is really help me.

[RadarBob]

Ok, here goes with the blind leading the blind.

There are 3 ADODB._______ objects: Connection, Recordset, and Command.

Yes, you can use a connection w/o a recordset, and in fact vice versa; but what you lose is some control over the settings for a given object.

You could access a DB directly w/ a recordset, the needed connection is created automagically. But with a recordset you can iterate through the set of records - which you can't do with a connection object.

In already written stuff I'm working with the original coders used all 3 objects - clearly overkill. They actually executed the same SQL stored procedure twice! Once in the Command object, and once again in the Recordset object. This just goes to show how poorly these gigantic doorstop books we all buy really don't explaing things well enough. Unfortunately none of the ASP books I've seen really explains it all. I cannot get a full picture of the why's and wherefores of when and how to use these 3 ASP objects.

I've done DB access using only Connection and Recordset. That worked too. By defining my own Connection I have more control over access to the data itself. By using a Recordset I can iterate through the recordset one record at a time and do what I need - like "build" the data into HTML table rows. But clearly when your access & data manipulation is very straight forward you can do it with a Recordset alone.

[Mhtml]

Morgoth are you making something which is largely DB dependant? Just curious as to your last two posts, although I have really learnt some db stuff from them.;)

[Morgoth]

RadarBob, just to get myself clear. Me not knowing exactly what one I should use, would you say it would be safe to use Recordset as my default? Meaning, everytime I write up a script i should just put recordset in there?

So far what I see, there is no problems with using one over the other, but maybe it's because it's the scripts I have making.


Mhtml, I am using the db to store alot of information for alot of small things as well as large things. Such as a forum, or a poll. Or a news section, or some for of a buglist. Very simple things, but I am still learning about db's as much as ASP. I have only been at it for a year, I'm not an expert in it. And so far, most of the work I do is not much to learn more of asp, mostly just to get what I want done.
I'll get into experimenting when I finish my work for my site. :)



I still want to know other methods of writing the data to a db.

I think I have seen another meathod that uses RS._? Would I be mistaken?
I need your expert help on this too, whammy.

[allida77]

Command:

Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = objConn
objComm.CommandType = adCmdText

objComm.CommandText = "INSERT INTO table (Info) VALUES ('" & StrInfp & "')"
objComm.Execute

If you want to grab a rs then do:

objRS = objComm.Execute

RS:

objRS.MoveFirst
objRS.Find "ID_NBR = '" & Request.Form("hdnID") & "'"
objRS("Afield") = StrInfp
objRS.Update

[RadarBob]

RadarBob, just to get myself clear. Me not knowing exactly what one I should use, would you say it would be safe to use Recordset as my default? Meaning, everytime I write up a script i should just put recordset in there?


Only use recordset when you need them. I'm inexperienced at this ASP stuff too; all I was saying is that existing code I'm working with does it different from what you do, but it works too.


I still want to know other methods of writing the data to a db.

OK, you asked for it..... :D
Our database has controlled access and the Connection object is used to not only open a connection to the DB but to also help verify user access permissions... User ID's and passwords are passed in the Connection object and an ASP page checks the user table in the data base to endure this particuar user has read, write, or write to specific tables access.

Here is how we've set up some security....

Data tables are accessable ONLY by stored procedures. We have Windows NT directory permissions set up to facilitate that. What this means is that there is no "in-line" SQL, only calls to SQL stored procedures. If I did put SQL in-line, like you do when you create a connection - that simply would not work on our system.

Next, ONLY "roles" have access to tables (this is set at the database/table level in SQL server), not individual users. Next, individual users are assigned to roles such as "read" "maintenance", etc. depending on their access needs.

And finally, we have a file of code that is #included with every web page. This ASP page, executing on the server of course, gets user ID and password info passed via the Connection object and looks up that user's access permissions in a table.

So.. we do security checking before we access any table - so we use the Connection object to do all that... Then if this user has the right DB access, the stored procedure is executed and a Recordset passes the data to the client.

[Morgoth]

allida77, that's exactly what I was trying to find. Thank you.


RadarBob, you have out done yourself. All that souns quite complicated, and I wouldn't touch that with a 10 foot long, clown pole. I'll just make sure no one can find my db :) :) :)