How to execute stored procedure from vb.net CodeBehind instead of a sql statement?

I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind.  I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..

Dim conn As SqlConnection
Dim comm As SqlCommand
Dim reader As SqlDataReader
Dim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionString
conn =
New SqlConnection(connectionString)

comm = New SqlCommand("INSERT into xxx (a, b) Values (@a, @b)", conn)
comm.Parameters.AddWithValue(
"a", a.Value)
comm.Parameters.AddWithValue("b", b.Value)
conn.Open()
reader = comm.ExecuteReader()
reader.Close()
conn.Close()


Never make important decisions on a Monday!
0
funluckykitty
1/22/2009 9:26:03 PM
asp.net.getting-started 91979 articles. 4 followers. Follow

2 Replies
1209 Views

Similar Articles

[PageSpeed] 7

Dim conn As SqlConnection
Dim comm As SqlCommand
Dim reader As SqlDataReader
Dim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionString
conn =
New SqlConnection(connectionString)

comm = New SqlCommand()

com.Connection = conn

com.CommandType = CommandType.StoredProcedure

com.CommandText = "youstoredprocname"

com.Parameters.Add("@a", Data.SqlDbType.Int)

com.Parameters("@a).Value = a.value
conn.Open()
reader = comm.ExecuteReader()
reader.Close()


Sri
Please mark as answer if it helped you
0
jsriharsha
1/22/2009 9:32:35 PM

 Here is a way of doing it..

 Dim dt As New DataTable()
 Dim connection As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionString
    connection.Open()
    Dim sqlCmd As New SqlCommand("INSERT into xxx (a, b) Values (@a, @b)", connection)
    Dim sqlDa As New SqlDataAdapter(sqlCmd)
    sqlCmd.Parameters.AddWithValue("a", a.Value)
    sqlCmd.Parameters.AddWithValue("b", b.Value)
    sqlDa.Fill(dt)
    If dt.Rows.Count > 0 Then
             'display data in labels
        Label1.Text = dt.Rows(0)("col1").ToString()
        Label2.Text = dt.Rows(0)("col2").ToString()
    End If
    connection.Close()



My Blog


"Don't be afraid to be wrong; otherwise you'll never be right."
0
budugu
1/22/2009 9:34:01 PM
Reply:

Similar Artilces:

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...

VB.net connect to SQL Express Stored Procedure
Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure. I would like to know how to call that SP. Below is what i did and it raised a error "Missing provider" I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below.. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Sub PushtoDB() Dim con As OleDbConnection Dim cmd As OleDbCommand = New OleD...

how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much. here's the sql stored procedure: ------------------------------------------------------------ CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Cr...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

Can somebody help
I Seem to be going nuts ! I know it has to be simple but I am missing the link. I haven't used crystal reports with asp.net before. So pardom me if my problem sounds silly. My requirement is very simple actually. I am using asp.net 1.1 with vb.net in vs.net 2003 with the inbuilt crystal reports control. I have to connect to a sql server 2000 database to extract rows from a stored procedure which accepts a parameter and the report has to be in Mail label format. I referred various books and web sites and according to that , added a crystal reports file to the project and selected the store...

Getting table records count with SQL statement VB.net
I have a table named USERS with a column named USERNAME. I am trying to get the count and cannot seem to get any results back. Code I've tried. cmd2.CommandText = "SELECT USERNAME AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("RETURNCOUNT") ************Did not return any*************  ******************************************************************************************************************************* cmd2.CommandText = "SELECT COUNT(0) AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("R...

Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

how to pass vb.net 2 dimensional array to a sql stored procedure
I am developing a online shoing cart for a ISP company. according to the business when we add some products we keep set of records for products such as domain name, address , tel, hosting option and so on so my basket contains a list of items as follow i would prefer to convert these in to datatable and pass to a stored procedure item     tel           name        add1      add2       add3      postcode   &n...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

How to get RS (From VB6) to .NET (VB.NET)
Hi Friends,         I have requirement, there is one function in VB6 it returns a RS, I need to call that function in  VB.NET, how to get those values into .NET, if can any body can help (pass me some code snippets), that will be greate, Thank you.Madhu... -Madhu hey guys i got this one just by using fill method.-Madhu...

Web resources about - How to execute stored procedure from vb.net CodeBehind instead of a sql statement? - asp.net.getting-started

ASP.NET - Wikipedia, the free encyclopedia
ASP.NET is a server-side Web application framework designed for Web development to produce dynamic Web pages . It was developed by Microsoft ...

C# dotnet SQL Editor (webform side)
Every time I need to develop a small business application I always try to choose the fastest and simplest way to implement it. This is why I ...

xClassNotDerivedFromElement – Another Non-Informative XAML Parse Error
... XAML is due to my lazyness. I Created a new xaml file but used the view template rather than a Resource Dictionary one, yes I deleted the codebehind ...

WPF Onyx
WPF Model-View-ViewModel framework.

Stronger Backbone - Part 2
... development, there was a framework called WebForms that developers would use to create web applications. WebForms used a system called "CodeBehind" ...

Use Custom Http Handlers To Improve Performance in ASP.NET
While reading a chapter from the upcoming book from Addison-Wesley entitled Essential ASP.NET with Examples in C# , I ran across a chapter on ...

Developing a Windows Phone Application using the MVVM Pattern
This documentation includes a companion sample application named PetrolTracker. PetrolTracker is an application written in Silverlight for Windows ...

Struggling for Competence
An event lets a publisher notify subscribers when something happens. The article examines event rules and convention, and presents a c# example. ...

CodeSOD: The Apex of T-SQL
... has all sorts of different ways to generate HTML. Rarely, it’s WebForms code living in as ASPX. Far more often, it’s hard-coded into the CodeBehind. ...

Happy Birthday, APRSdroid (my OSS Scala app for Android)!
APRSdroid is an Amateur Radio geo-location( APRS ) app for Android licensed under the GPL.It started as a Scala learning experience twoyears ...

Resources last updated: 12/5/2015 12:20:34 PM