December 16, 2010 at 10:14 pm
Hi,
I would like to know how I can pass date/time with Dim custCMD As OleDbCommand in visual basic.
somehow, it doesn't take the date/time from the SQL query.
Here's a snippet:
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
"Data Source=192.168.1.3;Integrated Security=SSPI;Initial Catalog=MyDB")
Dim custCMD As OleDbCommand = New OleDbCommand("SELECT scd.OrderNumber, od.RxNumber, scd.UnitNumber, scd.CellNumber, scd.OrderDetailNumber, scd.ContainerNumber, scd.QuantityToPick, scd.QuantityPicked, scd.VialWeight, scd.PickedDateTime FROM ScriptContainerData scd with (nolock) JOIN OrderDetails od with (nolock) on scd.ordernumber = od.ordernumber WHERE scd.OrderDetailNumber = od.OrderDetailNumber AND scd.UnitNumber = " & NewUnitNumber & " AND scd.CellNumber = " & NewCellNumber & " AND scd.PickedDateTime >= " & NewStartPickedDate & "", nwindConn)
It returns all data instead of the specified date data.
any help would be appreciated.
Thanks in advance.
December 16, 2010 at 11:13 pm
Here's almost entire codes below.
Public Function ReturnDate(ByVal UnitNum As Integer, ByVal CellNum As Integer, ByVal StartDate As Date, ByVal EndDate As Date) As Date
'Dim NewStartPickedDate, NewEndPickedDate As Date
Dim NewEndPickedDate As Date
Dim NewStartPickedDate As Date = "11/15/2010 00:00:00"
Dim NewUnitNumber, NewCellNumber As Integer
MessageBox.Show(StartDate)
'NewStartPickedDate = StartDate
'Set NewStartPickedDate = '2010/11/15 00:00:00'
NewEndPickedDate = EndDate
NewUnitNumber = UnitNum
NewCellNumber = CellNum
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _
"Data Source=192.168.1.3;Integrated Security=SSPI;Initial Catalog=MyDB")
Dim custCMD As OleDbCommand = New OleDbCommand("SELECT scd.OrderNumber, od.RxNumber, scd.UnitNumber, scd.CellNumber, scd.OrderDetailNumber, scd.ContainerNumber, scd.QuantityToPick, scd.QuantityPicked, scd.VialWeight, scd.PickedDateTime FROM ScriptContainerData scd with (nolock) JOIN OrderDetails od with (nolock) on scd.ordernumber = od.ordernumber WHERE scd.OrderDetailNumber = od.OrderDetailNumber AND scd.UnitNumber = " & NewUnitNumber & " AND scd.CellNumber = " & NewCellNumber & " AND scd.PickedDateTime >= " & NewStartPickedDate & "", nwindConn)
nwindConn.Open()
Dim custReader As OleDbDataReader = custCMD.ExecuteReader()
My.Computer.FileSystem.WriteAllText("C:\Temp\puckweights.txt", _
"OrderNumber" & vbTab & "RxNumber" & vbTab & "UnitNumber" & vbTab & vbTab & "CellNumber" & vbTab & vbTab & "OrderDetailNumber" & vbTab & "ContainerNumber" & vbTab & vbTab & "QuantityToPick" & vbTab & vbTab & "QuantityPicked" & vbTab & "VialWeight" & vbTab & "PickedDateTime" & vbCrLf, True)
Dim fNextResult As Boolean = True
Do Until Not fNextResult
'Console.WriteLine(vbTab & myReader.GetName(0) & vbTab & myReader.GetName(1))
Do While custReader.Read()
My.Computer.FileSystem.WriteAllText("C:\Temp\puckweights.txt", _
custReader.GetValue(0) & vbTab & custReader.GetValue(1) & vbTab & custReader.GetValue(2) & vbTab & vbTab & vbTab & custReader.GetValue(3) & vbTab & vbTab & vbTab & custReader.GetValue(4) & vbTab & vbTab & vbTab & custReader.GetValue(5) & vbTab & vbTab & vbTab & vbTab & custReader.GetValue(6) & vbTab & vbTab & custReader.GetValue(7) & vbTab & vbTab & custReader.GetValue(8) & vbTab & vbTab & custReader.GetValue(9) & vbCrLf, True)
Loop
'Console.WriteLine(vbTab & myReader.GetInt32(0) & vbTab & myReader.GetString(1))
fNextResult = custReader.NextResult()
Loop
'fNextResult = custReader.NextResult()
'Loop
custReader.Close()
nwindConn.Close()
MessageBox.Show("It is done!")
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply