1

I am attempting to run a query using a parameter in C#. I am getting an issue where no rows are being returned. I am pulling the sql from a file and putting it into the command text. When the query (a SELECT statement) is run, no results are returned. I have confirmed that the result is in my database and that the query is correct (after replacing the param) by running it normally. 

            conn.Open();

            //create the command
            var command = conn.CreateCommand();

            //Read sql from file
            FileInfo file = new FileInfo("SQL/GetPage.sql");
            string script = file.OpenText().ReadToEnd();


            command.CommandText = script;

            command.Parameters.AddWithValue("?PageID", PageName);
            command.Prepare();

            MySqlDataReader rdr = command.ExecuteReader();

            rdr.Read();

SQL: 

SELECT * FROM `Page` WHERE PageID = '?PageID'

I have tried with both the prepare and without it. I have no clue why it is not working. Also, I am only expecting one result max (PageID is unique), so that is why it isn't in a loop. I also know my connection is good because I hardcoded the query without the where clause and it worked fine.

Please let me know if anyone has any suggestions.

Thanks

DaDuStMaN20
  • 137
  • 2
  • 11
  • Your query expects an ID and you provide a Name? – juergen d Nov 20 '18 at 07:59
  • I hate to see you suffer like this :-) Grab [QueryFirst](https://marketplace.visualstudio.com/items?itemName=bbsimonbb.QueryFirst), paste your sql in a QueryFirst template (a .sql file just like you have already). All this reader, parameter, connection and command code will be generated for you. You just need to call the generated ExecuteScalar() method and you'll have your integer. Disclaimer: I wrote QueryFirst. – bbsimonbb Nov 20 '18 at 10:03

1 Answers1

0

Read() just advances the DataReader to the next record (this is why it used in a loop). You need to extract the data from this record

while (rdr.Read())
{
    int i = rdr.GetInt32(0);
    string s = rdr.GetString(1);
}
Guy
  • 46,488
  • 10
  • 44
  • 88