EDN Admin
Well-known member
<div style="font-family:Tahoma; font-size:13px; line-height:normal <br/>
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal i am using a c# windows form application and trying to connect to a very large Access database, a couple of million lines. i have been using an oledb command and adapter, it seems to work
fine for small queries but i slows down a lot when it loops through the SQL connection numerous times. i was wondering if there was a different way to connect to an access database that would be quicker.
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal <br/>
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
Here is part of my code, the first and second loop seems to be almost instant but then it seems to slow down and takes a few seconds per query which is too slow. Is there anything i can add to the oledb that would stop the decrease in performance.
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
many thanks
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal // build the where part of the sql string
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREString = WHEREString + <span style="color:#a31515 " AND AccountName = ?";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREcounter = WHEREcounter + 1;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // ticker
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (textBox8.Text != <span style="color:#a31515 "")
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREString = WHEREString + <span style="color:#a31515 " AND Sedol = ?";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREcounter = WHEREcounter + 1;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string oldmonth = <span style="color:#a31515 "";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string requestdb = <span style="color:#a31515 "";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbConnection conn = <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af Int64 OutputQuantity = 0;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue for (i = 0; i <= numberofdays; i += 1)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (<span style="color:#2b91af Convert.ToString(firstdate.DayOfWeek)
!= <span style="color:#a31515 "Saturday" || <span style="color:#2b91af Convert.ToString(firstdate.DayOfWeek) !=<span style="color:#a31515 "Saturday")
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string newmonth = <span style="color:#2b91af Convert.ToString(firstdate.Month);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //if in a new month then need to change database
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // if (newmonth != oldmonth)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.Close();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas oldmonth = newmonth;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <br/>
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas // built the oledb connection
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas requestdb = <span style="color:#a31515 "Y:\Ldn\MARKETS\recall\daily\Balances\" +
firstdate.Month + <span style="color:#a31515 "." + firstdate.Year + <span style="color:#a31515 ".mdb";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn = <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.ConnectionString = <span style="color:#a31515 "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source= " + requestdb;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue try
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.Open();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue catch
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af MessageBox.Show(<span style="color:#a31515 "Cant
access database");
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue return;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //}
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string sql = <span style="color:#a31515 "SELECT
[" + searchfor + <span style="color:#a31515 "] FROM tbl_ConsolidatedData WHERE " + WHEREString;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbConnection myConnection
= <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas myConnection.ConnectionString = <span style="color:#a31515 @"PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#a31515 @"Data source= " + requestdb;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbDataAdapter daad = <span style="color:blue new <span style="color:#2b91af OleDbDataAdapter(sql,
myConnection);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af DataTable myDataSet = <span style="color:blue new <span style="color:#2b91af DataTable();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string searchdate = <span style="color:#2b91af Convert.ToString(firstdate);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas searchdate = searchdate.Remove(10);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //add parameters
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "Date"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchdate, searchdate));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "AccountName"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchclient, searchclient));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "Sedol"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchsedol, searchsedol));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas //fill datatable with the result, IT SHOULD ONLY BE A COUPLE OF CELL BUT IT TAKES A VERY LONG TIME TO FILL THE TABLE,
SEEMS TO BE THE CAUSE OF THE PROBLEMS
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.Fill(myDataSet);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas OutputQuantity = 0;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue foreach (<span style="color:#2b91af DataRow myRow <span style="color:blue in myDataSet.Rows)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas OutputQuantity = OutputQuantity + <span style="color:#2b91af Convert.ToInt64(myRow[0]);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas dataGridView14.Columns.Name = firstdate.Day + <span style="color:#a31515 "/" +
firstdate.Month + <span style="color:#a31515 "/" + firstdate.Year;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas dataGridView14.Rows[0].Cells.Value = OutputQuantity;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<div style="font-family:Tahoma; font-size:13px; line-height:normal <span style="font-family:Consolas; font-size:9.5pt; line-height:17px; color:#2a2a2a conn.Close();
View the full article
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal i am using a c# windows form application and trying to connect to a very large Access database, a couple of million lines. i have been using an oledb command and adapter, it seems to work
fine for small queries but i slows down a lot when it loops through the SQL connection numerous times. i was wondering if there was a different way to connect to an access database that would be quicker.
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal <br/>
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
Here is part of my code, the first and second loop seems to be almost instant but then it seems to slow down and takes a few seconds per query which is too slow. Is there anything i can add to the oledb that would stop the decrease in performance.
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
many thanks
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal
<div style="font-family:Tahoma; font-size:13px; line-height:normal // build the where part of the sql string
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREString = WHEREString + <span style="color:#a31515 " AND AccountName = ?";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREcounter = WHEREcounter + 1;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // ticker
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (textBox8.Text != <span style="color:#a31515 "")
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREString = WHEREString + <span style="color:#a31515 " AND Sedol = ?";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas WHEREcounter = WHEREcounter + 1;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string oldmonth = <span style="color:#a31515 "";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string requestdb = <span style="color:#a31515 "";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbConnection conn = <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af Int64 OutputQuantity = 0;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue for (i = 0; i <= numberofdays; i += 1)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (<span style="color:#2b91af Convert.ToString(firstdate.DayOfWeek)
!= <span style="color:#a31515 "Saturday" || <span style="color:#2b91af Convert.ToString(firstdate.DayOfWeek) !=<span style="color:#a31515 "Saturday")
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string newmonth = <span style="color:#2b91af Convert.ToString(firstdate.Month);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //if in a new month then need to change database
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // if (newmonth != oldmonth)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green // {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.Close();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas oldmonth = newmonth;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <br/>
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas // built the oledb connection
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas requestdb = <span style="color:#a31515 "Y:\Ldn\MARKETS\recall\daily\Balances\" +
firstdate.Month + <span style="color:#a31515 "." + firstdate.Year + <span style="color:#a31515 ".mdb";
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn = <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.ConnectionString = <span style="color:#a31515 "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source= " + requestdb;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue try
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas conn.Open();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue catch
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af MessageBox.Show(<span style="color:#a31515 "Cant
access database");
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue return;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //}
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string sql = <span style="color:#a31515 "SELECT
[" + searchfor + <span style="color:#a31515 "] FROM tbl_ConsolidatedData WHERE " + WHEREString;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbConnection myConnection
= <span style="color:blue new <span style="color:#2b91af OleDbConnection();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas myConnection.ConnectionString = <span style="color:#a31515 @"PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#a31515 @"Data source= " + requestdb;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af OleDbDataAdapter daad = <span style="color:blue new <span style="color:#2b91af OleDbDataAdapter(sql,
myConnection);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:#2b91af DataTable myDataSet = <span style="color:blue new <span style="color:#2b91af DataTable();
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue string searchdate = <span style="color:#2b91af Convert.ToString(firstdate);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas searchdate = searchdate.Remove(10);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:green //add parameters
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "Date"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchdate, searchdate));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "AccountName"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchclient, searchclient));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue if (WHEREString.Contains(<span style="color:#a31515 "Sedol"))
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.SelectCommand.Parameters.Add(<span style="color:blue new <span style="color:#2b91af OleDbParameter(<span style="color:#a31515 "@" +
searchsedol, searchsedol));
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas //fill datatable with the result, IT SHOULD ONLY BE A COUPLE OF CELL BUT IT TAKES A VERY LONG TIME TO FILL THE TABLE,
SEEMS TO BE THE CAUSE OF THE PROBLEMS
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas daad.Fill(myDataSet);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas OutputQuantity = 0;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas <span style="color:blue foreach (<span style="color:#2b91af DataRow myRow <span style="color:blue in myDataSet.Rows)
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas {
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas OutputQuantity = OutputQuantity + <span style="color:#2b91af Convert.ToInt64(myRow[0]);
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas }
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas dataGridView14.Columns.Name = firstdate.Day + <span style="color:#a31515 "/" +
firstdate.Month + <span style="color:#a31515 "/" + firstdate.Year;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas dataGridView14.Rows[0].Cells.Value = OutputQuantity;
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<p style="margin-bottom:0.0001pt; line-height:19px; font-size:11pt; font-family:Calibri,sans-serif; color:#2a2a2a; padding-right:0px!important
<span style="line-height:17px; font-size:9.5pt; font-family:Consolas
<div style="font-family:Tahoma; font-size:13px; line-height:normal <span style="font-family:Consolas; font-size:9.5pt; line-height:17px; color:#2a2a2a conn.Close();
View the full article