C# application does not save data into SQL database

Question

Developing an employee/student management system with C# windows form application.
Two developers are working on this project, and using two versions of visual studio and SQL servers. (One PC is using VS 2013 Pro and MS SQL express 14, other PC is using VS 2015 enterprise and SQL 14 enterprise). Both PCs are working through team foundation server.

The C# application is connecting with the SQL database and the data retrieval is verified with the login interfaces. Username and passwords are stored in a table and the login process is working fine. So it's sure that the DB connection has no errors.

The problem is that when we tried to insert data into SQL table, C# shows no errors in try/catch exceptions, but data is not stored in the database. Have used the command.ExecuteNonQuery() too.

Not sure that the connection string has the problem.

This is the connectionManager class.

class ConnectionManager
{
    public static SqlConnection connection()
    {
        string connectionString = 
            @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\tdsdb.mdf;Integrated Security=True";
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        return con;
    }
}

This is the class which handles the SQL insert operation.

class Student
{
    public void addStudent(string studid, string fullname, string nameinit, string gend, string dob, int age, string nic, string nicdate, string hotp, string mobtp, string odlno, string odldt, string odlcls, string reqdlcls, int trtds, int active)
    {
        string sql= "INSERT INTO tblStudent VALUES ('"+studid+"','"+fullname+"','"+nameinit+"','"+gend+"','"+dob+"','"+age+"','"+nic+"','"+nicdate+"','"+hotp+"','"+mobtp+"','"+odlno+"','"+odldt+"','"+odlcls+"','"+reqdlcls+"','"+trtds+"','"+active+"')";
        SqlCommand com = new SqlCommand(sql, ConnectionManager.connection());
        com.ExecuteNonQuery();
    }
}

This is the class which handles the submit button click operation

private void button6_Click(object sender, EventArgs e)
{
    string radtext = "";
    bool isChecked = radioButton1.Checked;
    if(isChecked)
        radtext = radioButton1.Text;
    else
        radtext = radioButton2.Text;

    string hometp = "+94"+textBox8.Text;
    string mobiletp = "+94"+textBox9.Text;

    int activeint = 1;

    string trtdschk = comboBox3.Text;
    int trtds = 0;
    if (trtdschk == "Yes")
        trtds = 1;
    else
        trtds = 0;

    try
    {
        std.addStudent(textBox2.Text, textBox38.Text, textBox4.Text, radtext, dateTimePicker2.Text, int.Parse(textBox6.Text), textBox7.Text, dateTimePicker3.Text, hometp, mobiletp, textBox12.Text, dateTimePicker4.Text, comboBox1.Text, comboBox2.Text, trtds, activeint);
        MessageBox.Show("Personal details has been successfully added to the database!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message.ToString());
    }            
}

Don't know how multiple database connections are influencing each other, you should try to just maintain a single database connection. For example by using a singleton for the database connection. — Benji Wa, May 01 '16 at 11:41
Do not use mdf filename for the connection string. Instead connect to server name and include a 'Use DataBaseName' statement. The database is probably already attached to the database and doesn't need to be attached each time you use the database. — jdweng, May 01 '16 at 11:43
Just pointing out that your code appears to be vulnerable to SQL injection. — yaakov, May 01 '16 at 12:10
And use Parameters... http://stackoverflow.com/questions/19956533/sql-insert-query-using-c-sharp — Monty, May 01 '16 at 12:11
Your db is already attached to the project, change the string to be like this, within your config, `` — t0mm13b, May 01 '16 at 12:17

score 0 · Answer 1 · answered May 05 '16 at 14:16

AFAIK a parameterized query is better. See this generic sample.

 using(SqlConnection openCon=new SqlConnection("your_connection_String"))
    {
      string saveStaff = "INSERT into tbl_staff (staffName,userID,idDepartment) VALUES (@staffName,@userID,@idDepartment)";

      using(SqlCommand querySaveStaff = new SqlCommand(saveStaff))
       {
         querySaveStaff.Connection=openCon;
         querySaveStaff.Parameters.Add("@staffName",SqlDbType.VarChar,30).Value=name;
         .....
         openCon.Open();

         openCon.Close();
       }
     }

Also, take a look at this link for details about connection strings.

https://www.connectionstrings.com/sql-server/

C# application does not save data into SQL database

1 Answers1