Click here to Skip to main content
15,897,334 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello every one,
Below is my code. It works.
but i want to check if any date is already added to renewal dates.Then same date can not be added.
C#
private void buttonAddToRdates_Click(object sender, EventArgs e)
       {
           string connectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\nic-15\My Documents\ALRS.mdb");
           OleDbConnection con = new OleDbConnection(connectionString);
           con.Open();
           OleDbCommand cmd1 = new OleDbCommand("select renewal_dates from arms_records where thana='" + comboBoxthana.Text + "' and licence_number='" + textBoxLicence_number.Text + "'", con);
           OleDbDataAdapter da = new OleDbDataAdapter(cmd1);
           DataSet ds = new DataSet();
           da.Fill(ds);
           int i = ds.Tables[0].Rows.Count;
           if (i == 1)
           {
               DialogResult result = MessageBox.Show("Previous Renewal Dates:"+cmd1.ExecuteScalar().ToString()+"\n Do you want to add: "+dateTimePickerRenewDate.Value.ToShortDateString()+"  to renewal dates?", "Date will be added to Renewal dates.!!!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
               if (result == DialogResult.Yes)
                {
               //code for yes
                   ds.Clear();
                   string query = "update arms_records set renewal_dates='" + dateTimePickerRenewDate.Value.ToShortDateString() + "'&',' & renewal_dates where thana='" + comboBoxthana.Text + "' and licence_number='" + textBoxLicence_number.Text + "'";
                   OleDbCommand cmd = new OleDbCommand(query, con);
                   cmd.ExecuteNonQuery();
                   con.Close();
                   MessageBox.Show("Date added to Renewal Dates.\nPlease do enter next renewal data before updating record.");
                 }
           }
               else
               {
                   MessageBox.Show("No record found.");
               }
       }
Posted

1 solution

First off, stop converting dates to strings before sending them to Access: in fact, stop doing things like that at all! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead, and pass your date as a DateTime value, not a string. That way there are no localisation problems between systems...

Then, just change your test for number of rows returned from "one" to "non-zero" and check the values you have retrieved against the renewal date you are about to add.
 
Share this answer
 
Comments
Member 10358986 17-Jan-14 4:16am    
Thank you sir
Sir i am updating renewal_dates field.Every time update is executed new values are added (concatenated)to the same field in database, as records.
Generally a licence is renewed 10 to 15 times.
So instead of making separate fields for every renewal date i am concatenating dates as string to save in one column.
Now i want to check this string for a date before updating it.
OriginalGriff 17-Jan-14 4:22am    
Don't.
If you are going to force dates into a string so you can compare them, then store them in a fixed format:
yyyyMMdd,yyyyMMdd,...
for example, and use string.Split to break the string you get back from the DB and then DateTime.TryParseExact to convert them back to DateTime objects. Don't rely on default conversions because the string value will depend on the PC locale: and that means on the user settings.
A better solution would be to add a table for renewals, which contains an ID linking to the main table, and the renewal date (as a DateTime column) and other information. You can then retrieve all the renewal dates without any faffing about (and compare them directly in your SQL using a JOIN)
Member 10358986 17-Jan-14 4:56am    
Thank you sir
Yes sir i am storing date in a fixed format like this
07-01-2017,07-01-2017,07-01-2017,07-01-2017,07-01-2017,07-01-2017,07-01-2017..
just want to restrict duplicates.
Sir i am a beginner.
So please provide me the code.Please...
Thank you very much
OriginalGriff 17-Jan-14 5:53am    
Well...no, I'm not going to "give you the code".
You're a beginner, so it's important you develop a skill: working out when an idea was a mistake, and you should throw it away and do something better.

And this is such a time. I know, it's easy to store it as a string, but...you've just noticed that it gives problems whenever you actually want to use the values. And these problems don't go away, or get any better - if anything they get worse, so you end up with two systems that are so "locked together" you can't use either of them independently. You can't easily use your data for any purpose (or with any software) other than the stuff you are writing now. That's a bad idea, and in the real world that gets you shouted at by your boss. So, bit the bullet and throw the string away! Do the job properly and create a second table:
arms_records:
ID Int or GUID, unique value to identify the record
thana ?
license_number ?
New table (renewals):
ID Int or GUID, unique value to identify the record
ArmsId Matches table above, contains ID values
Renewal DateTime, renewal date
Other Any other data, such as who renewed it, when, etc.

The new table has many records for each one in the main table
When you do things this way, you can retrieve all records for an item, or select just ones that have expired, etc with a simple SQL query: you don't need much processing in your presentation language.
Trust me, it's worth doing!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900