473,695 Members | 2,989 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to call and use store procedures in vb.net

15 New Member
hi..
i m working with vb.net 1st time. i wants to work with stored procedures. i have created stored procedures successfully but have no idea about how to code in vb.net to use these store procedures for add, edit etc data in database. sql server 2000 i m using for databse.
kindly help me out as soon.
Sep 18 '07 #1
4 1535
Plater
7,872 Recognized Expert Expert
The stored procedure is created in the SQL server yes?
It will have a name.
Create an SQLCommand object.
Make it's SQL string be JUST the name of the stored procedure.
Set it's CommandType to that of a stored procedure
Then, for any parameters you have, you can add them in.

For example: (this code is c#, but should be similar for vb.net)
Expand|Select|Wrap|Line Numbers
  1. Int64 UserID=3;//looking for user #3
  2. SqlCommand sc = new SqlCommand("GetUserInfo", dbcon);// "GetUserInfo" is my stored procedure
  3. sc.CommandType = CommandType.StoredProcedure;//set the command type
  4. sc.Parameters.Add("@UserID", SqlDbType.BigInt );//my procedure has a parameter called "@UserID" of type "BIGINT"
  5. sc.Parameters["@UserID"].Value = UserID;//set the value
  6.  
And then execute as normal.
Sep 18 '07 #2
seep
15 New Member
thanks plater. but right now i find another error while executing a store procedure. the error is:

Error: Procedure 'phealthMISDepa rtmentAdd' expects parameter '@p_depart_id_o ut', which was not supplied.

here is the SP that i have created:

-- Creates a new record in the [dbo].[Department] table.
ALTER PROCEDURE phealthMISDepar tmentAdd
@p_Department_n ame text,
@p_depart_id_ou t numeric output
AS
BEGIN
INSERT
INTO [dbo].[Department]
(
[Department_name]
)
VALUES
(
@p_Department_n ame
)

SET @p_depart_id_ou t = SCOPE_IDENTITY( )

END



here is the code for adding data from procedure::


Dim sql As String = "phealthMISDepa rtmentAdd"
Dim strConnection As String = "Data Source=JBWKN1S; Initial Catalog=HealthM IS;Integrated Security=True"
Dim conn As New SqlConnection(s trConnection)

conn.Open()

Dim cmd As New SqlCommand(sql, conn)
cmd.CommandType = CommandType.Sto redProcedure

cmd.Parameters. Add("@p_Departm ent_name", SqlDbType.VarCh ar, 50).Value = Department_name .Text
cmd.CommandText = "dbo.phealthMIS DepartmentAdd"



Try
cmd.ExecuteNonQ uery()
Catch ex As Exception
Response.Write( "Error: " & ex.Message & "<br/>")
End Try
cmd.Connection. Close()
Sep 19 '07 #3
Plater
7,872 Recognized Expert Expert
Ahh yes, in code you have to add another parameter to your command object. and then set the direction to "out"

for example:
Expand|Select|Wrap|Line Numbers
  1. sc.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
  2. sc.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
  3.  
  4. sc.ExecuteNonQuery();
  5. retval = Int.Parse(sc.Parameters["@RETURN_VALUE"].Value.ToString());
  6.  
Sep 19 '07 #4
seep
15 New Member
thanks alot plater.
i got it really thanks:-)
Sep 20 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
7791
by: A.M. de Jong | last post by:
How does MicroSoft store the stored procedures in seperate files. What tools are used. Cause that's what I like to do too. Arno de Jong, The Netherlands. (SCPTFXR does not have the option to store the stored procedures in different files I think)
12
2379
by: Sanjay | last post by:
hi, We are currently porting our project from VB6 to VB .NET. Earlier we used to make scale transformations on objects like pictureBox , forms etc.Now Such transformations are made on the graphics object of the form/pictureBox. Should It be better if make a graphics object from my pictureBox in load event handler of the form and store it as member variable of the form , make
2
4248
by: serge | last post by:
My project is to automate testing of Stored Procedures of type SELECT (at least for now). I want to create a table where each stored procedure's input parameter values are entered and in another table the expected result value(s) are entered when executed against a sample database containing manually entered and verified data. My current problem is that the stored procedures' input
1
6194
by: John Sidney-Woollett | last post by:
Is it possible to call a Postgres stored function (as if it were a procedure), using a PreparedStatement which will receive no return value, instead of having to use a CallableStatement and ignore the return result? The reason... Our java middleware was written against Oracle (where we use stored procedures extensively) and is now being ported to Postgres. I would prefer not to have to rewrite all the java code replacing stored...
7
2848
by: George | last post by:
Hi , I am getting the below error while i am invoking a Java stored procedure from another java stored procedure. SQL0751N Routine "" (specific name "") attempted to execute a statement that is not allowed. The Main SP say A ,calls SP1 reads the data and in a while loop calls the SP2 to insert the data in that is retrieved in the
1
1567
by: esmith2112 | last post by:
We all of a sudden find ourselves in dire straits, because we have one of those mysteries where everything used to work, and apparently, all of a sudden everything went to pot just in time for a major software release. All of our Java stored procedures have developed some bad behavior and we've exhausted our resources in figuring this out. Symptoms: 1. Store procedures have stopped returning result sets. Whereas before, it would...
2
1348
by: Marc | last post by:
Hi: Does anyone know if IBM (or any other IT Software company) offers store procedure classes anywhere in the country? Thanks Marc
4
1469
by: Marc | last post by:
Hi: Does anyone know if any IBM branch across the country offers Store procedure classes?. Thanks Marc
1
3070
by: Fabio | last post by:
Hi, is there any method to call a store procedure into a function? Thanks Fabio
0
8619
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8971
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8824
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7651
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6484
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5831
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4336
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4571
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.