My DataGridView Not return all rows from Sql Statement

[jl3574]

Hi All, I have a WIndows App with a datagridview which selects a sql select command from sql server and display the rows in a datagridview. i have a refresh button which redraws the grid and recalls the sql statement however, leaving the win app on a whole day i notice when i click on the refresh it's not giving me the same query result if i were to enter the query in the sql server query?
////code////
public void draw_grid()
{
string strconn="the.connection.string";
string sqlstring="Select * From WorkOrder";
SqlDataAdapter da = new SqlDataAdapter(sqlstring,strconn);
DataSet ds = new DataSet();
da.Fill(ds,"DataGrid");
DataTable DT = ds.Tables[0];
DT.TableName="DataGridTable";
}

so each time i click refresh i'm calling draw_grid();

example if my sql table (WOrk Order) has 10 rows when i load up the win app the datagridview will load up 10 rows and let's say after 1 day 3 new rows has been created in the (work ORder) table to have a total of 13 rows if i click on refresh to redaw the datagrid it will only show 10 rows.

ANy Idea any one???

 

[jmeckley]

i really hope you are not hard coding the connection string like this in your app. use the ConnectionStrings node in the app.config to manage the connection strings.

as for you application. I don't see where you are loading the results into the grid. you get the data, but you are not displaying the data.

now, i work in a web environment which is stateless (unlike desktop), but I would assume you need to let the UI grid know there is new data.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jl3574]

Here is the full code of the function.
public void TestLoadDataGridView(string select, bool nonactive)
{
//the string select is the sqlstatement. bool nonactive shows you different types of table
example sql string would be like this :
string sql = "SELECT DISTINCT WOID.WOID, SOID.Customer, WOID.CusJob, WOID.CusJobID, WOID.Description, Service.ServiceCode, WOID_lineup.StatusName,WOID.APH, WOID.HC,Employee.FirstName,WOID.WB_Priority,WorkBoardOrder.OrderID " +
" FROM WOID INNER JOIN SOID ON WOID.SOID = SOID.SOID INNER JOIN WOID_lineup ON WOID_lineup.WOID = WOID.WOID INNER JOIN " +
"Service ON WOID.Service = Service.ServiceName INNER JOIN INVOICE On WOID.WOID=Invoice.WOID INNER JOIN Employee ON Employee.FirstName=WOID.WB_Name INNER JOIN WorkBoardOrder On WorkBoardOrder.WorkBoardOrderName=WOID_lineup.StatusName Where Invoice.Active=0 and WOID.VOID=0 ORDER BY WorkBoardOrder.OrderID;Select FirstName From Employee Where Non_Active = 0 Order By FirstName ";



SqlConnection dbconn = new SqlConnection(Properties.Settings.Default.Whitfield4ConnectionString.TrimEnd());
dbconn.Open();

using (SqlDataAdapter da = new SqlDataAdapter())
{
int GridSize = WorkBoardDataGridView.Size.Width;
int DefaultGridSize = 570;
WorkBoardDataGridView.DataSource = null;
WorkBoardDataGridView.Columns.Clear();

select = WorkBoardStringGenerator();

da.SelectCommand = new SqlCommand(select, dbconn);
da.AcceptChangesDuringFill = true;
da.AcceptChangesDuringUpdate = true;
DataSet ds = new DataSet();

////////////////////////////////////////////////////

//bool haschanged = ds.HasChanges();
//MessageBox.Show(haschanged.ToString().TrimEnd());

//////////////////////////////////////////////////////

da.Fill(ds, "WorkBoard");

DataTable DT1 = ds.Tables[0];
DT1.Locale = System.Globalization.CultureInfo.InvariantCulture;
DT1.TableName = "WorkBoard";

DataTable DT2 = ds.Tables[1];
DT2.TableName = "ExtraWB";
DT2.Locale = System.Globalization.CultureInfo.InvariantCulture;

da.Update(ds, "WorkBoard");

//////////////////////////////////////////////////////

DataGridViewTextBoxColumn WB_Cus = new DataGridViewTextBoxColumn();
WB_Cus.DataPropertyName = "Customer";
WB_Cus.HeaderText = "Customer";
WB_Cus.ValueType = typeof(String);
WB_Cus.Width = 125;
decimal percentage = ((((decimal)WB_Cus.Width) / DefaultGridSize));
WB_Cus.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_Cus);

DataGridViewTextBoxColumn WB_JobNum = new DataGridViewTextBoxColumn();
WB_JobNum.DataPropertyName = "CusJob";
WB_JobNum.HeaderText = "Job#";
WB_JobNum.ValueType = typeof(String);
WB_JobNum.Width = 40;
percentage = ((((decimal)WB_JobNum.Width) / DefaultGridSize));
WB_JobNum.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_JobNum);

DataGridViewTextBoxColumn WB_JobID = new DataGridViewTextBoxColumn();
WB_JobID.DataPropertyName = "CusJobID";
WB_JobID.HeaderText = "ID";
WB_JobID.ValueType = typeof(String);
WB_JobID.Width = 27;
percentage = ((((decimal)WB_JobID.Width) / DefaultGridSize));
WB_JobID.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_JobID);

DataGridViewTextBoxColumn WB_Desc = new DataGridViewTextBoxColumn();
WB_Desc.DataPropertyName = "Description";
WB_Desc.HeaderText = "Desc";
WB_Desc.ValueType = typeof(String);
WB_Desc.Width = 40;
percentage = ((((decimal)WB_Desc.Width) / DefaultGridSize));
WB_Desc.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_Desc);

DataGridViewTextBoxColumn WB_WOID = new DataGridViewTextBoxColumn();
WB_WOID.DataPropertyName = "WOID";
WB_WOID.HeaderText = "WOID";
WB_WOID.ValueType = typeof(Int32);
WB_WOID.Width = 35;
percentage = ((((decimal)WB_WOID.Width) / DefaultGridSize));
WB_WOID.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_WOID);

DataGridViewTextBoxColumn WB_Service = new DataGridViewTextBoxColumn();
WB_Service.DataPropertyName = "ServiceCode";
WB_Service.HeaderText = "Service";
WB_Service.ValueType = typeof(String);
WB_Service.Width = 55;
percentage = ((((decimal)WB_Service.Width) / DefaultGridSize));
WB_Service.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_Service);

if (nonactive == false)
{
DataGridViewTextBoxColumn WB_Status = new DataGridViewTextBoxColumn();
WB_Status.DataPropertyName = "StatusName";
WB_Status.HeaderText = "Status";
WB_Status.ValueType = typeof(String);
WB_Status.Width = 100;
percentage = ((((decimal)WB_Status.Width) / DefaultGridSize));
WB_Status.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_Status);

DataGridViewTextBoxColumn WB_APH = new DataGridViewTextBoxColumn();
WB_APH.DataPropertyName = "APH";
WB_APH.HeaderText = "APH";
WB_APH.ValueType = typeof(Int32);
WB_APH.Width = 23;
percentage = ((((decimal)WB_APH.Width) / DefaultGridSize));
WB_APH.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_APH);

DataGridViewTextBoxColumn WB_HC = new DataGridViewTextBoxColumn();
WB_HC.DataPropertyName = "HC";
WB_HC.HeaderText = "HC";
WB_HC.ValueType = typeof(Int32);
WB_HC.Width = 20;
percentage = ((((decimal)WB_HC.Width) / DefaultGridSize));
WB_HC.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_HC);

WB_Name_CB = new DataGridViewComboBoxColumn();
WB_Name_CB.DataPropertyName = "FirstName";
WB_Name_CB.HeaderText = "FirstName";
WB_Name_CB.ValueType = typeof(String);
WB_Name_CB.Width = 45;
percentage = ((((decimal)WB_Name_CB.Width) / DefaultGridSize));
WB_Name_CB.Width = (int)(GridSize * percentage);

WB_Name_CB.DataSource = RetrieveAlternativeTitles();
WB_Name_CB.ValueMember = "FirstName";
WB_Name_CB.DisplayMember = WB_Name_CB.ValueMember;

WorkBoardDataGridView.Columns.Add(WB_Name_CB);

DataGridViewTextBoxColumn WB_Order = new DataGridViewTextBoxColumn();
WB_Order.DataPropertyName = "WB_Priority";
WB_Order.HeaderText = "Order";
WB_Order.ValueType = typeof(Int32);
WB_Order.Width = 35;
percentage = ((((decimal)WB_Order.Width) / DefaultGridSize));
WB_Order.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(WB_Order);

WorkBoardDataGridView.AutoGenerateColumns = false;
WorkBoardDataGridView.DataSource = ds.Tables["WorkBoard"];
}
if (nonactive == true)
{
DataGridViewTextBoxColumn Received_Date = new DataGridViewTextBoxColumn();
Received_Date.DataPropertyName = "Received_Date";
Received_Date.HeaderText = "Received_Date";
Received_Date.ValueType = typeof(String);
Received_Date.Width = 70;
percentage = ((((decimal)Received_Date.Width) / DefaultGridSize));
Received_Date.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(Received_Date);

DataGridViewTextBoxColumn CompletionTime = new DataGridViewTextBoxColumn();
CompletionTime.DataPropertyName = "CompletionTime";
CompletionTime.HeaderText = "CompletionTime";
CompletionTime.ValueType = typeof(String);
CompletionTime.Width = 70;
percentage = ((((decimal)CompletionTime.Width) / DefaultGridSize));
CompletionTime.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(CompletionTime);

DataGridViewTextBoxColumn TurnAroundTime = new DataGridViewTextBoxColumn();
TurnAroundTime.DataPropertyName = "TurnAroundTime";
TurnAroundTime.HeaderText = "TurnAroundTime";
TurnAroundTime.ValueType = typeof(String);
TurnAroundTime.Width = 75;
percentage = ((((decimal)TurnAroundTime.Width) / DefaultGridSize));
TurnAroundTime.Width = (int)(GridSize * percentage);
WorkBoardDataGridView.Columns.Add(TurnAroundTime);
}



WorkBoardDataGridView.CellFormatting += new DataGridViewCellFormattingEventHandler(dataGridView1_CellFormatting);
WorkBoardDataGridView.CellValueChanged += new DataGridViewCellEventHandler(WorkBoardDataGridView_CellValueChanged);
}
dbconn.Close();

}

 

[jmeckley]

to start i would separate the presentation concerns from the data access concerns. this will make it easier to find the problem.

as it stands now you have 1 monolithic block of code. you also appear to have repetitive blocks of code where only a value or 2 are different. extract this code into a separate member to reduce the amount of duplicate code.

you have a single select statement that is loaded into a dataset, but you are defining 2 tables. not sure why.

you have a lot of problem with the database connection management. if any error is thrown you will have connections that are never closed. you also never dispose. look into try/catch/finally blocks or read my faq on database connection management. you will need to convert the web references to desktop, but the principles are the same.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[PatrickIRL]

Why 2 SELECT statements in the SQL string??? Appended to your first SELECT is another SELECT separated by a semi-colon. Is this deliberate? If so then the SQL will return 2 recordsets.

I totally agree with Jason, your method needs major work or at the very least refactoring. This will make it easier to debug.

Patrick

 

[jl3574]

The reason why i have two sql statement is that i have a combobox as one of the column which datsource is from the second set of datatable.

However i will try to add Dispose() Command instead of Close() and adding a try catch block with sqlconnection.dispose(). I Hope this works thanks.