C#•16h ago

SQL overwrites instead off adds

public bool AddNewCustomer(CustomerClass customer) { SqlCommand command = new SqlCommand(); command.CommandText = string.Format("INSERT INTO tblCustomer VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", customer.CTitle, customer.CForename, customer.CSurname, customer.CEmail, customer.CContactNum1, customer.CContactNum2); return ExecuteNonQuery(command); } so when i run this it adds a new customer just fine, i close the programme open it again add new customer it does it but replaces the previously made customer, i have a near identical prodject and run this same sql and it works perfectly anyone know if its a visual studio problem or is their a fix?

19 Replies

Angius•16h ago

Holy SQL injection

Potato PenguinOP•16h ago

SQL injection aint a problem its just a school project

Angius•16h ago

Could it be the database you're using is not persistent? If it's a school project, I would fail it on the grounds of SQL injection 😛

Potato PenguinOP•16h ago

its only alevel we dont learn about that stuff 😅 waht does not persistant mean

Angius•16h ago

Means it gets deleted between individual runs of the application It's not saved anywhere on disk, exists only while the app runs

Potato PenguinOP•16h ago

that sounds probable how would i go about fixing that because it used to work

Angius•16h ago

You'd use a persistent database instead First step would be to figure out what database you're using, though

Potato PenguinOP•15h ago

ive got chat gpt cooking probably shouldnt blindly copy it but we'll see what happens idk what happened but if you want some resolution it kinda works, i have the same problem with other queries that doesnt work but i manually added customers and then ran the system to see if it would do anything and it works, idk why it was getting tripped up and idk if adding things manually will fix the others but thanks anyway

Jimmacle•15h ago

an INSERT literally cannot remove data

Auger•15h ago

I don't see anywhere in your code where you actually connect to a data source that would actually house this information. Usually you do something like:

public bool AddNewCustomer(CustomerClass customer)
{
    // Generally you don't want connection strings in your code
    string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;";
    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "INSERT INTO tblCustomer VALUES(@CTitle, @CForename, @CSurname, @CEmail, @CContactNum1, @CContactNum2)";
        
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            // Add parameters for the CustomerClass properties
            command.Parameters.AddWithValue("@CTitle", customer.CTitle);
            command.Parameters.AddWithValue("@CForename", customer.CForename);
            command.Parameters.AddWithValue("@CSurname", customer.CSurname);
            command.Parameters.AddWithValue("@CEmail", customer.CEmail);
            command.Parameters.AddWithValue("@CContactNum1", customer.CContactNum1);
            command.Parameters.AddWithValue("@CContactNum2", customer.CContactNum2);
            
            // Open the connection
            connection.Open();
            
            // Execute the command and get rows affected
            int rowsAffected = command.ExecuteNonQuery();
            
            return rowsAffected > 0;
        }
    }
}

public bool AddNewCustomer(CustomerClass customer)
{
    // Generally you don't want connection strings in your code
    string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;";
    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "INSERT INTO tblCustomer VALUES(@CTitle, @CForename, @CSurname, @CEmail, @CContactNum1, @CContactNum2)";
        
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            // Add parameters for the CustomerClass properties
            command.Parameters.AddWithValue("@CTitle", customer.CTitle);
            command.Parameters.AddWithValue("@CForename", customer.CForename);
            command.Parameters.AddWithValue("@CSurname", customer.CSurname);
            command.Parameters.AddWithValue("@CEmail", customer.CEmail);
            command.Parameters.AddWithValue("@CContactNum1", customer.CContactNum1);
            command.Parameters.AddWithValue("@CContactNum2", customer.CContactNum2);
            
            // Open the connection
            connection.Open();
            
            // Execute the command and get rows affected
            int rowsAffected = command.ExecuteNonQuery();
            
            return rowsAffected > 0;
        }
    }
}

That SqlConnection piece being the actual part that connects to a database, which would run your SqlCommand

Jimmacle•15h ago

return ExecuteNonQuery(command); this is odd i guess there's another method in that class that may handle the connection?

Auger•15h ago

Yeah I thought that returned an int

Jimmacle•15h ago

look closely

Auger•15h ago

Oh private method

Jimmacle•15h ago

it's called ExecuteNonQuery but it's defined in their own class and takes the SqlCommand as a parameter :LUL:

Auger•15h ago

I guess I need more of the implementation and table schema. Do you have a non-incrementing primary key? If so, is CTitle the primary key?

Jimmacle•15h ago

maybe more detail as to how you're determining that it is "replacing" old data for all we know it's a bug on the read side that doesn't account for duplicates

Auger•15h ago

Idk if it can even update data in the same row if the primary key is the same, but that's all I can think of with what I've been given.

Jimmacle•14h ago

an insert will never update, it will give you an error if you try to insert a row with a PK that already exists basically, the original description of the problem can't possibly be right and there's something else going on

Gaming

Programming

SQL overwrites instead off adds

Did you find this page helpful?