11 Replies - 1621 Views - Last Post: 11 March 2013 - 06:14 AM Rate Topic: -----

#1 k3nnt0ter0  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 16-January 10

Updating Records in Database

Posted 10 March 2013 - 06:34 AM

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!

Is This A Good Question/Topic? 0
  • +

Replies To: Updating Records in Database

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5512
  • View blog
  • Posts: 11,817
  • Joined: 02-June 10

Re: Updating Records in Database

Posted 10 March 2013 - 09:06 AM

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
Was This Post Helpful? 0
  • +
  • -

#3 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5512
  • View blog
  • Posts: 11,817
  • Joined: 02-June 10

Re: Updating Records in Database

Posted 10 March 2013 - 09:11 AM

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


Was This Post Helpful? 0
  • +
  • -

#4 k3nnt0ter0  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 16-January 10

Re: Updating Records in Database

Posted 10 March 2013 - 09:32 AM

View PosttlhIn`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

Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5512
  • View blog
  • Posts: 11,817
  • Joined: 02-June 10

Re: Updating Records in Database

Posted 10 March 2013 - 09:50 AM

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.
Was This Post Helpful? 0
  • +
  • -

#6 k3nnt0ter0  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 16-January 10

Re: Updating Records in Database

Posted 10 March 2013 - 10:20 AM

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

Anyway, I am still reading those tutorials.
Was This Post Helpful? 0
  • +
  • -

#7 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3572
  • View blog
  • Posts: 11,106
  • Joined: 05-May 12

Re: Updating Records in Database

Posted 10 March 2013 - 01:08 PM

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.
Was This Post Helpful? 0
  • +
  • -

#8 k3nnt0ter0  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 16-January 10

Re: Updating Records in Database

Posted 10 March 2013 - 08:27 PM

View PostSkydiver, 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.
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3572
  • View blog
  • Posts: 11,106
  • Joined: 05-May 12

Re: Updating Records in Database

Posted 10 March 2013 - 08:31 PM

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

Was This Post Helpful? 0
  • +
  • -

#10 k3nnt0ter0  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 181
  • Joined: 16-January 10

Re: Updating Records in Database

Posted 10 March 2013 - 10:30 PM

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?
Was This Post Helpful? 0
  • +
  • -

#11 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3572
  • View blog
  • Posts: 11,106
  • Joined: 05-May 12

Re: Updating Records in Database

Posted 11 March 2013 - 05:14 AM

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.
Was This Post Helpful? 0
  • +
  • -

#12 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5826
  • View blog
  • Posts: 12,678
  • Joined: 16-October 07

Re: Updating Records in Database

Posted 11 March 2013 - 06:14 AM

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.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1