Solved

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

Posted on 2010-09-12
8
711 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Registering DLL 5 43
I need help embedding Base64 Images using VB.Net - When image is a .jpg 2 41
Anyway to make "All" the default in the dropdown? 6 34
Run time Error 4 34
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

911 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

16 Experts available now in Live!

Get 1:1 Help Now