AS400 from VB.NET

[Sehboo]

Hello,

we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
that from my vb.net application. I don't know anything about AS400.
Is it possible to get data from tables and stored procedures?

can somebody give me the code which shows how to do that?

Thanks

[Cor]

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor

we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
that from my vb.net application. I don't know anything about AS400.
Is it possible to get data from tables and stored procedures?

can somebody give me the code which shows how to do that?

Thanks

[Sehboo]

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>...

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor

we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
that from my vb.net application. I don't know anything about AS400.
Is it possible to get data from tables and stored procedures?

can somebody give me the code which shows how to do that?

Thanks

[Jay B. Harlow [MVP - Outlook]]

Sehboo,
"DSN=MYAS400" is telling you that it is using standard ODBC connection to
access the AS/400.

You need to use the ODBC Data Sources under Control panel to define the
MYAS400 "data source", you will need to select the IBM Client Access driver.

Its best to be on Client Access V5R2 or later (with current all PTFs) to be
fully supported from .NET. (I understand that you can remain on OS/400 V4R5,
its the client that needs to be current).

http://www-1.ibm.com/servers/eserver...edb/index.html

http://www-912.ibm.com/n_dir/nas4apa...ight=2,ii13341

The above links are from June 3rd, not sure if there are more current
versions with better support...

If you look around the SQL Thing site and the above sites you should come
across a number of samples and further information.

Hope this helps
Jay


"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message

news:<#w**************@TK2MSFTNGP11.phx.gbl>...

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor

we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
that from my vb.net application. I don't know anything about AS400.
Is it possible to get data from tables and stored procedures?

can somebody give me the code which shows how to do that?

Thanks

[DB2]

StarQuest has a driver for DB2 (www.starquest.com), I believe it's
much cheaper than Ritmo.

Bob

ma*********@hotmail.com (Sehboo) wrote in message news:<7b**************************@posting.google. com>...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>...

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor

we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
that from my vb.net application. I don't know anything about AS400.
Is it possible to get data from tables and stored procedures?

can somebody give me the code which shows how to do that?

Thanks

[adiel]

Another approach you can take and also to avoid using an ODBC driver
is to use the IBMDA400 OLE DB provider for .NET. This is probably the
fastest and best method to retrieve data from the AS/400 when using
..NET including record level access (RLA), SQL and stored procedures
(SQL), data queues (DQ), and commands and programs (CMD/PGM). The
following IBM Informational APAR has more details on this driver for
..NET:

http://www-912.ibm.com/n_dir/nas4apa...7?OpenDocument

Thanks,
Adiel

[Jay B. Harlow [MVP - Outlook]]

adiel,

This is probably the
fastest and best method to retrieve data from the AS/400 when using
I disagree that the OLE DB is the fastest & "best" method to retrieve data
from an AS/400. I have however only used the OLE DB driver with the AS/400
from .NET. From VB6 I have used both.

Howard Arner (from SQL Thing) has documented on different occasions where
the client access ODBC driver out performs the OLE DB provider, granted this
was with ADO, however I would not expect the performance numbers to change
(much) for ADO.NET. Unfortunately I cannot find the links right now.

Whether it is better is subjective, if you are primarily using SQL & stored
procedures, in my experience ODBC tends to be better then OLE DB. If you are
using RLA, which I advise against then the OLE DB driver is better. I
recommend using stored procedures to access AS/400 data as you have more
performance & security controls that RLA or straight SQL.

IMHO: The fastest & best method has not been released yet, the fastest &
best method would be a native AS/400 client for .NET ala the OracleClient &
the SqlClient. (I'm not even sure that one is being planned).

Independent of which driver you choose, I strongly recommend you use the
CURRENT driver complete with PTFs!

I do not know if Howard has done any performance tests on the V5R2 ODBC &
OLE DB drivers under .NET yet or not.

BTW: You do realize that he link you gave is the same document that I gave,
just a different URL, they are both APAR II13341! :-)

Hope this helps
Jay

"adiel" <ad*****@hotmail.com> wrote in message
news:fe**************************@posting.google.c om... Another approach you can take and also to avoid using an ODBC driver
is to use the IBMDA400 OLE DB provider for .NET. This is probably the
fastest and best method to retrieve data from the AS/400 when using
.NET including record level access (RLA), SQL and stored procedures
(SQL), data queues (DQ), and commands and programs (CMD/PGM). The
following IBM Informational APAR has more details on this driver for
.NET:

http://www-912.ibm.com/n_dir/nas4apa...7?OpenDocument
Thanks,
Adiel

[Sehboo]

Thanks Jay,

I don't have IBM Client Access driver installed on my machine. How
can I get it? Again, is it free?

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<uP**************@TK2MSFTNGP10.phx.gbl>...

Sehboo,
"DSN=MYAS400" is telling you that it is using standard ODBC connection to
access the AS/400.

You need to use the ODBC Data Sources under Control panel to define the
MYAS400 "data source", you will need to select the IBM Client Access driver.

Its best to be on Client Access V5R2 or later (with current all PTFs) to be
fully supported from .NET. (I understand that you can remain on OS/400 V4R5,
its the client that needs to be current).

http://www-1.ibm.com/servers/eserver...edb/index.html

http://www-912.ibm.com/n_dir/nas4apa...ight=2,ii13341

The above links are from June 3rd, not sure if there are more current
versions with better support...

If you look around the SQL Thing site and the above sites you should come
across a number of samples and further information.

Hope this helps
Jay


"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message

news:<#w**************@TK2MSFTNGP11.phx.gbl>...

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor
>
> we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
> that from my vb.net application. I don't know anything about AS400.
> Is it possible to get data from tables and stored procedures?
>
> can somebody give me the code which shows how to do that?
>
> Thanks

[Sehboo]

Alright,

Here is the code that I have (i got it from one of these newsgroups
and then changed it). I am calling a stored procedure on AS400. This
SP takes 5 parameters.

It seems like I can open the connection but when It blows up at
objCommand.ExecuteNonQuery() method call. The exception that I get is
"ERROR [HY000][IBM][Client Access Express ODBC Driver(32-bit)][DB2/400
SQL]SQL0301 - Input host variable

I have no idea what that means.

By they way I have already made sure that Stored procedure is there.

Can anybody help?

Thanks
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim objConnection As New
OdbcConnection("DSN=AS400Connection;UID=myuid;PWD= mypwd")
Dim objCommand As New OdbcCommand("{ Call DS101SP1(?, ?, ?, ?,
?) }", objConnection)
Dim objParameter1 As New OdbcParameter

With objParameter1
.ParameterName = "MyParm1"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 6
.Value = "147031"
End With
objCommand.Parameters.Add(objParameter1)

Dim objParameter2 As New OdbcParameter

With objParameter2
.ParameterName = "MyParm2"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 4
.Value = "8487"
End With
objCommand.Parameters.Add(objParameter2)

Dim objParameter3 As New OdbcParameter

With objParameter3
.ParameterName = "MyParm3"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "1"
End With
objCommand.Parameters.Add(objParameter3)

Dim objParameter4 As New OdbcParameter

With objParameter4
.ParameterName = "MyParm4"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "A"
End With
objCommand.Parameters.Add(objParameter4)

Dim objParameter5 As New OdbcParameter

With objParameter5
.ParameterName = "MyParm5"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "B"
End With
objCommand.Parameters.Add(objParameter5)
objCommand.CommandType = CommandType.StoredProcedure
objConnection.Open()
Try
objCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

objConnection = Nothing
objCommand = Nothing
objParameter1 = Nothing
objParameter2 = Nothing
objParameter3 = Nothing
objParameter4 = Nothing
objParameter5 = Nothing

End Sub
db*****@hotmail.com (DB2) wrote in message news:<a7*************************@posting.google.c om>...

StarQuest has a driver for DB2 (www.starquest.com), I believe it's
much cheaper than Ritmo.

Bob

ma*********@hotmail.com (Sehboo) wrote in message news:<7b**************************@posting.google. com>...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>...

Hi Sehboo,

This was in this newsgroup, just search for AS400 then you see a greath
answer from Jay B. at Scott Johnson,

This is a link he provided in this message

http://www.sqlthing.com/Resources/St...StopDebug.html

I hope this helps,

Cor
>
> we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access
> that from my vb.net application. I don't know anything about AS400.
> Is it possible to get data from tables and stored procedures?
>
> can somebody give me the code which shows how to do that?
>
> Thanks

[Jay B. Harlow [MVP - Outlook]]

Sehboo,
It is installed when you install Client Access (iSeries Access) Express on
your machine.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b*************************@posting.google.co m...

Thanks Jay,

I don't have IBM Client Access driver installed on my machine. How
can I get it? Again, is it free?

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<uP**************@TK2MSFTNGP10.phx.gbl>...

Sehboo,
"DSN=MYAS400" is telling you that it is using standard ODBC connection to access the AS/400.

You need to use the ODBC Data Sources under Control panel to define the
MYAS400 "data source", you will need to select the IBM Client Access driver.
Its best to be on Client Access V5R2 or later (with current all PTFs) to be fully supported from .NET. (I understand that you can remain on OS/400 V4R5, its the client that needs to be current).

http://www-1.ibm.com/servers/eserver...edb/index.html

http://www-912.ibm.com/n_dir/nas4apa...ight=2,ii13341
The above links are from June 3rd, not sure if there are more current
versions with better support...

If you look around the SQL Thing site and the above sites you should come across a number of samples and further information.

Hope this helps
Jay


"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message

news:<#w**************@TK2MSFTNGP11.phx.gbl>...

> Hi Sehboo,
>
> This was in this newsgroup, just search for AS400 then you see a greath > answer from Jay B. at Scott Johnson,
>
> This is a link he provided in this message
>
> http://www.sqlthing.com/Resources/St...StopDebug.html
>
> I hope this helps,
>
> Cor
> >
> > we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access > > that from my vb.net application. I don't know anything about AS400. > > Is it possible to get data from tables and stored procedures?
> >
> > can somebody give me the code which shows how to do that?
> >
> > Thanks

[Jay B. Harlow [MVP - Outlook]]

Sehboo,
I suspect your parameter definitions in your VB.NET code does not match the
parameters expected by your stored procedure. Reviewing the info at both
links I gave you should find the technical documentation on the driver with
samples, they will be in VB6 & VBA, however the concepts are the same in
VB.NET & ADO.NET.

Unfortunately I do not have any real good resources to help you track down
the problem. What you can (try) to do is to find the job log on the AS/400
for the job that handled the request, there should be messages in it that
will help identify the actual problem.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Alright,

Here is the code that I have (i got it from one of these newsgroups
and then changed it). I am calling a stored procedure on AS400. This
SP takes 5 parameters.

It seems like I can open the connection but when It blows up at
objCommand.ExecuteNonQuery() method call. The exception that I get is
"ERROR [HY000][IBM][Client Access Express ODBC Driver(32-bit)][DB2/400
SQL]SQL0301 - Input host variable

I have no idea what that means.

By they way I have already made sure that Stored procedure is there.

Can anybody help?

Thanks
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim objConnection As New
OdbcConnection("DSN=AS400Connection;UID=myuid;PWD= mypwd")
Dim objCommand As New OdbcCommand("{ Call DS101SP1(?, ?, ?, ?,
?) }", objConnection)
Dim objParameter1 As New OdbcParameter

With objParameter1
.ParameterName = "MyParm1"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 6
.Value = "147031"
End With
objCommand.Parameters.Add(objParameter1)

Dim objParameter2 As New OdbcParameter

With objParameter2
.ParameterName = "MyParm2"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 4
.Value = "8487"
End With
objCommand.Parameters.Add(objParameter2)

Dim objParameter3 As New OdbcParameter

With objParameter3
.ParameterName = "MyParm3"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "1"
End With
objCommand.Parameters.Add(objParameter3)

Dim objParameter4 As New OdbcParameter

With objParameter4
.ParameterName = "MyParm4"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "A"
End With
objCommand.Parameters.Add(objParameter4)

Dim objParameter5 As New OdbcParameter

With objParameter5
.ParameterName = "MyParm5"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "B"
End With
objCommand.Parameters.Add(objParameter5)
objCommand.CommandType = CommandType.StoredProcedure
objConnection.Open()
Try
objCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

objConnection = Nothing
objCommand = Nothing
objParameter1 = Nothing
objParameter2 = Nothing
objParameter3 = Nothing
objParameter4 = Nothing
objParameter5 = Nothing

End Sub
db*****@hotmail.com (DB2) wrote in message

news:<a7*************************@posting.google.c om>...

StarQuest has a driver for DB2 (www.starquest.com), I believe it's
much cheaper than Ritmo.

Bob

ma*********@hotmail.com (Sehboo) wrote in message news:<7b**************************@posting.google. com>...

Thanks Cor,

I also saw that...but my problem is that I don't even know how to
define the DSN for AS400. Like in that code they have the following
line

CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"

I don't know how to create the AS400 DSN. When I try to create
datasources for it...I don't see any AS400 driver.

Where can I get the driver from? is it free? What about Ritmo driver
(some third party driver)...that is very expensive(around 4000
dollars). we can't spend that much money.

thanks
"Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>... > Hi Sehboo,
>
> This was in this newsgroup, just search for AS400 then you see a greath > answer from Jay B. at Scott Johnson,
>
> This is a link he provided in this message
>
> http://www.sqlthing.com/Resources/St...StopDebug.html
>
> I hope this helps,
>
> Cor
> >
> > we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access > > that from my vb.net application. I don't know anything about AS400. > > Is it possible to get data from tables and stored procedures?
> >
> > can somebody give me the code which shows how to do that?
> >
> > Thanks

[Sehboo]

Thanks Jay,

You were right...there was a problem with the parameters...

Now, I see one more problem....and I hope that somebody can show me
the path to fix that...

I have a stored procedure on AS400 which returns a record set. I can
call the stored procedure but I just never see the records. Number of
returned rows is always 0 eventhough I know it returns more then 1
more.

Here is my code:
Dim dbDataAdapter As New OdbcDataAdapter(objCommand)
Dim i As Integer = dbDataAdapter.Fill(mdbDataset)
Dim aa As String = mdbDataset.GetXml()
If (mdbDataset.Tables.Count > 0) Then
miRecordCount = mdbDataset.Tables(0).Rows.Count
mdbRow = mdbDataset.Tables(0).Rows(0)
miFieldCount = mdbRow.Table.Columns.Count
End If

for some reason, miRecordCount is always 0.

Why?

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<Og**************@TK2MSFTNGP11.phx.gbl>...

Sehboo,
I suspect your parameter definitions in your VB.NET code does not match the
parameters expected by your stored procedure. Reviewing the info at both
links I gave you should find the technical documentation on the driver with
samples, they will be in VB6 & VBA, however the concepts are the same in
VB.NET & ADO.NET.

Unfortunately I do not have any real good resources to help you track down
the problem. What you can (try) to do is to find the job log on the AS/400
for the job that handled the request, there should be messages in it that
will help identify the actual problem.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Alright,

Here is the code that I have (i got it from one of these newsgroups
and then changed it). I am calling a stored procedure on AS400. This
SP takes 5 parameters.

It seems like I can open the connection but when It blows up at
objCommand.ExecuteNonQuery() method call. The exception that I get is
"ERROR [HY000][IBM][Client Access Express ODBC Driver(32-bit)][DB2/400
SQL]SQL0301 - Input host variable

I have no idea what that means.

By they way I have already made sure that Stored procedure is there.

Can anybody help?

Thanks
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim objConnection As New
OdbcConnection("DSN=AS400Connection;UID=myuid;PWD= mypwd")
Dim objCommand As New OdbcCommand("{ Call DS101SP1(?, ?, ?, ?,
?) }", objConnection)
Dim objParameter1 As New OdbcParameter

With objParameter1
.ParameterName = "MyParm1"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 6
.Value = "147031"
End With
objCommand.Parameters.Add(objParameter1)

Dim objParameter2 As New OdbcParameter

With objParameter2
.ParameterName = "MyParm2"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 4
.Value = "8487"
End With
objCommand.Parameters.Add(objParameter2)

Dim objParameter3 As New OdbcParameter

With objParameter3
.ParameterName = "MyParm3"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "1"
End With
objCommand.Parameters.Add(objParameter3)

Dim objParameter4 As New OdbcParameter

With objParameter4
.ParameterName = "MyParm4"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "A"
End With
objCommand.Parameters.Add(objParameter4)

Dim objParameter5 As New OdbcParameter

With objParameter5
.ParameterName = "MyParm5"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "B"
End With
objCommand.Parameters.Add(objParameter5)
objCommand.CommandType = CommandType.StoredProcedure
objConnection.Open()
Try
objCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

objConnection = Nothing
objCommand = Nothing
objParameter1 = Nothing
objParameter2 = Nothing
objParameter3 = Nothing
objParameter4 = Nothing
objParameter5 = Nothing

End Sub
db*****@hotmail.com (DB2) wrote in message

news:<a7*************************@posting.google.c om>...

StarQuest has a driver for DB2 (www.starquest.com), I believe it's
much cheaper than Ritmo.

Bob

ma*********@hotmail.com (Sehboo) wrote in message news:<7b**************************@posting.google. com>... > Thanks Cor,
>
> I also saw that...but my problem is that I don't even know how to
> define the DSN for AS400. Like in that code they have the following
> line
>
> CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"
>
> I don't know how to create the AS400 DSN. When I try to create
> datasources for it...I don't see any AS400 driver.
>
> Where can I get the driver from? is it free? What about Ritmo driver
> (some third party driver)...that is very expensive(around 4000
> dollars). we can't spend that much money.
>
> thanks
>
>
> "Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>... > > Hi Sehboo,
> >
> > This was in this newsgroup, just search for AS400 then you see a greath > > answer from Jay B. at Scott Johnson,
> >
> > This is a link he provided in this message
> >
> > http://www.sqlthing.com/Resources/St...StopDebug.html
> >
> > I hope this helps,
> >
> > Cor
> > >
> > > we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access > > > that from my vb.net application. I don't know anything about AS400. > > > Is it possible to get data from tables and stored procedures?
> > >
> > > can somebody give me the code which shows how to do that?
> > >
> > > Thanks

[Jay B. Harlow [MVP - Outlook]]

Sehboo,
How are you defining the Stored Procedure itself?

- All SQL
- RPG or COBOL & SQL External Stored Procedure statement
- Other

The SQL part of your stored procedure needs to indicate that it is returning
result sets. Whether or not the body of the stored procedure is in SQL, RPG,
COBOL, or Java.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b*************************@posting.google.co m...

Thanks Jay,

You were right...there was a problem with the parameters...

Now, I see one more problem....and I hope that somebody can show me
the path to fix that...

I have a stored procedure on AS400 which returns a record set. I can
call the stored procedure but I just never see the records. Number of
returned rows is always 0 eventhough I know it returns more then 1
more.

Here is my code:
Dim dbDataAdapter As New OdbcDataAdapter(objCommand)
Dim i As Integer = dbDataAdapter.Fill(mdbDataset)
Dim aa As String = mdbDataset.GetXml()
If (mdbDataset.Tables.Count > 0) Then
miRecordCount = mdbDataset.Tables(0).Rows.Count
mdbRow = mdbDataset.Tables(0).Rows(0)
miFieldCount = mdbRow.Table.Columns.Count
End If

for some reason, miRecordCount is always 0.

Why?

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<Og**************@TK2MSFTNGP11.phx.gbl>...

Sehboo,
I suspect your parameter definitions in your VB.NET code does not match the parameters expected by your stored procedure. Reviewing the info at both
links I gave you should find the technical documentation on the driver with samples, they will be in VB6 & VBA, however the concepts are the same in
VB.NET & ADO.NET.

Unfortunately I do not have any real good resources to help you track down the problem. What you can (try) to do is to find the job log on the AS/400 for the job that handled the request, there should be messages in it that will help identify the actual problem.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...

Alright,

Here is the code that I have (i got it from one of these newsgroups
and then changed it). I am calling a stored procedure on AS400. This
SP takes 5 parameters.

It seems like I can open the connection but when It blows up at
objCommand.ExecuteNonQuery() method call. The exception that I get is
"ERROR [HY000][IBM][Client Access Express ODBC Driver(32-bit)][DB2/400
SQL]SQL0301 - Input host variable

I have no idea what that means.

By they way I have already made sure that Stored procedure is there.

Can anybody help?

Thanks
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim objConnection As New
OdbcConnection("DSN=AS400Connection;UID=myuid;PWD= mypwd")
Dim objCommand As New OdbcCommand("{ Call DS101SP1(?, ?, ?, ?,
?) }", objConnection)
Dim objParameter1 As New OdbcParameter

With objParameter1
.ParameterName = "MyParm1"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 6
.Value = "147031"
End With
objCommand.Parameters.Add(objParameter1)

Dim objParameter2 As New OdbcParameter

With objParameter2
.ParameterName = "MyParm2"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 4
.Value = "8487"
End With
objCommand.Parameters.Add(objParameter2)

Dim objParameter3 As New OdbcParameter

With objParameter3
.ParameterName = "MyParm3"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "1"
End With
objCommand.Parameters.Add(objParameter3)

Dim objParameter4 As New OdbcParameter

With objParameter4
.ParameterName = "MyParm4"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "A"
End With
objCommand.Parameters.Add(objParameter4)

Dim objParameter5 As New OdbcParameter

With objParameter5
.ParameterName = "MyParm5"
.DbType = DbType.AnsiString
.Direction = ParameterDirection.InputOutput
.Size = 1
.Value = "B"
End With
objCommand.Parameters.Add(objParameter5)
objCommand.CommandType = CommandType.StoredProcedure
objConnection.Open()
Try
objCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try

objConnection = Nothing
objCommand = Nothing
objParameter1 = Nothing
objParameter2 = Nothing
objParameter3 = Nothing
objParameter4 = Nothing
objParameter5 = Nothing

End Sub
db*****@hotmail.com (DB2) wrote in message

news:<a7*************************@posting.google.c om>...

> StarQuest has a driver for DB2 (www.starquest.com), I believe it's
> much cheaper than Ritmo.
>
> Bob
>
> ma*********@hotmail.com (Sehboo) wrote in message

news:<7b**************************@posting.google. com>...

> > Thanks Cor,
> >
> > I also saw that...but my problem is that I don't even know how to
> > define the DSN for AS400. Like in that code they have the following > > line
> >
> > CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"
> >
> > I don't know how to create the AS400 DSN. When I try to create
> > datasources for it...I don't see any AS400 driver.
> >
> > Where can I get the driver from? is it free? What about Ritmo driver > > (some third party driver)...that is very expensive(around 4000
> > dollars). we can't spend that much money.
> >
> > thanks
> >
> >
> > "Cor" <no*@non.com> wrote in message

news:<#w**************@TK2MSFTNGP11.phx.gbl>...

> > > Hi Sehboo,
> > >
> > > This was in this newsgroup, just search for AS400 then you see a

greath

> > > answer from Jay B. at Scott Johnson,
> > >
> > > This is a link he provided in this message
> > >
> > > http://www.sqlthing.com/Resources/St...StopDebug.html
> > >
> > > I hope this helps,
> > >
> > > Cor
> > > >
> > > > we have data sitting on AS400 (V4R5M0) - DB2-400. I need to

access

> > > > that from my vb.net application. I don't know anything about

AS400.

> > > > Is it possible to get data from tables and stored procedures?
> > > >
> > > > can somebody give me the code which shows how to do that?
> > > >
> > > > Thanks

[Sehboo]

Thanks Jay - I really appreciate your help.

Stored procedure is fine because I can get the data using Ritmo
driver, but we want to use ODBC so that we won't have to buy
ritmo...it is very expensive...This means that I am missing something
on the frontend. All the examples that I see have no problem like
this.

ODBC call, doesn't throw any exception...it just never returns any
data...has anybody else done this before? our AS400 version is V4R5.
Maybe I am missing something on the DSN setting?

Also, if this works then why would anybody buy ritmo driver?

If I get this done then not just me, but my VP will appreciate it too
:-)

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:<Oc**************@TK2MSFTNGP11.phx.gbl>...

Sehboo,
How are you defining the Stored Procedure itself?

- All SQL
- RPG or COBOL & SQL External Stored Procedure statement
- Other

The SQL part of your stored procedure needs to indicate that it is returning
result sets. Whether or not the body of the stored procedure is in SQL, RPG,
COBOL, or Java.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b*************************@posting.google.co m...

Thanks Jay,

You were right...there was a problem with the parameters...

Now, I see one more problem....and I hope that somebody can show me
the path to fix that...

I have a stored procedure on AS400 which returns a record set. I can
call the stored procedure but I just never see the records. Number of
returned rows is always 0 eventhough I know it returns more then 1
more.

Here is my code:
Dim dbDataAdapter As New OdbcDataAdapter(objCommand)
Dim i As Integer = dbDataAdapter.Fill(mdbDataset)
Dim aa As String = mdbDataset.GetXml()
If (mdbDataset.Tables.Count > 0) Then
miRecordCount = mdbDataset.Tables(0).Rows.Count
mdbRow = mdbDataset.Tables(0).Rows(0)
miFieldCount = mdbRow.Table.Columns.Count
End If

for some reason, miRecordCount is always 0.

Why?

Thanks

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message

news:<Og**************@TK2MSFTNGP11.phx.gbl>...

Sehboo,
I suspect your parameter definitions in your VB.NET code does not match the parameters expected by your stored procedure. Reviewing the info at both
links I gave you should find the technical documentation on the driver with samples, they will be in VB6 & VBA, however the concepts are the same in
VB.NET & ADO.NET.

Unfortunately I do not have any real good resources to help you track down the problem. What you can (try) to do is to find the job log on the AS/400 for the job that handled the request, there should be messages in it that will help identify the actual problem.

Hope this helps
Jay

"Sehboo" <ma*********@hotmail.com> wrote in message
news:7b**************************@posting.google.c om...
> Alright,
>
> Here is the code that I have (i got it from one of these newsgroups
> and then changed it). I am calling a stored procedure on AS400. This
> SP takes 5 parameters.
>
> It seems like I can open the connection but when It blows up at
> objCommand.ExecuteNonQuery() method call. The exception that I get is
> "ERROR [HY000][IBM][Client Access Express ODBC Driver(32-bit)][DB2/400
> SQL]SQL0301 - Input host variable
>
> I have no idea what that means.
>
> By they way I have already made sure that Stored procedure is there.
>
> Can anybody help?
>
> Thanks
>
>
> Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button3.Click
> Dim objConnection As New
> OdbcConnection("DSN=AS400Connection;UID=myuid;PWD= mypwd")
> Dim objCommand As New OdbcCommand("{ Call DS101SP1(?, ?, ?, ?,
> ?) }", objConnection)
> Dim objParameter1 As New OdbcParameter
>
> With objParameter1
> .ParameterName = "MyParm1"
> .DbType = DbType.AnsiString
> .Direction = ParameterDirection.InputOutput
> .Size = 6
> .Value = "147031"
> End With
> objCommand.Parameters.Add(objParameter1)
>
> Dim objParameter2 As New OdbcParameter
>
> With objParameter2
> .ParameterName = "MyParm2"
> .DbType = DbType.AnsiString
> .Direction = ParameterDirection.InputOutput
> .Size = 4
> .Value = "8487"
> End With
> objCommand.Parameters.Add(objParameter2)
>
> Dim objParameter3 As New OdbcParameter
>
> With objParameter3
> .ParameterName = "MyParm3"
> .DbType = DbType.AnsiString
> .Direction = ParameterDirection.InputOutput
> .Size = 1
> .Value = "1"
> End With
> objCommand.Parameters.Add(objParameter3)
>
> Dim objParameter4 As New OdbcParameter
>
> With objParameter4
> .ParameterName = "MyParm4"
> .DbType = DbType.AnsiString
> .Direction = ParameterDirection.InputOutput
> .Size = 1
> .Value = "A"
> End With
> objCommand.Parameters.Add(objParameter4)
>
> Dim objParameter5 As New OdbcParameter
>
> With objParameter5
> .ParameterName = "MyParm5"
> .DbType = DbType.AnsiString
> .Direction = ParameterDirection.InputOutput
> .Size = 1
> .Value = "B"
> End With
> objCommand.Parameters.Add(objParameter5)
>
>
> objCommand.CommandType = CommandType.StoredProcedure
> objConnection.Open()
> Try
> objCommand.ExecuteNonQuery()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
>
> objConnection = Nothing
> objCommand = Nothing
> objParameter1 = Nothing
> objParameter2 = Nothing
> objParameter3 = Nothing
> objParameter4 = Nothing
> objParameter5 = Nothing
>
> End Sub
> db*****@hotmail.com (DB2) wrote in message news:<a7*************************@posting.google.c om>... > > StarQuest has a driver for DB2 (www.starquest.com), I believe it's
> > much cheaper than Ritmo.
> >
> > Bob
> >
> > ma*********@hotmail.com (Sehboo) wrote in message news:<7b**************************@posting.google. com>... > > > Thanks Cor,
> > >
> > > I also saw that...but my problem is that I don't even know how to
> > > define the DSN for AS400. Like in that code they have the following > > > line
> > >
> > > CON1.Open "DSN=MYAS400;UID=HARNER;PWD=SECRET;"
> > >
> > > I don't know how to create the AS400 DSN. When I try to create
> > > datasources for it...I don't see any AS400 driver.
> > >
> > > Where can I get the driver from? is it free? What about Ritmo driver > > > (some third party driver)...that is very expensive(around 4000
> > > dollars). we can't spend that much money.
> > >
> > > thanks
> > >
> > >
> > > "Cor" <no*@non.com> wrote in message news:<#w**************@TK2MSFTNGP11.phx.gbl>... > > > > Hi Sehboo,
> > > >
> > > > This was in this newsgroup, just search for AS400 then you see a greath > > > > answer from Jay B. at Scott Johnson,
> > > >
> > > > This is a link he provided in this message
> > > >
> > > > http://www.sqlthing.com/Resources/St...StopDebug.html
> > > >
> > > > I hope this helps,
> > > >
> > > > Cor
> > > > >
> > > > > we have data sitting on AS400 (V4R5M0) - DB2-400. I need to access > > > > > that from my vb.net application. I don't know anything about AS400. > > > > > Is it possible to get data from tables and stored procedures?
> > > > >
> > > > > can somebody give me the code which shows how to do that?
> > > > >
> > > > > Thanks