将varchar转换为数字时出错 [英] Error converting varchar to numeric

问题描述

非常感谢任何帮助。我有一个日期字段，当运行查询时，它会在达到空值或空值时出错。我是VB的新手，也是Visual Studio中的应用程序。我希望有人能告诉我。我收到错误'myTempCmd = cmd.ExecuteScalar'



代码：



Case DataFields。 SettleDate.DisplayName





Dim SQLTimeout = 120

Dim myTempCon As SqlConnection

myTempCon = New SqlConnection（ConfigurationManager.ConnectionStrings（DT_LoanReport.My.MySettings.DT_ConnectionString）。ConnectionString）

myTempCon.Open（）



Dim myTempCmd As String =

myTempCmd + =SELECT trades.del_date

myTempCmd + =FROM gen as gen WITH（NOLOCK）

myTempCmd + =LEFT外联接交易（NOLOCK）在gen.trade_no = trades.trade_no

myTempCmd + =WHERE

myTempCmd + =gen.trade_no ='+ myDataR eader（trade_no）。ToString.Trim +'



Dim cmd As New SqlCommand（）

cmd.Connection = myTempCon

cmd.CommandText = myTempCmd

'Dim myTempValue As String =

cmd.CommandTimeout = SQLTimeout





试试

myTempCmd = cmd.ExecuteScalar





Catch ex As SqlException

MsgBox（Querry花了太长时间，请再试一次）



myTempCon.Close（ ）



_Value =

退出选择

结束尝试



myTempCon.Close（）



_Value = myTempCmd

Any help would be greatly appreciated. I have a date field that when the query is run, it errors out when it reaches a blank or null value. I am new to VB and the application which is in Visual Studio. I am hoping someone can advise me. I get the error at ' myTempCmd = cmd.ExecuteScalar '

The code:

Case DataFields.SettleDate.DisplayName


Dim SQLTimeout = 120
Dim myTempCon As SqlConnection
myTempCon = New SqlConnection(ConfigurationManager.ConnectionStrings("DT_LoanReport.My.MySettings.DT_ConnectionString").ConnectionString)
myTempCon.Open()

Dim myTempCmd As String = ""
myTempCmd += "SELECT trades.del_date"
myTempCmd += " FROM gen as gen WITH (NOLOCK)"
myTempCmd += " LEFT outer join trades WITH (NOLOCK) on gen.trade_no = trades.trade_no"
myTempCmd += " WHERE "
myTempCmd += " gen.trade_no = '" + myDataReader("trade_no").ToString.Trim + "'"

Dim cmd As New SqlCommand()
cmd.Connection = myTempCon
cmd.CommandText = myTempCmd
'Dim myTempValue As String = ""
cmd.CommandTimeout = SQLTimeout


Try
myTempCmd = cmd.ExecuteScalar


Catch ex As SqlException
MsgBox("The Querry took too long, please try again")

myTempCon.Close()

_Value = ""
Exit Select
End Try

myTempCon.Close()

_Value = myTempCmd

推荐答案

首先看看你的数据：具体是DataReader为trade_no返回什么，以及交易和 gen 表格持有 trade_no 列。



但最多可能是DateRear将一个非数字值作为字符串传递给你的SQL。



并帮自己一个忙：不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

So start by looking at your data: specifically what the DataReader is returning for "trade_no", and what the trades and gen tables are holding for their trade_no columns.

But the most likely is that the DateRear is pass a non-numeric value as a string to your SQL.

And do yourself a favour: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

这篇关于将varchar转换为数字时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！