Getting Syntax Error Converting from Character String

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

33 Replies - 1793 Views - Last Post: 04 February 2011 - 09:18 AM Rate Topic: -----

#1 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 09:59 AM

I have the following code:

Imports System.Data.SqlClient
Public Class Main
    Protected WithEvents DataGridView1 As DataGridView
    Dim instForm2 As New Exceptions
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
        Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
                 "dateadd(s,  518399, max(payrolldate)) AS [Sunday]" & _
                  "from dbo.payroll" & _
                  " where payrollran = 'no'"
        Dim oCmd As System.Data.SqlClient.SqlCommand
        Dim oDr As System.Data.SqlClient.SqlDataReader
        oCmd = New System.Data.SqlClient.SqlCommand

        Try
            With oCmd
                .Connection = New System.Data.SqlClient.SqlConnection("Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx")
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = ssql
                oDr = .ExecuteReader()
            End With
            If oDr.Read Then
                payperiodstartdate = oDr.GetDateTime(1)
                payperiodenddate = payperiodstartdate.AddDays(7)
                Dim ButtonDialogResult As DialogResult
                ButtonDialogResult = MessageBox.Show("      The Next Payroll Start Date is: " & payperiodstartdate.ToString() & System.Environment.NewLine & "            Through End Date: " & payperiodenddate.ToString())
                If ButtonDialogResult = Windows.Forms.DialogResult.OK Then
                    exceptionsButton.Enabled = True
                    startpayrollButton.Enabled = False
                End If
            End If
            oDr.Close()
            oCmd.Connection.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try

    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exceptionsButton.Click
        Dim connection As System.Data.SqlClient.SqlConnection
        Dim adapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim connectionString As String = "Initial Catalog=mdr;Data Source=xxxxx;uid=xxxxx;password=xxxxx"
        Dim ds As New DataSet
        Dim _sql As String = "SELECT [Exceptions].Employeenumber,[Exceptions].exceptiondate, [Exceptions].starttime, [exceptions].endtime, [Exceptions].code, datediff(minute, starttime, endtime)  as duration into Scratchpad3" & _
        " FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _
         " where [Exceptions].exceptiondate between @payperiodstartdate and @payperiodenddate" & _
         " GROUP BY [Exceptions].Employeenumber, [Exceptions].Exceptiondate, [Exceptions].starttime, [exceptions].endtime," & _
         " [Exceptions].code, [Exceptions].exceptiondate"
        connection = New SqlConnection(connectionString)
        connection.Open()
        Dim _CMD As SqlCommand = New SqlCommand(_sql, connection)
        _CMD.Parameters.AddWithValue("@payperiodstartdate", payperiodstartdate)
        _CMD.Parameters.AddWithValue("@payperiodenddate", payperiodenddate)
        adapter.SelectCommand = _CMD
        Try
            adapter.Fill(ds)
            If ds Is Nothing OrElse ds.Tables.Count = 0 OrElse ds.Tables(0).Rows.Count = 0 Then
                'it's empty
                MessageBox.Show("There was no data for this time period. Press Ok to continue", "No Data")
                connection.Close()
                Exceptions.saveButton.Enabled = False
                Exceptions.Hide()
            Else
                connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            connection.Close()
        End Try
        Exceptions.Show()
    End Sub

    Private Sub payrollButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payrollButton.Click
        Payrollfinal.Show()
    End Sub
End Class


and when I run this code, I get the following error

System.Data.SQLClient.SQLException: Syntax error converting from datetime character string, line 57.

Which is this line:
adapter.Fill(ds)

When I debug this, I put the line break on that line and look at the Table Value, which shows 0, but I know that there is data for that time frame. Can anyone please assist?

Is This A Good Question/Topic? 0
  • +

Replies To: Getting Syntax Error Converting from Character String

#2 Guest_Jim*


Reputation:

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 10:13 AM

So your users are always gonna click "Button1" before "Button2"? Otherwise those variables ("payperiodstartdate", "payperiodenddate") don't have values, do they? My users aren't trained that well! ;-)

You should be using the "Option Explicit" directive at the top of your classes, modules, etc. as I don't even see where these variables are declared. Scoping is a likely culprit here?

Jim
Was This Post Helpful? 0

#3 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 12:41 PM

Jim,

My users don't have an option to click anything BUT button 1 or 2. I only enable them once the action of the other button is completed. Where would I put the option explicit?
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 12:46 PM

doug,
How is the starttime and endtime stored in the database? As a string? If so you might Try
datediff(minute, CDate(starttime), CDate(endtime))  as duration
In your query
Was This Post Helpful? 0
  • +
  • -

#5 Guest_Jim*


Reputation:

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:02 PM

View Postdougancil2010, on 31 January 2011 - 12:41 PM, said:

Jim,

My users don't have an option to click anything BUT button 1 or 2. I only enable them once the action of the other button is completed. Where would I put the option explicit?


OK, you got me there--didn't see that you'd changed your buttons' enabled properties!

You want "Option Explicit On" way the top of your code before the "Imports System.Data.SqlClient"

I'm also wondering about the

        " FROM Employees INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber" & _	 



I think you want something like "Employees.ID = [Exceptions].EmployeeNumber"?

Jim
Was This Post Helpful? 0

#6 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:15 PM

Charlie,

The starttime and endtime are stored as varchars in the database. I guess that's storing it as a string.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,147
  • Joined: 12-June 08

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:19 PM

Yes, change your date time to date time types... why were they not that in the first place?
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:21 PM

OK, well a string is not a date so did you try using CDate to Cast it to a date like I showed above?

Also, any reason why you're not storing dates as date types in the database?

One last thing.

Didn't modi123_1 work with you on fixing the problems in your statement. Specifically the JOIN that is not necessary in the context that you have it.

INNER JOIN Exceptions ON [Exceptions].EmployeeNumber = [Exceptions].Employeenumber
You are joining NOTHING here. You looking at a field and saying if it equals itself then join... Of course it's going to equal itself.

JOIN is for combining 2 tables. You're not even using information from any other table.

This post has been edited by CharlieMay: 31 January 2011 - 01:25 PM

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,147
  • Joined: 12-June 08

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:32 PM

CharlieMay, this issue seems oddly familiar, but I am not really up to sifting through sixty-plus replies of "thrash around wildly" learning process.
Was This Post Helpful? 0
  • +
  • -

#10 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:43 PM

Charlie,

I changed the date types to datetime types in the database. I also added what you suggested datediff(minute, CDate(starttime), CDate(endtime)) as duration and now I am getting this error:
"CDate is not a recognized function name"
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:46 PM

if you changed the database to datetime types, then you no longer need CDate as you are passing the proper type to the datadiff.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,147
  • Joined: 12-June 08

Re: Getting Syntax Error Converting from Character String

Posted 31 January 2011 - 01:47 PM

If your column type is Date now, would it make sense to keep the "CDate"? Did you even look what CDATE does?

Edit: please.. please.. please try and examine what you are doing. Flailing around won't help you write your code.

This post has been edited by modi123_1: 31 January 2011 - 01:48 PM

Was This Post Helpful? 0
  • +
  • -

#13 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Getting Syntax Error Converting from Character String

Posted 01 February 2011 - 09:17 AM

So I have modified my sql table and checked my sql query and ran through my code again. I set my test to a time when I knew that if I run my query, that there "should" be data in my table. If I run the sql query included in this file on its own (in SQL query analyzer) I produce 1 result, but when I run my program and put my line break on this line:

adapter.Fill(ds)

I see table count of 0.
Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Getting Syntax Error Converting from Character String

Posted 01 February 2011 - 09:59 AM

Please list the Table schema for exceptions and employees so that we can see what we're dealing with.
Was This Post Helpful? 0
  • +
  • -

#15 dougancil2010  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 94
  • Joined: 12-July 10

Re: Getting Syntax Error Converting from Character String

Posted 01 February 2011 - 10:40 AM

Charlie,

here's the schema for each table:

Exceptions

employeenumber varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
exceptiondate datetime no 8 yes (n/a) (n/a) NULL
starttime datetime no 8 yes (n/a) (n/a) NULL
endtime datetime no 8 yes (n/a) (n/a) NULL
duration varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
code varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approvedby varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
approved varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS
time timestamp no 8 yes (n/a) (n/a) NULL

employees

employeenumber varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
name varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
initials varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
loginname1 varchar no 50 yes no no SQL_Latin1_General_CP1_CI_AS

This post has been edited by dougancil2010: 01 February 2011 - 10:41 AM

Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3