Monday, March 19, 2012

Line 1: Incorrect syntax near =

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?

ThanksAt 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|||Thanks Simon , your quick glance worked, the space was the problem.|||Assimalyst (c_oxtoby@.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, esquel@.sommarskog.se

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

No comments:

Post a Comment