locked
SQL Insert statement with Join RRS feed

  • Question

  • Hello everyone,

    I'm trying to create an application using .net and linking to sql database, I have a problem after I create the "Insert and Update stored procedure" 

    database background:

    I have create a table (lets call  TableOne)  in DatabaseOne

    TableOne Column: ID, FranchiseID, JobID...etc.....

    the project background:

    I will going to create a win form project with a datagrid view which is link to the sql database and get the TableOne data (calling a "select statement" stored proce from the database

    SELECT
        AccidentID,
       F.[Name],
        JobID,......

    FROM    DatabaseOne A
    INNER JOIN  [DatabaseTwo].[dbo].[TableFranchise] F on A.FranchiseID = F.FranchiseID

    ....

    WHERE  AccidentDate BETWEEN @DateFrom AND @DateTo

    .....

    As you can see here the store proc is getting the column "Name " from another database call DatabaseTwo.TableFranchise, and I use the inner join here so the datagridview can display the Name (not the FranchiseID) for user.

    But my question here's

    The Franchise Name column on the grid is combo box column which I want the user and update the fields. And I also want the user Insert new row of data on the grid as well.

    Now the column value on the datagrid is a Franchise Name, if the user try to insert a new row of data, they will select the Franchise Name on the that column right, and the data will passing into the TableOne, however, the columns on TableOne is FranchiseID not Franchise Name.

    I created the store procedure (as below) for insert new data which is passing the Franchise ID into the TableOne, I did try to use pull " FROM   DatabaseOne A
    INNER JOIN  [DatabaseTwo].[dbo].[TableTwo] F on A.FranchiseID = F.FranchiseID"  just before the closing bracket but I get error.....

    And I can't create the stored proc passing the Franchise Name as the parameter into the table can I? because the Table One don't have Franchise Name column.

    Dose anyone know how can I sort out this problem please? I'm stuck , I can't think now....

    Appreciate for any advise.

    Many thanks

    ninjaPerson

    here's the stored proc:

    CREATE PROCEDURE [dbo].[spInsertTypeOthers]
    @FranchiseID varchar(5),
    @JobID varchar(7),
    @Implications varchar(50)
    AS
    INSERT INTO dbo.DatabaseOne
    (
    FranchiseID,
    JobID,

    Implications
    )
    VALUES
    (
    @FranchiseID,
    @JobID,
    @Implications

    .......

    )




    • Moved by Papy Normand Saturday, July 28, 2012 2:36 PM Creation of a stored procedure to insert into a database according informations coming from another database (From:SQL Server Data Access)
    • Edited by ninjaPerson Monday, July 30, 2012 8:27 AM
    Friday, July 27, 2012 3:08 PM

Answers

  • I do not know anything about .net, but maybe my remarks can be helpful. If I understand you correctly, the problem seems to be that what seems to be a single dataset in the client application, is implemented in different tables. What is more those tables reside in different databases. Instead of .net, I use Delphi for my client apps. In Delphi, you have a TUpdateSQL class that has separate properties for an update proc name, an insert proc name and a delete proc name. What you do inside the stored procedure, is your choice. So code like the example below is possible

    CREATE PROCEDURE [dbo].[spInsertTypeOthers]
    @FranchiseName varchar(50),
    @JobID varchar(7),
    @Implications varchar(50)
    AS
    INSERT INTO dbo.AccidentReporting
    (
    FranchiseID,
    JobID,
    Implications
    )
    SELECT
    F.FranchiseID,
    @JobID,
    @Implications
    FROM [DatabaseTwo].[dbo].[TableTwo] F
    WHERE F.FranchiseName = @FranchiseName;


    • Edited by Chris Sijtsma Saturday, July 28, 2012 7:41 PM
    • Proposed as answer by Naomi N Monday, July 30, 2012 8:58 AM
    • Marked as answer by ninjaPerson Monday, July 30, 2012 11:54 AM
    Saturday, July 28, 2012 7:40 PM

All replies

  • Hello,

    If i have well understood your post, you want help to create the stored procedures permitting the Insert/Update ( and i suppose Delete + Select ) inside the DatabaseOne.dbo.AccidentReporting. I think that your question is more related to Transact-SQL , it is why i will move this thread towards the Transact-SQL Forum, moreover , you are in a week-end of July ( so many people are on holiday ).

    I would suggest you to give more informations about your problem.

    - the full version ( year + last installed service pack ) and the full edition of your SQL Server instance ( Express ,Web , Workgroup , Standard,... )

    - the exact structure of your DatabaseOne.dbo.AccidentReporting table and maybe DatabaseTwo.dbo.TableFranchise ( the CREATE TABLE statements ) . It could be useful for someone who wanted to fill these tables for test. 

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Saturday, July 28, 2012 2:35 PM
  • Hello,

    Move done and i hope you will find a quick and full answer to your problem.

    A little remark : I would suggest you to have a look at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c6d69372-af26-452c-963a-de22cf9d7985  where Stephanie explained good rules to apply when you will provide the informations i asked you.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

    Saturday, July 28, 2012 2:41 PM
  • I do not know anything about .net, but maybe my remarks can be helpful. If I understand you correctly, the problem seems to be that what seems to be a single dataset in the client application, is implemented in different tables. What is more those tables reside in different databases. Instead of .net, I use Delphi for my client apps. In Delphi, you have a TUpdateSQL class that has separate properties for an update proc name, an insert proc name and a delete proc name. What you do inside the stored procedure, is your choice. So code like the example below is possible

    CREATE PROCEDURE [dbo].[spInsertTypeOthers]
    @FranchiseName varchar(50),
    @JobID varchar(7),
    @Implications varchar(50)
    AS
    INSERT INTO dbo.AccidentReporting
    (
    FranchiseID,
    JobID,
    Implications
    )
    SELECT
    F.FranchiseID,
    @JobID,
    @Implications
    FROM [DatabaseTwo].[dbo].[TableTwo] F
    WHERE F.FranchiseName = @FranchiseName;


    • Edited by Chris Sijtsma Saturday, July 28, 2012 7:41 PM
    • Proposed as answer by Naomi N Monday, July 30, 2012 8:58 AM
    • Marked as answer by ninjaPerson Monday, July 30, 2012 11:54 AM
    Saturday, July 28, 2012 7:40 PM
  • Thanks so much Chris :)  

    that is what I want, appreciate that , Thanks again :)


    NinjaPerson
    Monday, July 30, 2012 8:25 AM
  • Could you mark my reply as answer, in that case? I scan this forum on unanswered questions. It saves my time if the unanswered questions list does not contain answered questions that are still marked as unanswerd. I know, I know, I'm lazy. And also, it will give me extra points on the forum, although that is not my main reason for asking (he says, but maybe you should not believe me.

    Thanks!

    Monday, July 30, 2012 9:29 AM