Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Help Needed!!

OK, im a complete newbie to c#, im from a vb background so everything seems kinda backwards.

So i need help finishing writing it

Ok this is what i have....

on my form i have two datetime pickers called "dtpstart" and "dtpend" a combo box called "cbohotel", a textbox called "txttotalcost" and a button called "cmdgetprice".

I then also have my database which contains all the info, the tables are called

TBLCOST            TBLHOTEL          TBLDATE
-costid                 -hotelid              -dateid
-hotelid                -hotelname        -thedate
-dateid
-cost

So i when the user looksup for a hotel price between a specific date it will return the TOTAL price for a hotel.

Example:

16th October 2004(dtpstart)     --     24th October 2004  (dtpend)

Myhotel (cbohotel)

This is what i have done already....

public frmPriceFinder()
            {
InitializeComponent();

string cs = @"provider=Microsoft.JET.OLEDB.4.0;data source=hotelcosts.mdb";
                  
OleDbConnection conn = new OleDbConnection(cs);
                  
                                    
OleDbDataAdapter hotels = new OleDbDataAdapter("Select hotelid, hotelname FROM tblhotel ORDER BY hotelname",conn);

OleDbDataAdapter hotelcosts = new OleDbDataAdapter("SELECT tblcost.costid, tblcost.dateid, tblcost.hotelid, tbldate.thedate FROM tblcost INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.dateid",conn);
                                    
                        
DataSet dsf = new DataSet();
                  
cbohotel.DataSource = dsf.Tables["Hotels"];
cbohotel.DisplayMember = "hotelname";

                  
}

0
ziwez0
Asked:
ziwez0
  • 14
  • 10
1 Solution
 
ArvarisCommented:
I take it your problem is the data is not showing up?

Well that's because you're not filling the information from your data adapter into your dataset.
do this after DataSet dsf = new DataSet();

     hotels.Fill(dsf);

    cbohotel.DataSource = dsf
    cbohotel.DataTextField = 'hotelname'
    cbohotel.DataValueField = 'hotelid'
    cbohotel.DataBind()
0
 
ziwez0Author Commented:
oh no, the dateset is working fab, combo is populating

the problem is im stuck about what to do next, ok ive populated the combo box - great, but as i said i need to do a lookup in my database and return the cost based on the information selected from "dtpstart", "dtpend" and "cbohotel".
0
 
ziwez0Author Commented:
sorry by the way, i do have a .fill method just didnt show it - oops
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Farshid-ZakerCommented:
ok, what do you need to know? I think the problem is quite easy. You can narrow your sql statement, based on your date fields. Add an onClick event handler for your button. write these codes in it:

add this line after cbohotel.DisplayMember = "hotelname";
cbohotel.ValueMember = "hotelid";

and this will your button event handler:

OleDbConnection con = new OleDbConnection(".........");
con.Open();

string startdate = System.Data.SqlTypes.SqlDateTime.Parse(dtpstart.Value.ToShortDateString());
string enddate = System.Data.SqlTypes.SqlDateTime.Parse(dtpend.Value.ToShortDateString());

OleDbCommand command = new OleDbCommand("SELECT tblcost.costid, tblcost.dateid, tblcost.hotelid, tbldate.thedate FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.dateid >= " + stratdate + " AND tbldate.dateid <= " + enddate, con);
long result = Int32.Parse(command.ExecuteScalar());
con.Close();

result variable will contain the result! Have a good time.

Farshid
0
 
Farshid-ZakerCommented:
too sorry, change the previous code by this:

OleDbCommand command = new OleDbCommand("SELECT sum(tblcost.cost), tblhotel.hotelid  FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.dateid >= " + stratdate + " AND tbldate.dateid <= " + enddate + " group by tblhotel.hotelid", con);

Farshid
0
 
ziwez0Author Commented:
Hi,  i dont think that WHERE clause will work, cos its gonna look a date up in a tbldate.dateid (this is a ref and not the actual date) should it not be WHERE tbldate.thedate=....??
0
 
ziwez0Author Commented:
got this errors

(335): Cannot implicitly convert type 'System.Data.SqlTypes.SqlDateTime' to 'string'
(334): Cannot implicitly convert type 'System.Data.SqlTypes.SqlDateTime' to 'string'
0
 
Farshid-ZakerCommented:
sorry again! replace these two lines:

string startdate = System.Data.SqlTypes.SqlDateTime.Parse(dtpstart.Value.ToShortDateString()).ToString();
string enddate = System.Data.SqlTypes.SqlDateTime.Parse(dtpend.Value.ToShortDateString()).ToString();


Farshid
0
 
ziwez0Author Commented:
Got this error..., when i pressed the cmdget button

An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll

Additional information: String was not recognized as a valid DateTime

points to this line "string enddate = SqlDateTime.Parse(dtpend.Value.ToShortDateString()).ToString();"

in order to show the value i just done this..
txttotalcost.Text = command.ToString(); - is this correct?
0
 
ziwez0Author Commented:
ahh the error is something to do with the format as i want to do it in uk format date/month/year

its doing it in month/date/year
0
 
Farshid-ZakerCommented:
Here is the complete code. I did not understand what you mean by command.ToString()??? Try to use this code.

OleDbConnection con = new OleDbConnection(".........");
con.Open();

string startdate = (new System.Data.SqlTypes.SqlDateTime(dtpstart.Value)).ToString();
string enddate = (new System.Data.SqlTypes.SqlDateTime(dtpend.Value)).ToString();

OleDbCommand command = new OleDbCommand("SELECT sum(tblcost.cost), tblhotel.hotelid  FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.dateid >= " + stratdate + " AND tbldate.dateid <= " + enddate + " AND tblhotel.hotelid = " + cbohotel.SelectedValue.ToString() , con);

long result = Int32.Parse(command.ExecuteScalar());
con.Close();

Farshid
0
 
ziwez0Author Commented:
(339): Argument '1': cannot convert from 'object' to 'string'
(339): The best overloaded method match for 'int.Parse(string)' has some invalid arguments
point to this "long result = Int32.Parse(command.ExecuteScalar());"
got these error, what i meant was how do i display the result in command in mt text box called txttotalcost.

thanks David
0
 
Farshid-ZakerCommented:
Try this:
long result = Int32.Parse(command.ExecuteScalar().ToString());

Farshid
0
 
ziwez0Author Commented:
ok tried that and got

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll, and points to that line long result = Int32.Parse(command.ExecuteScalar().ToString());

0
 
Farshid-ZakerCommented:
please copy your code here. I will review and finalize it.

Farshid
0
 
ziwez0Author Commented:
public frmPriceFinder()
            {
                  
                  InitializeComponent();
                  

                  string cs = @"provider=Microsoft.JET.OLEDB.4.0;data source=hotelcosts.mdb";
                  
                  OleDbConnection conn = new OleDbConnection(cs);
                  
            
            OleDbDataAdapter hotels = new OleDbDataAdapter("Select hotelid, hotelname FROM tblhotel ORDER BY hotelname",conn);
            OleDbDataAdapter hotelcosts = new OleDbDataAdapter("SELECT tblcost.costid, tblcost.dateid, tblcost.hotelid, tbldate.thedate FROM tblcost INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.thedate=" + dtpstart,conn);
                   DataSet dsf = new DataSet();
                  
                  hotels.Fill(dsf,"Hotels");
            
                  cbohotel.DataSource = dsf.Tables["Hotels"];
                  cbohotel.DisplayMember = "hotelname";
                  
                  cbohotel.ValueMember = "hotelname";

            
            }

            /// <summary>
            /// Clean up any resources being used.
            /// </summary>
            protected override void Dispose( bool disposing )
            {
                  if( disposing )
                  {
                        if(components != null)
                        {
                              components.Dispose();
                        }
                  }
                  base.Dispose( disposing );
            }

            #region Windows Form Designer generated code
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {
                  this.components = new System.ComponentModel.Container();
                  System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(frmPriceFinder));
                  this.ImgList = new System.Windows.Forms.ImageList(this.components);
                  this.mainMenu1 = new System.Windows.Forms.MainMenu();
                  this.menuItem1 = new System.Windows.Forms.MenuItem();
                  this.menuItem2 = new System.Windows.Forms.MenuItem();
                  this.toolBar1 = new System.Windows.Forms.ToolBar();
                  this.toolBarButton1 = new System.Windows.Forms.ToolBarButton();
                  this.toolBarButton2 = new System.Windows.Forms.ToolBarButton();
                  this.dtpend = new System.Windows.Forms.DateTimePicker();
                  this.lblreturn = new System.Windows.Forms.Label();
                  this.lbldepart = new System.Windows.Forms.Label();
                  this.dtpstart = new System.Windows.Forms.DateTimePicker();
                  this.lblhotel = new System.Windows.Forms.Label();
                  this.cbohotel = new System.Windows.Forms.ComboBox();
                  this.lblsummary = new System.Windows.Forms.Label();
                  this.lblhotel2 = new System.Windows.Forms.Label();
                  this.lblduration = new System.Windows.Forms.Label();
                  this.lblcost = new System.Windows.Forms.Label();
                  this.lblhotelvalue = new System.Windows.Forms.Label();
                  this.txttotalcost = new System.Windows.Forms.TextBox();
                  this.dataGrid1 = new System.Windows.Forms.DataGrid();
                  this.cmdget = new System.Windows.Forms.Button();
                  ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
                  this.SuspendLayout();
                  //
                  // ImgList
                  //
                  this.ImgList.ColorDepth = System.Windows.Forms.ColorDepth.Depth24Bit;
                  this.ImgList.ImageSize = new System.Drawing.Size(25, 25);
                  this.ImgList.ImageStream = ((System.Windows.Forms.ImageListStreamer)(resources.GetObject("ImgList.ImageStream")));
                  this.ImgList.TransparentColor = System.Drawing.Color.Transparent;
                  //
                  // mainMenu1
                  //
                  this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                                                                this.menuItem1});
                  //
                  // menuItem1
                  //
                  this.menuItem1.Index = 0;
                  this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
                                                                                                                                this.menuItem2});
                  this.menuItem1.Text = "&File";
                  //
                  // menuItem2
                  //
                  this.menuItem2.Index = 0;
                  this.menuItem2.Text = "E&xit";
                  //
                  // toolBar1
                  //
                  this.toolBar1.Buttons.AddRange(new System.Windows.Forms.ToolBarButton[] {
                                                                                                                                    this.toolBarButton1,
                                                                                                                                    this.toolBarButton2});
                  this.toolBar1.ButtonSize = new System.Drawing.Size(25, 25);
                  this.toolBar1.DropDownArrows = true;
                  this.toolBar1.ImageList = this.ImgList;
                  this.toolBar1.Location = new System.Drawing.Point(0, 0);
                  this.toolBar1.Name = "toolBar1";
                  this.toolBar1.ShowToolTips = true;
                  this.toolBar1.Size = new System.Drawing.Size(656, 37);
                  this.toolBar1.TabIndex = 8;
                  this.toolBar1.ButtonClick += new System.Windows.Forms.ToolBarButtonClickEventHandler(this.toolBar1_ButtonClick);
                  //
                  // toolBarButton1
                  //
                  this.toolBarButton1.ImageIndex = 0;
                  //
                  // toolBarButton2
                  //
                  this.toolBarButton2.ImageIndex = 2;
                  //
                  // dtpend
                  //
                  this.dtpend.CalendarTitleBackColor = System.Drawing.Color.Navy;
                  this.dtpend.Location = new System.Drawing.Point(400, 80);
                  this.dtpend.Name = "dtpend";
                  this.dtpend.TabIndex = 12;
                  //
                  // lblreturn
                  //
                  this.lblreturn.Location = new System.Drawing.Point(344, 80);
                  this.lblreturn.Name = "lblreturn";
                  this.lblreturn.Size = new System.Drawing.Size(48, 23);
                  this.lblreturn.TabIndex = 11;
                  this.lblreturn.Text = "Return:";
                  //
                  // lbldepart
                  //
                  this.lbldepart.Location = new System.Drawing.Point(24, 80);
                  this.lbldepart.Name = "lbldepart";
                  this.lbldepart.Size = new System.Drawing.Size(72, 23);
                  this.lbldepart.TabIndex = 10;
                  this.lbldepart.Text = "Departure:";
                  //
                  // dtpstart
                  //
                  this.dtpstart.CalendarTitleBackColor = System.Drawing.Color.Navy;
                  this.dtpstart.Location = new System.Drawing.Point(96, 80);
                  this.dtpstart.MaxDate = new System.DateTime(2006, 12, 2, 0, 0, 0, 0);
                  this.dtpstart.MinDate = new System.DateTime(2004, 10, 14, 0, 0, 0, 0);
                  this.dtpstart.Name = "dtpstart";
                  this.dtpstart.Size = new System.Drawing.Size(216, 20);
                  this.dtpstart.TabIndex = 9;
                  //
                  // lblhotel
                  //
                  this.lblhotel.Location = new System.Drawing.Point(24, 112);
                  this.lblhotel.Name = "lblhotel";
                  this.lblhotel.Size = new System.Drawing.Size(40, 23);
                  this.lblhotel.TabIndex = 14;
                  this.lblhotel.Text = "Hotel:";
                  //
                  // cbohotel
                  //
                  this.cbohotel.Location = new System.Drawing.Point(96, 112);
                  this.cbohotel.Name = "cbohotel";
                  this.cbohotel.Size = new System.Drawing.Size(216, 21);
                  this.cbohotel.TabIndex = 13;
                  this.cbohotel.SelectedIndexChanged += new System.EventHandler(this.cbohotel_SelectedIndexChanged);
                  //
                  // lblsummary
                  //
                  this.lblsummary.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Underline, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
                  this.lblsummary.Location = new System.Drawing.Point(96, 160);
                  this.lblsummary.Name = "lblsummary";
                  this.lblsummary.Size = new System.Drawing.Size(80, 24);
                  this.lblsummary.TabIndex = 15;
                  this.lblsummary.Text = "Summary";
                  //
                  // lblhotel2
                  //
                  this.lblhotel2.Location = new System.Drawing.Point(96, 208);
                  this.lblhotel2.Name = "lblhotel2";
                  this.lblhotel2.Size = new System.Drawing.Size(40, 23);
                  this.lblhotel2.TabIndex = 16;
                  this.lblhotel2.Text = "Hotel:";
                  //
                  // lblduration
                  //
                  this.lblduration.Location = new System.Drawing.Point(96, 232);
                  this.lblduration.Name = "lblduration";
                  this.lblduration.Size = new System.Drawing.Size(56, 24);
                  this.lblduration.TabIndex = 17;
                  this.lblduration.Text = "Duration:";
                  //
                  // lblcost
                  //
                  this.lblcost.Location = new System.Drawing.Point(96, 256);
                  this.lblcost.Name = "lblcost";
                  this.lblcost.Size = new System.Drawing.Size(40, 24);
                  this.lblcost.TabIndex = 18;
                  this.lblcost.Text = "Cost:";
                  //
                  // lblhotelvalue
                  //
                  this.lblhotelvalue.Location = new System.Drawing.Point(144, 208);
                  this.lblhotelvalue.Name = "lblhotelvalue";
                  this.lblhotelvalue.Size = new System.Drawing.Size(88, 24);
                  this.lblhotelvalue.TabIndex = 19;
                  //
                  // txttotalcost
                  //
                  this.txttotalcost.Location = new System.Drawing.Point(136, 256);
                  this.txttotalcost.Name = "txttotalcost";
                  this.txttotalcost.TabIndex = 20;
                  this.txttotalcost.Text = "";
                  //
                  // dataGrid1
                  //
                  this.dataGrid1.DataMember = "";
                  this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
                  this.dataGrid1.Location = new System.Drawing.Point(304, 168);
                  this.dataGrid1.Name = "dataGrid1";
                  this.dataGrid1.TabIndex = 21;
                  //
                  // cmdget
                  //
                  this.cmdget.Location = new System.Drawing.Point(344, 112);
                  this.cmdget.Name = "cmdget";
                  this.cmdget.Size = new System.Drawing.Size(64, 32);
                  this.cmdget.TabIndex = 22;
                  this.cmdget.Text = "Search";
                  this.cmdget.Click += new System.EventHandler(this.cmdget_Click);
                  //
                  // frmPriceFinder
                  //
                  this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
                  this.ClientSize = new System.Drawing.Size(656, 305);
                  this.Controls.Add(this.cmdget);
                  this.Controls.Add(this.dataGrid1);
                  this.Controls.Add(this.txttotalcost);
                  this.Controls.Add(this.lblhotelvalue);
                  this.Controls.Add(this.lblcost);
                  this.Controls.Add(this.lblduration);
                  this.Controls.Add(this.lblhotel2);
                  this.Controls.Add(this.lblsummary);
                  this.Controls.Add(this.lblhotel);
                  this.Controls.Add(this.cbohotel);
                  this.Controls.Add(this.dtpend);
                  this.Controls.Add(this.lblreturn);
                  this.Controls.Add(this.lbldepart);
                  this.Controls.Add(this.dtpstart);
                  this.Controls.Add(this.toolBar1);
                  this.Menu = this.mainMenu1;
                  this.Name = "frmPriceFinder";
                  this.Text = "Pricefinder";
                  ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
                  this.ResumeLayout(false);

            }
            #endregion

            private void toolBar1_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e)
            {
                  frmconfig openfrmconfig = new frmconfig();
                  openfrmconfig.Focus();
            }

            private void cbohotel_SelectedIndexChanged(object sender, System.EventArgs e)
            {
                  lblhotelvalue.Text = cbohotel.SelectedValue.ToString();
            }

            private void cmdget_Click(object sender, System.EventArgs e)
            {
                  OleDbConnection con = new OleDbConnection("provider=Microsoft.JET.OLEDB.4.0;data source=hotelcosts.mdb");
                  con.Open();

                  string startdate = SqlDateTime.Parse(dtpstart.Value.ToShortDateString()).ToString();
                  string enddate = SqlDateTime.Parse(dtpend.Value.ToShortDateString()).ToString();

                  //OleDbCommand command = new OleDbCommand("SELECT sum(tblcost.cost), tblhotel.hotelid  FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.thedate >= " + startdate + " AND tbldate.dateid <= " + enddate + " group by tblhotel.hotelid", con);
                  OleDbCommand command = new OleDbCommand("SELECT sum(tblcost.cost), tblhotel.hotelid  FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.dateid >= " + startdate + " AND tbldate.dateid <= " + enddate + " AND tblhotel.hotelid = " + cbohotel.SelectedValue.ToString() , con);
                  long result = Int32.Parse(command.ExecuteScalar().ToString());
                  con.Close();

                  
                  MessageBox.Show(command.ToString());

            }

            

            

            
      }
}
0
 
Farshid-ZakerCommented:
Hi,
I created the database and a project containing your source. You should change the cmdget_Click method body with the following code:

         private void cmdget_Click(object sender, System.EventArgs e)
          {
               OleDbConnection con = new OleDbConnection("provider=Microsoft.JET.OLEDB.4.0;data source=hotelcosts.mdb");
               con.Open();

               string startdate = dtpstart.Value.ToString("#d/MMM/yyyy#");
               string enddate = dtpend.Value.ToString("#d/MMM/yyyy#");

               //OleDbCommand command = new OleDbCommand("SELECT sum(tblcost.cost), tblhotel.hotelid  FROM tblhotel INNER JOIN tblcost ON tblhotel.hotelid = tblcost.hotelid INNER JOIN tbldate ON tblcost.dateid = tbldate.dateid WHERE tbldate.thedate >= " + startdate + " AND tbldate.dateid <= " + enddate + " group by tblhotel.hotelid", con);
               OleDbCommand command = new OleDbCommand("SELECT Sum([cost]), TBLHOTEL.hotelid FROM (TBLCOST INNER JOIN TBLDATE ON TBLCOST.dateid = TBLDATE.dateid) INNER JOIN TBLHOTEL ON TBLCOST.hotelid = TBLHOTEL.hotelid WHERE tbldate.thedate >= " + startdate + " AND tbldate.thedate <= " + enddate + " AND tblhotel.hotelid=" + cbohotel.SelectedValue.ToString() + " GROUP BY TBLHOTEL.hotelid" , con);
               long result = Int32.Parse(command.ExecuteScalar().ToString());
               con.Close();

               
               // I don't know what this line means??? MessageBox.Show(command.ToString());

          }

<Farshid/>
0
 
ziwez0Author Commented:
got another break error on this line

long result = Int32.Parse(command.ExecuteScalar().ToString());

 // I don't know what this line means??? MessageBox.Show(command.ToString()); = i wanted to show the cost, in a mesagebox.

Ok what i have done is uploaded the code and database(zipped) this might help us solve the problem...

www.whathappenedwhen.co.uk/sample.zip


0
 
Farshid-ZakerCommented:
when trying to download, I got a file with zero bytes size.
Please send it to fzaker at gmail dot com.
0
 
ziwez0Author Commented:
sent..
0
 
ziwez0Author Commented:
oh got a message back saying failed to deliver

 ive uploaded again, FTP error last time, should be fine now
0
 
Farshid-ZakerCommented:
ok, downloading now.
0
 
Farshid-ZakerCommented:
change line 61 of file frmPriceFinder.cs to following:
instead of
      cbohotel.ValueMember = "hotelname";

insert:
      cbohotel.ValueMember = "hotelid";

<Farshid/>
0
 
ziwez0Author Commented:
ok, but how do i get the total value to display in txttotalcost?
0
 
ziwez0Author Commented:
ignore last post,

cheers for all your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now