how to use stored procedure in .net?

A

anand

Well-known member
Joined
Jan 29, 2003
Messages
76
Location
chicago
Pls give me a idea how to use stored procedure in .net...
whats the wrong in my code..................


Dim con1 As OleDbConnection
Dim cmd1 As OleDbCommand
Dim ds1 As New DataSet()
Dim str1 As String
str1 = "Provider=SQLOLEDB;Data Source=WIN2000\SQLANA;Initial Catalog=pubs;Integrated Security=SSIP"
cmd1.CommandText = "proc2"
con1 = New OleDbConnection(str1)
cmd1 = New OleDbCommand("Proc2", con1)
cmd1.CommandType = CommandType.StoredProcedure
con1.Open()

pls give me a idea soon....
Thank you
Satya
 
I use the dataadapter, something like this...
Code: 
Dim con1 As OleDbConnection
Dim cmd1 As OleDbCommand
added
Dim adp As OleDbDataadapter
Dim ds1 As New DataSet()
Dim str1 As String
str1 = "Provider=SQLOLEDB;Data Source=WIN2000\SQLANA;Initial Catalog=pubs;Integrated Security=SSIP"
con1 = New OleDbConnection(str1)
cmd1 = New OleDbCommand("Proc2", con1)
cmd1.CommandType = CommandType.StoredProcedure
added
adp.SelectCommand = cmd1
con1.Open()
added
adp.Fill(ds1)
added
con1.Close()
 
Hi Tim
Thnak you for your help...but i am getting the following error now...i used the same code what u send......i am getting the error

Server Error in /anand/style1 Application.
--------------------------------------------------------------------------------

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
style1.WebForm2.Button2_Click(Object sender, EventArgs e)
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
 
can you post the entire Button2_Click Sub? (in code blocks)

btw just noticed I didnt actually create the dataadapter. need to add the following line to your code too.
Code: 
adp = New OleDbDataadapter()
 
The following code builds a command that executes a stored procedure named InsertCustomer. I found it in my samples folder. Hope it helps...

Note that its not nessecary to use the calls by that order. In my code I create most of the objects in other Subs
Direction of the parameter... Im not really sure you need it either... (do you?)


Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim param As SQLParameter
Dim rowsAffected As Integer

Try
Create a new connection object
sqlConn = New SQLConnection(myConnString)
Create a new command object
sqlCmd = New SQLCommand()
Specify the stored procedure and connection
With sqlCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "InsertCustomer"
. Connection = sqlConn
End With
Define and add a parameter to the parameters collection
param = sqlCmd.Parameters.Add(New _
SQLParameter("@Alias", SQLDBType.NVarChar, 100))
Set the parameter direction
With param
.Direction = ParameterDirection.Input
Set the parameter value
.Value = "myAliasEx2"
End With
Add remaining parameters

Open the connection
sqlConn.Open()
Execute the command
rowsAffected = sqlCmd.ExecuteNonQuery()
Catch e As Exception
Handle the exception

Finally
Close connection regardless of outcome
sqlConn.Close()
End Try
 
Back
Top