VB.NET - Inserting/Deleting via ODBC Connection

Question

I'm connecting to an AS/400 by an ODBC connection. I'm getting wacky errors when I try to insert and delete from the file. First, the format I've found for constructing a query stirng was like this:

"DELETE FROM <library>.<filename> WHERE <field> = <value>"

So, based on that, I wrote this:

"DELETE FROM RM#AVLIB.AV90909JWB WHERE MBR_NUM = " & mbr_num

It threw an error about not liking the DELETE, oddly enough, but I got it to go away if I removed the library altogether:

"DELETE FROM AV90909JWB WHERE MBR_NUM = " & mbr_num

I'm guessing it just didn't like the # symbol and worked without the library because my default library is the same one I'm using here. But now I'm getting an error in my INSERT that says:

ERROR [22003][Micro Focus][RUMBA Data Access][S1023934]Numeric value out of range. SQLCODE = -420

After running through all the values I'm passing, I didn't see any values that were longer than the field length so I'm not sure where this is coming from.

Here's my code as it sits now:

delQry = "DELETE FROM AV90301JWB WHERE MBR_CD = '" & MBR_CD & "' AND LOC_CD = '" & LOC_CD & "' AND PRP_ITM = '" & PRP_ITM & "'"
pushQry = "INSERT INTO AV90301JWB ( " & _
    "MBR_NUM, " & _
    "LOC_CD, " & _
    "AVBLD_CLMT, " & _
    "ADDRESS1, " & _
    "ADDRESS2, " & _
    "CITY, " & _
    "STATE, " & _
    "ZIPCODE, " & _
    "AVBLD_DOS, " & _
    "CNST_QLTY, " & _
    "SEISMIC, " & _
    "WIND, " & _
    "AVBLD_DSC, " & _
    "AVBLD_DSC1, " & _
    "AVBLD_DSC2, " & _
    "AVBLD_DSC3, " & _
    "MISC_ADJ, " & _
    "SEC_ID, " & _
    "AVCOS_RC, " & _
    "YR_BUILT, " & _
    "NBR_STORY, " & _
    "SQR_FT, " & _
    "SUBCLASS, " & _
    "OCC_CD1, " & _
    "OCC_DSC1, " & _
    "OCC_PCT1, " & _
    "STORY_HT1, " & _
    "OCC_CD2, " & _
    "OCC_DSC2, " & _
    "OCC_PCT2, " & _
    "STORY_HT2, " & _
    "OCC_CD3, " & _
    "OCC_DSC3, " & _
    "OCC_PCT3, " & _
    "STORY_HT3, " & _
    "OCC_CD4, " & _
    "OCC_DSC4, " & _
    "OCC_PCT4, " & _
    "STORY_HT4, " & _
    "OCC_CD5, " & _
    "OCC_DSC5, " & _
    "OCC_PCT5, " & _
    "STORY_HT5, " & _
    "HEAT_SYS, " & _
    "COOL_SYS, " & _
    "PWALL_EXT, " & _
    "ROOF_MAT, " & _
    "SPRINKLER, " & _
    "MANL_FIRE, " & _
    "AUTO_FIRE, " & _
    "CNST_PCT1, " & _
    "CNST_PCT2, " & _
    "CNST_PCT3, " & _
    "CNST_PCT4, " & _
    "CNST_PCT5) " & _
  "VALUES (" & _
    "'" & MBR_NUM & "', " & _
    "'" & LOC_CD & "', " & _
    "'" & AVBLD_CLMT & "', " & _
    "'" & ADDRESS1 & "', " & _
    "'" & ADDRESS2 & "', " & _
    "'" & CITY & "', " & _
    "'" & STATE & "', " & _
    "'" & ZIPCODE & "', " & _
    "'" & AVBLD_DOS.ToShortDateString() & "', " & _
    "'" & CNST_QLTY & "', " & _
    "'" & SEISMIC & "', " & _
    "'" & WIND & "', " & _
    "'" & AVBLD_DSC & "', " & _
    "'" & AVBLD_DSC1 & "', " & _
    "'" & AVBLD_DSC2 & "', " & _
    "'" & AVBLD_DSC3 & "', " & _
    "'" & MISC_ADJ & "', " & _
    "'" & SEC_ID & "', " & _
    "" & Math.Round(AVCOS_RC, 2, MidpointRounding.AwayFromZero) & ", " & _
    "'" & YR_BUILT & "', " & _
    "'" & NBR_STORY & "', " & _
    "'" & SQR_FT & "', " & _
    "'" & SUBCLASS & "', " & _
    "'" & OCC_CD1 & "', " & _
    "'" & OCC_DSC1 & "', " & _
    "'" & OCC_PCT1 & "', " & _
    "'" & STORY_HT1 & "', " & _
    "'" & OCC_CD2 & "', " & _
    "'" & OCC_DSC2 & "', " & _
    "'" & OCC_PCT2 & "', " & _
    "'" & STORY_HT2 & "', " & _
    "'" & OCC_CD3 & "', " & _
    "'" & OCC_DSC3 & "', " & _
    "'" & OCC_PCT3 & "', " & _
    "'" & STORY_HT3 & "', " & _
    "'" & OCC_CD4 & "', " & _
    "'" & OCC_DSC4 & "', " & _
    "'" & OCC_PCT4 & "', " & _
    "'" & STORY_HT4 & "', " & _
    "'" & OCC_CD5 & "', " & _
    "'" & OCC_DSC5 & "', " & _
    "'" & OCC_PCT5 & "', " & _
    "'" & STORY_HT5 & "', " & _
    "'" & HEAT_SYS & "', " & _
    "'" & COOL_SYS & "', " & _
    "'" & PWALL_EXT & "', " & _
    "'" & ROOF_MAT & "', " & _
    "'" & SPRINKLER & "', " & _
    "'" & MANL_FIRE & "', " & _
    "'" & AUTO_FIRE & "', " & _
    "'" & CNST_PCT1 & "', " & _
    "'" & CNST_PCT2 & "', " & _
    "'" & CNST_PCT3 & "', " & _
    "'" & CNST_PCT4 & "', " & _
    "'" & CNST_PCT5 & "')"

Dim connectionString As String = ConfigurationManager.AppSettings("iSeriesConnString")
Dim insCommand As New OdbcCommand(pushQry)
Dim delCommand As New OdbcCommand(delQry)
Dim da As New OdbcDataAdapter

Using myConn As New OdbcConnection(connectionString)
    insCommand.Connection = myConn
    delCommand.Connection = myConn
    myConn.Open()
    da.InsertCommand = insCommand
    da.DeleteCommand = delCommand
    da.DeleteCommand.ExecuteNonQuery()
    da.InsertCommand.ExecuteNonQuery() 
End Using

Does anyone see something I'm missing or have any ideas about how to find my issue?

Thanks!

Avoid building a dynamic SQL statement. This is the way that SQL injection attacks happen. Use parameterized queries instead. Also, be sure to separate your individual statements with semi-colons. — Martin Soles, Oct 06 '15 at 16:36
What do you mean "be sure to separate your individual statements with semi-colons"? — TheIronCheek, Oct 06 '15 at 18:28
I misread and thought you were performing both the delete and insert statements in the same query statement. I know that the AS/400 complains if you don't put the end-of-statement semi-colon in between multiple statements in the same query string. — Martin Soles, Oct 06 '15 at 19:10

score 2 · Accepted Answer · edited May 23 '17 at 10:29

2

The standard ANSI SQL way of escaping invalid characters in an identifier or to escape an identifier which conflicts with a reserved keyword is to put it in double quotes:

DELETE FROM "RM#AVLIB".AV90909JWB WHERE ...

Obviously one of the values you are inserting or you are using in the where clause exceeds the range defined for that column. E.g. if a column has been defined as NUMERIC(2), you cannot insert 100.

Also I strongly urge you to use command parameters instead of string concatenation: See https://stackoverflow.com/a/2092851/880990

edited May 23 '17 at 10:29

Community

1
1

answered Oct 06 '15 at 16:36

Olivier Jacot-Descombes

104,806
13
138
188

Thanks for the help. I was looking for something like your example - inserting `100` into a `NUMERIC(2)` - but I was actually inserting a blank `String`into a `NUMERIC(2)` so I had some trouble finding it... Your answer helped me find the solution so I'm marking it as such. – TheIronCheek Oct 07 '15 at 14:04
1

If you have a `NOT NULL` constraint on a column a value **must** be inserted; either manually or the column can have a default value defined with `DEFAULT 123`. `CREATE TABLE mytable (mycolumn NUMERIC(5) DEFAULT -1 NOT NULL, ... )` – Olivier Jacot-Descombes Oct 07 '15 at 16:20
1

And of course you cannot insert a string (empty or not) into a numeric column; however, you can insert `NULL` into a column if either the column has not a NOT-NULL-constraint or has a DEFAULT-value defined. – Olivier Jacot-Descombes Oct 07 '15 at 16:35

VB.NET - Inserting/Deleting via ODBC Connection

1 Answers1