By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,500 Members | 1,571 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,500 IT Pros & Developers. It's quick & easy.

Using stored procedures to insert values into databse

P: n/a
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"

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=@@IDENTITY.

This is My .vb code:

sqlcon = New
SqlConnection(System.Configuration.ConfigurationMa nager.AppSettings("Connectionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_insert", sqlcon)
sqlcmd.CommandText = "sp_insert"
sqlcmd.Connection = sqlcon
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value =
txtname.Text
sqlcmd.Parameters.Add("@Number", SqlDbType.VarChar).Value
= txtnumber.Text
sqlcmd.Parameters.Add("@Date", SqlDbType.DateTime).Value =
txtdate.Text
param = sqlcmd.Parameters.Add("@RetunValue",
SqlDbType.VarChar, 50)
param.Direction = ParameterDirection.Output
sqlcmd.ExecuteNonQuery()
lblstatus.Text = "successfullyinserted"
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.

Feb 22 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 22 Feb., 12:19, "Sirisha" <sireesha.ard...@gmail.comwrote:
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"

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=@@IDENTITY.

This is My .vb code:

sqlcon = New
SqlConnection(System.Configuration.ConfigurationMa nager.AppSettings("Connectionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_insert", sqlcon)
sqlcmd.CommandText = "sp_insert"
sqlcmd.Connection = sqlcon
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value =
txtname.Text
sqlcmd.Parameters.Add("@Number", SqlDbType.VarChar).Value
= txtnumber.Text
sqlcmd.Parameters.Add("@Date", SqlDbType.DateTime).Value =
txtdate.Text
param = sqlcmd.Parameters.Add("@RetunValue",
SqlDbType.VarChar, 50)
param.Direction = ParameterDirection.Output
sqlcmd.ExecuteNonQuery()
lblstatus.Text = "successfullyinserted"
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

Feb 22 '07 #2

P: n/a
On Feb 22, 4:46 pm, "Thomas Schoch" <t...@swiss-soft.chwrote:
On 22 Feb., 12:19, "Sirisha" <sireesha.ard...@gmail.comwrote:


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"
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=@@IDENTITY.
This is My .vb code:
sqlcon = New
SqlConnection(System.Configuration.ConfigurationMa nager.AppSettings("Connec*tionstring"))
Try
sqlcon.Open()
sqlcmd = New SqlCommand("sp_insert", sqlcon)
sqlcmd.CommandText = "sp_insert"
sqlcmd.Connection = sqlcon
sqlcmd.CommandType = CommandType.StoredProcedure
sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value =
txtname.Text
sqlcmd.Parameters.Add("@Number", SqlDbType.VarChar).Value
= txtnumber.Text
sqlcmd.Parameters.Add("@Date", SqlDbType.DateTime).Value =
txtdate.Text
param = sqlcmd.Parameters.Add("@RetunValue",
SqlDbType.VarChar, 50)
param.Direction = ParameterDirection.Output
sqlcmd.ExecuteNonQuery()
lblstatus.Text = "successfullyinserted"
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,please write the code
how to pass that output parameter.
Regards,

Sirisha.

Feb 22 '07 #3

P: n/a
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.SqlClient.SqlException: Procedure or Function 'sp_insert'
expects parameter '@TestId', which was not supplied. at
System.Data.SqlClient.SqlConnection"
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(sqlConnectionString)

Dim sqlCmd As New SqlCommand("validateUser", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As New SqlParameter("@UserName", SqlDbType.NVarChar, 50)
sqlParam.Value = Trim(nam)
sqlCmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Password", SqlDbType.NVarChar, 24)
sqlParam.Value = Trim(pwd)
sqlCmd.Parameters.Add(sqlParam)

Dim sqlParamUserID As New SqlParameter("@UserID", SqlDbType.Char, 36)
sqlParamUserID.Value = ""
sqlParamUserID.Direction = ParameterDirection.Output
sqlCmd.Parameters.Add(sqlParamUserID)

sqlConn.Open()
sqlCmd.ExecuteReader()
sqlConn.Close()

If Not (sqlParamUserID.Value Is DBNull.Value) Then
loggedIn = True
myUserID = CType(sqlParamUserID.Value, String)
End If
----------------------------------------------------------------------

---------------------- Stored procedure ------------------------
CREATE PROCEDURE dbo.ValidateUser
@Username nVarChar(50),
@Password nVarChar(24),
@UserID nVarChar(36) OUTPUT
as
Select @UserID=cast(UserID as nvarchar(36)) From userdata
Where Username=@Username
and Password=@Password

GO
---------------------------------------------------------------------

HTH

Andrew
Feb 22 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.