Failed to convert value...Need some help!

**gggram2013** · January 21st, 2008, 10:43 AM

I have this form below in which I choose a start date and an end date on a dateTimePicker. My code looks like this:

Code:

string sSQL = " Select * From Guest g, Sales s Where s.Date >= '" + dateTimePicker1.Text + "' And s.Date <= '" + dateTimePicker2.Text + "' Order By s.Date ";
                sSQL = "@Details";
                try
                {
                    cmd.CommandText = sSQL;
                    cmd.Parameters.Add("@Details", SqlDbType.VarChar).Value = textBox.Text;                   

                    cmd.ExecuteNonQuery();                
                    myTrans.Commit();
                }

I'm supposed to obtain all the values between those dates from two tables in sql server 2005. I'm using Visual Studio 2005(c-sharp).
When I run the program and click on view(button) I get this error:

Code:

Incorrect syntax near '@Details'.

Can anyone assist me and share some knowledge on what I'm missing. Thanks!

**jshultz** · January 21st, 2008, 10:46 AM

Well, I am going to guess as to which line it is failing on since you didn't tell us. My guess is the

Code:

cmd.Parameters.Add("@Details", SqlDbType.Money).Value = textBox.Text;

line. Correct?

You need to cast your text box text to a decimal.

**gggram2013** · January 21st, 2008, 10:48 AM

Yea, I'm not sure if that is a correct way to create a parameter where all the values from string Sql will be retrieved into "Details" and then display them on a text box.

**Tischnoetentoet** · January 21st, 2008, 10:51 AM

The query is completly wrong.

First, you set up a query, but then you overwrite it with a useless value:

string sSQL = " Select * From Guest g, Sales s Where s.Date >= '" + dateTimePicker1.Text + "' And s.Date <= '" + dateTimePicker2.Text + "' Order By s.Date ";

// Value may be correct here (but I would use SelectedDate instead of text for the DateTimePickers)

sSQL = "@Details";

// Here, the value is @Details.

This message is return by SQL, since the query is wrong (the query is "@Details"). And, then you get this message.

Use += instead of = to append instead of overwrite.

**gggram2013** · January 21st, 2008, 11:16 AM

I still get the same error using +=

**jshultz** · January 21st, 2008, 11:35 AM

Okay, I actually read everything this time...

If you are trying to retrieve values, you need to fill a dataset or a datatable. You are calling ExecuteNonQuery which will simply return the number of rows affected by your search string, not return the information of your search.

**gggram2013** · January 21st, 2008, 12:09 PM

Ok, since i'm not sure how to obtain all values (Select *) I'ved selected all the columns that I really need, my code looks like this:

Code:

textBox1.Text = dt1.Rows[0]["GuestFirstName"].ToString();

and this successfully gives me the name of the person. Now my question is this: How can I make all columns and rows display on the same textbox, something like this:

Code:

textBox1.Text = dt1.Rows[0]["C1"].ToString();
textBox1.Text = dt1.Rows[0]["C2"].ToString();
textBox1.Text = dt1.Rows[0]["C3"].ToString();
textBox1.Text = dt1.Rows[1]["C1"].ToString();
textBox1.Text = dt1.Rows[1]["C2"].ToString();
textBox1.Text = dt1.Rows[1]["C3"].ToString();
//And so fort...but i want the textbox to expand so it scrolls to the side...

When I do that, the same person comes up instead of moving on to the second sale...Please I need some help

**gggram2013** · January 23rd, 2008, 10:46 AM

Ok I have tested it and I do get all the values...the problem would be when I have hundreds of rows(Sales) which will then be too tedious for me to code in on and on the same thing. Does anyone know of a simpler way to do this? Anyone???

**Tischnoetentoet** · January 23rd, 2008, 11:18 AM

???

A for-loop maybe ...

**Amerikon** · January 23rd, 2008, 11:18 AM

Aren't TextBoxes limited to a single line of text? You might want to try a TextArea. Just loop through the result set and use TextArea.Text += ...

**jshultz** · January 23rd, 2008, 11:25 AM

Originally Posted by Amerikon

Aren't TextBoxes limited to a single line of text? You might want to try a TextArea. Just loop through the result set and use TextArea.Text += ...

No, you can do multiple lines. TextBox.Multiline = true;

**gggram2013** · January 23rd, 2008, 12:11 PM

There's no TextArea control in the toolbox, and yea I have multiple lines but it doesn't skip to the next, let me show u how it looks when i use different textboxes;

**gggram2013** · January 23rd, 2008, 12:16 PM

My code for that is very tedious...let me show you.

Code:

textBox1.Text = dt1.Rows[0]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[0]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[0]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[0]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[0]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[0]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleTotal"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["InvoiceNumber"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleItem"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["PaymentType"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleDiscount"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SalesStatus"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["Date"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["Quantity"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SalePrice"].ToString();
                textBox1.Text += "                ";
                textBox1.Text += dt1.Rows[1]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[1]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[1]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[1]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[1]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[1]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[1]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SalePrice"].ToString();
                textBox1.Text += "                ";                
                textBox1.Text += dt1.Rows[2]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[2]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[2]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[2]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[2]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[2]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[2]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SalePrice"].ToString();
                textBox1.Text += "                ";
                textBox1.Text += dt1.Rows[3]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[3]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[3]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[3]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[3]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[3]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[3]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SalePrice"].ToString();
                textBox1.Text += "                ";

And that is only if there are four sales between those dates....

**jshultz** · January 23rd, 2008, 12:21 PM

Try using .AppendText("") instead of adding it to the .Text property.

**jshultz** · January 23rd, 2008, 12:25 PM

Originally Posted by gggram2013

My code for that is very tedious...let me show you.

Code:

textBox1.Text = dt1.Rows[0]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[0]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[0]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[0]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[0]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[0]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleTotal"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["InvoiceNumber"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleItem"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["PaymentType"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SaleDiscount"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SalesStatus"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["Date"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["Quantity"].ToString();
                textBox1.Text += "   ";
                textBox1.Text += dt1.Rows[0]["SalePrice"].ToString();
                textBox1.Text += "                ";
                textBox1.Text += dt1.Rows[1]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[1]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[1]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[1]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[1]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[1]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[1]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[1]["SalePrice"].ToString();
                textBox1.Text += "                ";                
                textBox1.Text += dt1.Rows[2]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[2]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[2]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[2]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[2]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[2]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[2]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[2]["SalePrice"].ToString();
                textBox1.Text += "                ";
                textBox1.Text += dt1.Rows[3]["GuestFirstName"].ToString();
                textBox1.Text += "   ";
                textBox5.Text += dt1.Rows[3]["GuestLastName"].ToString();
                textBox5.Text += "   ";
                textBox6.Text += dt1.Rows[3]["GuestAddress"].ToString();
                textBox6.Text += "   ";
                textBox7.Text += dt1.Rows[3]["Country"].ToString();
                textBox7.Text += "   ";
                textBox8.Text += dt1.Rows[3]["GuestPhone"].ToString();
                textBox8.Text += "   ";
                textBox9.Text += dt1.Rows[3]["GuestEmail"].ToString();
                textBox9.Text += "   ";
                textBox1.Text += dt1.Rows[3]["SaleTotal"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["InvoiceNumber"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SaleItem"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["PaymentType"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SaleDiscount"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SalesStatus"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["Date"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["Quantity"].ToString();
                textBox1.Text += " ";
                textBox1.Text += dt1.Rows[3]["SalePrice"].ToString();
                textBox1.Text += "                ";

And that is only if there are four sales between those dates....

If you are having to do this you really need to go back to the drawing board and figure out a better way. You shouldn't need to do this.

Thread: Failed to convert value...Need some help!

Thread Tools

Display

Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Trouble obtaining database table values...

Re: Trouble obtaining database table values...

Re: Trouble obtaining database table values...

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Re: Failed to convert value...Need some help!

Posting Permissions