deecree
asked on
SqlClient.SqlDataAdapter(strSQL, connection) - with a Date as a string :(
I have made a simple form with a Crystal Reports control embedded, with the simple simple code below to bind some data to it.
========================== ========== =
Dim daT1, daT2 As SqlClient.SqlDataAdapter
Dim LACARSDataSet As DataSet
Dim strSQL As String
Dim connection As New SqlConnection(DataDB.GetCo nnStr)
connection.Open()
LACARSDataSet = New DataSet
strSQL = "SELECT * FROM client WHERE ClientID = '" & intClientID & "'"
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "client")
strSQL = "SELECT * FROM driver"
daT1 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT1.Fill(LACARSDataSet, "driver")
If AllThisMonth Then
'strSQL = "SELECT * FROM job WHERE PickupDateTime > DateAdd(Month, -2, " & DateTime.Now().ToShortDate String() & ")"
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(- 2).ToShort DateString () & "'"
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "job")
Else
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & dtFrom.ToShortDateString() & "' AND PickupDateTime < '" & dtTo.ToShortDateString() & "'"
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "job")
End If
Dim rpt As New ClientsReport
rpt.SetDataSource(LACARSDa taSet)
CrystalReportViewer1.Repor tSource = rpt
connection.Close()
========================== ========== =
If you look in the " If AllThisMonth Then" if statement you will see that with this simple code I am trying to display only a specific date range in the Crystal Report.
However...
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(- 2).ToShort DateString () & "'"
Creating an SQL string like this would work well in something that queries a DB directly, BUT
with the SqlDataAdapter, I have compile errors with the date string if its not in .ToShortDateString and when it is it plain just doesnt have an effect.
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "job")
I can only assume that SqlClient.SqlDataAdapter(s trSQL, connection) does not know who to convert a sstring of a Date into an actual date as I would be able to normally.
I've explained this poorly, but I've no real understanding of why the DataSet wont fill the data with the correct time-relevent records and why with this simple code (i am new to CR and merely want this one report to show with no hassle) the date cant be read as a string.
I hope someone understands my ramblings and can help. Thanks.
==========================
Dim daT1, daT2 As SqlClient.SqlDataAdapter
Dim LACARSDataSet As DataSet
Dim strSQL As String
Dim connection As New SqlConnection(DataDB.GetCo
connection.Open()
LACARSDataSet = New DataSet
strSQL = "SELECT * FROM client WHERE ClientID = '" & intClientID & "'"
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "client")
strSQL = "SELECT * FROM driver"
daT1 = New SqlClient.SqlDataAdapter(s
daT1.Fill(LACARSDataSet, "driver")
If AllThisMonth Then
'strSQL = "SELECT * FROM job WHERE PickupDateTime > DateAdd(Month, -2, " & DateTime.Now().ToShortDate
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(-
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "job")
Else
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & dtFrom.ToShortDateString()
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "job")
End If
Dim rpt As New ClientsReport
rpt.SetDataSource(LACARSDa
CrystalReportViewer1.Repor
connection.Close()
==========================
If you look in the " If AllThisMonth Then" if statement you will see that with this simple code I am trying to display only a specific date range in the Crystal Report.
However...
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(-
Creating an SQL string like this would work well in something that queries a DB directly, BUT
with the SqlDataAdapter, I have compile errors with the date string if its not in .ToShortDateString and when it is it plain just doesnt have an effect.
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "job")
I can only assume that SqlClient.SqlDataAdapter(s
I've explained this poorly, but I've no real understanding of why the DataSet wont fill the data with the correct time-relevent records and why with this simple code (i am new to CR and merely want this one report to show with no hassle) the date cant be read as a string.
I hope someone understands my ramblings and can help. Thanks.
ASKER
System.Data.SqlClient.SqlE xception was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="Conversion failed when converting datetime from character string."
Number=241
Procedure=""
Server="localhost"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj)
at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlD ataReader. HasMoreRow s()
at System.Data.SqlClient.SqlD ataReader. ReadIntern al(Boolean setTimeout)
at System.Data.SqlClient.SqlD ataReader. Read()
at System.Data.Common.DataAda pter.FillL oadDataRow (SchemaMap ping mapping)
at System.Data.Common.DataAda pter.FillF romReader( DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAda pter.Fill( DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataA dapter.Fil lInternal( DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataA dapter.Fil l(DataSet dataSet, String srcTable)
at LACARS.ReportsClients.DoRe port() in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\LACARS\LACAR S\ReportsC lients.vb: line 48
at LACARS.ReportsClients.btnG o_Click(Ob ject sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\LACARS\LACAR S\ReportsC lients.vb: line 116
at System.Windows.Forms.Contr ol.OnClick (EventArgs e)
at System.Windows.Forms.Butto n.OnClick( EventArgs e)
at System.Windows.Forms.Butto n.OnMouseU p(MouseEve ntArgs mevent)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Butto nBase.WndP roc(Messag e& m)
at System.Windows.Forms.Butto n.WndProc( Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Run (Applicati onContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at LACARS.My.MyApplication.Ma in(String[ ] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain.nExecuteA ssembly(As sembly assembly, String[] args)
at System.Runtime.Hosting.Man ifestRunne r.Run(Bool ean checkAptModel)
at System.Runtime.Hosting.Man ifestRunne r.ExecuteA sAssembly( )
at System.Runtime.Hosting.App licationAc tivator.Cr eateInstan ce(Activat ionContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.App licationAc tivator.Cr eateInstan ce(Activat ionContext activationContext)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssemblyDeb ugInZone()
at System.Threading.ThreadHel per.Thread Start_Cont ext(Object state)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
==========================
This gives me this above exception at line;
daT2.Fill(LACARSDataSet, "job")
in this If statement...
If AllThisMonth Then
'strSQL = "SELECT * FROM job WHERE PickupDateTime > DateAdd(Month, -2, " & DateTime.Now().ToShortDate String() & ")"
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(- 2).ToStrin g("MMM d yyyy") & "'"
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "job")
Else
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & dtFrom.ToString("MMM d yyyy") & "' AND PickupDateTime < '" & dtTo.ToString("MMM d yyyy") & "'"
daT2 = New SqlClient.SqlDataAdapter(s trSQL, connection)
daT2.Fill(LACARSDataSet, "job")
End If
====================
This is the exception i was alluding at in the first post.
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="Conversion failed when converting datetime from character string."
Number=241
Procedure=""
Server="localhost"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlD
at System.Data.SqlClient.SqlD
at System.Data.SqlClient.SqlD
at System.Data.Common.DataAda
at System.Data.Common.DataAda
at System.Data.Common.DataAda
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at System.Data.Common.DbDataA
at LACARS.ReportsClients.DoRe
at LACARS.ReportsClients.btnG
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Butto
at System.Windows.Forms.Butto
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at LACARS.My.MyApplication.Ma
at System.AppDomain.nExecuteA
at System.Runtime.Hosting.Man
at System.Runtime.Hosting.Man
at System.Runtime.Hosting.App
at System.Runtime.Hosting.App
at Microsoft.VisualStudio.Hos
at System.Threading.ThreadHel
at System.Threading.Execution
at System.Threading.ThreadHel
==========================
This gives me this above exception at line;
daT2.Fill(LACARSDataSet, "job")
in this If statement...
If AllThisMonth Then
'strSQL = "SELECT * FROM job WHERE PickupDateTime > DateAdd(Month, -2, " & DateTime.Now().ToShortDate
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(-
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "job")
Else
strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & dtFrom.ToString("MMM d yyyy") & "' AND PickupDateTime < '" & dtTo.ToString("MMM d yyyy") & "'"
daT2 = New SqlClient.SqlDataAdapter(s
daT2.Fill(LACARSDataSet, "job")
End If
====================
This is the exception i was alluding at in the first post.
Humph... that should have worked.
Add a break (or a debug.print) to display the final value for strSQL to make sure it appears formatted correctly.
Add a break (or a debug.print) to display the final value for strSQL to make sure it appears formatted correctly.
ASKER
SELECT * FROM job WHERE PickupDateTime > 'jul 29 2006' AND PickupDateTime < 'oct 29 2006'
I believe this is correct. I have been working on this on and off for about 2 weeks now and growing extremely bitter. Please help.
I believe this is correct. I have been working on this on and off for about 2 weeks now and growing extremely bitter. Please help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BETWEEN is better to use than <
.ToString("MMM d yyyy")