473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Feb 26 '07 #1
6 8124

"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
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
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

"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
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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Mark | last post by:
I have a working PHP/MySQL application used for data entry. The data entry screen includes a "Save" button. The PHP code for this button looks like this: if (isset($_POST)) { if ($_POST ==...
0
by: Phil Bitis | last post by:
CREATE TABLE tbllayer ( LayerID int(11) NOT NULL default '0', LayerSize int(11) NOT NULL default '0', IceTypeID int(11) NOT NULL default '0', Fingerprint char(16) binary default NULL, PRIMARY...
5
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one...
7
by: Ilan Sebba | last post by:
I am trying to add a record using SQL. My problem is that the primary keys are foreign keys, and these foreign keys are autonumbers. I therefore do not know the primary keys of the record I am...
4
by: amywolfie | last post by:
I've been trying to do something for about 3 days – I get close, but not 100%. I am trying to: Open frmRevisionHistory from a button on frmFeeInput. If there is NO RELATED RECORD, then I...
20
by: Bryan | last post by:
hello all... im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and...
8
by: shorti | last post by:
Here is an example of what I want to do (syntax might not be entirely correct as this is just an example): CREATE TABLE ParentA ( name CHAR (6) NOT NULL; address CHAR(64); ) IN CUSTOMER_TS...
1
by: yeohyc | last post by:
Hi All, I have a problem here. I have done a request for move order it was stored into a custom table with the format: (O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500...
2
by: Question123 | last post by:
Hi i have one database table Table1.which contains almost 20000000 recordes. record to this table are inserted through storedprocedure. storedprocedure takes parameter as "value", Beginningdate,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.