You have an error in your SQL syntax check the manual that corresponds to your MariaDB server version for the right syntax to use near '[Id]

I have an error problem with this case. I would like to connect MySql with visual studio. When I run my program on browser, it says, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[Id] ,[FirstName] ,[LastName] ' at line 1"

and here is the source error:

and Here is the my code: POST: /Account/Login

    [HttpPost]
    [AllowAnonymous]
    [ValidateAntiForgeryToken]
    public ActionResult Login(LoginModel model, string returnUrl)
    {

        // if (ModelState.IsValid && WebSecurity.Login(model.UserName, model.Password, persistCookie: model.RememberMe))
        // {
        //    return RedirectToLocal(returnUrl);
        // }

        // If we got this far, something failed, redisplay form
        string query = @"
                 SELECT [Id]
                ,[FirstName]
                ,[LastName]
                ,[MiddleName]
                ,[FullName]
                ,[Usercol]
                ,[Title]
                ,[PersonalEmailAddress]
                ,[OfficialEmailAddress]
                ,[JobTitle]
                ,[HomePhone]
                ,[MobilePhone]
                ,[OfficePhone]
                ,[PhotoUrl]
                ,[UTCConversionTimeZoneCode]
                ,[APIToken]
                ,[Country]
                ,[UserName]
                ,[Password]
                ,[CRMUserId]
            FROM User WHERE UserName = @UserName AND Password = @Password
            ";

        UserModels UserModel = null;

        using (IDbConnection db = new MySqlConnection(ConfigurationManager.ConnectionStrings["CRMPORTALSQLCONN"].ConnectionString))
        {
            UserModel = (UserModels)db.Query<UserModels>(query, new
            {
                @UserName = model.UserName,
                @Password = model.Password
            }).FirstOrDefault();
        }        
        ModelState.AddModelError("", "The user name or password provided is incorrect.");
        return View(model);
    }

I would really appreciate if there is someone who could tell me which part I do wrong. Regards

Answers


If I understand you correctly, you are using the syntax of SQL Server Database not MySQL.

Rewrite your SQL query for MySQL as follows:

SELECT   `Id`
         ,`FirstName`
         ,`LastName`
         ,`MiddleName`
         ,`FullName`
         ,`Usercol`
         ,`Title`
         ,`PersonalEmailAddress`
         ,`OfficialEmailAddress`
         ,`JobTitle`
         ,`HomePhone`
         ,`MobilePhone`
         ,`OfficePhone`
         ,`PhotoUrl`
         ,`UTCConversionTimeZoneCode`
         ,`APIToken`
         ,`Country`
         ,`UserName`
         ,`Password`
         ,`CRMUserId`
 FROM `User` WHERE `UserName` = @UserName AND `Password` = @Password;

Replace [ and ] to back ticks(`)


Need Your Help

NullPointerException triggering while creating custom Adapter

android android-fragments gridview adapter

I'm trying to create an activity with a fragment. The fragment holds a gridview that contain "Movie Objects". Each object has to be represented using an image in the gridview. The image is a url th...

How to return custom JSON after successful sign_in in devise_token_auth gem?

ruby-on-rails json devise

I am creating a rails API using devise_token_auth gem. I want not just the token in header that this gem returns after successful sign_in but also some table associated with user.