CODEWITHSUNDEEP
sql-servervb.netparameters
user1077685
user1077685

Reputation:

Parameterized SQL Query Throwing Error - Incorrect Syntax?

I'm passing SQL and an array of parameters (captured from user input) to a OleDbDataAdapter like so:

Dim sql As New StringBuilder
Dim theparams As String(,)
sql.Append(" select distinct acc.?,  ")
sql.Append(" isnull(acc.?, acc.?) ?, ")
sql.Append( _
    "    Case when ptree.brandid=220 then 99 else ptree.brandgroupid end brandgroupid, Case when ptree.brandid=220 then 'Multibrand Prepacks' else isnull(ptree.brandgroupname,'1') end brandgroupname, isnull(ptree.brandgroup2,'1') brandgroup2 , ptree.brandgroup3, ptree.brandid, ptree.brandname  ")
sql.Append(" from ")
sql.Append(" (select acc.?,  ")
sql.Append(" acc.? from ")
sql.Append(" vw_ACCOUNT_TREE acc")
sql.Append(" UNION")
sql.Append(" SELECT 'Unknown','Unknown'")
sql.Append(" ) acc")
sql.Append(" CROSS JOIN vw_Product_tree ptree  ")
sql.Append(" WHERE (BRANDGROUPNAME <> 'Unknown' or  ptree.brandid=220)  ")
sql.Append(" and brandenabled=1  ")

theparams = New String(5, 2) {}
theparams(0, 0) = "@0"
theparams(0, 1) = Level
theparams(0, 2) = "OleDbType.String"
theparams(1, 0) = "@1"
theparams(1, 1) = Level + "Description"
theparams(1, 2) = "OleDbType.String"
theparams(2, 0) = "@2"
theparams(2, 1) = Level
theparams(2, 2) = "OleDbType.String"
theparams(3, 0) = "@3"
theparams(3, 1) = Level + "Description"
theparams(3, 2) = "OleDbType.String"
theparams(4, 0) = "@4"
theparams(4, 1) = Level
theparams(4, 2) = "OleDbType.String"
theparams(5, 0) = "@5"
theparams(5, 1) = Level + "Description"
theparams(5, 2) = "OleDbType.String"

I then pass the SQL and the array of parameters to the following method, which should create a Data Table:

Public Function GetTable(ByVal sql As String, ByVal qParameters(,) As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Try
        If sql.Length > 0 Then
            If ConnectToDB() Then
                Using oDa As New OleDbDataAdapter(sql, _conn)
                    If Not qParameters.GetValue(i, 0) Is Nothing Then
                        For i As Integer = 0 To (qParameters.Length / 3 - 1)
                            oDa.SelectCommand.Parameters.AddWithValue(qParameters(i, 0), qParameters(i, 1))
                            oDa.SelectCommand.Parameters(qParameters(i, 0)).OleDbType = GetOleParameterType(qParameters(i, 2))
                        Next
                    End If
                    Using oDs As New DataSet
                        oDa.Fill(oDs)
                        dt = oDs.Tables(0)
                    End Using
                End Using
            End If

        End If
    Catch ex As Exception
        _error = "Exception in GetTablewithParameters ~ " & ex.Message & " ~ " & sql
    End Try
    Return dt
End Function

However, it's catching the following exceptions:

Line 1: Incorrect syntax near ')'.
Line 1: Incorrect syntax near '@P5'.
Line 1: Incorrect syntax near '@P1'.

What is wrong with my code? If I replace the parameters with actual strings and remove the opening and closing of each and every sql.Append(), the query works fine in SQL Server Management Studio. Am I using parameters incorrectly?

EDIT: More defined errors:

Line 1: Incorrect syntax near '@P1'.
Line 8: Incorrect syntax near '@P5'.
Line 12: Incorrect syntax near ')'.

Here is the code broken down by line:

 1.  select distinct acc.?,  
 2.  isnull(acc.?, acc.?) ?, 
 3.  Case when ptree.brandid=220 then 99 else ptree.brandgroupid end brandgroupid, 
 4.  Case when ptree.brandid=220 then 'Multibrand Prepacks' 
 5.  else isnull(ptree.brandgroupname,'1') end brandgroupname, isnull(ptree.brandgroup2,'1') 
 6.  brandgroup2 , ptree.brandgroup3, ptree.brandid, ptree.brandname  
 7.  from 
 8.  (select acc.?,  
 9.  acc.? from 
 10. vw_ACCOUNT_TREE acc
 11. UNION
 12. SELECT 'Unknown','Unknown') acc
 13. CROSS JOIN vw_Product_tree ptree  
 14. WHERE (BRANDGROUPNAME <> 'Unknown' or  ptree.brandid=220)  
 15. and brandenabled=1

Upvotes: 0

Views: 1845

Answers (1)

Ben
Ben

Reputation: 35613

If you do the following this will be a lot easier to debug.

  1. Add a newline (CR LF) after each append. Then you will get sensible and useful line numbers instead of line 1 all the time.
  2. Output the actual string in your debug log. Then you can see what line the error occurs on.

Upvotes: 1

Related Questions