0

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  

1 Answer 1

1

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.
Sign up to request clarification or add additional context in comments.

1 Comment

Excellent advise, thank you. I now know that the errors occur on lines 1, 8, and 12. I will diagnose further, but it's much easier knowing where the errors are occurring.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.