SELECT query in c#

Question

Please tell me why the following code gives an exception

Cannot convert from type 'string' to 'int'

Code:

public static string Select (OleDbConnection connection, string Name, int ID)   
{
    DataTable data_table = new DataTable();
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT Name FROM Student WHERE ID = ID", connection);
    adapter.Fill(data_table);
    string row = data_table.Rows["Name"][ID].toString();
    return row;
}

Your query is not parameterised so the ID parameter for the method is not being used. it's literally running the query `SELECT Name FROM Student WHERE ID = ID` — Matt, Sep 24 '19 at 17:41
ok, i get it but is there anything else by which I can pass intiger index and string index? I need string index as my code depends on it — qqq, Sep 24 '19 at 18:01
You can't index ROWS by a string, no.. Rows can only be indexed by integer (c# arrays are not like javascript arrays) But once you have retrieved a row by integer you can retrieve a column by string. i.e. it's legal to call `string name = data_table.Rows[0]["Name"].ToString();` - it will return the Name of the first person in the Rows collection. — Caius Jard, Sep 24 '19 at 18:03

score 2 · Answer 1 · answered Sep 24 '19 at 18:18

Your line of code:

string row = data_table.Rows["Name"][ID].toString();

causes the error "cannot convert from string to integer" because a DataTable's .Rows collection expects an integer index, and you passed a string ("Name")

In other words think of datatable.Rows as an array - you have to index it with a number, not a string. Because you passed a string and it cannot be automatically converted to a number you get a "cannot convert" error

As an example to access the first row you would data_table.Rows[0], for the 10th row it would be .Rows[9]

To retrieve the name of the first person in the table:

string name = data_table.Rows[0]["Name"].ToString();

Your code has other issues:

toString(); needs a capital T
You don't make any attempt to use the parameters passed in to refine the search, so your query downloads all the rows from the DB. Perhaps it should have looked like:

    public static string SelectNameFromId(OleDbConnection connection, int id)   
    {
        DataTable data_table = new DataTable();
        OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT Name FROM Student WHERE ID = ?", connection);
        adapter.SelectCommand.Parameters.AddWithValue("p1", id); //positional indexing
        adapter.Fill(data_table);
        string n = data_table.Rows[0]["Name"].ToString();
        return n;
    }

Could you plz tell me why you used the line: ter.SelectCommand.Parameters.AddWithValue("p1", id); and what does that mean? my code does not get any errors without this line! — qqq, Sep 25 '19 at 09:45
I converted the SQL to something that would perform a lookup by ID, and I put a parameter placeholder into the sql `WHERE id = ?` - then I modify the `.Parameters` collection of the `.SelectCommand` so that I actually add a value for the parameter. `AddWithValue` expects a parameter name (can be anything) and a parameter value - `id`. The ? will become the value with the SQL is run. The order of calling AddWithValue is important. If the SQL has two `?` parameters then you must call AddWithValue in order, passing values for each ? in the same order. This is the safe way to pass data to SQL — Caius Jard, Sep 25 '19 at 10:13
Remember that I'm guessing here based on what I would have done - you've not made any attempt to explain why your method takes an ID and a Name parameter and doesn't use either of them, and looks like it's trying to returns a name. **IT'S LARGELY GUESSWORK** for me and you'll still have to closely inspect what I've written, understand how it works and ensure it does what you're expecting. — Caius Jard, Sep 25 '19 at 10:27

SELECT query in c#

1 Answers1