I am trying to fill a BO with filtered data , can anyone tell me what I need to change ?

public void pdt_data(string lic)

{

string s = "select * from truck where truck_license = "+lic;

FillDataTable(s);

}

OK so I have written the following code to look up the truck license it works fine if there is only one number

public bool GetTrucklic(long carrier, out string truckLic)

{

//-- Establish a return var

bool dataFound = false;

//-- Set default return value if there is no value in db.

truckLic = string.Empty;

//-- Build SQL string

StringBuilder sqlBuilder = new StringBuilder();

sqlBuilder.AppendLine("Select count(*)");

sqlBuilder.AppendLine("From truck");

sqlBuilder.AppendLine("Where FK_customer = @carr");

//-- Get data

using (SqlCommand cmd = new SqlCommand())

{

cmd.CommandText = sqlBuilder.ToString();

cmd.Parameters.AddWithValue("@carr", carrier).SqlDbType = SqlDbType.VarChar;

//-- Get Data, handle Null from database and nulls.

// This can return a DbNull.Value (i.e. a NULL from SQL) or it

// can return null (.NET) if there is no matching record in database

// (i.e. an empty recordset is returned). Thus before we can set the

// return variable, we have to test for these cases. It is also important

// to set a default value (as done above), in case no data is found

// in the database.

object result = this.ExecuteScalar(cmd);

if (!DBNull.Value.Equals(result) && result != null)

{

//-- By using an output variable (truckPdt) and a return variable

// (dataFound), I can somewhat simplify the

// client code because I can directly test

// the return value and get the value at the

// same time. It is equally valid to directly

// return the value (truckPdt) and then

// testing if it is a null or empty.

//truckPdt = result.ToString();

if (result.Equals(1))

{

dataFound = true;

}

else

{

dataFound = false;

}

}

}

if (dataFound)

{

//-- Build SQL string

StringBuilder sqlBuilder2 = new StringBuilder();

sqlBuilder2.AppendLine("Select truck_license");

sqlBuilder2.AppendLine("From truck");

sqlBuilder2.AppendLine("Where FK_customer = @carr");

//-- Get data

using (SqlCommand cmd2 = new SqlCommand())

{

cmd2.CommandText = sqlBuilder2.ToString();

cmd2.Parameters.AddWithValue("@carr", carrier).SqlDbType = SqlDbType.VarChar;

//-- Get Data, handle Null from database and nulls.

// This can return a DbNull.Value (i.e. a NULL from SQL) or it

// can return null (.NET) if there is no matching record in database

// (i.e. an empty recordset is returned). Thus before we can set the

// return variable, we have to test for these cases. It is also important

// to set a default value (as done above), in case no data is found

// in the database.

object result2 = this.ExecuteScalar(cmd2);

if (!DBNull.Value.Equals(result2) && result2 != null)

{

//-- By using an output variable (truckPdt) and a return variable

// (dataFound), I can somewhat simplify the

// client code because I can directly test

// the return value and get the value at the

// same time. It is equally valid to directly

// return the value (truckPdt) and then

// testing if it is a null or empty.

truckLic = result2.ToString();

dataFound = true;

}

}

}

//-- Return if data was found.

return dataFound;

}

I am calling this from the enter event of the license textbox. The problem is if a company has more than one truck and therefore more than one license number. I am trying to call a browse dialog by repopulating a BO based on the carriers PK.

private void Trk_lic_Enter(object sender, EventArgs e)

{

string trkLic = string.Empty;

string trkLic2 = string.Empty;

if (this.truckBO1.GetTrucklic(certificateBO1.FK_Customer2, out trkLic))

{

this.Trk_lic.Text = trkLic; // this works and returns either one license number or blank if none exists.

}

else

{

trkLic2 = this.getTrk_lic();

this.Trk_lic.Text = trkLic2;

}

This sets up the browseDIalog and it shows all the license numbers for a carrier

private string getTrk_lic()

{

bool trk_lic = false;

string ret_val = string.Empty;

//-- Only show one panel, and put the browse results in the visible panel

this.browseDialogtruck.AdvancedOptions = false;

this.browseDialogtruck.AllowHideResults = false;

this.browseDialogtruck.AllowSearchFieldsButton = false;

this.browseDialogtruck.SearchFields["FK_customer"].InitialValue = certificateBO1.FK_Customer2.ToString();

this.browseDialogtruck.BusinessObjectToPopulate.Equals(truckBO2);

//this.browseDialogtruck.OverrideSearchTableName.Equals(Tru);

this.browseDialogtruck.ShowDialog(true);

if (trk_lic)

{

return truckBO2.truck_license;

}

else

{

return "";

}

}

The problem is that I am back to the maintainence form it starts over again. My question is is there a way to use executeReader to return all the values and put them into the BrowseDialog?

So in the light of day I modified the code to be

private string getTrk_lic()

{

//-- Only show one panel, and put the browse results in the visible panel

this.browseDialogtruck.AdvancedOptions = false;

this.browseDialogtruck.AllowHideResults = false;

this.browseDialogtruck.AllowSearchFieldsButton = false;

this.browseDialogtruck.SearchFields["FK_customer"].InitialValue = certificateBO1.FK_Customer2.ToString();

// '-- Show the browse dialog  From the lookup control sample by Ivan

// If CustomersBrowseDialog1.ShowDialog() = DialogResult.OK Then

// Me.txtCode.Text = CustomersBO1.cust_pk.ToString

// End If

if (this.browseDialogtruck.ShowDialog(true).Equals(DialogResult.OK))

{

return truckBO2.truck_license;

}

else

{

return "";

}

}

this now works and returns the value but the form still wants to start over, how do I get the focus back to the forms main BO (certificateBO1) and back to the textbox that calls this browsedialog?

OK the fog is clearing. I had the tab control that textbox is on disabled in the FormEditingStaeChanged event - Don't know why but at some point it must have been a good idea! Code is now working great if there is only one value to assign it is assigned, if there are more than one value the BrowseDialog appears popualted with valid choices and choice selected is entered in the textbox or if there is no selection textbox is blank and allows entry as this may be a one time transaction using this license number.

Pretty good Ian, congratulations!!!

Edhy Rijo

Glad you are getting it going. While programming can be frustrating, I love those "fog is clearing" moments.