我无法从c#应用程序将数据保存到我的sql数据库中，没有错误。

Question

我无法从c#应用程序将数据保存到我的sql数据库，它甚至没有给我任何错误。我是不是错过了什么。这是一个简单的脚本，将从文本框和复选框的用户输入插入到SQL数据库。这是我的脚本。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace Kaizen_Tracking_System_V1
{
public partial class Individual : Form
{
    SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Asus\Documents\Visual Studio 2010\Projects\Kaizen Tracking System V1\Kaizen Tracking System V1\Database1.mdf;Integrated Security=True;User Instance=True");
    SqlCommand cmd = new SqlCommand();

    public Individual()
    {

        InitializeComponent();
    }

    private void Individual_Load(object sender, EventArgs e)
    {
        cmd.Connection = cn;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if (logTxtBox.Text != "" & lnameTextBox.Text != "" & fnameTextBox.Text != "" & depCheckBox1.Text != "" & DepCheckBox2.Text != "" & depCheckBox3.Text != "" & depCheckBox4.Text != "" & locationComboBox1.Text != "" & processTextBox.Text != "" & typeTextBox.Text != "" & odgrecdataTextBox.Text != "" & kimpdateTextBox.Text != "" & cipaTextBox.Text != "" & cspmTextBox.Text != "" & rewardgivenTextBox.Text != "" & rcppTextBox.Text != "" & kvdTextBox.Text != "" & ylocationTextBox.Text != "" & detailRichTextBox1.Text != "")
        {
            cn.Open();
            cmd.CommandText = "insert into kaizentracker (lognum,lname,fname,dept,location,process,type,odgrecdate,kimpdate,cipa,cspm,rewardgiven,rcpp,kverifieddate,ylocation,details) values ('" + logTxtBox.Text + "' , '" + lnameTextBox.Text + "' , '" + fnameTextBox.Text + "' , '" + depCheckBox1.Text + "' , '" + DepCheckBox2.Text + "' , '" + depCheckBox3.Text + "' ,'" + depCheckBox4.Text + "' , '" + locationComboBox1.Text + "' , '" + processTextBox.Text + "' , '" + typeTextBox.Text + "' , '" + odgrecdataTextBox.Text + "' , '" + kimpdateTextBox.Text + "' , '" + cipaTextBox.Text + "' , '" + cspmTextBox.Text + "' , '" + rewardgivenTextBox.Text + "' , '" + rcppTextBox.Text + "' , '" + kvdTextBox.Text + "' , '" + ylocationTextBox.Text + "' , '" + detailRichTextBox1.Text + "') ";
            cmd.ExecuteNonQuery();
            cmd.Clone();
            MessageBox.Show("Data Saved");
            cn.Close();
            logTxtBox.Text = "";
            lnameTextBox.Text = "";
            fnameTextBox.Text = "";
            depCheckBox1.Text = "";
            DepCheckBox2.Text = "";
            depCheckBox3.Text = "";
            depCheckBox4.Text = "";
            locationComboBox1.Text = "";
            processTextBox.Text = "";
            typeTextBox.Text = "";
            odgrecdataTextBox.Text = "";
            kimpdateTextBox.Text = "";
            cipaTextBox.Text = "";
            cspmTextBox.Text = "";
            rewardgivenTextBox.Text = "";
            rcppTextBox.Text = "";
            kvdTextBox.Text = "";
            ylocationTextBox.Text = "";
            detailRichTextBox1.Text = "";
        }
    }
}         
}

Answer 1

连接字符串中缺少Initial catalog。您应该在那里提到您的数据库名称。

@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Asus\Documents\Visual Studio 2010\Projects\Kaizen Tracking System V1\Kaizen Tracking System V1\Database1.mdf;Initial Catalog=MyDatabase; Integrated Security=True;User Instance=True"

Answer 2

1.在if条件块中使用双&&符号而不是单&

替换为:

if (logTxtBox.Text != "" & lnameTextBox.Text != "" & fnameTextBox.Text != "" & depCheckBox1.Text != "" & DepCheckBox2.Text != "" & depCheckBox3.Text != "" & depCheckBox4.Text != "" & locationComboBox1.Text != "" & processTextBox.Text != "" & typeTextBox.Text != "" & odgrecdataTextBox.Text != "" & kimpdateTextBox.Text != "" & cipaTextBox.Text != "" & cspmTextBox.Text != "" & rewardgivenTextBox.Text != "" & rcppTextBox.Text != "" & kvdTextBox.Text != "" & ylocationTextBox.Text != "" & detailRichTextBox1.Text != "")

包含以下内容的 :

if (logTxtBox.Text != "" && lnameTextBox.Text != "" && fnameTextBox.Text != "" && depCheckBox1.Text != "" && DepCheckBox2.Text != "" && depCheckBox3.Text != "" && depCheckBox4.Text != "" && locationComboBox1.Text != "" && processTextBox.Text != "" && typeTextBox.Text != "" && odgrecdataTextBox.Text != "" && kimpdateTextBox.Text != "" && cipaTextBox.Text != "" && cspmTextBox.Text != "" && rewardgivenTextBox.Text != "" && rcppTextBox.Text != "" && kvdTextBox.Text != "" && ylocationTextBox.Text != "" && detailRichTextBox1.Text != "")

2.您正在尝试向表中插入的值多于在query中指定的值。

您已经指定了16个要插入到表中的值，如下所示：

"insert into kaizentracker(lognum,lname,fname,dept,location,process,type,odgrecdate,kimpdate,cipa,cspm,rewardgiven,rcpp,kverifieddate,ylocation,details)"

但是您插入了19个值，如下所示：

 values ('" + logTxtBox.Text + "' , '" + lnameTextBox.Text + "' , '" + fnameTextBox.Text + "' , '" + depCheckBox1.Text + "' , '" + DepCheckBox2.Text + "' , '" + depCheckBox3.Text + "' ,'" + depCheckBox4.Text + "' , '" + locationComboBox1.Text + "' , '" + processTextBox.Text + "' , '" + typeTextBox.Text + "' , '" + odgrecdataTextBox.Text + "' , '" + kimpdateTextBox.Text + "' , '" + cipaTextBox.Text + "' , '" + cspmTextBox.Text + "' , '" + rewardgivenTextBox.Text + "' , '" + rcppTextBox.Text + "' , '" + kvdTextBox.Text + "' , '" + ylocationTextBox.Text + "' , '" + detailRichTextBox1.Text + "') ";

3.连接字符串中缺少数据库名称。

替换为:

SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Asus\Documents\Visual Studio 2010\Projects\Kaizen Tracking System V1\Kaizen Tracking System V1\Database1.mdf;Integrated Security=True;User Instance=True");

具有以下名称的名称:例如，您的数据库名称= sampledatabase。例如，您的数据库名称为。

SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Asus\Documents\Visual Studio 2010\Projects\Kaizen Tracking System V1\Kaizen Tracking System V1\Database1.mdf;Initial Catalog=sampledatabase;Integrated Security=True;User Instance=True");

SQLSQL4.使用参数化查询来避免

注入攻击：

示例：

string SqlCommand= "INSERT INTO myTable ([param1],[param2])VALUES(@param1,@param2)";

        command.Parameters.Add("@param1", SqlDbType.NVarChar,50);
        command.Parameters.Add("@param2", SqlDbType.NVarChar,50);
        command.Parameters["@param1"].Value = name1;
        command.Parameters["@param2"].Value = name2;

5.将代码打包到try-catch/finally块中：

示例：

 try { 
//DB Statements 
} 
finally 
{ 
//handle exceptions and close all open connections
 }

SQLSQL6.在操作结束时关闭

连接对象。

示例：

try
{
SqlConnection connection = new SqlConnection(strConnectionString);
connection.Open();
}
finally
{
connection.Close();
}

Answer 3

整个用户实例和AttachDbFileName=方法都是有缺陷的--充其量！当在Visual Studio中运行你的应用程序时，它会复制.mdf文件(从你的App_Data目录到输出目录-通常是.\bin\debug -你应用程序运行的目录)和很可能是，你的INSERT运行得很好-但你最终只是看到了错误的.mdf文件！

如果您想坚持这种方法，那么尝试在myConnection.Close()调用上设置一个断点-然后使用SQL Server Mgmt Studio Express检查.mdf文件-我几乎可以肯定您的数据就在那里。

在我看来，真正的解决方案应该是

1. 安装SQL Server Express (并且您已经在SSMS Express中安装了SQL Server Management Studio Express
2. create您的数据库，给它一个逻辑名称(例如KaizenDatabase)
3. 使用它的逻辑数据库名称连接到它(当您在服务器上创建它时给定)-并且不要乱动物理数据库文件和用户实例。在这种情况下，您的连接字符串将类似于：

Data Source=.\SQLEXPRESS;Database=KaizenDatabase;Integrated Security=True

和其他一切都是完全一样的与以前一样...