C#.Net 2008 SQL WHERE Clause problem

Lennie · #1 (**permalink**) March 31st, 2010, 09:09 PM

Hi Good Guys,

Encounter another problem.
The strCustID variable used in the SQL String WHERE Clause is causing error.
Here are the coding

Code:

  private String FCreateSQLString( ) 
        {
            string strCustID = listBoxCust.SelectedItem(0).ToString;
            string strSql;

            strSql +=  "Select O.OrderID, ShipName  ";
            strSql +=  "Convert(char(10), O.OrderDate, 103) as OrderDate, ";
            strSql +=  "Convert(char(10), O.RequiredDate,103) as RequiredDate, ";
            strSql +=  "Convert(char(10), O.ShippedDate, 103) as ShipDate, ";
            strSql +=  "From TestOrders as [O] ";     
            strSql += " WHERE  O.customerID = '" & strCustID & "'"; <-- error     
                        
            return strSql;
            
        }

-------------------------------------------------
ListBox SelectedItem Problem:
trying to retrieve the CustomerID from LISTBOX SelectedItem(0) is causing error too.

Shasur · #2 (**permalink**) March 31st, 2010, 10:44 PM

Hi Lennie

Can you post the error message/number of the error. Are you sure that customerID is declared as string. If not change the following line

strSql += " WHERE O.customerID = '" & strCustID & "'";

with

strSql += " WHERE O.customerID = " & strCustID & ""; (remove the single quote)

Cheers
Shasur

Lennie · #3 (**permalink**) April 1st, 2010, 12:29 AM

Quote:

Originally Posted by Shasur

Hi Lennie

Can you post the error message/number of the error. Are you sure that customerID is declared as string. If not change the following line

strSql += " WHERE O.customerID = '" & strCustID & "'";

with

strSql += " WHERE O.customerID = " & strCustID & ""; (remove the single quote)

Cheers
Shasur

----------------------------
Hi Shasur,

Thank you for your help.

In the NorthWind database the ORDER TABLE contain CustomerID with datatype nChar and in my posting I did declare it as string. Here is the the coding:

string strCustID = listBoxCust.SelectedItem(0).ToString;

I have change SQL Where coding according to your suggestion :
strSql += " WHERE O.customerID = " & strCustID & ""; (remove the single quote)

And this error messge was generated :
Error 3 Operator '&' cannot be applied to operands of type 'string' and 'string'

Then I change this & to + there was no error message:
strSql += " WHERE O.customerID = " + strCustID + "";

-----------------------
This coding creates error as well:
string strCustID = listBoxCust.SelectedItem(0).ToString;
Error message:
Error 2 Non-invocable member 'System.Windows.Forms.ListBox.SelectedItem' cannot be used like a method.

samjudson · #4 (**permalink**) April 1st, 2010, 03:46 AM

Oh dear. That isn't a SQL error - you are getting a COMPILER error, because you are using & to add two strings together.

C# doesn't use &, it uses +. VB.Net uses &.

strSql += " WHERE O.customerID = " + strCustID + "";

Likewise, you are then trying to refer to SelectedItem as a method by using (0) - this is the VB.Net way of refering to an array, not the C# way. C# uses square brackets:

string strCustID = listBoxCust.SelectedItem[0].ToString();

Lennie · #5 (**permalink**) April 1st, 2010, 03:59 AM

Hi Sam,
Thank you very much for your suggestion. Yea, you are right in you statement as I was a VB.NET developer. I am new to C# and learning it.

Thank you for sharing information with me. By the way I am using C#.Net 2008

-----------------------------------------
Hi Sam,
I have tried out this suggestion:
string strCustID = this.listBoxCust.SelectedItem[0].ToString();

Surprising this error message appears. I was shocked.
Error 3 Cannot apply indexing with [] to an expression of type 'object'

Lennie · #6 (**permalink**) April 1st, 2010, 04:17 AM

Hi Good Guys,

Being a VB.NET programmer I am having problem using C# within a same FORM to retrieve SQL string from another another method.

Here is the coding

Code:

  private void FLoadDataGridView ()         
            {

            try
               {

        String strSql = FCreateSQLString;  <--- error                 
                sqlconn = new SqlConnection(connstr);
                sqlconn.Open();
                sqlDA = new SqlDataAdapter(strSql ,  sqlconn);
                DS = new DataSet();
                
                //clear dataset before refill
                DS.Clear();
                DS.CaseSensitive = true;
                sqlDA.Fill(DS, "Order");

                // Fill datagridview then format column
                this.dataGridView1.DataSource = DS.Tables["Order"]; 
                FFormatDataGridViewColm();

             }   catch (Exception addEx)
                   { 

                    }
            }

-----------------------------------------------------------------

Code:

  private String FCreateSQLString( ) 
        {
            string strCustID = listBoxCust.SelectedItem(0).ToString;
            string strSql;

            strSql +=  "Select O.OrderID, ShipName  ";
            strSql +=  "Convert(char(10), O.OrderDate, 103) as OrderDate, ";
            strSql +=  "Convert(char(10), O.RequiredDate,103) as RequiredDate, ";
            strSql +=  "Convert(char(10), O.ShippedDate, 103) as ShipDate, ";
            strSql +=  "From TestOrders as [O] ";     
            strSql += " WHERE  O.customerID = " + strCustID+"";  
                        
            return strSql;
            
        }

-------------------------------------------------
ListBox SelectedItem Problem:
trying to retrieve the CustomerID from LISTBOX SelectedItem(0) is causing error too.[/QUOTE]

This are the coding that fills the ListBoxCust:
this.listBoxCust.DisplayMember = "Cust.CompanyName";
this.listBoxCust.ValueMember = "Cust.CustomerID";
this.listBoxCust.DataSource = DS.Tables["Cust"];

samjudson · #7 (**permalink**) April 1st, 2010, 04:38 AM

If SelectedItem[0] gives you an error saying SelectedItem is not an array, then don't try and use it as an array....

string strCustID = this.listBoxCust.SelectedItem.ToString();

For your other error, what does the actual error say, and what do you think it means?

Lennie · #8 (**permalink**) April 1st, 2010, 04:56 AM

Quote:

Originally Posted by samjudson

If SelectedItem[0] gives you an error saying SelectedItem is not an array, then don't try and use it as an array....

string strCustID = this.listBoxCust.SelectedItem.ToString();

For your other error, what does the actual error say, and what do you think it means?

-------------------------------------------
If I use this coding string strCustID = this.listBoxCust.SelectedItem.ToString();

How am I gonna retrieve the CustomerId from the ListBoxCust where under the ListBox Category it is VALUEMEMBER.

samjudson · #9 (**permalink**) April 1st, 2010, 05:01 AM

How about SelectedValue?

Are you even bothering to read the documentation here, or are you just posting random questions because you can't be bothered to do the work yourself?

http://msdn.microsoft.com/en-us/libr...ctedvalue.aspx

Lennie · #10 (**permalink**) April 1st, 2010, 05:21 AM

Quote:

Originally Posted by samjudson

How about SelectedValue?

Are you even bothering to read the documentation here, or are you just posting random questions because you can't be bothered to do the work yourself?

http://msdn.microsoft.com/en-us/libr...ctedvalue.aspx

-----------------------------------------

I have tried out your suggestion of using SelectedValue and it generated this error message

string strCustID = this.listBoxCust.Selectedvalue.ToString();
Error 1'System.Windows.Forms.ListBox' does not contain a definition for 'Selectedvalue' and no extension method 'Selectedvalue' accepting a first argument of type 'System.Windows.Forms.ListBox' could be found (are you missing a using directive or an assembly reference?)

-----------------------------------------
Are you even bothering to read the documentation here, or are you just posting random questions because you can't be bothered to do the work yourself?

Regarding your accusation refer it to yourself. You are 100% wrong. I am facing the coding problem is because I tried to work it myself and couldn't find the solution because I am new to C# and not VB.NET 2003, Vb.NET 2008 and VB6. I have tried out your suggestions and it's not working that proof that the accusation you made should refer to yourself because I presume you did not try out the coding yourself. My office programmers are trying out all your suggestion and they are facing the same problems.

Need to download code?

Navigation