Input string was not in a correct format. using MYSQL

Question

I had a code that was working the past few weeks and am getting an ERROR "Input string was not in a correct format"

NB:(MVC Asp.net)

the view gets data from the Razor page URL and Execute a query

the URL is like this: https://localhost:44348/Devices/Details/5?typeName=Dongle

and the following the View code :

    public ActionResult Details(string typeName, int? id)
    {
        var sql = "SELECT A.*," +
             " COALESCE(ps.first_name, '') as firstname," +
             " COALESCE(ps.last_name, '') as lastname," +
             " COALESCE(p.program_name, '') as program_Name, " +
             " COALESCE(l.loan_date, '') as loan_date, " +
             " COALESCE(l.return_date, '') as return_date" +
             " FROM devices A" +
             " LEFT JOIN device_loans l on l.device_id = A.device_id" +
             " LEFT JOIN persons ps on ps.person_id = l.person_id" +
             " LEFT JOIN programs p on A.program_id = p.program_id" +
             " WHERE A.device_type = '" + typeName + "' and p.program_id = "+ id +";";

        var devices = _context.DeviceDetails
            .FromSqlRaw(sql)
            .ToList();

        return View(devices);
    }

I have tried Passing parameters with parameter placeholders but still not working

please help.

Please use parameterized sql command: learn.microsoft.com/en-us/dotnet/api/… — Indrit Kello
– Indrit Kello, Commented Jun 1, 2021 at 8:53

youbl · Accepted Answer · 2021-06-01 08:13:27Z

1

Because your parameter id: is nullable, you need combine sql like:

var sql = "SELECT A.*," +
     " COALESCE(ps.first_name, '') as firstname," +
     " COALESCE(ps.last_name, '') as lastname," +
     " COALESCE(p.program_name, '') as program_Name, " +
     " COALESCE(l.loan_date, '') as loan_date, " +
     " COALESCE(l.return_date, '') as return_date" +
     " FROM devices A" +
     " LEFT JOIN device_loans l on l.device_id = A.device_id" +
     " LEFT JOIN persons ps on ps.person_id = l.person_id" +
     " LEFT JOIN programs p on A.program_id = p.program_id" +
     " WHERE A.device_type = '" + typeName + "'";
     
if(id!=null){
 sql += " and p.program_id = "+ id +";";
 }

BTW: your code has SQL injection risk

answered Jun 1, 2021 at 8:13

youbl

1441 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

youbl Over a year ago

if you still want combine sql, you must replace single quot like : typeName = typeName.Replace("'", "''");

Collectives™ on Stack Overflow

Input string was not in a correct format. using MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related