Re: insert Q

POLILOP wrote:
>> Nope. I meant that you should not use a string as a Command's
>> ActiveConnection. Use an explicit Connection object/
>>
> Little more Help
>
> if i'm not mistaken this should be explicit
>
> cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("/<pathtofile.mdb>")
>
> set conn = CreateObject("ADODB.Connection")
> conn.open cst

Correct
>
> 1. so i execute the sql with
> conn.execute SQL

Sort of. When you use a connection's Execute method, a Command object is
created in the background to do the actual execution. Implicit Command
objects are OK.

In addition, always tell ADO what type of command you are executing and if
you don't expect the command to return records.
1 = adCmdText - a string containing a sql statement
128 = adExecuteNoRecords - no records are being returned so don't create a
recordset to retrieve them

These can be combined by adding them to get 129:

conn.execute SQL,,129

> or read recordsets with conn.Fields.Item("ITEM").value

No, don't do this. Always use an explicit recordset object:

Set rs = conn.execute(SQL,,1)

> (still puzzled whats the difference)

The documentation can be found at
http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadooverview.asp

Basically:
A Connection object implements the connection to the database.
A Command object executes a command (can be a query, the name of a table, or
a stored procedure)
A Recordset object contains the results of the execution of a command that
returns records (a select statement or a stored procedure/saved query that
contains a select statement)

You should control when these objects are created and destroyed (set to
nothing), because
1. They are "heavy" objects, especially the recordset, so needlessly
allowing it to be created can waste resources
2. Failure to close and destroy them in the proper order can lead to memory
leaks which can ultimately crash IIS. The idea is to close and destroy the
child objects (recordsets) before closing and destroying the parent object
(connection)
>
> 2, wondering, if i have the conn.open can i do different SQL
> statments

Yes, of course.

> Before closing it ( I need to make a select, put a Rs into
> a variable and then make a insert with that variable being a value ?

Yes, use a single Connection object.

My recommendation is to avoid using dynamic sql due to the dangers of sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

I prefer using stored procedures (saved parameter queries in Access):
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplain
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

But you can also use an explicit Command object to pass parameters to a
string containing ODBC parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: More ASP.Net Newbie Questions
    ... The Command is then what you're doing with this connection, ... In regards to your final point, making grids and controls in general do ... > Connection and Recordset objects into, like, 37 different things. ...
    (microsoft.public.dotnet.framework.aspnet)
  • ADO recordset closed, cannot read
    ... puts it into the spreadsheet using the CopyFromRecordset command. ... I was using an ADODB Recordset object but I ... I changed it to a plain ADO connection instead. ... 'Initialize parameters ...
    (microsoft.public.excel.programming)
  • Re: Recordset Closed After Having Just Being Opened
    ... by 'pass the command object to a recordset as the source parameter'. ... You must set the command.ActiveConnection to an already-opened connection ...
    (microsoft.public.vb.database.ado)
  • Re: How to fill a Client Side Recorset with a command (sp exec)
    ... To be able to open recordset on a client side using Command, ... CursorLocation property of the Connection to adUseClient ... > command from a connection, but i´m having problems with the parameters. ...
    (microsoft.public.data.ado)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)