INTERFACE AS/400 - .NET

[Gina L. Hernandez]

Hello:

I wrote this code to have an interface between AS/400 and Visual .Net.
This code works for a limited number of items, but after that it blows up
and says this message

ERROR String input in the wrong format just in the line of
da.Fill(mytable), it means that just in the moment when I am trying to
retrieve the data with the data Adpater , something happen.

Does Somebody know what it's happening or have a nother way to have the
interface between AS/400 and .NETT??.

SOme of thew fields in the list for the accesss to AS/400 are PACKFIELDS,
that's why I haven't been ABLE to use OLEDDB with that.

I will aprecciate all the help.

Thanks











Public Function VALITEM400(ByRef vOPT As String, ByVal vIpaddress As String,
ByVal vIncoming As String, ByVal vSQLConn As SqlConnection, ByRef messtext
As String, ByRef FlagBoolean As Boolean, ByVal genparams() As String) As
String

Dim oODBCConnection As Odbc.OdbcConnection

Dim sConnString As String = "Driver={Client Access ODBC Driver (32-bit)};" &
_

"System=AS/400;" & _

"Uid=user;" & _

"Pwd=password"

oODBCConnection = New Odbc.OdbcConnection(sConnString)

oODBCConnection.Open()

Try

Dim myQuery As String = "SELECT ZAITEM, ZALOC,ZAQTY,ZAWET, PDES35,WTSU35,
WUOM35, AVWG35, BARC35 " & _

"FROM PRDAURF3.INP77U0B, PRDJBF3.INP35ITM " & "WHERE ZABAR = '" &
Trim(vIncoming) & "'" & " AND ZAITEM = PNUM35"

Dim da As New Odbc.OdbcDataAdapter(myQuery, sConnString)

Dim mytable As New DataTable

Dim myrow As DataRow

Dim t_messtext As StringBuilder = New StringBuilder

Dim temp As Decimal

Dim SQL As String

Dim result As String

Dim mySQLDA As New SqlDataAdapter

Dim mySQLDS As New DataSet

Dim mySqlCmd1 As New SqlCommand

Dim w_description As String

da.Fill(mytable)

If mytable.Rows.Count = 0 Then

messtext = "C1515~M1501ITEM NOT FOUND~B~"

vOPT = "OPT1"

Else

myrow = mytable.Rows(0)

w_description = delete_quotation(myrow("PDES35"))

SQL = "sp_w_receiving_item_400 '" & vIncoming & "','" & myrow("ZAITEM") &
"'," & _

myrow("ZAQTY") & "," & myrow("ZAWET") & ",'" & myrow("ZALOC") & "','" &
genparams(0) & "','" & _

w_description & "'," & myrow("WTSU35") & ",'" & myrow("WUOM35") & "','" &
myrow("BARC35") & "'"

mySqlCmd1.Connection = vSQLConn

mySqlCmd1.CommandText = SQL

result = mySqlCmd1.ExecuteScalar()

Select Case result

Case "1"

vOPT = "OPT1" 'everything ok

t_messtext.Append("C0811~")

If Not IsDBNull(myrow("ZAITEM")) Then t_messtext.Append("M0801ITEM: " +
Trim(myrow("ZAITEM")) + "~")

If Not IsDBNull(myrow("ZAQTY")) Then

temp = CDec(myrow("ZAQTY"))

t_messtext.Append("M0901QTY: " + temp.ToString("#####") + "~")

End If

If Not IsDBNull(myrow("ZAWET")) Then

temp = CDec(myrow("ZAWET"))

t_messtext.Append("M1001WEIGHT " + temp.ToString("######0.0") + "~")

End If

If Not IsDBNull(myrow("ZALOC")) Then t_messtext.Append("M1101LOCATION : " +
myrow("ZALOC") + "~")

t_messtext.Append("M1501ITEM ACCEPTED" + "~")

messtext = t_messtext.ToString

Case "2"

vOPT = "OPT2" 'DUPLICATED ITEM

messtext = "C1515~M1501DUPLICATED ITEM~B~"

Case "3" 'Transaction not accepted IN DATABASE

vOPT = "OPT2"

messtext = "C1515~M1501DATABASE ERROR~B~"

Case "4" 'Duplicated Barcode

vOPT = "OPT2"

messtext = "C1515~M1501DUPLICATE BARCODE~B~"

End Select

End If

Catch e As Exception

messtext = "ERROR" + e.Message.ToString

End Try

oODBCConnection.Close()

End Function