Solved

SqlClient.SqlDataAdapter(strSQL, connection) - with a Date as a string :(

Posted on 2006-10-28
9
636 Views
Last Modified: 2008-02-01
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.GetConnStr)
        connection.Open()

        LACARSDataSet = New DataSet


        strSQL = "SELECT * FROM client WHERE ClientID = '" & intClientID & "'"
        daT2 = New SqlClient.SqlDataAdapter(strSQL, connection)
        daT2.Fill(LACARSDataSet, "client")


        strSQL = "SELECT * FROM driver"
        daT1 = New SqlClient.SqlDataAdapter(strSQL, connection)
        daT1.Fill(LACARSDataSet, "driver")

        If AllThisMonth Then
            'strSQL = "SELECT * FROM job WHERE PickupDateTime > DateAdd(Month, -2, " & DateTime.Now().ToShortDateString() & ")"
            strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(-2).ToShortDateString() & "'"
            daT2 = New SqlClient.SqlDataAdapter(strSQL, connection)
            daT2.Fill(LACARSDataSet, "job")
        Else
            strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & dtFrom.ToShortDateString() & "' AND PickupDateTime < '" & dtTo.ToShortDateString() & "'"
            daT2 = New SqlClient.SqlDataAdapter(strSQL, connection)
            daT2.Fill(LACARSDataSet, "job")
        End If



        Dim rpt As New ClientsReport
        rpt.SetDataSource(LACARSDataSet)
        CrystalReportViewer1.ReportSource = 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).ToShortDateString() & "'"

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(strSQL, connection)
            daT2.Fill(LACARSDataSet, "job")

I can only assume that SqlClient.SqlDataAdapter(strSQL, 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.
0
Comment
Question by:deecree
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
9 Comments
 
LVL 41

Expert Comment

by:graye
ID: 17829393
The DateTime format for SQL Server wants the "medium" date format

.ToString("MMM d yyyy")

0
 

Author Comment

by:deecree
ID: 17829538
System.Data.SqlClient.SqlException 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.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.HasMoreRows()
       at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
       at System.Data.SqlClient.SqlDataReader.Read()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at LACARS.ReportsClients.DoReport() in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\LACARS\LACARS\ReportsClients.vb:line 48
       at LACARS.ReportsClients.btnGo_Click(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\LACARS\LACARS\ReportsClients.vb:line 116
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at LACARS.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
==========================

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().ToShortDateString() & ")"
            strSQL = "SELECT * FROM job WHERE PickupDateTime > '" & DateTime.Now().AddMonths(-2).ToString("MMM d yyyy") & "'"
            daT2 = New SqlClient.SqlDataAdapter(strSQL, 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(strSQL, connection)
            daT2.Fill(LACARSDataSet, "job")
        End If

====================

This is the exception i was alluding at in the first post.
0
 
LVL 41

Expert Comment

by:graye
ID: 17829591
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.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:deecree
ID: 17829894
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.
0
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 17833663
did you try changing it to

SELECT * FROM job WHERE PickupDateTime BETWEEN 'jul 29 2006' AND  'oct 29 2006'

or

or

SELECT * FROM job WHERE PickupDateTime BETWEEN '07/27/2006' AND '10/29/2006'


SELECT * FROM job WHERE PickupDateTime > '07/27/2006' AND PickupDateTime < '10/29/2006'
0
 
LVL 35

Expert Comment

by:YZlat
ID: 17833679
BETWEEN is better to use than <
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vb.net threads keep increasing 2 59
Reactjs with .NET 3 121
orderby list (from Json) 1 42
C# MDI - Use Button on Child Form to Open (or Show) a Different Child Form 3 42
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question