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]" <Jay_Harlow_MVP@msn.com> wrote in message news:<OcdwE#b6DHA.2576@TK2MSFTNGP11.phx.gbl>...[color=blue]
> 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" <masoodadnan@hotmail.com> wrote in message
> news:7bfc97ca.0402021041.14ddfd5@posting.google.co m...[color=green]
> > 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]" <Jay_Harlow_MVP@msn.com> wrote in message[/color]
> news:<Oged8n45DHA.2576@TK2MSFTNGP11.phx.gbl>...[color=green][color=darkred]
> > > Sehboo,
> > > I suspect your parameter definitions in your VB.NET code does not match[/color][/color]
> the[color=green][color=darkred]
> > > parameters expected by your stored procedure. Reviewing the info at both
> > > links I gave you should find the technical documentation on the driver[/color][/color]
> with[color=green][color=darkred]
> > > 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[/color][/color]
> down[color=green][color=darkred]
> > > the problem. What you can (try) to do is to find the job log on the[/color][/color]
> AS/400[color=green][color=darkred]
> > > for the job that handled the request, there should be messages in it[/color][/color]
> that[color=green][color=darkred]
> > > will help identify the actual problem.
> > >
> > > Hope this helps
> > > Jay
> > >
> > > "Sehboo" <masoodadnan@hotmail.com> wrote in message
> > > news:7bfc97ca.0401300838.17fcad2f@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
> > > >
db2team@hotmail.com (DB2) wrote in message[/color][/color]
> news:<a78ec628.0401300207.a33e77d@posting.google.c om>...[color=green][color=darkred]
> > > > > StarQuest has a driver for DB2 (
www.starquest.com), I believe it's
> > > > > much cheaper than Ritmo.
> > > > >
> > > > > Bob
> > > > >
> > > > >
masoodadnan@hotmail.com (Sehboo) wrote in message[/color][/color]
> news:<7bfc97ca.0401291443.582ed0c9@posting.google. com>...[color=green][color=darkred]
> > > > > > 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[/color][/color]
> following[color=green][color=darkred]
> > > > > > 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[/color][/color]
> driver[color=green][color=darkred]
> > > > > > (some third party driver)...that is very expensive(around 4000
> > > > > > dollars). we can't spend that much money.
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > >
> > > > > > "Cor" <non@non.com> wrote in message[/color][/color]
> news:<#wlY8Jq5DHA.2392@TK2MSFTNGP11.phx.gbl>...[color=green][color=darkred]
> > > > > > > Hi Sehboo,
> > > > > > >
> > > > > > > This was in this newsgroup, just search for AS400 then you see a[/color][/color]
> greath[color=green][color=darkred]
> > > > > > > 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[/color][/color]
> access[color=green][color=darkred]
> > > > > > > > that from my vb.net application. I don't know anything about[/color][/color]
> AS400.[color=green][color=darkred]
> > > > > > > > Is it possible to get data from tables and stored procedures?
> > > > > > > >
> > > > > > > > can somebody give me the code which shows how to do that?
> > > > > > > >
> > > > > > > > Thanks[/color][/color][/color]
