If record exists if not Insert

[Matt]

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

 

[Mike Brind]

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?

 

[Matt]

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?

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

 

[Matt]

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 -

 

[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.

 

[Brian Tkatch]

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.

 

[Mark J. McGinty]

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