Writing Data to AS/400 - Help Please

[bigfoot]

I am trying to write to an AS/400 file from VB.NET. I am getting errors when I try to do this.

My error is:
System.Data.Odbc.OdbcException: ERROR [HY000] [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0301 - Input host variable CUSNUM
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
at AS400Communication.Form1.cmdParameterInsert_Click(Object sender, EventArgs e) in C:\Source\@DotNet\AS400Communication\Form1.vb:line 426

Someone has done this before. Please help.
I created a small test project, and that does not work either. I am having trouble writing to the Decmal field. All of the text fields work great.


*********************************************************
This is my code test.
*********************************************************
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb

Dim myDataSet As New DataSet

'Works as long as you have a DSN set up
Private myConnection As New OdbcConnection("DSN=AS400;UID=myid;PWD=mypass&quot

Dim myCommand As New OdbcCommand

'This works great, but I'm having trouble with single quotes in the text fields
'myCommand.CommandText = "Insert Into GARYLIB.GARY01 (CUSNUM, CUSNAM, FIELD1, FIELD2) Values(123456, 'CUSNAM', '', 'FIELD2')"


'This is the part I want to get working.
myCommand.CommandText = "Insert Into GARYLIB.GARY01 (CUSNUM, CUSNAM, FIELD1, FIELD2) Values(?,?,?,?)"


'This is the command that .NET made when I told it to create the code for me - NO WORK
'myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("CUSNUM", System.Data.Odbc.OdbcType.Decimal, 0, System.Data.ParameterDirection.Input, False, CType(8, Byte), CType(0, Byte), "CUSNUM", System.Data.DataRowVersion.Current, Nothing))

'I tried this too - HELP
myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("CUSNUM", System.Data.Odbc.OdbcType.Decimal, 0, "CUSNUM&quot)


'THIS ALL SEEEMS TO WORK. It's the decimal field that stinks.
myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("CUSNAM", System.Data.Odbc.OdbcType.VarChar, 20, "CUSNAM&quot)
myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("FIELD1", System.Data.Odbc.OdbcType.VarChar, 10, "FIELD1&quot)
myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("FIELD2", System.Data.Odbc.OdbcType.VarChar, 10, "FIELD2&quot)

'ASSIGN INFO TO THE PARAMETERS
myCommand.Parameters("CUSNUM&quot.Value = 5555
myCommand.Parameters("CUSNAM&quot.Value = "Gary"
myCommand.Parameters("FIELD1&quot.Value = "GGGG"
myCommand.Parameters("FIELD2&quot.Value = "AAAA"

myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection

myConnection.Open()

Try
myCommand.ExecuteNonQuery()

Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try

myConnection.Close()


HERE IS THE DDS FOR THE FILE. NOTHING SPECIAL

Columns . . . : 1 80 Browse
SEU==>
FMT A* .....A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
*************** Beginning of data ************************************************************
0001.00 A***************************************************************** 030117
0002.00 A* 030117
0003.00 A* File Name: GARY01 031201
0004.00 A* File Attr: Physical MAXMBR(1) MAXRCD(*NOMAX) 030117
0005.00 A* File Desc: Gary's test file 031201
0006.00 A* 030117
0007.00 A*---------------------------------------------------------------- 030117
0008.00 A* 030117
0009.00 A R TESTREC 031201
0010.00 A CUSNUM 8S 0 COLHDG(' ' 'User' 'Number') 031201
0011.00 A CUSNAM 20A COLHDG(' ' 'User' 'Name') 031201
0012.00 A FIELD1 10A TEXT('Blank - Available') 031201
0013.00 A FIELD2 10A TEXT('Blank - Available') 031201
0014.00 A K CUSNUM 031209
****************** End of data ***************************************************************




I know some of you are doing this, and it works for you. I have been through the books on line, even got IBM on the phone. They were nice, but could not help me.

Thanks to anyone that can help.
If you need more info, I will be watching this thread. Please ask.

 

[bigfoot]

This seems to work:

myCommand.Parameters.Add(New System.Data.Odbc.OdbcParameter("CUSNUM", System.Data.Odbc.OdbcType.Double, 0, "CUSNUM&quot)

Himmmmmmm