更新SQL Server表 - 表未更新
问题描述:
我试图从C#更新我的SQL Server表。我一直在努力更新桌子几个小时,现在我已经搜索了很多东西,我无法将自己的头围绕在错误的问题上。更新SQL Server表 - 表未更新
public class SQLConnect
{
public SQLConnect(string startUp)
{
startupPath = startUp;
connectionSuccesful = false;
OpenConnection();
}
public SqlConnection sqlConnect;
public string startupPath { get; set; }
public bool connectionSuccessful { 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 [email protected] WHERE [email protected]", sqlConnect); //, ø[email protected]øvrige, [email protected], gæ[email protected]æld, [email protected], [email protected] " + "WHERE [email protected] AND må[email protected] AND å[email protected]"
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();
connectionSuccessful = true;
}
catch
{
connectionSuccessful = false;
}
}
}
的重点是IndtastBeløb方法,即时试图确定是否有数据库的当前日期的条目,如果我想更新该行。
一切都在代码工作,直到我试图与
changeRow.ExecuteNonQuery();
程序更新不抛出异常,或给我任何错误,数据库根本不会改变:)
我希望有人能帮助我! :)
答
代码没有问题,似乎没有匹配的更新条件行。
下面是测试的代码工作正常;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApplication1
{
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 maned = @month AND ar = @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["ovrige"];
iTransport = (int)reader["transport"];
iLoan = (int)reader["gold"];
iMad = (int)reader["mad"];
iDiverse = (int)reader["diverse"];
switch (kategori)
{
case "Bolig":
iBolig += beløb;
break;
case "ovrige":
iOther += beløb;
break;
case "Transport":
iTransport += beløb;
break;
case "gold":
iLoan += beløb;
break;
case "mad":
iMad += beløb;
break;
case "diverse":
break;
default:
break;
}
}
reader.Close();
SqlCommand changeRow = new SqlCommand("UPDATE Entries SET [email protected] WHERE [email protected]", sqlConnect); //, ø[email protected]øvrige, [email protected], gæ[email protected]æld, [email protected], [email protected] " + "WHERE [email protected] AND må[email protected] AND å[email protected]"
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);*/
int cc = changeRow.ExecuteNonQuery();
}
else
{
temp = false;
}
}
public void OpenConnection()
{
sqlConnect = new SqlConnection(@"initial catalog=*;User Id=sa;password=xxxxxx;Server=.;");
//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;
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
SQLConnect ocon = new WindowsFormsApplication1.SQLConnect(Application.StartupPath);
if(ocon.connectionSuccesful)
{
ocon.IndtastBeløb(1, "Bolig");
}
}
SQL脚本;
create table Entries (bolig int, dag int, maned int, ar int, ovrige int,transport int, gold int, mad int,diverse int)
insert into Entries values(1,27,9,2016,1,2,3,4,5)
-- after update
select * from Entries
结果
+0
哦,上帝,谢谢! 我一直在弄乱我的代码,并没有意识到发生了什么事情!谢谢你证明代码工作正常,否则我会在这里坐几天。似乎每次我调试程序时,Visual Studio都会将基本服务器复制到Debug文件夹并覆盖之前所做的更改,这就是为什么我无法看到它所做的更改! –
SQLReader的自IDisposable派生,请确保您使用的是'using'条款或您的一次性处置。 –
我做了一个代表简单数据访问层的类http://*.com/questions/25816609/checking-user-name-or-user-email-already-exists/25817145#25817145这里。目前你的代码有点乱。如果你想要,你可以检查它。您的代码中存在多个问题,例如仅使用一个连接,不处理资源,我也没有看到关闭连接的位置。 – mybirthname
如果您在management studio中使用相同的参数值执行结果查询,您会看到什么? – SpaceghostAli