Hi,
I am inserting values into databse using sqlserver stored
procedures. i wrote stored preocedure,but in codebehind file(.vb file)
i dont know how to pass the parameters, i got error message like this
"System.Data.Sq lClient.SqlExce ption: Procedure or Function 'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.Sql Client.SqlConne ction"
I wrote Stored Procedure Like this:
ALTER PROCEDURE [dbo].[sp_insert]
(@TestId int output ,
@Name varchar(50),
@Number varchar(50),
@Date Datetime
)
AS
INSERT INTO Testtable(Name, Number,Date)
VALUES
(@Name, @Number,@Date)
SELECT @TestId=@@IDENT ITY.
This is My .vb code:
sqlcon = New
SqlConnection(S ystem.Configura tion.Configurat ionManager.AppS ettings("Connec tionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_ insert", sqlcon)
sqlcmd.CommandT ext = "sp_insert"
sqlcmd.Connecti on = sqlcon
sqlcmd.CommandT ype = CommandType.Sto redProcedure
sqlcmd.Paramete rs.Add("@Name", SqlDbType.VarCh ar).Value =
txtname.Text
sqlcmd.Paramete rs.Add("@Number ", SqlDbType.VarCh ar).Value
= txtnumber.Text
sqlcmd.Paramete rs.Add("@Date", SqlDbType.DateT ime).Value =
txtdate.Text
param = sqlcmd.Paramete rs.Add("@RetunV alue",
SqlDbType.VarCh ar, 50)
param.Direction = ParameterDirect ion.Output
sqlcmd.ExecuteN onQuery()
lblstatus.Text = "successfullyin serted"
sqlcon.Close()
ReturnValue = param.Value
Catch ex As Exception
lblstatus.Text = ex.ToString
End Try.
Please check this code and find out my Error.
Regards,
Sirisha.
3  23704 
On 22 Feb., 12:19, "Sirisha" <sireesha.ard.. .@gmail.comwrot e:
Hi,
I am inserting values into databse using sqlserver stored
procedures. i wrote stored preocedure,but in codebehind file(.vb file)
i dont know how to pass the parameters, i got error message like this
"System.Data.Sq lClient.SqlExce ption: Procedure or Function 'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.Sql Client.SqlConne ction"
I wrote Stored Procedure Like this:
ALTER PROCEDURE [dbo].[sp_insert]
(@TestId int output ,
@Name varchar(50),
@Number varchar(50),
@Date Datetime
)
AS
INSERT INTO Testtable(Name, Number,Date)
VALUES
(@Name, @Number,@Date)
SELECT @TestId=@@IDENT ITY.
This is My .vb code:
sqlcon = New
SqlConnection(S ystem.Configura tion.Configurat ionManager.AppS ettings("Connec tionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_ insert", sqlcon)
sqlcmd.CommandT ext = "sp_insert"
sqlcmd.Connecti on = sqlcon
sqlcmd.CommandT ype = CommandType.Sto redProcedure
sqlcmd.Paramete rs.Add("@Name", SqlDbType.VarCh ar).Value =
txtname.Text
sqlcmd.Paramete rs.Add("@Number ", SqlDbType.VarCh ar).Value
= txtnumber.Text
sqlcmd.Paramete rs.Add("@Date", SqlDbType.DateT ime).Value =
txtdate.Text
param = sqlcmd.Paramete rs.Add("@RetunV alue",
SqlDbType.VarCh ar, 50)
param.Direction = ParameterDirect ion.Output
sqlcmd.ExecuteN onQuery()
lblstatus.Text = "successfullyin serted"
sqlcon.Close()
ReturnValue = param.Value
Catch ex As Exception
lblstatus.Text = ex.ToString
End Try.
Please check this code and find out my Error.
Regards,
Sirisha.
You have to add @TestID as parameter and set it's direction to Output.
Then read the value of @Testid parameter after the sp has finished.
Thomas
On Feb 22, 4:46 pm, "Thomas Schoch" <t...@swiss-soft.chwrote:
On 22 Feb., 12:19, "Sirisha" <sireesha.ard.. .@gmail.comwrot e:
Hi,
I am inserting values into databse using sqlserver stored
procedures. i wrote stored preocedure,but in codebehind file(.vb file)
i dont know how to pass the parameters, i got error message like this
"System.Data.Sq lClient.SqlExce ption: Procedure or Function 'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.Sql Client.SqlConne ction"
I wrote Stored Procedure Like this:
ALTER PROCEDURE [dbo].[sp_insert]
(@TestId int output ,
@Name varchar(50),
@Number varchar(50),
@Date Datetime
)
AS
INSERT INTO Testtable(Name, Number,Date)
VALUES
(@Name, @Number,@Date)
SELECT @TestId=@@IDENT ITY.
This is My .vb code:
sqlcon = New
SqlConnection(S ystem.Configura tion.Configurat ionManager.AppS ettings("Connec *tionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_ insert", sqlcon)
sqlcmd.CommandT ext = "sp_insert"
sqlcmd.Connecti on = sqlcon
sqlcmd.CommandT ype = CommandType.Sto redProcedure
sqlcmd.Paramete rs.Add("@Name", SqlDbType.VarCh ar).Value =
txtname.Text
sqlcmd.Paramete rs.Add("@Number ", SqlDbType.VarCh ar).Value
= txtnumber.Text
sqlcmd.Paramete rs.Add("@Date", SqlDbType.DateT ime).Value =
txtdate.Text
param = sqlcmd.Paramete rs.Add("@RetunV alue",
SqlDbType.VarCh ar, 50)
param.Direction = ParameterDirect ion.Output
sqlcmd.ExecuteN onQuery()
lblstatus.Text = "successfullyin serted"
sqlcon.Close()
ReturnValue = param.Value
Catch ex As Exception
lblstatus.Text = ex.ToString
End Try.
Please check this code and find out my Error.
Regards,
Sirisha.
You have to add @TestID as parameter and set it's direction to Output.
Then read the value of @Testid parameter after the sp has finished.
Thomas- Hide quoted text -
- Show quoted text -
I dont know how to pass that output parameter,pleas e write the code
how to pass that output parameter.
Regards,
Sirisha.
Sirisha wrote:
Hi,
I am inserting values into databse using sqlserver stored
procedures. i wrote stored preocedure,but in codebehind file(.vb file)
i dont know how to pass the parameters, i got error message like this
"System.Data.Sq lClient.SqlExce ption: Procedure or Function 'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.Sql Client.SqlConne ction"
Note that you must tell it the length of all strings, and I think you need a
new sqlparameter each time you add one.
Example:
--------------------------- VB.NET snippet ----------------------------
Dim sqlConn As New SqlConnection(s qlConnectionStr ing)
Dim sqlCmd As New SqlCommand("val idateUser", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure
Dim sqlParam As New SqlParameter("@ UserName", SqlDbType.NVarC har, 50)
sqlParam.Value = Trim(nam)
sqlCmd.Paramete rs.Add(sqlParam )
sqlParam = New SqlParameter("@ Password", SqlDbType.NVarC har, 24)
sqlParam.Value = Trim(pwd)
sqlCmd.Paramete rs.Add(sqlParam )
Dim sqlParamUserID As New SqlParameter("@ UserID", SqlDbType.Char, 36)
sqlParamUserID. Value = ""
sqlParamUserID. Direction = ParameterDirect ion.Output
sqlCmd.Paramete rs.Add(sqlParam UserID)
sqlConn.Open()
sqlCmd.ExecuteR eader()
sqlConn.Close()
If Not (sqlParamUserID .Value Is DBNull.Value) Then
loggedIn = True
myUserID = CType(sqlParamU serID.Value, String)
End If
----------------------------------------------------------------------
---------------------- Stored procedure ------------------------
CREATE PROCEDURE dbo.ValidateUse r
@Username nVarChar(50),
@Password nVarChar(24),
@UserID nVarChar(36) OUTPUT
as
Select @UserID=cast(Us erID as nvarchar(36)) From userdata
Where Username=@Usern ame
and Password=@Passw ord
GO
---------------------------------------------------------------------
HTH
Andrew This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Etienne M. St-Georges |
last post by:
Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ??? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
|
by: Robin Lawrie |
last post by:
Hi again, another problem!
I've moved from an Access database to SQL server and am now having trouble
inserting dates and times into seperate fields. I'm using ASP and the code
below to get the date and time, but my script is erroring.
'-- Get login date and time
cmdLoginDate = Date()
cmdLoginTime = Time()
|
by: pkruti |
last post by:
Below is a stored procedure i am working with and i am trying to drop
the yesno_holding table if it exists but how do i add it back? Meaning
i want it to drop if it exists but i want to add it back if it doesnt
exist. the reason why i have to drop it is because when i run the
stored procedure it creates mulitple rows with the values in them but
in my report it takes the top row and insert those values into the
report and i dont want that i...
|
by: dtwilliams |
last post by:
OK, i'm trying to do some error checking on stored procedures and am
following the advise in Erland Sommarskog's 'Implementing Error
Handling with Stored Procedures' document.
Can anybody help with my stored procedures and why it keeps erroring at
the '-- Create new Address Detail stage'? The errorCode value that is
being return in my web app is 0, so i'm not even sure why it's even
raising the error!!
Rather than executing the INSERT...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;
works from a query tool.
| | |
by: syntego |
last post by:
We commonly use triggers to log changes to our main tables to
historical log tables.
In the trigger, we create a concatenated string of the old values by
casting them as follows:
CAST(O.MYDATE AS CHAR(30))
When directly updating date fields in the main table, the logged value
gets saved in the format YYYY-MM-DD as expected.
|
by: Sirisha |
last post by:
Hello,
I want code for insert values into database using
storedprocedure and how to write stored procedures.
I want code in codebehined file, imean .aspx code
Regards,
|
by: Sirisha |
last post by:
Hi,
I am inserting values into databse using sqlserver stored
procedures. i wrote stored preocedure,but in codebehind file(.vb
file)
i dont know how to pass the parameters, i got error message like this
"System.Data.SqlClient.SqlException: Procedure or Function
'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.SqlClient.SqlConnection"
|
by: insirawali |
last post by:
Hi all,
I have this problem, i need to know is there a way i cn use the data
adapter's update method in this scenario.
i have 3 tables as below
create table table1{
id1 int identity(1,1)
Constraint pk_table1 Primary Key,
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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,...
|
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...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
