locked
Tracing error in function calling a SQL Query RRS feed

  • Question

  • User1132575917 posted

    Hi,

    I'm pretty new to asp.net, so I apologise if this is a basic question!

    I'm getting an error when I call a function that's defined in a .vb file in my App_Code directory.  The code of the function is:

    Public Shared Function NextItemNumber(strItemTypeName As String) As Integer
     objConn = New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
     objConn.Open()
     Dim strSQLStatement = New String("If (SELECT MAX (Number) Number From Items.tbl" + strItemTypeName + ") IS NOT NULL" & vbCrLf &
       "SELECT Case MAX (Number) Number From Items.tbl" + strItemTypeName + "" & vbCrLf &
      "ELSE" & vbCrLf &
       "SELECT StartOffset from Items.tblItemTypes WHERE Name = '" + strItemTypeName + "'")
     objCmd = New SqlCommand(strSQLStatement, objConn)
     Dim intLastNumber = objCmd.ExecuteScalar()
     Dim intNextNumber = intLastNumber + 1
     Return intNextNumber
     objConn.Close()
    End Function

    When I load the page which calls this function, I get the error message:

    Incorrect syntax near 'Number'. 
      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
     Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'Number'.

    Can anyone suggest where to look for the fault?  There are quite a few instances of 'Number' in the code, I'm not sure which one has the syntax error.  I've tested the SQL Query in isolation using SQL Server Management studio, which is generating the results I expect.  I'm successfully calling other functions defined in the same .vb file as this one.

    Many thanks

    Tuesday, April 24, 2018 12:28 PM

Answers

  • User-1716253493 posted

    Seem like you want to get StartOffset when Max(Number) is null

    It's very simple if you use isnull function

    SELECT ISNULL(MAX(Number),StartOffset) From yourtable

    or try this

    "If NOT (SELECT MAX(Number) From Items.tbl" + strItemTypeName + ") IS NULL " ...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 25, 2018 1:08 AM

All replies

  • User753101303 posted

    Hi,

    You perhaps mistakenly introduced "Case" in your SQL statement which happens to start a valid expression in Transact SQL (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017)  :

    SELECT Case MAX (Number) Number From Items.tbl

    Tuesday, April 24, 2018 5:53 PM
  • User-1716253493 posted

    Seem like you want to get StartOffset when Max(Number) is null

    It's very simple if you use isnull function

    SELECT ISNULL(MAX(Number),StartOffset) From yourtable

    or try this

    "If NOT (SELECT MAX(Number) From Items.tbl" + strItemTypeName + ") IS NULL " ...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 25, 2018 1:08 AM
  • User1132575917 posted

    Seem like you want to get StartOffset when Max(Number) is null

    It's very simple if you use isnull function

    SELECT ISNULL(MAX(Number),StartOffset) From yourtable

    That did the trick, thank you.  Plus, it means one less select statement to my Database.

    Wednesday, April 25, 2018 12:21 PM