Trouble obtaining database table values...

[gggram2000]

Hi, I need some suggestions...

I have created a form in visual studio 2005 in which a user chooses between two dates. I placed two dateTimePickers and a View Button. When the user clicks the view button, I have a sql command that is supposed to retrieve all values from both table 'Guest' and table 'Sales' in sql server 2005.
My code looks like this:
(c-sharp)

SqlConnection dbConnection = new SqlConnection(xmldb);
            SqlCommand cmd = new SqlCommand(" SELECT * From Guest g, Sales s Where s.Date >= '" + dateTimePicker1.Value + "' And s.Date <= '" + dateTimePicker2.Value + "' Order By s.Date ", dbConnection1);                                    
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);             
            DataTable dt = new DataTable();
            da.Fill(dt);
            textBox1.Text = dt.Rows[0][0].ToString();

My obvious problem here is that since I want all values from both tables, I don't know what other process I should use. For example:
textBox1.Text = dt.Rows[0][0].ToString(); will not work because it will only return the number of rows...The other problem is that I have tested the function with actual dates and it works fine, so it has something to do with dateTimePickers1.Text or something else...I would really appreciate some suggestions as to how to obtains all values and details found on those two tables between those two dates and on what would be the easiest way to display it. Thanks. Any input as to obtain all the values will be greatly appreaciated...

--GM

[gggram2000]

Is there any way to retrieve all the info from two tables into a textBox? has anyone ever tried something like it?....

[ck9663]

Hi, I need some suggestions...

I have created a form in visual studio 2005 in which a user chooses between two dates. I placed two dateTimePickers and a View Button. When the user clicks the view button, I have a sql command that is supposed to retrieve all values from both table 'Guest' and table 'Sales' in sql server 2005.
My code looks like this:
(c-sharp)

SqlConnection dbConnection = new SqlConnection(xmldb);
            SqlCommand cmd = new SqlCommand(" SELECT * From Guest g, Sales s Where s.Date >= '" + dateTimePicker1.Value + "' And s.Date <= '" + dateTimePicker2.Value + "' Order By s.Date ", dbConnection1);                                    
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);             
            DataTable dt = new DataTable();
            da.Fill(dt);
            textBox1.Text = dt.Rows[0][0].ToString();

My obvious problem here is that since I want all values from both tables, I don't know what other process I should use. For example:
textBox1.Text = dt.Rows[0][0].ToString(); will not work because it will only return the number of rows...The other problem is that I have tested the function with actual dates and it works fine, so it has something to do with dateTimePickers1.Text or something else...I would really appreciate some suggestions as to how to obtains all values and details found on those two tables between those two dates and on what would be the easiest way to display it. Thanks. Any input as to obtain all the values will be greatly appreaciated...

--GM

is it ok for you to post the structure of these two tables?

-- CK

[gggram2000]

is it ok for you to post the structure of these two tables?

-- CK

Sure...Here it is below, sorry for the size...this thing doesn't allow any bigger.

[gggram2000]

Hi, since I'm not sure how to obtain all values (select*), I'ved decided to select the columns I need. When I want to display them on the textBox my code looks like this:

textBox1.Text = dt1.Rows[0]["GuestFirstName"].ToString();                
textBox1.Text += dt1.Rows[0]["GuestLastName"].ToString();                
textBox1.Text += dt1.Rows[0]["GuestAddress"].ToString();                
textBox1.Text += dt1.Rows[0]["Country"].ToString();                
textBox1.Text += dt1.Rows[0]["GuestPhone"].ToString();                
textBox1.Text += dt1.Rows[0]["GuestEmail"].ToString();                
textBox1.Text += dt1.Rows[0]["SaleID"].ToString();                
textBox1.Text += dt1.Rows[0]["SaleTotal"].ToString();                
textBox1.Text += dt1.Rows[0]["InvoiceNumber"].ToString();                
textBox1.Text += dt1.Rows[0]["SaleItem"].ToString();                
textBox1.Text += dt1.Rows[0]["PaymentType"].ToString();                
textBox1.Text += dt1.Rows[0]["SaleDiscount"].ToString();                
textBox1.Text += dt1.Rows[0]["SalesStatus"].ToString();
textBox1.Text += dt1.Rows[0]["Date"].ToString();
textBox1.Text += dt1.Rows[0]["Quantity"].ToString();
textBox1.Text += dt1.Rows[0]["SalePrice"].ToString();

But this method will only get me the first row, what can I do to get all the rows that are (n) number. Also the values are not spaced out, any links to a similar problem will be great.

[gggram2000]

Ok I do get all the rows with that method, the only issue is that it's too tedious when it comes to hundreds of sales in between those dates. I would need to enter each row a hundred times and still don't know if it will be spacious enough to display the info in. Is there an easier method to display all the info? Anyone??

[ck9663]

Ok I do get all the rows with that method, the only issue is that it's too tedious when it comes to hundreds of sales in between those dates. I would need to enter each row a hundred times and still don't know if it will be spacious enough to display the info in. Is there an easier method to display all the info? Anyone??

you're right. it does not get any bigger than that. could you just write here the structure, please.

-- ck

[gggram2000]

you're right. it does not get any bigger than that. could you just write here the structure, please.

-- ck

Ok. The columns I need from Guest are these:
GuestFirstName, GuestLastName, GuestAddress, Country, GuestPhone, and GuestEmail.

From Sales are these:
SaleTotal, InvoiceNumber, SaleItem, PaymentType, SaleDiscount, SalesStatus, Date, Quantity, SalePrice

so basically i have this select statement:

SELECT g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleID,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.SalesStatus,s.Date,s.Quantity,s.SalePrice From Guest g, Sales s Where g.GuestID = s.GuestID And s.Date Between '" + dateTimePicker1.Value + "' And '" + dateTimePicker2.Value + "' Order By s.Date

but I still don't know how to display it properly on a single textBox.

[ck9663]

Ok. The columns I need from Guest are these:
GuestFirstName, GuestLastName, GuestAddress, Country, GuestPhone, and GuestEmail.

From Sales are these:
SaleTotal, InvoiceNumber, SaleItem, PaymentType, SaleDiscount, SalesStatus, Date, Quantity, SalePrice

so basically i have this select statement:

SELECT g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail,s.SaleID,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.SalesStatus,s.Date,s.Quantity,s.SalePrice From Guest g, Sales s Where g.GuestID = s.GuestID And s.Date Between '" + dateTimePicker1.Value + "' And '" + dateTimePicker2.Value + "' Order By s.Date

but I still don't know how to display it properly on a single textBox.

you want the entire record in a single textbox? it's not really wrong, but it'll be unreadable. here goes:

first try:

SELECT g.GuestFirstName+g.GuestLastName+g.GuestAddress+g. Country+g.GuestPhone+g.GuestEmail+s.SaleID+s.SaleT otal+s.InvoiceNumber+s.SaleItem+s.PaymentType+s.Sa leDiscount+s.SalesStatus+s.Date+s.Quantity+s.SaleP rice as singleresult From Guest g, Sales s Where g.GuestID = s.GuestID And s.Date Between '" + dateTimePicker1.Value + "' And '" + dateTimePicker2.Value + "' Order By s.Date

it will return a single field called "singleresult". on your GUI side, you will need a datagrid viewer or you might want to do a DO UNTIL RecordSet.EOF = true and display each rows.

-- ck

[gggram2000]

I tested it to see if it would work in sql server but it displays this error:

Conversion failed when converting the varchar value 'MarioBetancourt14 Hopkins [e-mail]' to data type int.
 

[ck9663]

I tested it to see if it would work in sql server but it displays this error:

Conversion failed when converting the varchar value 'MarioBetancourt14 Hopkins [e-mail]' to data type int.
 

for all non-char fields, you need to cast it...do a CAST(ColumnName as varchar(50))

-- ck

[gggram2000]

for all non-char fields, you need to cast it...do a CAST(ColumnName as varchar(50))

-- ck

Ok kool, hey i found an easier way to do it. I ended up using two datagridviews. One displaying the Guest info and the next displaying the sales info.

            SqlConnection dbConnection1 = new SqlConnection(xmldb);                
            SqlDataAdapter da1 = new SqlDataAdapter("Select g.GuestID,g.GuestFirstName,g.GuestLastName,g.GuestAddress,g.Country,g.GuestPhone,g.GuestEmail from Guest as g", dbConnection1);
            DataSet ds1 = new DataSet();            
            da1.Fill(ds1, "Guest");
            SqlDataAdapter da2 = new SqlDataAdapter("Select s.GuestID,s.SaleTotal,s.InvoiceNumber,s.SaleItem,s.PaymentType,s.SaleDiscount,s.SalesStatus,s.Date,s.Quantity,s.SalePrice From Sales s Where s.Date Between '" + dateTimePicker1.Value + "' And '" + dateTimePicker2.Value + "' Order By s.Date ", dbConnection1);
            da2.Fill(ds1, "Sales");
 
            DataRelation rel = new DataRelation
              ("Sales for this Guest",
               ds1.Tables["Guest"].Columns["GuestID"],
               ds1.Tables["Sales"].Columns["GuestID"]);
            ds1.Relations.Add(rel);
                dataGridView1.DataSource = ds1;
                dataGridView1.DataMember = "Guest";
                dataGridView2.DataSource = ds1;
                dataGridView2.DataMember = "Guest.Sales for this Guest";

Thanks Again, Apreciate it

--GM