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