Query Error

[Netcode]

Hello everyone!
Am trying to retrieve data from sql server based on two columns. Its a web application being developed using VS2010 (vb.net) but i keep getting the error: incorrect syntax near '='
Here's my code below:

Sub GetBasicMidwives()

        ' Set the SelectCommand properties...
        With objDataAdapter
            .SelectCommand = New SqlCommand()
            .SelectCommand.Connection = objConnection
            .SelectCommand.CommandText = "SELECT Reference_Number, Year, Title, Surname," + _
                "FirstName, Date_of_Birth, Origin, Phone, State_Deployed, Facility_Deployed FROM HEALTHWORKERS" + _
                "WHERE DESIGNATION = BASIC MIDWIVES"


            .SelectCommand.CommandType = CommandType.Text
        End With

        ' Open the database connection...
        objConnection.Open()
        ' Fill the DataSet object with data...
        objDataAdapter.Fill(objDataSet, "HEALTHWORKERS")
        ' Close the database connection...
        objConnection.Close()
        ' Set the DataGridView properties to bind it to our data...

        With BMGridView

            .AutoGenerateColumns = True
            .DataSource = objDataSet
            .DataMember = "HEALTHWORKERS"
            .DataBind()

        End With

        RecordCountLabel.Text = BMGridView.Rows.Count & " " & "Record(s)"

        'clear memory
        objDataAdapter = Nothing
        objConnection = Nothing

    End Sub

[Unhnd_Exception]

Try adding single quotations around 'Basic MidWives'

[Netcode]

Still the same error

[Unhnd_Exception]

That would be one problem.

Add a space before Where. The table name and Where are joined.

Change the + To &.

<Edit>

Future reference: Its a good idea to use vbcrlf to break the query into lines.

"Select WhatEver" & vbcrlf & _
"From WhereEver" & vbcrlf & _
"Where WhenEver"

Advantage would be the error message would say which line the error was on and you will also avoid spacing mistakes.

[Netcode]

i tried changing the '+' to '&' and got the same error but when i used the line break (vbcrlf), i returned the page without errors but i got no record which is not possible cos i have records that match the search

[adam_k]

use both Unhnd_Exception's posts to get your results. You need both the & and the vbcrlf (or just a space before where) AND single quotes around BASIC MIDWIVES.

[Eekhoorn]

You forgot the semi-locon

....' BASIC MIDWIVES ';"

on the end. (Somehow the code colours green as a comment, but it should be red as a string. Sorrie for the inconvenience)

[Pgmer]

Before running the execute query statement, take the select statement and try to run in DB so u will come to know where and what is missing...

[sandeepparekh9]

.SelectCommand.CommandText = "SELECT Reference_Number, Year, Title, Surname," + _
"FirstName, Date_of_Birth, Origin, Phone, State_Deployed, Facility_Deployed FROM HEALTHWORKERS" + _
"WHERE DESIGNATION = BASIC MIDWIVES"

a silly mistake.. you forgot a space between "FROM HEALTHWORKERS" AND "WHERE"

JUST change your query to :

.SelectCommand.CommandText = "SELECT Reference_Number, Year, Title, Surname," + _
                "FirstName, Date_of_Birth, Origin, Phone, State_Deployed, Facility_Deployed FROM HEALTHWORKERS " + _
                " WHERE DESIGNATION = 'BASIC MIDWIVES'"

[Netcode]

thanks sandee, but it returned no record. I really don't know where am getting it wrong.I've done this multiple time before but cant figure out why this is different

[sandeepparekh9]

can you show your table's data here??

[Netcode]

Solved. Thanks everyone