Hi
I have to select the values and display in the textboxes named txtcomm and txtdate from comm table. For that I have given the coding as following.
In stored pro I have given query as - set ANSI_NULLS ON
-
set QUOTED_IDENTIFIER ON
-
go
-
-
ALTER Procedure [dbo].[sp_CommisionSelect]
-
@ID int,
-
@Comm decimal(2,2),
-
@EffectiveDate datetime
-
-
as
-
Begin
-
-
SELECT Comm=@Comm, EffectiveDate=@EffectiveDate FROM comm WHERE ID=@ID
-
-
End
In front I have given query as - Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
-
'Request.QueryString["editid"]
-
'If Page.IsValid Then
-
Me.Label1.Text = Request.QueryString("editid")
-
Dim con As SqlConnection
-
Dim cmd, cmd1, cmd2 As New SqlCommand
-
Dim str As String
-
Dim rd As SqlDataReader
-
str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
-
con = New SqlConnection(str)
-
'Try
-
con.Open()
-
cmd.CommandType = CommandType.StoredProcedure
-
cmd.CommandText = "sp_CommisionSelect"
-
cmd.Connection = con
-
-
Dim ID As New SqlParameter("@ID", SqlDbType.Int)
-
'ID.Direction = ParameterDirection.Output
-
ID.Value = Label1.Text.ToString()
-
-
'CDID1.Value = CDID;
-
-
Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
-
'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
-
Comm.Direction = ParameterDirection.Output
-
-
Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
-
'EffectiveDate.Value = CDate(txtdate.Text.ToString())
-
EffectiveDate.Direction = ParameterDirection.Output
-
-
cmd.Parameters.Add(ID)
-
cmd.Parameters.Add(Comm)
-
cmd.Parameters.Add(EffectiveDate)
-
Dim cnt As Int16
-
cnt = cmd.ExecuteNonQuery()
-
-
'cnt = cmd.ExecuteScalar()
-
'cmd1.CommandType = CommandType.StoredProcedure
-
'cmd1.CommandText = "sp_CommisionUpdate"
-
'cmd1.Connection = con
-
'Dim Comm1 As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
-
'Comm1.Value = Convert.ToDecimal(txtcomm.Text.ToString())
-
-
'Dim EffectiveDate1 As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
-
'EffectiveDate1.Value = CDate(txtdate.Text.ToString())
-
-
'cmd1.Parameters.Add(Comm1)
-
'cmd1.Parameters.Add(EffectiveDate1)
-
'cmd1.ExecuteNonQuery()
-
con.Close()
-
-
'End If
-
End Sub
Once I run, it is showing error as
The formal parameter "@ID" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.
5 1331
Hi suganya,
The error explains itself really - you've defined all of your parameters as output parameters which means the the database is expecting to return the value of the parameter to the calling process.
If you meant to do this then you need to change your stored procedure to define them as output parameters when you declare them, like this: -
@ID int OUTPUT,
-
@Comm decimal(2,2) OUTPUT,
-
@EffectiveDate datetime OUTPUT
-
If you don't actually want the value of the parameters returned (and looking at your stored proc I can't see why you would as you don't change them at all) then remove the lines where you set the direction property for each of your parameters.
Hope this helps,
Dr B
PS Please remember to wrap your code in CODE tags using the # button - this makes your posts much easier to read.
Hi
Again I modified my code as - @ID int,
-
@Comm decimal(2,2) OUTPUT,
-
@EffectiveDate datetime OUTPUT
in DB. -
-
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
-
'Request.QueryString["editid"]
-
'If Page.IsValid Then
-
Me.Label1.Text = Request.QueryString("editid")
-
Dim con As SqlConnection
-
Dim cmd, cmd1, cmd2 As New SqlCommand
-
Dim str As String
-
Dim rd As SqlDataReader
-
str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
-
con = New SqlConnection(str)
-
'Try
-
con.Open()
-
cmd.CommandType = CommandType.StoredProcedure
-
cmd.CommandText = "sp_CommisionSelect"
-
cmd.Connection = con
-
-
-
-
-
-
Dim ID As New SqlParameter("@ID", SqlDbType.Int)
-
'ID.Direction = ParameterDirection.Output
-
'ID.Value = Label1.Text.ToString()
-
ID.Value = Label1.Text
-
-
'CDID1.Value = CDID;
-
-
Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
-
'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
-
Comm.Direction = ParameterDirection.Output
-
-
-
Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
-
'EffectiveDate.Value = CDate(txtdate.Text.ToString())
-
EffectiveDate.Direction = ParameterDirection.Output
-
-
-
-
cmd.Parameters.Add(ID)
-
cmd.Parameters.Add(Comm)
-
cmd.Parameters.Add(EffectiveDate)
-
Dim cnt As Int16
-
cnt = cmd.ExecuteNonQuery()
-
con.Close()
-
End Sub
but I have commented some lines that specify the textboxes.
eg.
'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
I think, I have to specify both the ParameterDirection.Output and the textbox name.
Why are you setting the parameters direction to output? If your stored procedure is just returning a record set then this is unnecessary.
Dr B
Hi
My stored procedure have to select and return data (comm, effective date) from comm table based on id. Thats why
You could do this rather than trying to extract the variables as ouputs - SELECT Comm, EffectiveDate FROM comm WHERE ID=@ID
Then retrieve them with a data reader.
Or am I missing the point? :-)
Dr B
Sign in to post your reply or Sign up for a free account.
Similar topics
by: mpinsley |
last post by:
We are a software company that provides Inventory & Procurement
mangement to the hospitality industry. For the past twenty years we
have been using Progress Software as both the development...
|
by: Kim |
last post by:
Hello,
I am selecting data from a text file in the following format:
INSERT INTO SELECT * FROM " & sSource & " IN '' " & _
"'text;Database=" & sPath & ";FMT=Delimited;HDR=No' " & _
"WHERE =...
|
by: Sami |
last post by:
I hope someone will tell me how to do this without having to do any VB
as I know nothing in that area. I am a rank beginner in using Access.
I have created a database consisting of student...
|
by: mpinsley |
last post by:
We are a software company that provides Inventory & Procurement
mangement to the hospitality industry. For the past twenty years we
have been using Progress Software as both the development...
|
by: aaj |
last post by:
Hi all
I use a data adapter to read numerous tables in to a dataset.
The dataset holds tables which in turn holds full details of the records
i.e. keys, extra colums etc..
In some cases I...
|
by: rickou812 |
last post by:
What I am attempting to do is create a form field in which a company name can be selecting from a drop down box. When selecting I want to display the information from my database about the selected...
|
by: larry |
last post by:
I am working on a DB for family data, and in this application the data
spans variable amount of rows in multiple tables (one for the adults
data, one for "family", one for the kids, another for...
|
by: darrel |
last post by:
I have a DDL list along these lines:
item value="1" text="a"
item value="2" text="b"
item value="3" text="c"
item value="2" text="d"
item value="2" text="e"
item value="1" text="f"
item...
|
by: megahurtz |
last post by:
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an...
|
by: megahurtz |
last post by:
I need to put together an SQL statement and I can't think of how to make it work properly. The scenario is that I have news items in a database that have a launch time and can optionally have an...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |