如何从数据库中获取数据<日期 [英] How do I get data from database where < date
问题描述
我正在使用SQL Server 2012和vb.net 2015创建一个查询,以检索少于一个日期的信息。当我运行查询时,我收到错误从字符串转换日期和/或时间时转换失败
我的错误是什么?
提前致谢。
我的尝试:
私人 Sub GetOpenBalance()
Dim TotalDr,TotalCr As 新 Double
Dim DatFrom 正如 新 日期
Dim Bal 作为 新 DataTable
conn.Open()
DatFrom = CDate ( Me .MaskedTextBox1.Text)
DatFrom =格式(DatFrom, yyyy-MM-dd)
SQLString = 选择Sum(Dr_Acc_Value)作为TotDR, Sum(Cr_Acc_Value)As TotCr来自JV_QRY其中AccountNumber ='& 我 .TextBox1.Text& '和JV_Date<'& DatFrom& '和JV_Status = 1和JV_Del_Status = 0 Group By AccountNumber
Dim Adpt As 新 SqlDataAdapter(SQLString,conn)
Adpt.Fill(Bal)
conn.Close()
对于 每个行 As DataRow In Bal.Rows
TotalDr = row( 0 )
TotalCr = row( 1 )
下一步
OP_Balance = TotalDr - TotalCr
结束 Sub
您需要使用参数而不是将sql语句连接在一起。以这种方式编写sql语句意味着我可以破解你的数据库并删除和/或窃取你的所有数据。它被称为Sql Injection,是一个花时间研究的好主题。
所以,做这样的事情:
SQLString = 选择Sum(Dr_Acc_Value)作为TotDR,Sum(Cr_Acc_Value)作为TotCr来自JV_QRY Where AccountNumber = @accountNumber和JV_Date< @jvDate和JV_Status = 1和JV_Del_Status = 0 Group By AccountNumber
' 然后以这种方式添加参数
da.UpdateCommand.Parameters.AddWithValue( @accoutnNumber,txtAccountNumber.Text)
da.UpdateCommand.Parameters.AddWithValue( @jvDate,jvDate)
...
此外,请确保你实际上通过了日期。该错误表示它无法转换您传递给日期的任何内容。
最后,为控件使用有意义的名称,这样你才能知道它们是什么。从未使用过textbox1。总是使用类似txtAccountNumber等的东西。
除了上面的RyanDev解决方案之外,Format
返回一个字符串,所以你应该收到一个错误
DatFrom =格式(DatFrom,yyyy-MM-dd)因为DatFrom是一个日期。
你可以使用datFrom.ToString(yyyy-MM-dd)将日期格式化为字符串。
您还应该考虑使用Date.TryParse()
而不是CDate
(尽管在这种情况下,因为您使用过那么MaskedTextBox可能没关系)。
以下是我修复Sub的方法 - 它显示了在SqlDataAdaptor上使用参数化查询的另一种方法
私有 Sub GetOpenBalance()
Dim totalDr,totalCr As 新 Double
Dim datFrom As 新 日期
Dim bal As 新 DataTable
conn.Open()
如果( Date .TryParse(MaskedTextBox1.Text,datFrom))然后
SQLString = 选择Sum(Dr_Acc_Value)作为TotDR,Sum(Cr_Acc_Value)As TotCr来自JV_QRY其中AccountNumber = @ tb1和JV_Date< @DatFrom和JV_Status = 1和JV_Del_Status = 0 Group By AccountNumber
Dim cmd 作为 新 SqlCommand(SQLString,conn)
cmd.Parameters.AddWithValue( @ tb1,TextBox1.Text)
cmd.Parameters.AddWithValue( @ DatFrom,datFrom.ToString( yyyy-MM -dd))
Dim Adpt As 新 SqlDataAdapter()
Adpt.SelectCommand = cmd
Adpt.Fill(bal)
conn.Close ()
对于 每个行 As DataRow 在 bal.Rows
totalDr = CDbl (行( 0 ))
totalCr = CDbl (行( 1 ))
下一步
OP_Balance = totalDr - totalCr
结束 如果
结束 Sub
Hi,
I am creating an query using SQL server 2012 and vb.net 2015 to retrieve information for less than a date . When i am running the query i got an error Of "Conversion failed when converting date and/or time from character string"
What is my mistake?
Thanks in advance.
What I have tried:
Private Sub GetOpenBalance()
Dim TotalDr, TotalCr As New Double
Dim DatFrom As New Date
Dim Bal As New DataTable
conn.Open()
DatFrom = CDate(Me.MaskedTextBox1.Text)
DatFrom = Format(DatFrom, "yyyy-MM-dd")
SQLString = "Select Sum(Dr_Acc_Value) As TotDR,Sum(Cr_Acc_Value) As TotCr From JV_QRY Where AccountNumber = '" & Me.TextBox1.Text & "' And JV_Date < '" & DatFrom & "' And JV_Status = 1 And JV_Del_Status = 0 Group By AccountNumber"
Dim Adpt As New SqlDataAdapter(SQLString, conn)
Adpt.Fill(Bal)
conn.Close()
For Each row As DataRow In Bal.Rows
TotalDr = row(0)
TotalCr = row(1)
Next
OP_Balance = TotalDr - TotalCr
End Sub
You need to use parameters instead of concatenating your sql statement together. Writing your sql statement the way it is means I can hack your db and delete and/or steal all your data. It's called Sql Injection and is a good topic to spend some time researching.
So, instead do something like this:
SQLString = "Select Sum(Dr_Acc_Value) As TotDR,Sum(Cr_Acc_Value) As TotCr From JV_QRY Where AccountNumber = @accountNumber And JV_Date < @jvDate And JV_Status = 1 And JV_Del_Status = 0 Group By AccountNumber" ' Then add the parameters this way da.UpdateCommand.Parameters.AddWithValue("@accoutnNumber", txtAccountNumber.Text) da.UpdateCommand.Parameters.AddWithValue("@jvDate", jvDate) ...
Also, make sure you're actually passing a date. The error says that it couldn't convert whatever you passed into a date.
Lastly, use meaningful names for your controls so you know what they are. Never used textbox1. Always use something like txtAccountNumber, etc.
Further to RyanDev's solution above,Format
returns a string so you should be getting an error with
DatFrom = Format(DatFrom, "yyyy-MM-dd")because DatFrom is a date.
You can usedatFrom.ToString("yyyy-MM-dd")to format the date as a string.
You should also consider usingDate.TryParse()
rather thanCDate
(although in this case as you have used a MaskedTextBox then it probably doesn't matter).
Here is how I fixed your Sub - it shows an alternative way of using parameterised queries on a SqlDataAdaptor
Private Sub GetOpenBalance() Dim totalDr, totalCr As New Double Dim datFrom As New Date Dim bal As New DataTable conn.Open() If (Date.TryParse(MaskedTextBox1.Text, datFrom)) Then SQLString = "Select Sum(Dr_Acc_Value) As TotDR,Sum(Cr_Acc_Value) As TotCr From JV_QRY Where AccountNumber = @tb1 And JV_Date < @DatFrom And JV_Status = 1 And JV_Del_Status = 0 Group By AccountNumber" Dim cmd As New SqlCommand(SQLString, conn) cmd.Parameters.AddWithValue("@tb1", TextBox1.Text) cmd.Parameters.AddWithValue("@DatFrom", datFrom.ToString("yyyy-MM-dd")) Dim Adpt As New SqlDataAdapter() Adpt.SelectCommand = cmd Adpt.Fill(bal) conn.Close() For Each row As DataRow In bal.Rows totalDr = CDbl(row(0)) totalCr = CDbl(row(1)) Next OP_Balance = totalDr - totalCr End If End Sub
这篇关于如何从数据库中获取数据<日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!