I am not able to save data to my sql database from c# app no errors

Question

I am not able to save data to my sql database from c# app its not even giving me any errors. Am I missing something. Its a simple script which will pick up user input from Textbox and check box inserting it into SQL database. Here is my script.

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 = "";
        }
    }
}         
}

warning your code is extremely vulnerable to sql injection attacks! — Daniel A. White
– Daniel A. White, Commented Nov 4, 2013 at 17:19
You need to wrap your code in a try-catch block or any exceptions (errors) that are thrown will not be caught. You also need to call .Dispose() on your Connection object or wrap it in a using block. — Amicable
– Amicable, Commented Nov 4, 2013 at 17:20
I would switch to parameterized queries first, then see if the problem goes away. You need to do this regardless. — Mike Christensen
– Mike Christensen, Commented Nov 4, 2013 at 17:21
Agreed with commenters saying to change it to parameterised queries. better yet is to used SPROCS (stored procedures). also MessageBox.Show() will NOT work in ASP.NET apps nor is it intended on using it like this. — Ahmed ilyas
– Ahmed ilyas, Commented Nov 4, 2013 at 17:24
Also, this is a minor quibble: you really shouldn't include the "version" identifier in your namespace. That belongs in the project settings area. — ChrisLively
– ChrisLively, Commented Nov 4, 2013 at 17:33

Kurubaran · Accepted Answer · 2013-11-04 17:22:51Z

7

Initial catalog is missing in the connection string. you should mention your database name there.

@"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"

answered Nov 4, 2013 at 17:22

Kurubaran

8,9625 gold badges47 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sudhakar Tillapudi · Accepted Answer · 2013-11-04 19:13:11Z

1. use double && symbol instead of single & in your if conditional block

Replace this :

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 != "")

with following :

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.You are trying to insert more values into table than specified in query.

you have specified that 16 values you are going to insert into table as below:

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

but you are inserting 19 values as below:

 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.You are missing the Database name in SQL Connection String.

Replace this :

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");

With Following : for example your database name = 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");

4.Use Parameterized queries to avoid SQL Injection Attacks:

Example:

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.Wrap up your code into try-catch/finally block:

Example :

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

6.Close your Sql Connection Object at the end of the operation.

Example:

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

should be using a using block for the connection, no matter what it will be close then. otherwise +1
@Malachi:Thanks, Yes using block is much better than finally,as itself takes care of closing as soon as using block execution gets Finished.

marc_s · Accepted Answer · 2013-11-04 18:12:34Z

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. KaizenDatabase)
connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
```
Data Source=.\\SQLEXPRESS;Database=KaizenDatabase;Integrated Security=True
```
and everything else is exactly the same as before...

Collectives™ on Stack Overflow

I am not able to save data to my sql database from c# app no errors

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related