Solved

SQL UPDATE query

Posted on 2004-08-02
20
435 Views
Last Modified: 2010-04-15
Hi Guys,
I'm trying to update fields within the database. The ones i'm tring to update are the cekeck boxes. When clicked i want the check boxes to change. I've doent his query but keep on getting an error with the checkChanged event handler. Can anyone direct me on how i could fix the problem:

private void button5_Click(object sender, System.EventArgs e)
            {DataSet ds = new DataSet();
                  SqlConnection con = new SqlConnection (@"Data Source= xxx.xxx.xxx.xxx;"+
                        "Initial Catalog = drmTest;" +
                        "Network Library=DBMSSOCN;"+
                        "user id =xx;"+
                        "Password=xx"); // assign conString
                  String sQuery = "SELECT * FROM License WHERE Name = '"+ textBox2.Text +"' AND ContentOwnerID = '"+ textBox3.Text +"'";
                  String sUpdate = "UPDATE AllowBackupRestore, AllowBurnToCD, AllowPlayOnCD" + "VALUES ('"+ checkBox1.CheckedChanged +"','"+ checkBox2.CheckedChanged +"' )";
                  Console.WriteLine (sQuery); // to get the query for debug, you can try execute this query in sql query analyser
                  SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(sQuery, con);

                  sqlDataAdapter1.Fill(ds, "License");
                  Form2 frm = new Form2();
                  frm.dataGrid1.DataSource = ds.Tables["License"].DefaultView;
                  tabPage2.Show();
                  con.Close();
}
0
Comment
Question by:lesmydad
  • 12
  • 3
  • 2
  • +2
20 Comments
 
LVL 2

Expert Comment

by:jkrill
ID: 11692858
CheckChanged is an EventHandler, and not a property.  You can capture this event in your constructor by adding a line like the following:

checkBox1.CheckedChanged += new EventHandler(checkBox1_CheckedChanged);

and then creating a method that handles it:

private void checkBox1_CheckedChanged(object sender, System.EventArgs e)
{
// this will be called whenever the checkbox state has been changed.
}

If you want to determine whether the box is currently checked or not, you use the Checked property:

if(checkBox1.Checked)
{
// check box is selected
}
else
{
// check box is not selected.
}

If  you want to determine if it has just been changed, you'll have to store the value elsewhere and compare it:

bool check1InitialState = checkBox1.Checked;

Then elsewhere:

if(check1InitialState != checkBox1.Checked)
{
// Checkbox was changed.
}
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11692908
Hi jkrill
I've already got the method called. I'm having problem with the UPDATE string. I'm trying to update the databse field of ckeck boxes via update queries. When the checkboxes are clicked or un checked i want the action also to take place in the database.
Thanks
Les
0
 
LVL 2

Expert Comment

by:jkrill
ID: 11693001
Just to clarify, do you want the database values to be change as soon as the checkbox values are changed, or not until after a button is clicked?  Right now it appears you're handling it when a button is clicked.  Either way, you can't use checkBox1.CheckChanged in the manner you currently are.  You'll need to use checkBox1.Checked.
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11693050
Yes i wan the database values to change when the checkboxes values are changed. These changes should take place whne i click the update button.
Thanks
0
 
LVL 2

Assisted Solution

by:jkrill
jkrill earned 20 total points
ID: 11693229
Ok. I don't really know how you have your database layed out, but an update statement should follow this syntax:

UPDATE MyDatabaseTable
SET MyDatabaseField = 'MyDatabaseFieldValue', MyOtherDatabaseField = 'MyOtherDatabaseFieldValue'
WHERE (MyPrimaryKey = MyPrimaryKeyValue)

You still will need to use checkBox1.Checked instead of checkBox1.CheckedUpdated to retrieve the state of the checkbox.
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11694126
jkrill,

I have a SQL database (through remote connection) table, i'm developing a software where it is being intergrated with sql database. At present when a user click a button they are able to view thier details (login is required). Now i want to let the user update thier detail (again fileds to update will be chossen via login details). All fields will be shown in a dataGrid. I have checkboxes in that database. If a user wants to change the values of the checkboxex (ticks), they will first amend that value, then when they press a button (update), automatically these values will change in teh database, and when they press the view butoon they will be able to see these changes.
I've produced the above script, but i know theres something wrong with the Update string, thats where the error keeps on popping up.

String sQuery = "SELECT * FROM License WHERE Name = '"+ textBox2.Text +"' AND LicenseID = '"+ textBox3.Text +"'";

String sUpdate = "UPDATE AllowBackupRestore, AllowBurnToCD" + "VALUES ('"+ checkBox1.Checked +"','"+ checkBox2.Checked +"' )";

Console.WriteLine (sUpdate); // to get the query for debug, you can try execute this query in sql query analyser
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(sUpdate, con);
 sqlDataAdapter1.Fill(ds, "License");
                  Form2 frm = new Form2();
                  frm.dataGrid1.DataSource = ds.Tables["License"].DefaultView;
                  frm.Show();
                  con.Close();

Can anyone figure out how i could solve this problem. Will prefer if they amended the above script to show thw changes.
Thanks,
Les
0
 

Assisted Solution

by:karra
karra earned 40 total points
ID: 11694676
First look at the statement pops me with this: Give spaces after the statements: (which u did not got the update)

String sQuery = "SELECT * FROM License WHERE Name = '"+ textBox2.Text +"'  AND LicenseID = '"+ textBox3.Text +"'";
Console.WriteLine(sQuery);
String sUpdate = "UPDATE AllowBackupRestore, AllowBurnToCD VALUES ( '"  +  checkBox1.Checked  + "' , '" + checkBox2.Checked  + "' )";
Console.WriteLine(sUpdate);
0
 

Expert Comment

by:karra
ID: 11694968
sorry not statements.. but i mean words..
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11695043
Hi Karra,
the above code bring this error:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','.
I'm not sure where this section relates to, i'm not calling any exception at present.
Is there anything wrong with the update string, or aer the methods are being called correctly.
Thanks
0
 
LVL 1

Expert Comment

by:artyx
ID: 11695239
try to modify your SQL like this

String sUpdate = "UPDATE License(AllowBackupRestore, AllowBurnToCD, AllowPlayOnCD) VALUES(" + checkBox1.Checked.ToString() + "," +
checkBox2.Checked.ToString() + "," +
checkBox3.Checked.ToString() + ")";

One obvious error to me is that your UPDATE SQL statement is wrong, you need to write the UPDATE as

UPDATE tablename(col1, col2, ...) VALUES(val1, val2, ...)

You have three values to update, then you need to provide three.

Since I don't know the table schema, I'm not sure if the data conversion of values is right.  Maybe you give a try and let me know.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:tinh911
ID: 11713709
I agree with artyx!
Your UPDATE  query is wrong!
Have you tried all experts' suggestions?
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11713895
Ok Guys,
This is causing confusion amongst us. I'll now change the whole scenario an dmake it easy.
I'm not going to use dataGrid anymore. I now have these textBoxes, i want to retrive the information from the database to the approipate textBox. Then through these textBox i will want to update the fields.
First can some one tell me what scritpting will i need to insert in order to retrive data from the database to these textboxes, i have three textBoxes. How do i show the results in the textBoxes:

SqlConnection con = new SqlConnection (@"Data Source= xxx.xxx.xxx.xxx;"+
                   "Initial Catalog = drmTest;" +
                   "Network Library=DBMSSOCN;"+
                   "user id =xx;"+
                   "Password=xxxxxxxx"); // assign conString
                  String sQuery = "SELECT Name, Description, Price FROM License WHERE Name = '"+ textBox5.Text +"' AND LicenseID = '"+ textBox6.Text +"'";


Then when they are retrived, if any editing is done, another button will be used fore the updates.

Thanks
Les

0
 
LVL 1

Author Comment

by:lesmydad
ID: 11714614
Found the Answer
String sQuery = "SELECT Name, Description, Price FROM License WHERE Name = '"+ textBox5.Text +"' AND LicenseID = '"+ textBox6.Text +"'";
            //Console.WriteLine(sQuery);
                  SqlCommand cmd = new SqlCommand(sQuery, con);
                  //SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(sQuery,con);
                  con.Open();
                  SqlDataReader dr = cmd.ExecuteReader();
                  
                  if( dr.Read() )
                  {
                        textBox1.Text = dr[ "Name" ].ToString();
                        textBox2.Text = dr[ "Description" ].ToString();
                        textBox3.Text = dr[ "Price" ].ToString();
                        // ......
                  }
                  dr.Close();

Thanks Anyway
Les
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11716303
Hi Guys,
need help again, How would i retrive the attribute of a checkBox and radio button. In the database these checkBoxes and radio button is defined by 1 and 0 which means true/false. If checkbox value is 1 then the database in the form will be checked to indicate its true and vice versa.

I tried:     checkBox1.Checked = dr[ "expiry" ].ToString();
but this send string error.

Any one has any idea how i go through this hurdle.
Thanks
0
 
LVL 1

Accepted Solution

by:
artyx earned 40 total points
ID: 11719559
Try

checkBox1.Checked = Convert.ToBoolean(dr["expiry"].ToString());

Since it concerns to type conversion, if I know the type of "expiry" then I can give this suggestion with more confidence.
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11724617
What about for radioButton, where i have either Static or Dynamic instead of 1 and 0
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11725083
Can someone also help me with the update script now, its much clearer then before. After retriving all the filds from the database to its approipate textBox, checkBox etc if i make a change tot any of the text or any other filrds, after pressing update i want that effect to take place in the database as well as the form. I wrote this script but having problem with the update section:

String sUpdate = "Update * FROM License SET Name = '" + checkBox1.Checked.ToString() + "', AllowPlayOnPC ='" + checkBox1.Checked + "'"  +
                        "WHERE Name = '"+ textBox5.Text +"' AND LicenseID = '"+ textBox6.Text +"'";
                  SqlCommand cmd = new SqlCommand(sUpdate, con);
                  con.Open();
                  SqlDataReader dr = cmd.ExecuteReader();
                  con.Close();

Thanks
Les
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11725096
I will split the points amongst these who have given me an answer which functioned.
Thanks
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11727546
Sorted, found the answer:
String sUpdate = "Update License SET Name = '" + textBox1.Text.ToString() + "'" +
                        " WHERE Name = '"+ textBox5.Text +"' AND LicenseID = '"+ textBox6.Text +"'";
                  SqlCommand cmd = new SqlCommand(sUpdate, con);
                  con.Open();
                  cmd.ExecuteNonQuery();
                  con.Close();
0
 
LVL 1

Author Comment

by:lesmydad
ID: 11727671
How will i update the changes to the checkBox and radioButtons. With them its true and false which determines the event. I tried using:
AllowPlayOnPC ='" + checkBox1.Checked + "' - But this give error of true and fals enot declared properly. What will go after
checkBox1.Checked  to convert into Int (1 and 0).

Also still having problem with declaring the radio button event where i have either Static or Dynamic instead of 1 and 0, how will i declare this in the form and the database.

Thanks
Les
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now