[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-11
6
Medium Priority
?
194 Views
Last Modified: 2010-04-23
Hi,

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.
0
Comment
Question by:craigewens
  • 3
5 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 16661545
Imports System.Data.SqlClient


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

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

        'add the first two parameters
        myCommand.Parameters.Add("@Parameter1Name")
        myCommand.Parameters.Add("@Parameter2Name")

        '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
        myCommand.Parameters.Add(TextBox.Text)
        myCommand.Parameters.Add(DropDownList.SelectedValue)

        'execute the query
        myCommand.ExecuteNonQuery()

     

0
 
LVL 34

Expert Comment

by:Sancler
ID: 16663636
And don't forget to close the connection

        conn.Close()

at the end ;-).  Not for points

Roger



0
 
LVL 2

Author Comment

by:craigewens
ID: 16665488
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?

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

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

Author Comment

by:craigewens
ID: 16684174
YZlat,
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.
Thanks.
0
 
LVL 2

Author Comment

by:craigewens
ID: 16698532
Well i've figured out the ConnectionString question by myself as well now...

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 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