Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

updating two tables from a form

Posted on 2005-05-03
8
Medium Priority
?
192 Views
Last Modified: 2010-04-23
Hi
I have got a database which includes the following two tables:
tbl_job
tbl_call

when i create a new record in  tbl_job, i want to automatically create a record in the call table with the primary key of tbl_job (jobid) as a foreign key in the table tbl_call... i will also want to enter some other default data:
calldate=<todays date>
comments="default text"
consultantID = <from the form>
jobID=<the primary key from the record written to tbl_job>

I was originally thinking of doing this with a trigger but then realised that it might not be possible since i need form data as well as the tbl_job primary key.

Is it possible to do this all in one go? if so how would i do it? is it best to do it in code or as a trigger? or should i create two pages, write the row to tbl_job first and then create the record for the call seperately (something i would prefer not to do)

any thoughts?

thanks
Nick
0
Comment
Question by:lz7cjc
  • 5
  • 3
8 Comments
 

Author Comment

by:lz7cjc
ID: 13928307
Please note... i am not looking for the code answer at this stage... more some sort of pseudo code answer that explains the best way to approach this... if i then get stuck in implementing the solution i will post relevant questions to resolve

thanks
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 200 total points
ID: 13955573
Why not use a stored procedure to create both records.  Pass in the necessary information as parameters.  In the stored procedure, you can access the newly created primary key using @@Identity

create PROCEDURE proc_tblJob_Insert
    (
    @Jobname varchar(50),
    @ConsultantID int,
    @calldate datetime
    )
AS
    Declare @NewID int
    INSERT INTO
        [tblJob]
    (
        [Jobname]
       
    )
    VALUES
    (
        @Jobname
    )
    SELECT @NewID = @@IDENTITY
   
    INSERT INTO
        [tblCall]
    (
        JobID,
        ConsultantID,
        Calldate
    )
    VALUES
    (
        @NewID,
        @ConsultantID,
        @calldate
    )

GO

Details of this approach can be found here: http://www.princeton.edu/~rcurtis/ultradev/tutorial3.html

Let me know if you need help with the VB data access code that creates the command and parameter objects to run the stored procedure.
0
 

Author Comment

by:lz7cjc
ID: 13957808
thanks Paul
I will try this today and if i can get the sproc working - if i can't i shall get back to you on this!

i will post another question should i need help on the command and parameter objects
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lz7cjc
ID: 13959995
Hi Paul
I have pasted this in and the syntax checks out... however i want to check it runs properly but can't see where i can run the Sproc from... i assume it is going to ask me for the various values but i just want to see what it is doing when it executes before i start asking how i pass it the values from the page

thanks
Nick
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 13960225
Easiest way to test it would be to call it from Query Analyser if you have Sql Server, or use oSQL if you have MSDE.  The syntax to call a stored proc using either of these tools would be like this:

exec procname parm1, parm2, parm3, ... etc.

For the procedure I posted above, it would look like this:

exec  proc_tblJob_Insert 'Jobname', 1, '2005/5/9 10:00:00 AM'
0
 

Author Comment

by:lz7cjc
ID: 13960596
fantastic
now i need to know how to pass the variables from a web page to the Sproc... i shall close this question and open another one so I can award you the points and offer you somemore

thanks for your help so far Paul
Nick
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 13960928
Looks like you have an answer already.  :)  Thanks for the points and the grade.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question