none
Ambiguous column name

    Question

  • Dear all, 
    I have the problem that mention 3 ambiguous  name error as the following:
    Ambiguous column name 'keyID'.
    Ambiguous column name 'status'.
    Ambiguous column name 'clientid'.

    And here is my code:
    void bindGrid()
    		{
    			DataGrid1.Visible=true;
    
    			CheckingCondition();
    
    			MKData.DB db = new MKData.DB(connStrSel);
    
    			try
    			{
                    //string strSQL1 = "SELECT recvto,recvfrom,recvtext,recvdate, price, status, keyword, seckeyword FROM "+tableName+" WITH (NOLOCK) WHERE "+ strshortcode + strkeyword + sqlDate + statusCase + strclientid + " ORDER BY recvdate DESC";
                    string strSQL1 = "SELECT recvto, recvfrom, recvtext, recvdate, recvtext, K.keyword AS PrimaryKeyword, K.seckeyword AS SecondaryKeyword from "+@tableName+" R WITH (NOLOCK) INNER JOIN INAPremium..INA_Keyword K on R.recvto = K.shortcode WHERE " + strshortcode + strkeyword + sqlDate + statusCase + strclientid + " ORDER BY recvdate DESC";
                    DataGrid1.DataSource = db.getDS(strSQL1).Tables [0].DefaultView;
    		      DataGrid1.DataBind();
    			}
    			catch(Exception ex)
    			{
    				error.Text = System.Configuration.ConfigurationManager.AppSettings.Get("generalErr");
    				Log.LogToFile(pathName + "ViewKeyword_INAReqLogBySer.log", DateTime.Now.ToString() + " " + ex.Message);
    				Log.LogToFile(pathName + "ViewKeyword_INAReqLogBySer", "StackTrace: " + ex.StackTrace);
    				Log.LogToFile(pathName + "ViewKeyword_INAReqLogBySer", "Source " + ex.Source);
    			}
    
    			finally
    			{
    				db.closeDB();
    			}
    		}
    if(shortcode.SelectedItem.Value.Equals("0"))  //if Shortcode is not selected
    			{
    				strshortcode ="";
    
                    GrabClientID(""); //02102012
    
                    strclientid = " and clientid in (" + clientList + ") "; //02102012
                    
                }
    			else
    			{
    				strshortcode =" recvto= '"+shortcode.SelectedItem.Value.ToString()+"' and ";
    
                    GrabClientID(shortcode.SelectedItem.Value.ToString()); //02102012
    
                    strclientid = " and clientid in (" + clientList + ") "; //02102012
                  
    			}
     if (pKeyword == "All Primary Keyword")
                   {
                       strkeyword = "";
                   }
                   else if (pKeyword != "All Primary Keyword")
                   {
                       strkeyword = " keyID='" + keyID + "' AND ";
                   }
                   else
                   {
                       strkeyword = " keyID='" + keyID + "' AND ";
                   }
               }
               else
               {
                   strkeyword = "";
               }
    if (ddlStatus.SelectedItem.Value != "")    //if Status is selected
    			{
                    
    				statusCase = " AND status='" + ddlStatus.SelectedItem.Value +"'" ;
    			}
    if(shortcode.SelectedItem.Value.Equals("0"))  //if Shortcode is not selected
    			{
    				strshortcode ="";
    
                    GrabClientID(""); //02102012
    
                    strclientid = " and clientid in (" + clientList + ") "; //02102012
                   
                }
    			else
    			{
    				strshortcode =" recvto= '"+shortcode.SelectedItem.Value.ToString()+"' and ";
    
                    GrabClientID(shortcode.SelectedItem.Value.ToString()); //02102012
    
                    strclientid = " and clientid in (" + clientList + ") "; //02102012
    			}
    and according to sql query 
    SELECT recvto, recvfrom, recvtext, recvdate, recvtext, K.keyword AS PrimaryKeyword, K.seckeyword AS SecondaryKeyword 
    from INA_Requestlog  R WITH (NOLOCK) INNER JOIN INAPremium..INA_Keyword K on R.recvto = K.shortcode 
    WHERE  recvto= '95899' and 
     K.keyid='5624' AND 
      recvdate between ('2017-04-12') and ('2017-04-12 23:59:59.997') 
       AND status='200'
        and clientid in ('3559') 
    	ORDER BY recvdate DESC
    Ambiguous column name 'keyID'.
    Ambiguous column name 'status'.
    Ambiguous column name 'clientid'.

    May I know how to solve the problem?

    Thank you.






    I'm newbie in enterprise as developer.

    Wednesday, April 12, 2017 7:25 AM

All replies

  • Try adding the “K.” prefix in multiple places for keyID, status and clientid, such as: ‘strkeyword = " K.keyID='"…’ etc.

    Wednesday, April 12, 2017 7:39 AM
  • Hi eng teong,

    I would suggest you to put a break point at the following and debug your code to check that based on your conditions your SQL query is generating correctly or not. Hope this helps you.

    string strSQL1 = "SELECT recvto, recvfrom, recvtext, recvdate, recvtext, K.keyword AS PrimaryKeyword, K.seckeyword AS SecondaryKeyword from "+@tableName+" R WITH (NOLOCK) INNER JOIN INAPremium..INA_Keyword K on R.recvto = K.shortcode WHERE " + strshortcode + strkeyword + sqlDate + statusCase + strclientid + " ORDER BY recvdate DESC";


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Thursday, April 13, 2017 8:56 AM
    Moderator
  • Hi eng teong,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help other members to find solution if they faces similar issue.

    Your understanding and cooperation will be grateful

    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Monday, May 15, 2017 1:06 PM
    Moderator