0

I keep getting this error on page load for a dynamic chart, based on dropdownlist items:

*Incorrect syntax near ')'. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'. Source Error:

Line 44: objAdapter.Fill(objDataSet)*

Page load code:

    DropDownList1.Text = "Show All"
    Dim strSQLQuery As String
    Dim objConnection As SqlConnection
    Dim objCommand As SqlCommand
    Dim objAdapter As SqlDataAdapter
    Dim objDataSet As DataSet

    objConnection = New SqlConnection("Data Source=MYSVR01;Initial Catalog=HMBX;Integrated Security=True")

    If DropDownList1.Text = "Show All" Then
        strSQLQuery = "SELECT * from table"
    Else
        strSQLQuery = "SELECT * from table where col1 = '"& dropdownlist1.text &'""
    End If
    objCommand = New SqlCommand(strSQLQuery, objConnection)

    objAdapter = New SqlDataAdapter(objCommand)
    objDataSet = New DataSet()
    objAdapter.Fill(objDataSet)

    'Dim source As New DataView(objDataSet.Tables(0))

    Chart1.DataSource = objDataSet
    Chart1.Series("Series1").XValueMember = "TimeOfDay"
    Chart1.Series("Series1").YValueMembers = "visitors"

    Chart1.DataBind()

I appreciate your input...THANKS!!

user851971
  • 23
  • 1
  • 7
  • is it working for "Show All" – Mohammad Arshad Alam Jan 07 '13 at 14:39
  • What's the actual SQL query being executed? Your query has a _huge_ SQL injection vulnerability, so any SQL code could be in that query at runtime. You'll need to step through in the debugger to see the final query being executed, or run a profiler against the database to see the query as the database receives it. – David Jan 07 '13 at 14:40
  • Come to think of it... How would the code even get to the `Else` branch in that conditional? You set the value to `"Show All"` and then immediately check if the value is set to `"Show All"`. What's the point of the conditional if you manually set the condition? – David Jan 07 '13 at 14:43
  • "Show All" is not working but the Chart loads when bound to a SqlDatasource. Also the dropdownlist has items that corresponds to a column in the table except "Show All", that is why i set the sql to 'select all'. @David - the sql query works very well with the sqldatasource that is bound to the chart but I want the chart to change based on the where clause and the dropdownlist is on autopost back. Thanks!! – user851971 Jan 07 '13 at 14:54
  • @user851971: There are a number of different problems here. First and foremost is that you have a SQL injection vulnerability. At the very least you'll want to look into using parameterized queries for your ADO objects. Second, according to the code you've posted, `DropDownList1.Text` will _always_ equal `"Show All"` when you get to the `If` statement, because you're manually setting it to that value a few lines before. Finally, the error you're getting is from the SQL server. So you need to find the run-time query being executed against the database to determine what's wrong. – David Jan 07 '13 at 17:14
  • Thanks David, I`ve been able to sort that out. it was as a result of syntax errors in the sql query, I used the continuation xters(& _) without a space at the end of each line. – user851971 Jan 08 '13 at 09:12

1 Answers1

0

Problem Solved...had to correct some syntax errors in sql

user851971
  • 23
  • 1
  • 7