+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Software Development (https://hackforums.net/forumdisplay.php?fid=118)
+---- Thread: [C#] C# Updating SQL Table - Table not updating (/showthread.php?tid=5417629)
C# Updating SQL Table - Table not updating - siimon00 - Sep 27, 2016
Hello Guys!
I'm trying to update my SQL table in C#. I've been struggling with updating the table for a couple hours now, and I've searched so much, I can't wrap my head around what is wrong.
Code
public class SQLConnect
{
public SQLConnect(string startUp)
{
startupPath = startUp;
connectionSuccesful = false;
OpenConnection();
}
public SqlConnection sqlConnect;
public string startupPath { get; set; }
public bool connectionSuccesful { get; set; }
public bool temp { get; set; }
public void IndtastBeløb(int beløb, string kategori)
{
DateTime time = DateTime.Now;
int iBolig = 0; int iOther = 0; int iTransport = 0; int iLoan = 0; int iMad = 0; int iDiverse = 0;
SqlCommand command = new SqlCommand("SELECT * FROM Entries WHERE dag = @day AND måned = @month AND år = @year", sqlConnect); //
command.Parameters.AddWithValue("@day", time.Day);
command.Parameters.AddWithValue("@month", time.Month);
command.Parameters.AddWithValue("@year", time.Year);
/*command.Parameters.Add("@day", SqlDbType.Int);
command.Parameters["@day"].Value = time.Day;
command.Parameters.Add("@month", SqlDbType.Int);
command.Parameters["@month"].Value = time.Month;
command.Parameters.Add("@year", SqlDbType.Int);
command.Parameters["@year"].Value = time.Year;*/
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
//thing = reader["bolig"].ToString();
//int iBolig = reader.GetInt32(0);
iBolig = (int)reader["bolig"];
iOther = (int)reader["øvrige"];
iTransport = (int)reader["transport"];
iLoan = (int)reader["gæld"];
iMad =(int)reader["mad"];
iDiverse = (int)reader["diverse"];
switch (kategori)
{
case "Bolig":
iBolig += beløb;
break;
case "Øvrige":
iOther += beløb;
break;
case "Transport":
iTransport += beløb;
break;
case "gæld":
iLoan += beløb;
break;
case "mad":
iMad += beløb;
break;
case "diverse":
break;
default:
break;
}
}
reader.Close();
SqlCommand changeRow = new SqlCommand("UPDATE Entries SET bolig=@bolig WHERE dag=@day", sqlConnect); //, øvrige=@øvrige, transport=@transport, gæld=@gæld, mad=@mad, diverse=@diverse " + "WHERE dag=@day AND måned=@month AND år=@year"
changeRow.Parameters.AddWithValue("@bolig", iBolig);
changeRow.Parameters.AddWithValue("@day", time.Day);
/*changeRow.Parameters.AddWithValue("@øvrige", iOther);
changeRow.Parameters.AddWithValue("@transport", iTransport);
changeRow.Parameters.AddWithValue("@gæld", iLoan);
changeRow.Parameters.AddWithValue("@mad", iMad);
changeRow.Parameters.AddWithValue("@diverse", iDiverse);
changeRow.Parameters.AddWithValue("@month", time.Month);
changeRow.Parameters.AddWithValue("@year", time.Year);*/
changeRow.ExecuteNonQuery();
}
else
{
temp = false;
}
}
public void OpenConnection()
{
sqlConnect = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='" + startupPath + @"LuksusDatabase.mdf';Integrated Security=True");
//sqlConnect = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='c:\users\simo8211\documents\visual studio 2015\Projects\LuksusFældenForms\LuksusFældenForms\bin\Debug\LuksusDatabase.mdf';Integrated Security=True");
try
{
sqlConnect.Open();
connectionSuccesful = true;
}
catch
{
connectionSuccesful = false;
}
}
}The focus is on the IndtastBeløb method, im trying to determine if there is an entry in the database for the current date, if there is I want to update that row. Everything in the code works until I'm trying to update with the changeRow.ExecuteNonQuery(); The program doesn't throw an exception or give me any errors, the database simply does not change :)
I hope that someone out there can help me! :)
* - Yoshi - Sep 27, 2016
Have you tried setting breakpoints and check if the variables got the right values?
* - siimon00 - Sep 27, 2016
Hi Yoshi.
Yes I have actually! and all of the variables got the exact values from the database as they should.
if I run it with:
int affectedRows = changeRow.ExecuteNonQuery();
affectedRows even = 1 after the query.
But when I check my database nothing has changed
* - Temploit - Sep 27, 2016
Most libraries require you to do some sort of Commit after an insert query. Look at the example of this page:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.commit(v=vs.110).aspx
* - Ninja - Sep 27, 2016
Code
connectionSuccesful = false;Also, you should really handle that try catch statement better in the OpenConnection function. Make it so it tells you what is wrong with the connection so end users could maybe even know how to fix it, or make it retry, or something. Dont just set a bool to false.
* - some_dude13 - Sep 28, 2016
Are you a 100% sure the update clause used in your tests is valid and would affect an existing row? Are there records in your table with a day equivalent to "today" on the day you run your test? Another note, the code inside your read statement seems to reset almost all variables each iteration, I don't know what you are specifically trying to accomplish, but on first impressions it seems like you want to use iBolig,iOther,iTransport,iLoan,iMad and iDiverse as accumulators, yet their value is changed each iteration, maybe I am wrong depending on your needs.