473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

selecting from database

39
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
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. go
  4.  
  5. ALTER Procedure [dbo].[sp_CommisionSelect]
  6.     @ID int,    
  7.     @Comm decimal(2,2),
  8.     @EffectiveDate datetime
  9.  
  10. as
  11. Begin
  12.  
  13. SELECT Comm=@Comm, EffectiveDate=@EffectiveDate FROM comm WHERE ID=@ID
  14.  
  15. End
In front I have given query as

Expand|Select|Wrap|Line Numbers
  1. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  2.         'Request.QueryString["editid"]
  3.         'If Page.IsValid Then
  4.         Me.Label1.Text = Request.QueryString("editid")
  5.         Dim con As SqlConnection
  6.         Dim cmd, cmd1, cmd2 As New SqlCommand
  7.         Dim str As String
  8.         Dim rd As SqlDataReader
  9.         str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
  10.         con = New SqlConnection(str)
  11.         'Try
  12.         con.Open()
  13.         cmd.CommandType = CommandType.StoredProcedure
  14.         cmd.CommandText = "sp_CommisionSelect"
  15.         cmd.Connection = con
  16.  
  17.         Dim ID As New SqlParameter("@ID", SqlDbType.Int)
  18.         'ID.Direction = ParameterDirection.Output
  19.         ID.Value = Label1.Text.ToString()
  20.  
  21.         'CDID1.Value = CDID;
  22.  
  23.         Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
  24.         'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
  25.         Comm.Direction = ParameterDirection.Output
  26.  
  27.         Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
  28.         'EffectiveDate.Value = CDate(txtdate.Text.ToString())
  29.         EffectiveDate.Direction = ParameterDirection.Output
  30.  
  31.         cmd.Parameters.Add(ID)
  32.         cmd.Parameters.Add(Comm)
  33.         cmd.Parameters.Add(EffectiveDate)
  34.         Dim cnt As Int16
  35.         cnt = cmd.ExecuteNonQuery()
  36.  
  37.         'cnt = cmd.ExecuteScalar()
  38.         'cmd1.CommandType = CommandType.StoredProcedure
  39.         'cmd1.CommandText = "sp_CommisionUpdate"
  40.         'cmd1.Connection = con
  41.         'Dim Comm1 As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
  42.         'Comm1.Value = Convert.ToDecimal(txtcomm.Text.ToString())
  43.  
  44.         'Dim EffectiveDate1 As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
  45.         'EffectiveDate1.Value = CDate(txtdate.Text.ToString())
  46.  
  47.         'cmd1.Parameters.Add(Comm1)
  48.         'cmd1.Parameters.Add(EffectiveDate1)
  49.         'cmd1.ExecuteNonQuery()
  50.         con.Close()
  51.  
  52.         'End If
  53.     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.
Aug 14 '08 #1
5 1331
DrBunchman
979 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. @ID int OUTPUT,    
  2. @Comm decimal(2,2) OUTPUT,
  3. @EffectiveDate datetime OUTPUT
  4.  
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.
Aug 14 '08 #2
suganya
39
Hi

Again I modified my code as


Expand|Select|Wrap|Line Numbers
  1. @ID int,    
  2.     @Comm decimal(2,2) OUTPUT,
  3.     @EffectiveDate datetime OUTPUT

in DB.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  3.         'Request.QueryString["editid"]
  4.         'If Page.IsValid Then
  5.         Me.Label1.Text = Request.QueryString("editid")
  6.         Dim con As SqlConnection
  7.         Dim cmd, cmd1, cmd2 As New SqlCommand
  8.         Dim str As String
  9.         Dim rd As SqlDataReader
  10.         str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
  11.         con = New SqlConnection(str)
  12.         'Try
  13.         con.Open()
  14.         cmd.CommandType = CommandType.StoredProcedure
  15.         cmd.CommandText = "sp_CommisionSelect"
  16.         cmd.Connection = con
  17.  
  18.  
  19.  
  20.  
  21.  
  22.         Dim ID As New SqlParameter("@ID", SqlDbType.Int)
  23.         'ID.Direction = ParameterDirection.Output
  24.         'ID.Value = Label1.Text.ToString()
  25.         ID.Value = Label1.Text
  26.  
  27.         'CDID1.Value = CDID;
  28.  
  29.         Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
  30.         'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
  31.         Comm.Direction = ParameterDirection.Output
  32.  
  33.  
  34.         Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
  35.         'EffectiveDate.Value = CDate(txtdate.Text.ToString())
  36.         EffectiveDate.Direction = ParameterDirection.Output
  37.  
  38.  
  39.  
  40.         cmd.Parameters.Add(ID)
  41.         cmd.Parameters.Add(Comm)
  42.         cmd.Parameters.Add(EffectiveDate)
  43.         Dim cnt As Int16
  44.         cnt = cmd.ExecuteNonQuery()
  45.         con.Close()
  46.     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.
Aug 14 '08 #3
DrBunchman
979 Expert 512MB
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
Aug 14 '08 #4
suganya
39
Hi

My stored procedure have to select and return data (comm, effective date) from comm table based on id. Thats why
Aug 14 '08 #5
DrBunchman
979 Expert 512MB
You could do this rather than trying to extract the variables as ouputs

Expand|Select|Wrap|Line Numbers
  1. SELECT Comm, EffectiveDate FROM comm WHERE ID=@ID
Then retrieve them with a data reader.

Or am I missing the point? :-)

Dr B
Aug 14 '08 #6

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

Similar topics

12
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...
0
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 =...
4
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...
2
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...
6
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...
9
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...
2
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...
4
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...
5
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...
2
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...
0
isladogs
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
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...
0
isladogs
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...

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.