473,226 Members | 1,359 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,226 software developers and data experts.

access odbc output parameter

Hello all,
I am executing a stored procedure (on an SQL Server) using ODBC but i can't
get the output parameter's value on the client. The stored proc has 3
parameters ,2 of them are input and 1 is output. (for shake of simplicity
let's suppose that the proc seems something like
--------------------------------------------------------------------
alter storedProcsName @inputParam1 varchar(20), @inputParam2 varchar(20),
@outputParam varchar(40) output
as
set @outputParam = @inputParam1 + @inputParam2
end
------------------------------------------------------------------

The vb.net code i am using is the following
----------------------------------------------------
Private Sub execODBC()
Dim a_string As String = "a_string"
Dim another_string As String = "another_string"
Dim a_dummy_string As String = "a_dummy_string"

Dim cmdObj As Odbc.OdbcCommand
Dim inputParam1 As Odbc.OdbcParameter
Dim inputParam2 As Odbc.OdbcParameter
Dim outputParam As Odbc.OdbcParameter

cmdObj = New Odbc.OdbcCommand

inputParam1 = cmdObj.Parameters.Add("@inputParam1",
Odbc.OdbcType.VarChar)
inputParam2 = cmdObj.Parameters.Add("@inputParam2",
Odbc.OdbcType.VarChar)
outputParam = cmdObj.Parameters.Add("@outputParam",
Odbc.OdbcType.VarChar)
inputParam1.Direction = ParameterDirection.Input
inputParam2.Direction = ParameterDirection.Input
outputParam.Direction = ParameterDirection.Output
outputParam.Size = 40

inputParam1.Value = a_string
inputParam2.Value = another_string
outputParam.Value = a_dummy_string

With cmdObj
..Connection = a_odbc_connection
..CommandType = CommandType.StoredProcedure

..CommandText = "{call storedProcsName('" & inputParam1.Value & " ','" &
inputParam2.Value & "',?)}"

..ExecuteNonQuery()
End With

End Sub
When the code is executed, outputParam has a value of system.dbnull . Is
there something wrong with the cmdObj.commandText assignement?

another problem i have is that i can't pass - as an input paremeter - a
parameter of datetime type . Should i use a specific notation in the
..commandText ?
thanx
theodore

Nov 21 '05 #1
3 4537

If SQL Server, forget your odbc stuff and go as follows:

At the top of your form write the following:
Imports System.Data.SqlClient

Then in the body of your code:

Dim cnn As New
SqlConnection("Server=YOURSERVER;Database=YOURDATA BASE;uid=YOURUSERID;pwd=YOURPASSWORD")

Dim cmd As New SqlCommand("storedProcsName", cnn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@inputParam1",
SqlDbType.VarChar, 20)).Value = "whatever"

cmd.Parameters.Add(New SqlParameter("@inputParam2",
SqlDbType.VarChar, 20)).Value = "more"

cmd.Parameters.Add(New SqlParameter("@outputParam",
SqlDbType.VarChar, 40))
cmd.Parameters("@OutputParam").Direction =
ParameterDirection.Output
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
MsgBox(cmd.Parameters("@OutputParam").Value)

You should get "Whatevermore" as the result. Use "Try Catch finally" to
ensure safe connection handling and error handling too.

Gerry

"juststarter" wrote:
Hello all,
I am executing a stored procedure (on an SQL Server) using ODBC but i can't
get the output parameter's value on the client. The stored proc has 3
parameters ,2 of them are input and 1 is output. (for shake of simplicity
let's suppose that the proc seems something like
--------------------------------------------------------------------
alter storedProcsName @inputParam1 varchar(20), @inputParam2 varchar(20),
@outputParam varchar(40) output
as
set @outputParam = @inputParam1 + @inputParam2
end
------------------------------------------------------------------

The vb.net code i am using is the following
----------------------------------------------------
Private Sub execODBC()
Dim a_string As String = "a_string"
Dim another_string As String = "another_string"
Dim a_dummy_string As String = "a_dummy_string"

Dim cmdObj As Odbc.OdbcCommand
Dim inputParam1 As Odbc.OdbcParameter
Dim inputParam2 As Odbc.OdbcParameter
Dim outputParam As Odbc.OdbcParameter

cmdObj = New Odbc.OdbcCommand

inputParam1 = cmdObj.Parameters.Add("@inputParam1",
Odbc.OdbcType.VarChar)
inputParam2 = cmdObj.Parameters.Add("@inputParam2",
Odbc.OdbcType.VarChar)
outputParam = cmdObj.Parameters.Add("@outputParam",
Odbc.OdbcType.VarChar)
inputParam1.Direction = ParameterDirection.Input
inputParam2.Direction = ParameterDirection.Input
outputParam.Direction = ParameterDirection.Output
outputParam.Size = 40

inputParam1.Value = a_string
inputParam2.Value = another_string
outputParam.Value = a_dummy_string

With cmdObj
.Connection = a_odbc_connection
.CommandType = CommandType.StoredProcedure

.CommandText = "{call storedProcsName('" & inputParam1.Value & " ','" &
inputParam2.Value & "',?)}"

.ExecuteNonQuery()
End With

End Sub
When the code is executed, outputParam has a value of system.dbnull . Is
there something wrong with the cmdObj.commandText assignement?

another problem i have is that i can't pass - as an input paremeter - a
parameter of datetime type . Should i use a specific notation in the
.commandText ?
thanx
theodore

Nov 21 '05 #2
With DateTime parameters I find it best to pass in the date as a string
(varchar(20)) or something and then CAST the value to DATETIME in the Stored
Proc.

e.g.

CREATE PROCEDURE Test

@inputParam1 varchar(20),
@inputParam2 varchar(20),
@MyDate varchar(20) , --In VB Pass in as a string
@outputParam varchar(40) output
AS

SET @outputParam = @inputParam1 + @inputParam2

SELECT CAST(@MyDate AS DateTime) --SQL will cast it to a datetime value
GO


"juststarter" wrote:
Hello all,
I am executing a stored procedure (on an SQL Server) using ODBC but i can't
get the output parameter's value on the client. The stored proc has 3
parameters ,2 of them are input and 1 is output. (for shake of simplicity
let's suppose that the proc seems something like
--------------------------------------------------------------------
alter storedProcsName @inputParam1 varchar(20), @inputParam2 varchar(20),
@outputParam varchar(40) output
as
set @outputParam = @inputParam1 + @inputParam2
end
------------------------------------------------------------------

The vb.net code i am using is the following
----------------------------------------------------
Private Sub execODBC()
Dim a_string As String = "a_string"
Dim another_string As String = "another_string"
Dim a_dummy_string As String = "a_dummy_string"

Dim cmdObj As Odbc.OdbcCommand
Dim inputParam1 As Odbc.OdbcParameter
Dim inputParam2 As Odbc.OdbcParameter
Dim outputParam As Odbc.OdbcParameter

cmdObj = New Odbc.OdbcCommand

inputParam1 = cmdObj.Parameters.Add("@inputParam1",
Odbc.OdbcType.VarChar)
inputParam2 = cmdObj.Parameters.Add("@inputParam2",
Odbc.OdbcType.VarChar)
outputParam = cmdObj.Parameters.Add("@outputParam",
Odbc.OdbcType.VarChar)
inputParam1.Direction = ParameterDirection.Input
inputParam2.Direction = ParameterDirection.Input
outputParam.Direction = ParameterDirection.Output
outputParam.Size = 40

inputParam1.Value = a_string
inputParam2.Value = another_string
outputParam.Value = a_dummy_string

With cmdObj
.Connection = a_odbc_connection
.CommandType = CommandType.StoredProcedure

.CommandText = "{call storedProcsName('" & inputParam1.Value & " ','" &
inputParam2.Value & "',?)}"

.ExecuteNonQuery()
End With

End Sub
When the code is executed, outputParam has a value of system.dbnull . Is
there something wrong with the cmdObj.commandText assignement?

another problem i have is that i can't pass - as an input paremeter - a
parameter of datetime type . Should i use a specific notation in the
.commandText ?
thanx
theodore

Nov 21 '05 #3
Thx Gerry for both your answers.
the tip with datetimes is fine :D

Unfortunately i have to find the way using ODBC (the program already works
with sqlclient and oledb, but it has to support ODBC as well). I will keep on
searching ;-)
theodore

Nov 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
5
by: rob | last post by:
Hi to all. I am pretty new to using Access and am having a problem I hope someone can help me with. I want to access a MS-Access database from a web page. I have managed to get it "sort" of...
3
by: Raghuraman | last post by:
Hai I have a designed Crystal report .rpt file which has 4 access tables .. After designing the report i put a parameter field which filteres the desired records quite naturally. Every thing...
0
by: LesM | last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002 SP3. I have Progress table that is linked via ODBC into Access using OpenLink Lite for Progress 9.0b. For over a year, using...
42
by: PC Datasheet | last post by:
I have zero experience with using a SQL database for a backend and Access for a frontend. I have some questions: 1. Does an SQL database have tables? 2. How does Access connect to the data in...
0
by: totierne | last post by:
comp.databases.ms-access, I want to know how to use Oracle views with session variables in Access. The parameterised views in access, are migrated to views with per session variables. The...
0
by: ASP.Confused | last post by:
The old message looked a little stale, so I am re-posting it here. Anybody have any ideas of what I could do?!? The previous responses to this question are below. If you want to look at the...
2
by: Andy | last post by:
Hi guys I having a problem creating a report in Access 2003 project talking to a SQL database through and ODBC connect. After hours of trying things from Access Help, MSDN and Google I still...
7
ADezii
by: ADezii | last post by:
There are essentially three techniques for publishing Access Data on the Web. The first technique is static, and does not allow for the dynamic addition or modification to the data, There is no...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.