Data from viewmodel doesn't go into database C# MVC

Question

The model in the parameter has values all the way to the DAL method where the query is but somehow nothing changes in the database. Anyone got any clue why? Also no errors occurred. Controller:

public ActionResult AddCar(CarModel car)
{
        if(ModelState.IsValid)
        {
            u.AddCar(new DTO.CarDTO(car.Color, car.Type, car.NumberOfSeats, car.Price, car.MaxKilometres, car.Addition, car.ModelID, car.License_Plate, car.Fueltype, car.ReservationStatus));
            return RedirectToAction("Index", "Home");
        }
        return View();
}

Logic:

public void AddCar(CarDTO c)
{
    carDAL.AddCar(new CarDTO(c.Color, c.Type, c.NumberOfSeats, c.Price, c.MaxKilometres, c.Addition, c.ModelID, c.License_Plate, c.Fueltype, c.ReservationStatus));
}

Interface layer:

public interface ICarDAL<CarDTO>
{
    public void AddCar(CarDTO c) { }
}

DAL, class does have connectionstring:

public void AddCar(CarDTO c)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        string query = "INSERT INTO [dbo].[Car](Price, Addition, License_Plate, MaxKm, Type, Color, NumberOfSeats, Fueltype, reservationStatus, ModelID) " +
            "VALUES(@Price, @Addition, '@LicensePlate', @MaxKm, '@Type', '@Color', @NumberOfSeats, @FuelType, @Reserved, @ModelID)";

        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Price", c.Price);
            cmd.Parameters.AddWithValue("@Addition", c.Addition);
            cmd.Parameters.AddWithValue("@LicensePlate", c.License_Plate);
            cmd.Parameters.AddWithValue("@MaxKm", c.MaxKilometres);
            cmd.Parameters.AddWithValue("@Type", c.Type);
            cmd.Parameters.AddWithValue("@Color", c.Color);
            cmd.Parameters.AddWithValue("@NumberOfSeats", c.NumberOfSeats);
            cmd.Parameters.AddWithValue("@FuelType", c.Fueltype);
            cmd.Parameters.AddWithValue("@ReservationStatus", c.ReservationStatus);
            cmd.Parameters.AddWithValue("@ModelID", c.ModelID);
        }
    }
}

Found the error but I still dont know how to fix it: https://i.stack.imgur.com/Stdh5.jpg

Solution: First of all I needed to use ExecuteNonQuery() to make the query actually do something...I thought SqlCommand already executed it for you. Secondly my password consisted out of ************ which I thought it wouldn't matter but I guess it did.

You're not executing the command. So `cmd.ExecuteNonQuery();` after assigning your params would work. Also on another note use `Add` and specify the data type *not* `AddWithValue` as the values would be inferred and may not be what you need. — Trevor, Nov 10 '19 at 17:31
@Çöđěxěŕ So I put that code in the cmd using, so right under the last `cmd.Parameters.AddWithValue();`. But now I get 'ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.' — zhrgci, Nov 10 '19 at 18:00
That's because you haven't opened the connection. Have you tried to follow an ADO.NET tutorial? Or searched for examples on the internet? They are pretty trivial to find. Here's a simple one: https://stackoverflow.com/a/40786473/5947043 — ADyson, Nov 10 '19 at 18:44
P.s. your image is illegible. Paste the text of any errors instead. — ADyson, Nov 10 '19 at 18:45
no I am not following ADO.NET because I'm trying to learn ASP.NET now...or is that the same? Also is it still illegible if you zoom in? — zhrgci, Nov 10 '19 at 18:49
If I do like your links say with open and closing the connection at the end it gives an error: _'A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)'_ — zhrgci, Nov 10 '19 at 18:54
Sounds like you need to make a secure connection to the database and it cannot. You perhaps need to check the server settings. But it's progress, better than no connection at all — ADyson, Nov 10 '19 at 21:13
P.s. ADO.NET is one of the ways you can connect to a database from .NET programs. So you can use it as part of an asp.net application, or part of a desktop app or console app, or whatever. — ADyson, Nov 10 '19 at 21:14
@ADyson how do i check server settings? And what would i want to change? Does it have anything to do with the class being public or not static? — zhrgci, Nov 11 '19 at 09:31
No. That's completely irrelevant. Did you try googling your error message? There are several previous questions on this site about that error, as well as on other sites. For example [this one](https://stackoverflow.com/questions/43597294/error-occurred-during-the-pre-login-handshake) or [this one](https://stackoverflow.com/questions/3270199/a-connection-was-successfully-established-with-the-server-but-then-an-error-occ) and several others all have a lot of useful suggestions which might be relevant to your situation. Please do some searching and try out the possible solutions. — ADyson, Nov 11 '19 at 09:38
@ADyson I sincerely thank you for helping me. I didn't know the query wouldn't execute if I didn't use `ExecuteNonQuery()`. Also the problem was that my password consisted of only ********* instead of the actual password, but thanks for helping :D — zhrgci, Nov 11 '19 at 10:06

score 1 · Answer 1 · answered Nov 11 '19 at 05:09

1

public void AddCar(CarDTO c)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        if (con.State==ConnectionState.Closed)
            {                      
                con.Open();   
             }
         [Your code]
         con.Close();
    }
 }

answered Nov 11 '19 at 05:09

erdi yılmaz

352
1
4
15

It says "SqlException: Login failed for user 'mydatabase'" – zhrgci Nov 11 '19 at 09:30
Check your connectionstring is true. – erdi yılmaz Nov 11 '19 at 10:41
and check your user can access your database – erdi yılmaz Nov 11 '19 at 10:48

cklimowski · Answer 2 · 2019-11-11T17:48:14.880

-2

You may need to use a "save" function, I'm not seeing anywhere in your code where the SQL command is actually executed, I see it being built, but I've used it in the past with something similar like cmd.Execute()

edited Nov 11 '19 at 17:48

answered Nov 10 '19 at 17:25

cklimowski

589
6
21

I didn't downvote anything so i dont know. Sorry how can you see if you use entity framework or not? – zhrgci Nov 10 '19 at 17:56
So what you're saying is `using (SqlCommand cmd = new SqlCommand(query, con))` isn't going to execute the query? I thought it would... – zhrgci Nov 10 '19 at 18:05
2

Don't know about anyone else but I downvoted this because it's self-evident from the code shown that it isn't using entity framework – ADyson Nov 10 '19 at 18:46
Thanks for letting me know, I edited my response. I think my answer is applicable, the actual SQL command is not being executed without the Execute function. – cklimowski Nov 11 '19 at 17:49

Data from viewmodel doesn't go into database C# MVC

2 Answers2