-
Apr 24th, 2019, 01:31 PM
#1
Thread Starter
Hyperactive Member
[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
-
Apr 24th, 2019, 02:06 PM
#2
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).
-
Apr 24th, 2019, 05:53 PM
#3
Thread Starter
Hyperactive Member
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
-
Apr 24th, 2019, 06:16 PM
#4
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.
-
Apr 24th, 2019, 07:34 PM
#5
Thread Starter
Hyperactive Member
-
Apr 25th, 2019, 05:38 AM
#6
Re: SQL syntax problem
Originally Posted by Alfarata
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|