Solved

ASP.NET run a stored procedure (INSERT) using Visual Web Developer

Posted on 2010-09-12
8
718 Views
Last Modified: 2012-08-13
I have a page with a bunch of controls on it.

I have added a SQL Data Source to the page which connects to an Insert stored procedure in SQL 2005 DB.

The data source gets values from controls etc ready to Insert.

I have done all of this using the GUI in Visual Studio.

What I want to know is how to I make the SP run when I click the button?

I can't use SqlDataSource1.Insert() as it's a stored procedure.  

Surely this should be easy?
0
Comment
Question by:nickrjsmith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 30

Expert Comment

by:MlandaT
ID: 33658055
<%@Page  Language="VB" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<SCRIPT runat="server">

 Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)

    Dim insertedKey As SqlParameter  
    insertedKey = New SqlParameter("@PK_New", SqlDbType.Int)
    insertedKey.Direction    = ParameterDirection.Output    
   
    e.Command.Parameters.Add(insertedKey)

 End Sub 'On_Inserting

 Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
    Dim command As DbCommand
    command = e.Command
   
    ' The label displays the primary key of the recently inserted row.
    Label1.Text = command.Parameters("@PK_New").Value.ToString()    
   
    ' Explicitly call DataBind to refresh the data
    ' and show the newly inserted row.
    GridView1.DataBind()
 End Sub 'On_Inserted

</SCRIPT>

<HTML>
  <BODY>
    <FORM runat="server">

      <asp:GridView
        id="GridView1"
        runat="server"
        AutoGenerateColumns="False"
        DataKeyNames="EmployeeID"        
        DataSourceID="SqlDataSource1">
        <columns>          
          <asp:BoundField HeaderText="First Name" DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" DataField="LastName" />
          <asp:BoundField HeaderText="Title" DataField="Title" />
          <asp:ButtonField ButtonType="Link" CommandName="Select" Text="Details..." />
        </columns>
      </asp:GridView>

      <asp:SqlDataSource
        id="SqlDataSource1"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind %>"
        SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
      </asp:SqlDataSource>

      <HR>

      <asp:DetailsView
        id="DetailsView1"
        runat="server"
        DataSourceID="SqlDataSource2"
        AutoGenerateRows="False"
        AutoGenerateInsertButton="True">
        <fields>
          <asp:BoundField HeaderText="First Name" DataField="FirstName" ReadOnly="False"/>
          <asp:BoundField HeaderText="Last Name" DataField="LastName" ReadOnly="False"/>
          <asp:TemplateField HeaderText="Title">
            <ItemTemplate>
              <asp:DropDownList
                id="TitleDropDownList"
                runat="server"
                selectedvalue="<%# Bind('Title') %>" >
                <asp:ListItem Selected>Sales Representative</asp:ListItem>
                <asp:ListItem>Sales Manager</asp:ListItem>
                <asp:ListItem>Vice President, Sales</asp:ListItem>
              </asp:DropDownList>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:BoundField HeaderText="Notes" DataField="Notes" ReadOnly="False"/>
        </fields>
      </asp:DetailsView>


      <asp:SqlDataSource
        id="SqlDataSource2"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
        SelectCommand="SELECT * FROM Employees"
        InsertCommandType = "StoredProcedure"
        InsertCommand="sp_insertemployee"        
        OnInserting="On_Inserting"
        OnInserted ="On_Inserted"
        FilterExpression="EmployeeID={0}">
        <FilterParameters>
          <asp:ControlParameter Name="EmployeeID" ControlId="GridView1" PropertyName="SelectedValue" />
        </FilterParameters>
      </asp:SqlDataSource>

<!--
     -- An example sp_insertemployee stored procedure that returns
     -- the primary key of the row that was inserted in an OUT parameter.
     CREATE PROCEDURE sp_insertemployee
        @FirstName nvarchar(10),
        @LastName nvarchar(20) ,
        @Title nvarchar(30),
        @Notes nvarchar(200),
        @PK_New int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
        SELECT @PK_New = @@IDENTITY
        RETURN (1)    
      GO
-->      

      <asp:Label
        id="Label1"
        runat="server" />
     
    </FORM>
  </BODY>
</HTML>
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33658058
The example uses a stored procedure to insert records and returns the primary key of the inserted row. The stored procedure name is set in the InsertCommand property and identified as a stored procedure by setting the InsertCommandType property to the StoredProcedure value.

After data is inserted into the database by the DetailsView control, the OnInserted event handler is called to handle the Inserted event, the value of the primary key of the inserted row is displayed, and the DataBind method of the GridView control is called explicitly to refresh the data.

(This is from MSDN)
0
 
LVL 7

Expert Comment

by:klakkas
ID: 33660596
Just to keep it simple, to run a normal insert query, we do this:

Dim cn as new SQLConnection(cnString)
cn.open
Dim cmd as new SQLCommand("Insert into tbl Values (1,2)", cn)
cmd.executeNonQuery.

To run a StoredProcedure, you only need to add one line:

Dim cn as new SQLConnection(cnString)
cn.open
Dim cmd as new SQLCommand("Exec myStoreProc 1, 2", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.executeNonQuery
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:nickrjsmith
ID: 33666272
um.. i'm very new to this.

1)  What is this?  (cnString) i.e. do i have to write my connection string to = cnString
2)  here's the variable i want to pass into the sp


here's the sp i want to run.

wsp_InsertProduct @img1 = "blank.gif", @img2 = "blank.gif", @NewImgID = null, @name = "Upload Suit", @price = 500, @thickness = "5mm", @brandID = 2, @live = 1, @homepage = 0, @newGenAgeID = null, @genderAge1 = 1, @genderAge2 = 2, @genderAge3 = 3, @NewProdID = null, @brandDesc = "Testing the description", @type1 = 1, @type2 = 2, @season1 = 1, @sportType1 = 1, @sportType2 = 2
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33666540
yes... cnstring is your connectionstring...

Dim cn as new SQLConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
cn.open
Dim cmd as new SQLCommand("EXEC   wsp_InsertProduct @img1 = 'blank.gif', @img2 = 'blank.gif', @NewImgID = null, @name = 'Upload Suit', @price = 500, @thickness = '5mm', @brandID = 2, @live = 1, @homepage = 0, @newGenAgeID = null, @genderAge1 = 1, @genderAge2 = 2, @genderAge3 = 3, @NewProdID = null, @brandDesc = 'Testing the description', @type1 = 1, @type2 = 2, @season1 = 1, @sportType1 = 1, @sportType2 = 2", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.executeNonQuery
0
 

Author Comment

by:nickrjsmith
ID: 33666556
i've started to do this... is this wrong...

 Dim sql As String = "wsp_InsertProduct"
        Dim strConnection As String = "Data Source="*****"
        Dim conn As New SqlConnection(strConnection)

        conn.Open()

        Dim cmd As New SqlCommand(sql, conn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@img1", FileUpload1.ToString)
        cmd.Parameters.AddWithValue("@img2", FileUpload2.ToString)
        'cmd.Parameters.AddWithValue("@NewImgID" = Nullable)
        cmd.Parameters.AddWithValue("@name", productName.ToString)
        cmd.Parameters.AddWithValue("@price", price)
        cmd.Parameters.AddWithValue("@thickness", thickness.ToString)
        cmd.Parameters.AddWithValue("@brandID", chkBrand.SelectedValue)
0
 
LVL 30

Accepted Solution

by:
MlandaT earned 500 total points
ID: 33666674
or a much better example using the Parameters collection:

Dim cn as new SQLConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;")
cn.open
Dim cmd as new SQLCommand("wsp_InsertProduct",cn)
cmd.CommandType = CommandType.StoredProcedure
with cmd.Parameters
     .AddWithValue("@img1","blank.gif'")
     .AddWithValue("@img2","blank.gif'")
     .AddWithValue("@NewImgID","null")
     .AddWithValue("@name","Upload Suit'")
     .AddWithValue("@price","500")
     .AddWithValue("@thickness","5mm'")
     .AddWithValue("@brandID",2)
     .AddWithValue("@live",1)
     .AddWithValue("@homepage",0)
     .AddWithValue("@newGenAgeID","null")
     .AddWithValue("@genderAge1",1)
     .AddWithValue("@genderAge2",2)
     .AddWithValue("@genderAge3",3)
     .AddWithValue("@NewProdID","null")
     .AddWithValue("@brandDesc","Testing the description'")
     .AddWithValue("@type1",1)
     .AddWithValue("@type2",2)
     .AddWithValue("@season1",1)
     .AddWithValue("@sportType1",1)
     .AddWithValue("@sportType2",2)
end with
cmd.executeNonQuery
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33666682
Hadnt seen your comment. That is fine. Should work.
0

Featured Post

Technology Partners: 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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

632 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