Line 1: Incorrect syntax near '='

[Assimalyst]

Hi,

I'm getting the above error when i try to fill a Dataset through a
dataAdapter.

I presume it is to do with the sql statement. Below is the relevant
code:

string strPntUnitID = patientCodeLbl.Text;
string strPntFName = fNameLbl.Text;
string strPntLName = lNameLbl.Text;

// Create DataAdapter & Dataset
SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
doctorNo FROM tblPatient" +
"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
strPntFName +"')"+
"AND (pntLName = '"+ strPntLName +"')", conn);

DataSet dsDocNoToPnt = new DataSet();

// Create command builder, automatically generates the update commands
SqlCommandBuilder pntCmd = new SqlCommandBuilder(daRelateDocToPnt);

// Set the MissingSchemaAction property to AddWithKey because Fill will
not cause primary
// key & unique key information to be retrieved unless AddWithKey is
specified.
daRelateDocToPnt.MissingSchemaAction = MissingSchemaAction.AddWithKey;

// Use dataAdapter to fill DataSet
daRelateDocToPnt.Fill(dsDocNoToPnt, "Patient");

// place intDocNo into the dataset schema
dsDocNoToPnt.Tables["Patient"].Rows[0]["doctorNo"] = "intDocNo";

// Update The Database
daRelateDocToPnt.Update(dsDocNoToPnt, "Patient");

Can anyone spot the problem?

Thanks

[Simon Hayes]

At a quick glance, it seems that you have no space between tblPatient
and WHERE, but without seeing the actual command, it's hard to say. If
you build a SQL string dynamically, it's a good idea to provide a debug
mechanism - perhaps you can build up the string first and display it,
then instantiate the adapter using the string?

You might also want to consider writing a stored procedure to return
the results, and pass the ID and names as parameters.

Simon

[Assimalyst]

Thanks Simon , your quick glance worked, the space was the problem.

[Erland Sommarskog]

Assimalyst (c_******@hotmail.com) writes:

I presume it is to do with the sql statement. Below is the relevant
code:

string strPntUnitID = patientCodeLbl.Text;
string strPntFName = fNameLbl.Text;
string strPntLName = lNameLbl.Text;

// Create DataAdapter & Dataset
SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
doctorNo FROM tblPatient" +
"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
strPntFName +"')"+
"AND (pntLName = '"+ strPntLName +"')", conn);

Rather than building the entire command this way, use parameterised
commands:

"SELECT patientNo, doctorNo FROM tblPatient " +
"WHERE (pntUnitID = @PntUnitID AND (pntFName = @strPntFName " +
"AND (pntLName = @PntLName "

The use the parameters collection on the command object to define the
parameter.

If you wonder why, try your current code with someone whose last name
is O'Brien.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp