If record exists if not Insert

I need some guidance on how to handle an issue. I have an .asp page
that correctly queries a table and returns data if a 'job number' and
week ending date exist and the user can update the information that is
there. What I need to do is, if a record does not exist the page
needs to create one and then refresh/requery so the user can edit the
data. Any suggestions on how to accomplish this?

THanks

Re: If record exists if not Insert

"Matt" wrote in message
news:1172509233.886974.131350@m58g2000cwm.googlegroups.com.. .
>I need some guidance on how to handle an issue. I have an .asp page
> that correctly queries a table and returns data if a 'job number' and
> week ending date exist and the user can update the information that is
> there. What I need to do is, if a record does not exist the page
> needs to create one and then refresh/requery so the user can edit the
> data. Any suggestions on how to accomplish this?
>
> THanks
>

If Not rs.EOF Then
'show existing record
Else
'Insert new record into db
'Get its ID (SCOPE_IDENTITY/@@IDENTITY)
'Retrieve it
'Display it in edit mode
End If

Does that clarify your approach for you?

--
Mike Brind

Re: If record exists if not Insert

On Feb 26, 9:20 am, "Mike Brind" wrote:
> "Matt" wrote in message
>
> news:1172509233.886974.131350@m58g2000cwm.googlegroups.com.. .
>
> >I need some guidance on how to handle an issue. I have an .asp page
> > that correctly queries a table and returns data if a 'job number' and
> > week ending date exist and the user can update the information that is
> > there. What I need to do is, if a record does not exist the page
> > needs to create one and then refresh/requery so the user can edit the
> > data. Any suggestions on how to accomplish this?
>
> > THanks
>
> If Not rs.EOF Then
> 'show existing record
> Else
> 'Insert new record into db
> 'Get its ID (SCOPE_IDENTITY/@@IDENTITY)
> 'Retrieve it
> 'Display it in edit mode
> End If
>
> Does that clarify your approach for you?
>
> --
> Mike Brind

Thanks. That is what I was thinking as well. I added this piece to
my page:

IF objRecordset.EOF then
Set objRecordset = conn.Execute(strSQL_Insert)
Else

What command should I use to get back to the edit mode?
Reponse.Redirect(xxxxxx) ?? THanks again

Re: If record exists if not Insert

On Feb 26, 9:48 am, "Matt" wrote:
> On Feb 26, 9:20 am, "Mike Brind" wrote:
>
>
>
>
>
> > "Matt" wrote in message
>
> >news:1172509233.886974.131350@m58g2000cwm.googlegroups.com. ..
>
> > >I need some guidance on how to handle an issue. I have an .asp page
> > > that correctly queries a table and returns data if a 'job number' and
> > > week ending date exist and the user can update the information that is
> > > there. What I need to do is, if a record does not exist the page
> > > needs to create one and then refresh/requery so the user can edit the
> > > data. Any suggestions on how to accomplish this?
>
> > > THanks
>
> > If Not rs.EOF Then
> > 'show existing record
> > Else
> > 'Insert new record into db
> > 'Get its ID (SCOPE_IDENTITY/@@IDENTITY)
> > 'Retrieve it
> > 'Display it in edit mode
> > End If
>
> > Does that clarify your approach for you?
>
> > --
> > Mike Brind
>
> Thanks. That is what I was thinking as well. I added this piece to
> my page:
>
> IF objRecordset.EOF then
> Set objRecordset = conn.Execute(strSQL_Insert)
> Else
>
> What command should I use to get back to the edit mode?
> Reponse.Redirect(xxxxxx) ?? THanks again- Hide quoted text -
>
> - Show quoted text -

Re: If record exists if not Insert

"Matt" wrote in message
news:1172512122.290985.95270@8g2000cwh.googlegroups.com...
> On Feb 26, 9:20 am, "Mike Brind" wrote:
>> "Matt" wrote in message
>>
>> news:1172509233.886974.131350@m58g2000cwm.googlegroups.com.. .
>>
>> >I need some guidance on how to handle an issue. I have an .asp page
>> > that correctly queries a table and returns data if a 'job number' and
>> > week ending date exist and the user can update the information that is
>> > there. What I need to do is, if a record does not exist the page
>> > needs to create one and then refresh/requery so the user can edit the
>> > data. Any suggestions on how to accomplish this?
>>
>> > THanks
>>
>> If Not rs.EOF Then
>> 'show existing record
>> Else
>> 'Insert new record into db
>> 'Get its ID (SCOPE_IDENTITY/@@IDENTITY)
>> 'Retrieve it
>> 'Display it in edit mode
>> End If
>>
>> Does that clarify your approach for you?
>>
>> --
>> Mike Brind
>
> Thanks. That is what I was thinking as well. I added this piece to
> my page:
>
> IF objRecordset.EOF then
> Set objRecordset = conn.Execute(strSQL_Insert)
> Else
>
> What command should I use to get back to the edit mode?
> Reponse.Redirect(xxxxxx) ?? THanks again
>

I don't know how you have structured your pages, although it seems to me
that whatever happens, you are going to return a record - either an existing
one or the newly added one, so I can't see that your approach to the edit
mode will be any different now to the one you have planned for an existing
record.

One thing I would suggest - get a pen(cil) and paper out, if you haven't
already. Staring at an ASP editor is not the way to plan the flow of
execution of an app. Write and plan each bit using a kind of pseudo-code
approach that I gave earlier. Or use boxes and arrows. Or even just draw
pictures. It will help you clarify things a lot easier in your own mind.

--
Mike Brind

Re: If record exists if not Insert

On 26 Feb 2007 09:00:33 -0800, "Matt" wrote:

>I need some guidance on how to handle an issue. I have an .asp page
>that correctly queries a table and returns data if a 'job number' and
>week ending date exist and the user can update the information that is
>there. What I need to do is, if a record does not exist the page
>needs to create one and then refresh/requery so the user can edit the
>data. Any suggestions on how to accomplish this?
>
>THanks

A conditional INSERT can be achieved via a subquery for
data-SELECTion:

INSERT INTO MyTable
SELECT 12345 FROM [table with one record, or use DISTINCT]
WHERE NOT EXISTS
(SELECT * FROM MyTable WHERE [condition if record exists])

B.

Re: If record exists if not Insert

"Matt" wrote in message
news:1172512122.290985.95270@8g2000cwh.googlegroups.com...
> On Feb 26, 9:20 am, "Mike Brind" wrote:
>> "Matt" wrote in message
>>
>> news:1172509233.886974.131350@m58g2000cwm.googlegroups.com.. .
>>
>> >I need some guidance on how to handle an issue. I have an .asp page
>> > that correctly queries a table and returns data if a 'job number' and
>> > week ending date exist and the user can update the information that is
>> > there. What I need to do is, if a record does not exist the page
>> > needs to create one and then refresh/requery so the user can edit the
>> > data. Any suggestions on how to accomplish this?
>>
>> > THanks
>>
>> If Not rs.EOF Then
>> 'show existing record
>> Else
>> 'Insert new record into db
>> 'Get its ID (SCOPE_IDENTITY/@@IDENTITY)
>> 'Retrieve it
>> 'Display it in edit mode
>> End If
>>
>> Does that clarify your approach for you?
>>
>> --
>> Mike Brind
>
> Thanks. That is what I was thinking as well. I added this piece to
> my page:
>
> IF objRecordset.EOF then
> Set objRecordset = conn.Execute(strSQL_Insert)
> Else
>
> What command should I use to get back to the edit mode?
> Reponse.Redirect(xxxxxx) ?? THanks again

You can pass compound SQL statements to ADO, that can insert a row and
return it in one round trip:

SET NOCOUNT OFF;
Declare @id int
INSERT INTO MyTable (f1, f2, f3) VALUES (1, 2, 3);
Set @id = SCOPE_IDENTITY();
SELECT * FROM MyTable WHERE IDENTITYCOL = @id;

But in reality, since you are inserting a blank record, all you need to do
is return SCOPE_IDENTITY().


-Mark