By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,336 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

If record exists if not Insert

P: n/a
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

Feb 26 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"Matt" <mw******@caldrywall.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.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
Feb 26 '07 #2

P: n/a
On Feb 26, 9:20 am, "Mike Brind" <d...@newsgroups.comwrote:
"Matt" <mwago...@caldrywall.comwrote in message

news:11**********************@m58g2000cwm.googlegr oups.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

Feb 26 '07 #3

P: n/a
On Feb 26, 9:48 am, "Matt" <mwago...@caldrywall.comwrote:
On Feb 26, 9:20 am, "Mike Brind" <d...@newsgroups.comwrote:


"Matt" <mwago...@caldrywall.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.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 -

Feb 26 '07 #4

P: n/a

"Matt" <mw******@caldrywall.comwrote in message
news:11*********************@8g2000cwh.googlegroup s.com...
On Feb 26, 9:20 am, "Mike Brind" <d...@newsgroups.comwrote:
>"Matt" <mwago...@caldrywall.comwrote in message

news:11**********************@m58g2000cwm.googleg roups.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
Feb 26 '07 #5

P: n/a
On 26 Feb 2007 09:00:33 -0800, "Matt" <mw******@caldrywall.comwrote:
>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.
Feb 27 '07 #6

P: n/a

"Matt" <mw******@caldrywall.comwrote in message
news:11*********************@8g2000cwh.googlegroup s.com...
On Feb 26, 9:20 am, "Mike Brind" <d...@newsgroups.comwrote:
>"Matt" <mwago...@caldrywall.comwrote in message

news:11**********************@m58g2000cwm.googleg roups.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


Mar 1 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.