468,272 Members | 2,041 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

WebMethod returning a Dataset

Hi,
Firstly, I will apologise now if I have posted in the wrong discussion
group. Please let me know if I have for future reference. Below is the code
to a WebMethod that is querying an Access 2003 database, saving the results
as a dataset and returning the dataset.

[WebMethod(Description = "Method to take in assessment_id as input" +
" and send the assessment data to the aap web website to populate" +
" its fields")]

public DataSet findAAPAssessment(string assessmentID)
{
//Establish a connection with the database
//Assign the type and destination of the database being queried
string source = "Provider = Microsoft.JET.OLEDB.4.0;data source =
C:\\leedsuni\\mscproject\\seconddemo\\snomedepr.md b;";

//Create a new connection and use the string "source" to assign
//a value to the connection
OleDbConnection o = new OleDbConnection(source);

//Open Connection
o.Open();

//Initialise string representing the query to search the database
string query = "SELECT * FROM abdominalpainassessmentrecords WHERE
assessment_id = " + assessmentID + ");

//Create a dataset and load assessment data into database
//For creating a dataset with the results of the query
OleDbDataAdapter assessmentTableAdapter = new
OleDbDataAdapter(query, source);
DataSet assessmentDataSet = new DataSet();
assessmentTableAdapter.Fill(assessmentDataSet,
"abdominalpainassessmentrecords");

//Close connection
o.Close();

return assessmentDataSet;

}

However I get a SOAP exception error at the client side when client attempts
to consume the webmethod by providing a string value assessmentID. I should
point it out that the above method works perfectly if I explicitly assign a
value to my webMethod call i.e "3" instead of assessmentID.

protected void getAssessmentData_Click(object sender, EventArgs e)
{

string assessmentID = assessmentIDTextBox.Text;
DataSet assessmentDataSet =
aapassessmentdata.findAAPAssessment(assessmentID);

}

Thank you kindly and I look forward to your replies

Khurram
Jul 18 '06 #1
3 2743
Hi,

What does the error message says?

From inspecting your code it seems that the problem is here:

string query = "SELECT * FROM abdominalpainassessmentrecords WHERE
assessment_id = " + assessmentID + "";

you should enclose in single quotes the assesmentID:

string query = "SELECT * FROM abdominalpainassessmentrecords WHERE
assessment_id = '" + assessmentID + "'";

BTW, in your code you have a ) at the end , I hope this is a type cause
otherwise it would not compile
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Khurram" <kn*****@gmail.comwrote in message
news:E0**********************************@microsof t.com...
Hi,
Firstly, I will apologise now if I have posted in the wrong discussion
group. Please let me know if I have for future reference. Below is the
code
to a WebMethod that is querying an Access 2003 database, saving the
results
as a dataset and returning the dataset.

[WebMethod(Description = "Method to take in assessment_id as input" +
" and send the assessment data to the aap web website to populate"
+
" its fields")]

public DataSet findAAPAssessment(string assessmentID)
{
//Establish a connection with the database
//Assign the type and destination of the database being queried
string source = "Provider = Microsoft.JET.OLEDB.4.0;data source =
C:\\leedsuni\\mscproject\\seconddemo\\snomedepr.md b;";

//Create a new connection and use the string "source" to assign
//a value to the connection
OleDbConnection o = new OleDbConnection(source);

//Open Connection
o.Open();

//Initialise string representing the query to search the database
string query = "SELECT * FROM abdominalpainassessmentrecords WHERE
assessment_id = " + assessmentID + ");

//Create a dataset and load assessment data into database
//For creating a dataset with the results of the query
OleDbDataAdapter assessmentTableAdapter = new
OleDbDataAdapter(query, source);
DataSet assessmentDataSet = new DataSet();
assessmentTableAdapter.Fill(assessmentDataSet,
"abdominalpainassessmentrecords");

//Close connection
o.Close();

return assessmentDataSet;

}

However I get a SOAP exception error at the client side when client
attempts
to consume the webmethod by providing a string value assessmentID. I
should
point it out that the above method works perfectly if I explicitly assign
a
value to my webMethod call i.e "3" instead of assessmentID.

protected void getAssessmentData_Click(object sender, EventArgs e)
{

string assessmentID = assessmentIDTextBox.Text;
DataSet assessmentDataSet =
aapassessmentdata.findAAPAssessment(assessmentID);

}

Thank you kindly and I look forward to your replies

Khurram

Jul 18 '06 #2
Thank you Ignacio for your reply. You wrote:
you should enclose in single quotes the assesmentID:

string query = "SELECT * FROM abdominalpainassessmentrecords WHERE
assessment_id = '" + assessmentID + "'";
I tried that with the rest of the code unchanged and it gave me a Data Type
Mismatch Error. I believe that is because my assessment_id field in the
Access 2003 database is of type Number. I was told not to use single quotes
if the database field type is Number and I'm using strings.

"Ignacio Machin ( .NET/ C# MVP )" wrote:

What does the error message says?
My original error message at the Client side was a SOAP Exception. I'm not
sure what part of the error you require so I am printing the whole message:

System.Web.Services.Protocols.SoapException: Server was unable to process
request. ---System.Data.OleDb.OleDbException: Syntax error (missing
operator) in query expression 'assessment_id ='.
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior)
at
System.Data.OleDb.OleDbCommand.System.Data.IDbComm and.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at AccessSpine.findAAPAssessment(String assessmentID) in
c:\Inetpub\wwwroot\Spinedatabase\App_Code\AccessSp ine.cs:line 45
--- End of inner exception stack trace ---
Also you wrote:
BTW, in your code you have a ) at the end , I hope this is a type cause
otherwise it would not compile
Yes that was just a typo. Both client and Web service codes compile
successfully.

Thank you again

Khurram
Jul 18 '06 #3
Figured out the problem.

My client website on load is pickingup queryString values from the URL and
populating the required fields. However, everytime I refresh that page,
those values are being picked up. If no values then "0" values are replacing
the current ones. That is why my assessment_id seemed to be missing
everytime.

regards
Khurram
Jul 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mike P | last post: by
2 posts views Thread by Sumaira Ahmad | last post: by
1 post views Thread by Inas Hafez | last post: by
15 posts views Thread by Joseph Geretz | last post: by
reply views Thread by Looch | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.