Web service

Trouble with a select statement. Newish to c#.

A gridview holds information for a forum, the forum has a column which displays a hyperlink. The user can click on this hyperlink and be redirected to another page where the comments about that topic can be displayed.

However all the comments are being displayed. Can anyone help?

Topic Page

<asp:HyperLinkField DataNavigateUrlFormatString="~/topic.aspx?TopicID={0}" NavigateUrl="~/topic.aspx" Text="Comments" DataNavigateUrlFields="TopicID" />

Comments Page

localhost.Topic topic = new localhost.Topic();
datasource = myws.GetDataSetForum2("SELECT * FROM Comment WHERE TopicID = '""'");
GridView1.DataSource = datasource;
GridView1.DataBind();

The select statement is wrong but dont know what to do. 

Hi,

You got it right. You are mistaking in query.

In Select query you have to pass topicID which is you are passing from querystring.

First you fetch Topic ID from query string.

Then type your query like this :

string variableName = QueryString["TopicID"].ToString();

GridView1.DataSource = BLogic.GetCategory("Select * from Test where TopicID= '"+varibleName +"' ");

OR

localhost.Topic topic = new localhost.Topic();

datasource = myws.GetDataSetForum2("SELECT * FROM Comment WHERE TopicID ="+GridView1.Rows.Count);

GridView1.DataSource = datasource; GridView1.DataBind();

When trying this, I am getting an error. This might be a stupid question but how do i fetch a query string?

u can fetch querystring data like this.

string topicId = Request.QueryString["TopicID"].ToString();

then use ur topicId variable in select query like this.

datasorce = myws.GetDataSetForum2("select * from comment where TopicID="+topicId);

I am getting one error:

No overload for method 'GetDataSetForum2' takes '1' arguments

I have looked on the web and what people are saying is that something is missing but i dont know what.

you have to pass argument for that method.

when you r defing tht method

GetForum(string myString)

This is what I have

localhost.Topic topic = new localhost.Topic();

string topicId = Request.QueryString["TopicID"].ToString();

datasource = topic.GetDataSetForum2("SELECT * FROM Comment WHERE TopicID =" +topicId);

GridView1.DataSource = datasource;

GridView1.DataBind();

Could this problem have anything to do with the select string within web service?

As I have just noticed that I have no query stating display all (web service) and another (application) stating select all where...

Look @ this line.

datasource = topic.GetDataSetForum2("SELECT * FROM Comment WHERE TopicID =" +topicId);

Now you are having GetDataSetForum2 Method in Topic Class. But there you might not have passed correct no of arguments(parameteres).

Definition of GetDataSetForum2 should look like this.

public DataSet static GetDataSetForum2(string myQuery)

{

//ADO.NET code to fetch code. 

} 

And then you can pass string to it. So first store your Query in String. Like this.

string myQuery = "SELECT * FROM Comment WHERE TopicID =" +topicId;

then pass myQuery to your method.

datasource = topic.GetDataSetForum2(myQuery);

    [WebMethod]
    public DataSet GetDataSetForum2()
    {
        DataSet datasource = new DataSet();
        string database = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/topic.mdb;Persist Security Info=True";
        string queryStr = "Select * from Comment";
        OleDbConnection myConn = new OleDbConnection(database);
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(queryStr, myConn);
        myConn.Open();
        myDataAdapter.Fill(datasource, "Comment");
        myConn.Close();
        return datasource;
    }

string topicId = Request.QueryString["TopicID"].ToString();

string id = "SELECT * FROM Post WHERE ForumID='" + topicId + "'"; 

datasource = topic.GetDataSetForum2(id);

GridView1.DataSource = datasource;

GridView1.DataBind();

topicid is a int not a string if that makes any difference.

So does a query string need to be within the webservice? I have noticed that there are two querystrings,however if I remove the querystring from the web service it doesnt display anything, as its not querying the table I think? I added the querystring which is located within the website however it stated that the topicid could not be found, so I added that but then it didnt like the response statement.

Thank you for your help 

Hi JohnnyJ,

JohnnyJ:

[WebMethod] public DataSet GetDataSetForum2() { DataSet datasource = new DataSet(); string database = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/topic.mdb;Persist Security Info=True"; string queryStr = "Select * from Comment"; OleDbConnection myConn = new OleDbConnection(database); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(queryStr, myConn); myConn.Open(); myDataAdapter.Fill(datasource, "Comment"); myConn.Close(); return datasource; }

So you need to change your webmethod to:

    [WebMethod]
    public DataSet GetDataSetForum2(string id)
    {
        DataSet datasource = new DataSet();
        string database = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/topic.mdb;Persist Security Info=True";
        string queryStr = "Select * from Comment where topicid = "+id;
        OleDbConnection myConn = new OleDbConnection(database);
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(queryStr, myConn);
        myConn.Open();
        myDataAdapter.Fill(datasource, "Comment");
        myConn.Close();
        return datasource;
    }