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
Member Avatar for Unhnd_Exception

Try adding single quotations around 'Basic MidWives'

Still the same error

Member Avatar for 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.

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

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.

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)

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...

.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'"

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

can you show your table's data here??

Solved. Thanks everyone

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.