none
SQL not OK in VB, OK in SQL Server RRS feed

  • Question

  • The following SQL statement gives me an "Incorrect syntax near 1900" in VB, but when I run it on SQL server it runs fine.

    dtmNextScalingDate is DateTime

                strSQL = "SELECT t.intID, t.intCustomerID, t.intDoctorID, t.nvcLUC, t.dtmNextScalingDate, " _
                 & " t.dtmFollowUpExam, t.bitCleanRecall, t.bitRecall, t.intRecallDescription, t.nvcNote, " _
                 & " t.intSMSID, t.intIDCopy " _
                 & " FROM tblCustomerRecall AS t " _
                 & "    INNER JOIN " _
                 & "     (SELECT intCustomerID, MAX(intID) As max_id " _
                 & "     FROM tblCustomerRecall " _
                 & "     WHERE (dtmNextScalingDate <> '1900-01-01 00:00:00') " _
                 & "     GROUP BY intCustomerID) " _
                 & " AS a ON a.intCustomerID = t.intCustomerID And a.max_id = t.intID "

    Saturday, September 30, 2017 12:07 PM

Answers

  • On repeated look of the original SQL, it works OK. There was another issue with parameter passing.

    Anyway, thanks for your assistance and we will look into the Stored Procedure suggestion.

    • Marked as answer by JohnPapa05 Sunday, October 1, 2017 8:06 AM
    Sunday, October 1, 2017 8:06 AM

All replies

  • Thanks Karen,

    You know that "dtmNextScalingDate" is a field in "tblCustomerRecall", right?

    Is there a change I can make to the existing SQL statement within VB?

    Thanks,

    John

    Saturday, September 30, 2017 12:58 PM
  • Thanks Karen,

    You know that "dtmNextScalingDate" is a field in "tblCustomerRecall", right?

    Is there a change I can make to the existing SQL statement within VB?

    Thanks,

    John

    Ooops, need to drink my coffee.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 30, 2017 12:59 PM
    Moderator
  • Here is a revised version against one of my example tables and works just fine

    Imports System.Data.SqlClient
    Public Class Operations
        Private ConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"
        Public Sub CallerMethod()
            Demo(#1/1/1900#)
        End Sub
        Public Sub Demo(ByVal pDate As Date)
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                        SELECT OrderID
                              ,CustomerIdentifier
                              ,EmployeeID
                              ,ShipCountry
                          FROM Orders
                          WHERE OrderDate &lt;&gt; @pDate
                        </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@pDate", pDate)
                    cn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    If reader.HasRows Then
                        While reader.Read
                            Console.WriteLine(reader.GetInt32(0))
                        End While
                    End If
                End Using
            End Using
        End Sub
    End Class

    Even without parameters

    Imports System.Data.SqlClient
    Public Class Operations
        Private ConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"
        Public Sub CallerMethod()
            Demo()
        End Sub
        Public Sub Demo()
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                        SELECT OrderID
                              ,CustomerIdentifier
                              ,EmployeeID
                              ,ShipCountry
                          FROM Orders
                          WHERE OrderDate &lt;&gt; '1900-01-01 00:00:00'
                        </SQL>.Value
    
                    cn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    If reader.HasRows Then
                        While reader.Read
                            Console.WriteLine(reader.GetInt32(0))
                        End While
                    End If
                End Using
            End Using
        End Sub
    End Class

    I always use parameters and test them as shown here so when dropping the SQL into code (without the declared param) I'm already setup to go and run. Here I test against a specific date because I picked a table that has no empty dates.

    DECLARE @pDate DATETIME = '2014-07-05 00:00:00.000'
    SELECT OrderID
          ,CustomerIdentifier
          ,EmployeeID
          ,ShipCountry
      FROM Orders
      WHERE OrderDate <> @Pdate


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Saturday, September 30, 2017 1:14 PM
    Moderator
  • Thanks again Karen.

    I am just trying to create a dataset with the result

                Dim dst As DataSet = New DataSet
    
                strSQL = "SELECT t.intID, t.intCustomerID, t.intDoctorID, t.nvcLUC, t.dtmNextScalingDate, " _
                 & " t.dtmFollowUpExam, t.bitCleanRecall, t.bitRecall, t.intRecallDescription, t.nvcNote, " _
                 & " t.intSMSID, t.intIDCopy " _
                 & " FROM tblCustomerRecall AS t " _
                 & "    INNER JOIN " _
                 & "     (SELECT intCustomerID, MAX(intID) As max_id " _
                 & "     FROM tblCustomerRecall " _
                 & "     WHERE (dtmNextScalingDate <> '1900-01-01 00:00:00') " _
                 & "     GROUP BY intCustomerID) " _
                 & " AS a ON a.intCustomerID = t.intCustomerID And a.max_id = t.intID "
    
                dst = fungGetCustomerManagementClean(strSQL)

    Is there a simple way to do this. As I mentioned when I run this in SQL Server it runs fine.

    Thanks,

    John

    Saturday, September 30, 2017 1:30 PM
  • Well if it runs on SQL-Server fine then why not use a stored procedure?

    Imports System.Data.SqlClient
    Public Class Operations
        Private ConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"
        Public Property DataSet As DataSet
        Public Sub CallerMethod()
            Demo(#1/1/1900#)
            For Each row As DataRow In DataSet.Tables("Orders").Rows
                Console.WriteLine(String.Join(",", row.ItemArray))
            Next
        End Sub
        Public Sub Demo(ByVal pDate As Date)
            DataSet = New DataSet
    
            Dim dt As New DataTable With {.TableName = "Orders"}
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With
                    {
                        .Connection = cn,
                        .CommandText = "dbo.uspJohnPaPaDateCheck",
                        .CommandType = CommandType.StoredProcedure}
    
                    cmd.Parameters.AddWithValue("@pDate", pDate)
    
                    cn.Open()
    
                    dt.Load(cmd.ExecuteReader)
                    DataSet.Tables.Add(dt)
                End Using
            End Using
        End Sub
    End Class

    SP

    USE [ForumExamples]
    GO
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[uspJohnPaPaDateCheck] (@pDate DATETIME) AS
    BEGIN
    
    SELECT OrderID
          ,CustomerIdentifier
          ,EmployeeID,OrderDate
          ,ShipCountry
      FROM Orders
      WHERE OrderDate <> @Pdate
    END
    GO
    
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Saturday, September 30, 2017 1:38 PM
    Moderator
  • I have not used stored procedures in my project.

    Is there a way to alter the code in VB?

    Thanks,

    John

    Saturday, September 30, 2017 1:56 PM
  • I have not used stored procedures in my project.

    Is there a way to alter the code in VB?

    Thanks,

    John

    I have given you my suggestions, have nothing more to offer. I suggest you consider using a stored procedure, see the following to get started.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 30, 2017 2:05 PM
    Moderator
  • It is not that simple. We communicate with the SQL Server with Web Service software which we wrote, so Stored Procedures may not be an option.

    Anyway, thanks for your help.

    John

    Saturday, September 30, 2017 2:33 PM
  • It is not that simple. We communicate with the SQL Server with Web Service software which we wrote, so Stored Procedures may not be an option.

    Anyway, thanks for your help.

    John

    I would not had mentioned SP's if you had indicated you were utilizing a web service not your own.

    So let's go back to the original query format but ask you to try this method where the date is passed in not with year first but with month first.

    Imports System.Data.SqlClient
    Public Class Operations
        Private ConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=ForumExamples;Integrated Security=True"
        Public Sub CallerMethod()
            Demo(#1/1/1900#)
        End Sub
        Public Sub Demo(ByVal pDate As Date)
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                        SELECT OrderID
                              ,CustomerIdentifier
                              ,EmployeeID
                              ,ShipCountry
                          FROM Orders
                          WHERE OrderDate &lt;&gt; @pDate
                        </SQL>.Value
    
                    cmd.Parameters.AddWithValue("@pDate", pDate)
                    cn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    If reader.HasRows Then
                        While reader.Read
                            Console.WriteLine(reader.GetInt32(0))
                        End While
                    End If
                End Using
            End Using
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 30, 2017 3:09 PM
    Moderator
  • Karen, for example, the following works,

            Dim Str As String = "SELECT tblAppointment.intAppointID AS intAppointID " _
              & " FROM tblAppointment " _
              & " INNER JOIN tblDoctor On tblAppointment.intAppointDoctorID = tblDoctor.intDoctorID" _
              & " WHERE (LEN(tblAppointment.dtmAppointTime) = 5) " _
              & " AND tblDoctor.intPracticeID=" & intgPracticeLogin _
              & " AND (tblAppointment.dtmAppointDate BETWEEN '" & strDate & " 00:00' AND '" & strDate & " 23:59:59') "
    
            dstPatient = fungGetDatasetBySqlWS(strgDBSN, Str)
    It must have something to do with the fact that I am trying to do a JOIN with the same table

    Saturday, September 30, 2017 3:50 PM
  • Hello John;

    >>>We communicate with the SQL Server with Web Service software which we wrote

    All sorts of alarms went off when I read this. If your web service is in any way exposed to the open web you are risking all sorts of SQL Injection attacks. 

    I have two suggestions:

    1. since you wrote the web service, alter it to accept either a JSON or XML structure containing the values used for parameters.

    2. On the server side, create the query using SQLParameter objects, not a concactinated string.

    3 and even better, as Karen pointed out, use a stored procedure rather than sending the text of a SQL Query.

    I don't know your environment or your project specs but I suggest you break the habit of concatenating SQL strings with values.  Keep the knowledge of your data store on the server side. Sending queries across the wire allows others to determine your data schema and more effectively set up their attacks on your system.

    And bye the way, using SQLParameters and Stored Procedures will probably fix you date formatting issue.

    Ibrahim


    Ibrahim Malluf MCP http://www.malluf.com


    • Edited by Ibrahim Y Saturday, September 30, 2017 6:11 PM
    Saturday, September 30, 2017 6:10 PM
  • Thanks Ibrahim,

    Our Web service used JSON and XML which we encrypt when we send and receive.

    I agree with what you said about keeping the data store knowledge on the server and will look into it.

    But out of curiosity, do you know what causes the problem?

    John

    Sunday, October 1, 2017 5:06 AM
  • On repeated look of the original SQL, it works OK. There was another issue with parameter passing.

    Anyway, thanks for your assistance and we will look into the Stored Procedure suggestion.

    • Marked as answer by JohnPapa05 Sunday, October 1, 2017 8:06 AM
    Sunday, October 1, 2017 8:06 AM