Trouble connecting to mysql using ASP.NET

  Kiến thức lập trình

I’m working on a basic CRUD app using C# and React and I seem to be having trouble connecting to my MySQL database. When I register a user, I get an error response message:

“message”: “Error creating user: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-manager.users (username, password) VALUES (‘test’, ‘test’)’ at line 1rn

here’s the backend

using MySql.Data.MySqlClient;
using Microsoft.AspNetCore.Builder;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.DependencyInjection;
using System.Data.SqlTypes;
using dotenv.net;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowLocalHost3000", builder =>
    {
        builder.WithOrigins("http://localhost:3000")
               .AllowAnyHeader()
               .AllowAnyMethod()
               .AllowCredentials();
    });
});

DotEnv.Load();
var dbServer = Environment.GetEnvironmentVariable("DB_SERVER");
var dbPort = Environment.GetEnvironmentVariable("DB_PORT");
var dbDatabase = Environment.GetEnvironmentVariable("DB_DATABASE");
var dbUser = Environment.GetEnvironmentVariable("DB_USER");
var dbPassword = Environment.GetEnvironmentVariable("DB_PASSWORD");

var connectionString = $"server={dbServer};port={dbPort};database={dbDatabase};user={dbUser};password={dbPassword};";

builder.Services.AddTransient<MySqlConnection>(_ => new MySqlConnection(connectionString));

var app = builder.Build();

app.UseCors("AllowLocalHost3000");
app.UseRouting();

app.MapGet("/", () => "Hello World!");

app.MapPost("/register", async (User user, MySqlConnection connection) => {
try {
    var q = "INSERT INTO employee_manager.users (`username`, `password`) VALUES (?, ?)";

    await connection.OpenAsync();
    using var cmd = new MySqlCommand(q, connection);
    cmd.Parameters.AddWithValue("@Username", user.Username);
    cmd.Parameters.AddWithValue("@Password", user.Password);
    await cmd.ExecuteNonQueryAsync();

   return Results.Json(new { StatusCode = 201, Message = "User created successfully" });


} catch (Exception ex) {
    return Results.Json(new { StatusCode = 500, Message = $"Error creating user: {ex}" });
}
finally {
    connection.Close();
}
});

app.Run();

and in case it helps, how I’m requesting

 async function registerHandler(e) {
        e.preventDefault();
    
        try {
          const response = await fetch(`${process.env.REACT_APP_SERVER_URL}/register`, {
            method: "POST",
            headers: { "Content-type": "application/json" },
            credentials: "include",
            body: JSON.stringify({ username, password }),
          });
    
          if (response.status === 201) {
            alert("Registration successful! You can now login");
            navigate("/login");
          }
        } catch (err) {
          console.log(err);
        }
      }

I’ve quadruple checked the .env configuration is correct and that the query string works when used directly in the mysql workbench. I’m guessing this means that I’m not connecting to the database properly?

LEAVE A COMMENT