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";

                  
}

LVL 2
ziwez0Asked:
Who is Participating?
 
Farshid-ZakerCommented:
change line 61 of file frmPriceFinder.cs to following:
instead of
      cbohotel.ValueMember = "hotelname";

insert:
      cbohotel.ValueMember = "hotelid";

<Farshid/>
0
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ziwez0Author Commented:
sorry by the way, i do have a .fill method just didnt show it - oops
0
 
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
 
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.

All Courses

From novice to tech pro — start learning today.