Data not saving into database

Question

0.00/5 (No votes)

See more:

, +

I have developed a form which has 3 fields(Name,USN and Sem). I have attached my code below. When I run this program I dont get any error and it works totally fine. Once the data has been inserted from UI, I get back and check in database table, and its empty. When I click on View button it shows the gridview of what all I have inserted at that instant of run. Not sure what is wrong. Please help.

^]

C#

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.Configuration;
using System.Data.SqlClient;

namespace StudentEXE
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TRY"].ConnectionString);
            con.Open();
            SqlCommand query = new SqlCommand("insert into Student_Data values('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "')", con);
            query.ExecuteNonQuery();
            MessageBox.Show("Data Added Succesfully");
            con.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            PopupWindos popup = new PopupWindos();
            popup.ShowDialog();
        }
    }
}

app.config file :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="TRY"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Posted 21-May-15 5:50am

partha143

Updated 1-Jun-15 23:56pm

RajeeshMenoth

v4

Add a Solution

2 solutions

Solution 1

First off:
Understanding SQL Injection and Creating SQL Injection Proof ASP.NET Applications[^]

Never create queries from user input.

Have you met my Dad? His name is '; drop table *;'--
https://xkcd.com/327/[^]

C#

namespace StudentEXE
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            int rowsChanged = 0;
            using( SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TRY"].ConnectionString))
            {
            
            using(SqlCommand query = new SqlCommand("insert into Student_Data values(@param1,@param2,@param3)",con))
            {
            query.Parameters.AddRange({
             new SqlParameter("@param1",SqlDbType.Varchar){Value=textBox1.Text},
             new SqlParameter("@param2",SqlDbType.Varchar){Value=textBox2.Text},
             new SqlParameter("@param2",SqlDbType.Varchar){Value=textBox2.Text}
             });

            con.Open();
            //make sure something happened
            rowsChanged  = query.ExecuteNonQuery();
            con.Close();
            }
           }
           if(rowsChanged == 1)
             MessageBox.Show("Data Added Succesfully");
           if(rowsChanged == 0)
             MessageBox.Show("Didn't work");
           if(rowsChanged > 1)
             MessageBox.Show("Something went VERY wrong");
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            PopupWindos popup = new PopupWindos();
            popup.ShowDialog();
        }
    }
}

try that - see what you get.

Posted 21-May-15 6:21am

Andy Lanng

Comments

partha143 21-May-15 14:08pm

Still same problem. I am getting "Data Added Successfully" message and also when I click on view in the same instance of run(i.e without re-running the project), I am able to view the entries. But when i go to the datatable to view my entries manually, it is blank. Please help.

Andy Lanng 21-May-15 14:13pm

Ok. If it says that it's saved, but it's not, then the transaction but be being rolled back. Are you aware of any transaction setting in your app?

partha143 21-May-15 14:20pm

Sorry sir. I havent heard of it. Could you please provide me any link from which i can learn about it

Andy Lanng 21-May-15 14:43pm

I'm on my phone atm so my abilities are somewhat reduced. Just google "sql transactions"

partha143 21-May-15 14:46pm

Okay. Thank you for your help :)

Andy Lanng 21-May-15 14:48pm

I won't forget the post until we have found your solution. I'm out for the day now so be sure to update your progress :)

partha143 2-Jun-15 0:08am

Andy, I tried SqlTransaction as you suggested me in your last replt, but I was unable to resolve my issue yet. I have posted the code below. Please have a look at it. Thank you.
try
{
SqlTransaction transaction;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TRY"].ConnectionString);
con.Open();
transaction = con.BeginTransaction();
SqlCommand query = new SqlCommand("insert into Student_Data values(@param1,@param2,@param3)", con);
query.Transaction = transaction;
query.Parameters.Add("@param1", SqlDbType.VarChar).Value = textBox1.Text;
query.Parameters.Add("@param2", SqlDbType.VarChar).Value = textBox2.Text;
query.Parameters.Add("@param3", SqlDbType.VarChar).Value = textBox3.Text;
query.ExecuteNonQuery();
transaction.Commit();
MessageBox.Show("Data Added Succesfully");
con.Close();
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
<connectionstrings>
<add name="TRY" connectionstring="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providername="System.Data.SqlClient">

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-06-01T22:21:00

It works fine for me.

Try using transactionscope instead:

C#

using System.Transactions;

...

public static void button1_Click()
{
    var options = new TransactionOptions
    {
        Timeout = TimeSpan.FromSeconds(120)
    };
    try
    {
        using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TRY"].ConnectionString))
            {
                using (SqlCommand query = con.CreateCommand())
                {
                    query.CommandText = ("insert into wfe.Student_Data values(@param1,@param2,@param3)");
                    query.CommandType = CommandType.Text;
                    query.Parameters.Add("@param1", SqlDbType.VarChar).Value = "a";
                    query.Parameters.Add("@param2", SqlDbType.VarChar).Value = "b";
                    query.Parameters.Add("@param3", SqlDbType.VarChar).Value = "c";
                    con.Open();
                    var rowsadded = query.ExecuteNonQuery();
                    con.Close();
                    scope.Complete();

                    Console.WriteLine(@"{0} rows added",rowsadded);
                }
            }
        }
    }
    catch (Exception ec)
    {
        Console.WriteLine(ec);
    }
}

Note that you only need to open the connection at the last minute. The Using wrappers will help cleanly dispose of the connection even if an error does occur.

Note that I tested this in a console app so you will have to add your messageboxes back in