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!
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