Solved

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

Posted on 2010-09-12
8
707 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
  • 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now