[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Asp.net stored procedure with parameters

Posted on 2012-09-18
20
Medium Priority
?
548 Views
Last Modified: 2012-09-18
I have an ASP.NET Solution with a gridview displaying shipping load record information.

gridview is bound to this datasource sql table

tbl_LoadTest
      loadID  int (key)
      LoadRecord  int
      Driver       nvarchar(50)
      ShipID  int
      DeliveryID int
      Rate     Money

The ShipID is populated by a drop downbox connected to this datasource table

     tbl_GenShip
           ShipID
           PickupLocation
           Address

The DeliveryID is populated in the gridview by datasource table

      tbl_GenDairy
            DeliveryID int
             Dairy
              Address


I have another table that holds the ship rates based on ship location to delivery location
       tbl_Rate
              RateID
              ShipID
              DeliveryID
              Rate

    sample data would be
           Rateid      shipID     DeliveryID    Rate
               1              1                 2           $40.00

I have this stored procedure

USE Develop_TareWeight

GO

            

ALTER PROCEDURE find_rate
AS

DECLARE @ShipID int = 1
DECLARE @DeliveryID int = 2

SELECT tbl_GenShip.PickUpLocation
            ,tbl_GenDairy.Dairy
            ,tbl_Rate.Rate

            
            
            
            
            
            
FROM  tbl_Rate INNER JOIN
                        tbl_GenShip ON tbl_Rate.ShipID = tbl_GenShip.ShipID INNER JOIN
                        
                        tbl_GenDairy ON tbl_Rate.DeliveryID = tbl_GenDairy.DeliveryID
                        
                        
                        
WHERE  (tbl_Rate.ShipID = @ShipID) AND (tbl_Rate.DeliveryID  = @DeliveryID)

This procedure works and gives the correct rate based on the shipID and DeliveryID.

What I am looking for is a way to pass the parameters LoadID, ShipID and DeliveryID from my load record in the gridview, run the procedure and insert the RATE generated from the procedure in to the "rate" field in the table tbl_loadTest.  

Any ideas?  Thanks for the help.
0
Comment
Question by:rtay
  • 10
  • 10
20 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38410597
Is tbl_LoadTest already loaded or do you load it from the application?
0
 
LVL 5

Author Comment

by:rtay
ID: 38410620
It is loaded from the application to populate the gridview for insert / edit.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38410651
It is loaded from the application to populate the gridview for insert / edit.

If you're adding the records in tbl_LoadTest through the application you can use that same stored procedure to fill in the Rate field.
How are you inserting into tbl_LoadTest?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Author Comment

by:rtay
ID: 38410676
I am using the built in gridview insert to add records into tbl_LoadTest, but I am open to other suggestions.  I am not sure how place the output of that stored procedure into the table.  It does not need to post back to the gridview
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38410712
What you could do is call the stored procedure with the selected values (of the dropdowns), using a sqlcommand and ExecuteReader, then save the value in a variable and use that value to insert in your gridView.

If you show me the code to insert in the gridView, I might be able to provide some code to call the stored procedure and use the value to insert into the gridView.
0
 
LVL 5

Author Comment

by:rtay
ID: 38410954
Here is the insert page.  Thanks for the help


</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
            DataKeyNames="LoadID" DataSourceID="SqlDataSource1" DefaultMode="Insert" 
            Height="50px" Width="125px">
            <Fields>
                <asp:BoundField DataField="LoadRecord" HeaderText="LoadRecord" 
                    SortExpression="LoadRecord" />
                <asp:BoundField DataField="Driver" HeaderText="Driver" 
                    SortExpression="Driver" />
                <asp:TemplateField HeaderText="DeliveryID" SortExpression="DeliveryID">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("DeliveryID") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ds_delivery" 
                            DataTextField="Dairy" DataValueField="DeliveryID">
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="ds_delivery" runat="server" 
                            ConnectionString="<%$ ConnectionStrings:ConnString_GenFrieght %>" 
                            SelectCommand="SELECT [DeliveryID], [Dairy] FROM [tbl_GenDairy]">
                        </asp:SqlDataSource>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("DeliveryID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ShipID" SortExpression="ShipID">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ShipID") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="ds_ship" 
                            DataTextField="PickUpLocation" DataValueField="ShipID">
                        </asp:DropDownList>
                        <asp:SqlDataSource ID="ds_ship" runat="server" 
                            ConnectionString="<%$ ConnectionStrings:ConnString_GenFrieght %>" 
                            SelectCommand="SELECT [ShipID], [PickUpLocation] FROM [tbl_GenShip]">
                        </asp:SqlDataSource>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("ShipID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnString_GenFrieght %>" 
            DeleteCommand="DELETE FROM [tbl_LoadTest] WHERE [LoadID] = @LoadID" 
            InsertCommand="INSERT INTO [tbl_LoadTest] ([LoadRecord], [Driver], [Rate], [DeliveryID], [ShipID]) VALUES (@LoadRecord, @Driver, @Rate, @DeliveryID, @ShipID)" 
            SelectCommand="SELECT [LoadID], [LoadRecord], [Driver], [Rate], [DeliveryID], [ShipID] FROM [tbl_LoadTest]" 
            UpdateCommand="UPDATE [tbl_LoadTest] SET [LoadRecord] = @LoadRecord, [Driver] = @Driver, [Rate] = @Rate, [DeliveryID] = @DeliveryID, [ShipID] = @ShipID WHERE [LoadID] = @LoadID">
            <DeleteParameters>
                <asp:Parameter Name="LoadID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="LoadRecord" Type="Int32" />
                <asp:Parameter Name="Driver" Type="String" />
                <asp:Parameter Name="Rate" Type="Decimal" />
                <asp:Parameter Name="DeliveryID" Type="Int32" />
                <asp:Parameter Name="ShipID" Type="Int32" />
                <asp:Parameter Name="LoadID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="LoadRecord" Type="Int32" />
                <asp:Parameter Name="Driver" Type="String" />
                <asp:Parameter Name="Rate" Type="Decimal" />
                <asp:Parameter Name="DeliveryID" Type="Int32" />
                <asp:Parameter Name="ShipID" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>

Open in new window

0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411510
Since you're not running a procedure to insert into the database you can't insert the Stored Procedure call in your insert statement.

I'm not very sure if there's a way for you to insert SQL code before the execution for your insert.
0
 
LVL 5

Author Comment

by:rtay
ID: 38411527
Can I do it at the database level.  it does not need to update at the application.  Do you know a way to have that stored procedure get the values at the record level of the ShipID and DeliveryID from the tbl_testload run and then place the value in the rate field on that record.  It can be done at table Update.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411720
Yes you can do it at the database level. You can use a trigger on insert/update.
So everytime you insert (or update) a row in the table it will execute and update the value for Rate.

Let me know if you're interested in that, I can provide an example of how it could be done.
0
 
LVL 5

Author Comment

by:rtay
ID: 38411817
If you have a sample or a place to look, I would appreciate it.
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38411855
You'd need to create the trigger on the table you insert the data (i.e. tbl_LoadTest).
Try this:

CREATE TRIGGER dbo.trgUpRate
ON tbl_LoadTest
AFTER INSERT, UPDATE
AS

UPDATE tbl_LoadTest
SET Rate = tbl_Rate.Rate
FROM  tbl_Rate INNER JOIN tbl_LoadTest ON
           tbl_Rate.ShipID = tbl_LoadTest.ShipID AND
           tbl_Rate.DeliveryID = tbl_LoadTest.DeliveryID INNER JOIN Inserted ON
           tbl_LoadTest.loadID = Inserted.loadID
GO

No SQL at the moment, so give this a try and let me know if it works.
0
 
LVL 5

Author Comment

by:rtay
ID: 38411863
No, i does not update the rate to the table
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411883
Did you try inserting or updating a row?
0
 
LVL 5

Author Comment

by:rtay
ID: 38411902
I tried both.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411916
Weird because I created test tables and copied/pasted the trigger definition I posted, inserted and updated rows and the Rate field updates automatically.

Is anything happening at all? What SQL version are you using?
0
 
LVL 5

Author Comment

by:rtay
ID: 38411921
SQL 2008.  Nothing happens when I update and I varified the trigger was applied.
0
 
LVL 5

Author Closing Comment

by:rtay
ID: 38411929
Thank you.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411934
What happened?
0
 
LVL 5

Author Comment

by:rtay
ID: 38411939
stupid overlook on my part trying to implement the trigger.  That is what I get for doing 5 things at once.  thanks again for all your help.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38411946
Glad it worked out!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

872 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