insert Q

[polilop]

I have a table of persons that have a Personal id (Not auto Number) which is
a Primary key.
When i do a insert i have to check if it allready exists in the table. I do
this by by doing a select of that
Personal id and if its not there then i insert it.
Is there a way to do that in one step, something like try to insert and if
it fails write out that it allready exists.
If possible how!!!
thx

[Bob Barrows [MVP]]

The answer depends on the type and version of database you are using.
Never ask a database-related question without telling us what database you
are using.
By your use of the term "auto Number" I might guess that you are using
Access, but it would be better if you did not make me guess.

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"

[polilop]

yes it's access, Here is the insert

InsertTXT="INSERT INTO SOMETABLE WHERE SOMETHING = SOMETHING"

Set RS_Insert = Server.CreateObject("ADODB.Command")
RS_Insert.ActiveConnection = strCon
RS_Insert.commandText = InsertTXT
RS_Insert.Execute
RS_Insert.ActiveConnection.Close
Set Rs_Insert=Nothing

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OaJt$Dt4FH...@TK2MSFTNGP15.phx.gbl...

[Bob Barrows [MVP]]

polilop wrote:
> yes it's access, Here is the insert
>
> InsertTXT="INSERT INTO SOMETABLE WHERE SOMETHING = SOMETHING"

This is not a valid insert statement, but I'm sure you knew that ...

>
> Set RS_Insert = Server.CreateObject("ADODB.Command")

You don't need a Command object, unless you are passing parameters
(recommended)

> RS_Insert.ActiveConnection = strCon

It's a bad idea to use implicit connections. Create a connection object and
use it. You don't want to turn off connection pooling, especially with
Access

> RS_Insert.commandText = InsertTXT
> RS_Insert.Execute
> RS_Insert.ActiveConnection.Close
> Set Rs_Insert=Nothing

What you have to do is modify your insert statement so it looks something
like this:

insert into sometable (field list)
select top 1 <data list> from MSysobjects
where not exists (select * from sometable where id = someid)

Then use the "records affected" argument when executing the statement. Using
dynamic sql (not recommended) it would look like this:

dim lrecs, cn
set cn=createobject("adodb.connection")
cn.open strCon
cn.execute InsertTXT,lrecs,129
if lrecs > 0 then
'record inserted
else
'id already existed
end if

If you want me to be more specific, then provide more details

[polilop]

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message

news:Ofodhix4...@TK2MSFTNGP12.phx.gbl...

> polilop wrote:
>> yes it's access, Here is the insert
>>
>> InsertTXT="INSERT INTO SOMETABLE WHERE SOMETHING = SOMETHING"
>
> This is not a valid insert statement, but I'm sure you knew that ...

Sorry, clumsy i guess.

>
>>
>> Set RS_Insert = Server.CreateObject("ADODB.Command")
>
> You don't need a Command object, unless you are passing parameters
> (recommended)

Nice. Good Tip Thx

>
>> RS_Insert.ActiveConnection = strCon
>
> It's a bad idea to use implicit connections. Create a connection object
> and use it. You don't want to turn off connection pooling, especially with
> Access

the strCon is just a string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=
C:\Inetpub\wwwroot\diplomski\database\kolokvij.mdb;"
I have to tell it where the database is, or do you mean that i use DSN and
with DSN pooling works (if too much bother explaining a good web link be
nice)?????

>
>> RS_Insert.commandText = InsertTXT
>> RS_Insert.Execute
>> RS_Insert.ActiveConnection.Close
>> Set Rs_Insert=Nothing
>
> What you have to do is modify your insert statement so it looks something
> like this:
>
> insert into sometable (field list)
> select top 1 <data list> from MSysobjects

what is 'MSysobjects' an what dose 'select top 1 <data list> from
MSysobjects' do????

> where not exists (select * from sometable where id = someid)

**********************************************
This is just one SQL statment( goes into the InsertTXT) ?????

> insert into sometable (field list)
> select top 1 <data list> from MSysobjects
> where not exists (select * from sometable where id = someid)

Can u do that????
***********************************************

[Bob Barrows [MVP]]

polilop wrote:
>>> RS_Insert.ActiveConnection = strCon
>>
>> It's a bad idea to use implicit connections. Create a connection
>> object and use it. You don't want to turn off connection pooling,
>> especially with Access
>
>
> the strCon is just a string
> strCon = "DRIVER={Microsoft Access Driver (*.mdb)};

http://www.aspfaq.com/show.asp?id=2126

> uid=;pwd=; DBQ=
> C:\Inetpub\wwwroot\diplomski\database\kolokvij.mdb;"
> I have to tell it where the database is, or do you mean that i use
> DSN and with DSN pooling works (if too much bother explaining a good
> web link be nice)?????
>
>

Nope. I meant that you should not use a string as a Command's
ActiveConnection. Use an explicit Connection object/

>>
>>> RS_Insert.commandText = InsertTXT
>>> RS_Insert.Execute
>>> RS_Insert.ActiveConnection.Close
>>> Set Rs_Insert=Nothing
>>
>> What you have to do is modify your insert statement so it looks
>> something like this:
>>
>> insert into sometable (field list)
>> select top 1 <data list> from MSysobjects
>
> what is 'MSysobjects'

MSysObjects is a system table that is always guaranteed to contain at least
one row.

> an what dose 'select top 1 <data list> from
> MSysobjects' do????
>

It causes a single row of data to be generated

>> where not exists (select * from sometable where id = someid)
>
>
>
> **********************************************
> This is just one SQL statment( goes into the InsertTXT) ?????
>

Yes

>> insert into sometable (field list)
>> select top 1 <data list> from MSysobjects
>> where not exists (select * from sometable where id = someid)
>
> Can u do that????
> ***********************************************

Absolutely. Try it

[POLILOP]

> 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

1. so i execute the sql with
conn.execute SQL
or read recordsets with conn.Fields.Item("ITEM").value
(still puzzled whats the difference)

2, wondering, if i have the conn.open can i do different SQL statments
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 ?

[Bob Barrows [MVP]]

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

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:
> What you have to do is modify your insert statement so it looks
> something like this:
>
> insert into sometable (field list)
> select top 1 <data list> from MSysobjects
> where not exists (select * from sometable where id = someid)
>

I goofed here. i forgot the system tables (MSysObjects is a system table)
could be inaccessible from ASP. You should create a table containing a
single record that will replace "MSysObjects" in the above statement. The
table can contain any number of fields, even one. Since it will contain only
a single record, you can eliminate the "top 1" from the above statement.

[POLILOP]

Thx for patience and information
this has been more then helpful

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message

news:O8RXoS54...@TK2MSFTNGP15.phx.gbl...

[polilop]

Been following your advice,did not realize what loss of connection's i was
making. Allso the idea of using access views is so clean and easier (not so
many sql statements on the page).
cannot express my gratitude for the help, now my work seems to look a bit
more serius.
THX again.

"POLILOP" <fmatosic@@inet.hr> wrote in message
news:eE5Q2f5...@TK2MSFTNGP10.phx.gbl...

[Bob Barrows [MVP]]

polilop wrote:

> cannot express my gratitude for the help

You just did. I'm glad to have been of help.