dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] SQL syntax problem

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    309

    Resolved [RESOLVED] SQL syntax problem

    This is my query in MSSQL 2017 works fine in the database
    Code:
    SELECT        WorkOrderID, MachineNumber, WorkorderDate, Priority, Problem, Description, EmployeeName
    FROM            dbo.WorkOrders
    WHERE        (WorkorderDate IS NOT NULL) AND (WorkorderDate <= { fn NOW() } - 7)

    Here is the code in VB.net 2017
    This show ZERO records. not sure what I hVE WRONG COULD USE SOME HELP
    THANKS

    Code:
    Public Class DGVWeeklateWO
        Private SQL As New SQLControl
        Private Sub DGVWEEKLATEwo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            MdiParent = frmMain
            'EXECUTE QUERY AND POPULATE GRID
    
    
            SQL.ExecQuery("SELECT WorkOrderID, MachineNumber, WorkorderDate, Priority, Problem, Description, EmployeeName" &
              "FROM WorkOrders" &
              "WHERE (WorkorderDate Is Not NULL) And (WorkorderDate <= {fn NOW() } - 7);")
    
            LOADGRID()
    
        End Sub
        Private Sub LOADGRID()
            'IF DATA IS RETURNED POPULATE GRID AND BUILD UPDATE COMMAND
            If SQL.RecordCount > 0 Then
                DGVIEW5.DataSource = SQL.DBDT
    
            End If
        End Sub
        Private Sub cmdEXIT_Click(sender As Object, e As EventArgs) Handles cmdEXIT.Click
            Me.Close()
    
        End Sub
    Code:
    Imports System.Data.SqlClient
    
    
    Public Class SQLControl
        'INFORMATION FOR THE CONNECTION TO THE SQL DATABASE
        'FIRST IS THE SERVERS NAME; DATABASE NAME;USER; PASSWORD;
        'DO NOT FORGET THE SEMICOLINS AS SEPARTOR
    
        Private DBCon As New SqlConnection("Server=localhost\SQLEXPRESS;Database=HiTech;User=Maint1;Pwd=Password1;")
    
        'THIS WILL BE RECREATED EVERY TIME WE RUN A QUERY
    
        Private DBcmd As SqlCommand
    
        'localhost\SQLEXPRESS
        ' DB Data
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable ' THIS A STORAE CONTAINER FOR THE DATA
    
    
        ' Qury Parameters
        ' THESE WILL BE USED WHEN WE RUN QUERY'S
        Public Params As New List(Of SqlParameter)
    
        'Query Statistics
        Public RecordCount As Integer
        Public Exception As String   'USED TO STOR ANY ERROR'S ALONG THE WAY
    
    
        Public Sub New()
    
        End Sub
        'Allow Connection String Override
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
        End Sub
        'Execute Query Sub
        Public Sub ExecQuery(Query As String)
            'Reset Query Stats
            RecordCount = 0
            Exception = ""
            Try
                DBCon.Open()
    
                'Creat DB Command
                DBcmd = New SqlCommand(Query, DBCon)
    
                'Load Parans into DB Command
                Params.ForEach(Sub(p) DBcmd.Parameters.Add(p))
    
                'Clear Param List
                Params.Clear()
    
                'Execute Command & Fill Dataset
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBcmd)
                RecordCount = DBDA.Fill(DBDT)
    
            Catch ex As Exception
                'Capture Error
                Exception = "ExceQuery Error: " & vbNewLine & ex.Message
    
    
            Finally
                'Close Connection
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
    
    
            End Try
        End Sub
        'Add Params
        Public Sub AddParam(name As String, value As Object)
            Dim NewParam As New SqlParameter(name, value)
            Params.Add(NewParam)
        End Sub
    
        'Error Checking
        Public Function hasexception(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
            Return True
    
        End Function
    
    
    End Class

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,904

    Re: SQL syntax problem

    What you are doing wrong is not checking the Exception, which will have some useful info for you on this occasion (you've accidentally created a couple of syntax issues).

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    309

    Re: SQL syntax problem

    I am only guessing here but are you saying I should look at the msgboxes with any exception text? If so then I'm lost because I have seen nothing about a problem. I just see and empty data view grid box no errors or messages. Just empty.
    All I know for sure is I see the record count is 0. I made some changes in the SQL text and it works But I do not know why this work and the first one shown does not Work
    Code:
      SQL.ExecQuery("SELECT WorkOrderID, MachineNumber, WorkorderDate, Priority, Problem, Description, EmployeeName FROM workorders" &
                         " WHERE (WorkorderDate Is Not NULL) And (WorkorderDate <= {fn NOW() } - 7);")
    thanks

  4. #4
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    736

    Re: SQL syntax problem

    Original code:

    Code:
            SQL.ExecQuery("SELECT WorkOrderID, MachineNumber, WorkorderDate, Priority, Problem, Description, EmployeeName" &
              "FROM WorkOrders" &
              "WHERE (WorkorderDate Is Not NULL) And (WorkorderDate <= {fn NOW() } - 7);")
    Results in this inside the query string:

    "...Description, EmployeeNameFROM WorkOrdersWHERE (WorkorderDate..."


    The reason your second one works is because you are properly using spaces between things.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    309

    Re: SQL syntax problem

    I see it now thanks

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,904

    Re: SQL syntax problem

    Quote Originally Posted by Alfarata View Post
    I am only guessing here but are you saying I should look at the msgboxes with any exception text?
    No, because your SQLControl class explicitly hides exceptions from you... and instead stores the information to .Exception (and also provides a .hasexception method).

    I don't like that design myself, but if you are using it you should use it properly.

    Whenever you run any SQL statement (even if it seems to work fine), you should check whether any exceptions occurred. Failing to do that means that you can be doing all kinds of nasty things to your data and not realise you've have (including not returning the records you should, or failing to update/delete a row, or updating/deleting more rows than you intended).

    In this case .Exception would have contained something along the lines of " syntax error near 'EmployeeNameFROM' ", which gives a very clear hint about what was wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width