How to get data from db in c#?

Question

I need get data from sqlite database, but I get this error:

System.InvalidCastException: 'Specified cast is not valid.'

Am I changing type of data in a bad way?

conn.Open();
string stm = new CustomQueries().GetUsersByName(this.searchedName, this.searchedSurname);

SQLiteCommand cmd = new SQLiteCommand(stm, conn);
SQLiteDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    this.personResults.Add(new Person(
                    (int)rdr["Id"],
                    (int)rdr["School_id"],
                    (int)rdr["Role_id"],
                    (string)rdr["Name"],
                    (string)rdr["Surname"],
                    (int)rdr["Study_year"],
                    (string)rdr["Identification_number"],
                    (string)rdr["Address"],
                    (string)rdr["Phone"],
                    (string)rdr["Email"],
                    (int)rdr["Age"],
                    (string)rdr["Birth_date"],
                    (string)rdr["Year_letter"]
                    ));
}

conn.Close();

My database structure is same as fields in code. Columns are of type Text or int in the database - again same as type in ()

edit> Query:

 public string GetUsersByName(string name, string surname)
        {
            return "select * from user where name LIKE '%" + name + "%' and surname LIKE '%" + surname + "%'";
        }

DB User table structure

`return "select * from user where name LIKE '%" + name + "%' and surname LIKE '%" + surname + "%'";` Don't do that - https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . — mjwills, Jan 17 '21 at 10:46
The code you show, should not produce any problems, because only text columns are nullable. Please show also the code of the `new Person(...)` constructor. Maybe you are doing some casts there? — derpirscher, Jan 17 '21 at 10:50
@derpirscher `null` and `dbnull` aren't the same - hence my question. — mjwills, Jan 17 '21 at 10:56
If you think the error is in the cast of the columns, put the various columns in variables, like `int id = (int)rdr["Id"]; int schoolId = (int)rdr["School_id"]; ...`. In this way you'll see exactly where the problem is. — xanatos, Jan 17 '21 at 11:04
I'd suggest using Dapper to avoid this kind of manual mapping. — mjwills, Jan 17 '21 at 11:43

score 0 · Accepted Answer · answered Jan 17 '21 at 10:58

0

you are trying to convert a value or an object to its string representation by using a casting operator . When you execute the program you will get " System.InvalidCastException : Unable to cast object of type 'System.Int32' to type 'System.String'."

The ToString() method is defined by the Object class and therefore is either inherited or overridden by all managed types. So, here we call its ToString method because it successfully covert an instance of any type to its string representation .

(int)rdr["Id"] Here is the problem. you can use Convert.ToInt32(rdr["Id"]) to fix your problem.

answered Jan 17 '21 at 10:58

MD. RAKIB HASAN

3,670
4
22
35

Yes thsi fix the problem thx – Young L. Jan 17 '21 at 11:05
@YoungL. This would _not_ fix the issues for int - but it will (kind of) for strings that are `NULL` in the database (although @Rakib's explanation of _why_ is wrong). – mjwills Jan 17 '21 at 11:08

How to get data from db in c#?

1 Answers1