Re: insert Q

Thx for patience and information
this has been more then helpful

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:O8RXoS54FHA.3188@xxxxxxxxxxxxxxxxxxxxxxx
> 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: UPDATE query in Access 2003 raising error
    ... The only reason I questioned the recordset is that you could conceivably ... the SQL looks fine to me. ... Set qdfTemp = db.CreateQueryDef ... Elsewhere in the code I use the same technique to execute an SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: MySQL - VB6, Excel , VBA - Stored Procedures
    ... with CreateParameter method. ... ' Create recordset by executing the command. ... Set rstByRoyalty = .Execute ...
    (microsoft.public.vb.database.ado)
  • Re: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT I
    ... This statement causes ADO to create a recordset to receive the results ... of the query being executed and assign that recordset to your qryAddProj ... You should use the ExecuteOptions argument of the Execute ... string containing a sql statement to be executed. ...
    (microsoft.public.data.ado)
  • Re: Running a program from a SQL job
    ... This will allow you to go to a SQL prompt:- ... >> Use XP_CMDSHELL to fire a OS command from command prompt. ... I've made a bat file that contains the string to run to ... When I set the SQL job to execute the same ...
    (microsoft.public.sqlserver.server)
  • Re: ADODB.Recordset: Operation is not allowed when the object is closed
    ... > ' Create the ADO Connection and Recordset objects. ... > ' Set the connection string, open the connection and execute the ... If your sql string is an insert/update/delete statement, ...
    (microsoft.public.scripting.vbscript)
Quantcast