Using Stored Procedure take values from text boxes and put into SQL Server 2005

Posted on 2006-05-11
Last Modified: 2010-04-23

I am developing a web page form which links into a SQL Server 2005 database.
The database has several stored procedures which get data out and into drop down lists which the web site uses. The stored procedures are simple SELECT statements basically.

I am now at the stage where I've created the whole page with all the controls i require and wish to post the data back into the database.

To simplify the explanation here is the situation...
Web site with a text box and a drop down list.
I have a button which i wish to press and the text from the web controls get pushed into the stored procedure and put into the database.

How do i go about doing this? I'm quite new to .NET and have only really used the wizard type functions on getting data out of my database so please be kind with the explanations.

A probably naive thought on how something like this might be accomplished is shown below (again, please no laughing, i know it's not going to be this easy!)

Sub Button_click()
      Call SQL.StoredProc1(TextBox.Text, DropDownList.SelectedItem)
End Sub

Thank you for any help and sample code you can offer.
Question by:craigewens
    LVL 35

    Accepted Solution

    Imports System.Data.SqlClient

            Dim conn As New SqlConnection("Integrated Security=SSPI;" _
                      & "Persist Security Info=False;Initial Catalog=DatabaseName;" _
                      & "Data Source=ServerName;")

            'set up the command object
            Dim myCommand As New SqlCommand("stored procedure name", conn)
            myCommand.CommandType = CommandType.StoredProcedure

            'add the first two parameters

            'The output and return parameters must be created as objects
            Dim parm1 As New SqlParameter("@Parameter1Name")
            parm1.Direction = ParameterDirection.Input

            Dim parm2 As New SqlParameter(@Parameter2Name")
            parm2.Direction = ParameterDirection.Input

            'add them to the parameter collection

            'execute the query


    LVL 34

    Expert Comment

    And don't forget to close the connection


    at the end ;-).  Not for points


    LVL 2

    Author Comment

    Thanks for the replies... i already have a connection string which i am using in a few places on the web site. Can i utilise the same connection string by referencing it some how instead of writing this?

            Dim conn As New SqlConnection("Integrated Security=SSPI;" _
                   & "Persist Security Info=False;Initial Catalog=DatabaseName;" _
                   & "Data Source=ServerName;")

    Also I'm a little confused as to how exactly this line "myCommand.Parameters.Add(TextBox.Text)" knows it should be Parm1 or Parm2?
    LVL 2

    Author Comment

    Almost nothing from you example seemed to work for me, each time i seemed to have double the amount of parameters going into my stored procedure :( It was helpful in assisting me to find a similar solution though... In the end i came across this which seems a much simpler solution.

    conn.Parameters.AddWithValue("@ProjectID", ddlProject.SelectedValue)

    i added as many parameters as required then finished with conn.ExecuteNonQuery() and then a close().

    I'd still really like to know how i reference a connection string i already use for the site though instead of having to hard code it again.
    LVL 2

    Author Comment

    Well i've figured out the ConnectionString question by myself as well now...

    Dim cn As New SqlConnection()
    cn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringNameHere").ConnectionString

    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

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    728 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

    22 Experts available now in Live!

    Get 1:1 Help Now