VB.NET - 通过ODBC连接插入/删除
我通过ODBC连接连接到AS/400。当我尝试从文件中插入和删除时,出现了古怪的错误。首先,我发现构造查询stirng格式是这样的:VB.NET - 通过ODBC连接插入/删除
"DELETE FROM <library>.<filename> WHERE <field> = <value>"
因此,基于这一点,我写了这个:
"DELETE FROM RM#AVLIB.AV90909JWB WHERE MBR_NUM = " & mbr_num
它扔约不顺心删除错误,奇怪的是,但我得到了它的消失,如果我完全去除库:
"DELETE FROM AV90909JWB WHERE MBR_NUM = " & mbr_num
我猜它只是不喜欢的#
符号,没有图书馆,因为我的默认librar工作y和我在这里使用的是一样的。但现在我得到我的INSERT
一个错误,说:
ERROR [22003] [微聚焦] [RUMBA数据存取] [S1023934]数值超出范围。 SQLCODE = -420
经过我传递的所有值后,我没有看到任何比字段长度更长的值,所以我不确定这是从哪里来的。
这里是我的代码,因为它位于现在:
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
有谁看到我丢失的东西还是有关于如何找到我的问题的任何想法?
谢谢!
在标识符逸出无效字符或逃脱其冲突使用一个保留关键字是把它放在双引号标识符的标准ANSI SQL方式:
DELETE FROM "RM#AVLIB".AV90909JWB WHERE ...
显然的值之一您正在插入或者您在where子句中使用的内容超出了为该列定义的范围。例如。如果列已定义为NUMERIC(2)
,则不能插入100
。
此外,我强烈建议你,而不是使用字符串连接命令参数:见https://*.com/a/2092851/880990
感谢您的帮助。我正在寻找类似你的例子的东西 - 在'NUMERIC(2)'中插入'100' - 但实际上我在'NUMERIC(2)'中插入了一个空白的'String',所以我发现它有些麻烦......你的答案帮助我找到解决方案,所以我将它标记为这样。 – TheIronCheek
如果对列有'NOT NULL'约束,则必须插入值**;手动或列可以有一个默认值定义'DEFAULT 123'。'CREATE TABLE mytable(mycolumn NUMERIC(5)DEFAULT -1 NOT NULL,...)' –
当然,你不能插入一个字符串(空或不是)到一个数字列;但是,如果列中没有NOT-NULL约束或定义了DEFAULT值,则可以将NULL插入到列中。 –
避免建立动态SQL语句。这是SQL注入攻击发生的方式。改用参数化查询。此外,请务必用分号分隔您的个人陈述。 –
你是什么意思“确保用分号分隔你的个人陈述”? – TheIronCheek
我误解了,并认为你在同一个查询语句中同时执行了删除和插入语句。我知道,如果您不在相同查询字符串中的多个语句之间放置语句末尾分号,AS/400会发出抱怨。 –