access odbc output parameter

[Guest]

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

 

[Guest]

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=YOURDATABASE;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

 

[Guest]

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

 

[Guest]

Thx Gerry for both your answers.
the tip with datetimes is fine

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