查询某一期间内的项目

Question

有一个表格显示了药品的类型和每种药品的序列号。有一列表示从该项目接收的数量，另一列表示从该项目花费的数量。有一列日期和最后一个要删除的布尔值，并通过不设置true来隐藏元素

数据库访问2010代码vb.net。

表: MEDICALCOME_TB

列MEDICALCOME_CODE :物料编码MEDICALCOME_SERIAL :公司序列MEDICALCOME_NAME :物料名称MEDICALCOME_COM :入库数量MEDICALCOME_LEAVE :出库数量MEDICALCOME_DATE :日期MEDICALCOME_CHECK :通过删除和存档检查布尔值

我想计算两个日期之间的时间之和DateTimePicker -总的传入和传出-以及传入和传出之间的差值

我使用以下代码在两个不同的日期段之间进行搜索和分组。未显示任何内容

Dim A = Format(DATETIM_START.Value, "yyyy/dd/MM")

Dim B = Format(DATETIM_END.Value, "yyyy/dd/MM")

Dim DataAdapter As New OleDbDataAdapter("Select MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL, Sum(MEDICALCOME_TB.MEDICALCOME_COM) As SumOfMEDICALCOME_COM, Sum(MEDICALCOME_TB.MEDICALCOME_LEAVE) As SumOfMEDICALCOME_LEAVE
From MEDICALCOME_TB
Where (((MEDICALCOME_TB.MEDICALCOME_CHECK) = True) And ((MEDICALCOME_TB.MEDICALCOME_DATE)between #" & B & "# And #" & A & "#))
Group By MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL ;", con)

Answer 1

将查询参数化并将日期作为date对象传递

Dim DataAdapter As New OleDbDataAdapter("Select MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL, Sum(MEDICALCOME_TB.MEDICALCOME_COM) As SumOfMEDICALCOME_COM, Sum(MEDICALCOME_TB.MEDICALCOME_LEAVE) As SumOfMEDICALCOME_LEAVE From MEDICALCOME_TB Where (((MEDICALCOME_TB.MEDICALCOME_CHECK) = True) And ((MEDICALCOME_TB.MEDICALCOME_DATE) between ? AND ?)) Group By MEDICALCOME_TB.MEDICALCOME_CODE, MEDICALCOME_TB.MEDICALCOME_SERIAL ;", con)

DataAdapter.SelectCommand.Parameters.Add("@StartDate", OleDbType.DBDate).Value = DATETIM_Start.Value)
DataAdapter.SelectCommand.Parameters.Add("@EndDate", OleDbType.DBDate).Value = DATETIM_END.Value)

这是从记忆中得出的，但应该足够接近，可以让您继续进行。查看此处了解更多信息：https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparameter?redirectedfrom=MSDN&view=netframework-4.7.2