[k3nnt0ter0]

I am having a dificulty in updating the database. I am not sure what's wrong with the query . . Help me figure it out.

SQLControls Class

public string editContact(Contact contact)
        {
            string sql = "SELECT * FROM ContactDetails WHERE StudentID = " + contact.studentID;

            openConn();
            oDa = new SqlDataAdapter(sql, oConn);
            oDa.Fill(oDs);            
            oConn.Close();
           
                try
                {
                    openConn();
                    string sSql = "UPDATE ContactDetails SET FirstName = '" + contact.fname + "', LastName = '" + contact.lname + "' , ContactNo = '" + contact.cnumber + "'" + " WHERE StudentID ='" + contact.studentID + "'" + ")";
                    oCmd.CommandText = sSql;
                    oCmd.Connection = oConn;
                    oCmd.ExecuteNonQuery();
                    oConn.Close();

                    return "Contact was successfully edited!!";
                }
                catch
                {
                    return "Contact was not successfully edited!!";
                }
            }
        }

Contact Class

 class Contact
    {
        public int studentID;
        public string fname;
        public string lname;
        public string cnumber;

        public Contact(int s, string f, string l, string c)
        {
            studentID = s;
            fname = f;
            lname = l;
            cnumber = c;
        }
    }

Form

private void btnEdit_Click(object sender, EventArgs e)
        {
            int sid = int.Parse(tbSID.Text);
            String fn = tbFN.Text;
            String ln = tbLN.Text;
            String cn = tbCon.Text;
            
            Contact edit = new Contact(sid, fn, ln, cn);

            SQLControls sc = new SQLControls();
            //string msg = sc.addContact(contact);
            string msg = sc.editContact(edit);
            DialogResult dr = new DialogResult();
            dr = MessageBox.Show(msg);
            if (dr == DialogResult.OK)
                this.Close();
        }

        }

Thanks!

[tlhIn`toq]

You REALLLy want to get away from this.

string sSql = "UPDATE ContactDetails SET FirstName = '" + contact.fname + "', LastName = '" + contact.lname + "' , ContactNo = '" + contact.cnumber + "'" + " WHERE StudentID ='" + contact.studentID + "'" + ")";

and use parameterized queries. Links below from my FAQ list


Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes

[tlhIn`toq]

At the very least use string.format to make this cleaner and easier to find mistakes. See how much easier it is to tell if you have mismatched your quotes and parentheses? And how much cleaner it is without all the '" + blah + '" crap?

            string sSql =string.Format("UPDATE ContactDetails SET FirstName = '{0}', LastName = '{1}' , ContactNo = '{2}' WHERE {3} ='{4}')",
                
                contact.fname,
                contact.lname,
                contact.cnumber,
                "StudentID",
                contact.studentID
                );

[k3nnt0ter0]

tlhIn`toq, on 10 March 2013 - 09:11 AM, said:

At the very least use string.format to make this cleaner and easier to find mistakes. See how much easier it is to tell if you have mismatched your quotes and parentheses? And how much cleaner it is without all the '" + blah + '" crap?

            string sSql =string.Format("UPDATE ContactDetails SET FirstName = '{0}', LastName = '{1}' , ContactNo = '{2}' WHERE {3} ='{4}')",
                
                contact.fname,
                contact.lname,
                contact.cnumber,
                "StudentID",
                contact.studentID
                );

Woah! Thanks! I've learned something. It's not complicated to check the '"crap"' anymore.
Anyway, I still can't make the database update :|

This post has been edited by k3nnt0ter0: 10 March 2013 - 09:33 AM

[tlhIn`toq]

There's all sorts of reasons for that. "it doesn't work" is pretty vague. Could be you aren't connected, could be you have a bad address to the server, could be you don't have permissions, etc. etc.

Time to work through those tutorials and do some basic debugging.

[k3nnt0ter0]

It's connected and I don't have problem saving "New" records and deleting it.

Anyway, I am still reading those tutorials.

[Skydiver]

What is the return value of ExecuteNonQuery? Is it at least 1? Is an exception being thrown? If so what do the exception details say?

Is StudentID a string or an integer. If it's an integer, I think that your WHERE clause is incorrect.

[k3nnt0ter0]

Skydiver, on 10 March 2013 - 01:08 PM, said:

What is the return value of ExecuteNonQuery? Is it at least 1? Is an exception being thrown? If so what do the exception details say?

Is StudentID a string or an integer. If it's an integer, I think that your WHERE clause is incorrect.

Yes my StudentID is an Integer. I tried changing it to String to check if it will work but it still not.

[Skydiver]

Did you change the database schema, or the class definition? Changing the class definition would have been futile since the error would have been in the SQL, not in the C#.

This post has been edited by Skydiver: 10 March 2013 - 08:32 PM

[k3nnt0ter0]

I did not change it.

public string editContact(Contact contact)
        {
            string sql = "SELECT * FROM ContactDetails WHERE StudentID = " + contact.studentID;
            openConn();
            oDa = new SqlDataAdapter(sql, oConn);
            oDa.Fill(oDs);
            oConn.Close();

            if (oDs.Tables[0].Rows.Count < 1)
            {                             
                return "Invalid Student Number";
            }
            else
            {
                try
                {
                    openConn();
                    string sSql = string.Format("UPDATE ContactDetails SET FirstName = '{0}', LastName = '{1}' , ContactNo = '{2}' WHERE {3} ='{4}')", contact.fname, contact.lname, contact.cnumber, "StudentID", contact.studentID);

                    oCmd.CommandText = sSql;
                    oCmd.Connection = oConn;
                    oCmd.ExecuteNonQuery();
                    oConn.Close();

                    return "Contact was successfully edited!!"; 
                }
                catch
                {
                    return "Contact was not successfully edited!!";                    
                }
            }
        }

My problem I guess is somewhere in the try part. Could it be my query?

[Skydiver]

Is an exception being thrown? If so, look at the Message property of the exception and it will tell you what SQL thinks is the the problem with your query.

[baavgai]

The string format was offered as the lesser option. A parameterized query will still solve most problems.

As it stands, some guy named "O'Reilly" will still blow you up. And some joker who puts in '"; delete ContactDetails; '" will crush you.