Solved

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

Posted on 2010-09-12
8
715 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.net Progress Bar - Maximum Value too large 2 15
SSRS 2016 Rendering HTML tables 3 29
Code works but breaks when I add one section 4 20
Code enhancement 4 22
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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