SQL Query in Visual Basic connection

• ksjoecho

  Valued Member

  Points: 66

  More actions

  December 16, 2010 at 10:14 pm

  #242528

  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.

• ksjoecho

  Valued Member

  Points: 66

  More actions

  December 16, 2010 at 11:13 pm

  #1263900

  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!")