Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

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

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
nickrjsmith
Asked:
nickrjsmith
  • 5
  • 2
1 Solution
 
MlandaTCommented:
<%@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
 
MlandaTCommented:
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
 
klakkasCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nickrjsmithAuthor Commented:
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
 
MlandaTCommented:
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
 
nickrjsmithAuthor Commented:
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
 
MlandaTCommented:
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
 
MlandaTCommented:
Hadnt seen your comment. That is fine. Should work.
0

Featured Post

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.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now