Asp.net stored procedure with parameters

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.
LVL 5
rtayIT DirectorAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
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
 
LIONKINGCommented:
Is tbl_LoadTest already loaded or do you load it from the application?
0
 
rtayIT DirectorAuthor Commented:
It is loaded from the application to populate the gridview for insert / edit.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LIONKINGCommented:
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
 
rtayIT DirectorAuthor Commented:
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
 
LIONKINGCommented:
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
 
rtayIT DirectorAuthor Commented:
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
 
LIONKINGCommented:
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
 
rtayIT DirectorAuthor Commented:
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
 
LIONKINGCommented:
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
 
rtayIT DirectorAuthor Commented:
If you have a sample or a place to look, I would appreciate it.
0
 
rtayIT DirectorAuthor Commented:
No, i does not update the rate to the table
0
 
LIONKINGCommented:
Did you try inserting or updating a row?
0
 
rtayIT DirectorAuthor Commented:
I tried both.
0
 
LIONKINGCommented:
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
 
rtayIT DirectorAuthor Commented:
SQL 2008.  Nothing happens when I update and I varified the trigger was applied.
0
 
rtayIT DirectorAuthor Commented:
Thank you.
0
 
LIONKINGCommented:
What happened?
0
 
rtayIT DirectorAuthor Commented:
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
 
LIONKINGCommented:
Glad it worked out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.