none
SQL in C# with inner join table

    Question

  • Hi everyone,

    if I want remain my R.clientid in the query same as below to get the result
    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 
     recvdate between ('2017-04-13') and ('2017-04-13 23:59:59.997')
      AND  R.clientid in ('315','622','1045','1122','1303','2648','3558','3559') 

    and here is my sql in C#
    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";
    and here is the all +
    		public void CheckingCondition()
    		{
                
    			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
    
                   
                }
    
              
    
                try
                {
    
               string strPriKeyword = ddlKeyword.SelectedItem.Text.ToString();
               if (strPriKeyword != "All Primary Keyword")
               {
                   string strSecKeyword = string.Empty;
                   strSecKeyword = ddlSecKeyword.SelectedItem.Text.ToString();
    
                   if (strSecKeyword == "All Secondary Keyword" ||strSecKeyword == "")
                       secKeyword = " ";
                   else
                       secKeyword = " AND secKeyword = '" + strSecKeyword + "' ";
    
                   strSQL = "SELECT keyid FROM INA_Keyword WHERE Keyword='" + strPriKeyword + "' " + secKeyword + " " +
                       " AND Shortcode= " + shortcode.SelectedItem.Value.ToString() + "";
                   keyID = db.scalarQuery(strSQL);
    
                   string pKeyword = ddlKeyword.SelectedItem.Text.ToString();
                   //string strkeyword = "";
                   if (pKeyword == "All Primary Keyword")
                   {
                       strkeyword = "";
                   }
                   else if (pKeyword != "All Primary Keyword")
                   {
                      
                       strkeyword = " R.keyID='" + keyID + "' AND ";
                   }
                   else
                   {
                       strkeyword = " keyID='" + keyID + "' AND ";
                    
                   }
               }
               else
               {
                   strkeyword = "";
               }
                    
                }
                catch (Exception ex)
                {
                    error.Text = System.Configuration.ConfigurationManager.AppSettings.Get("generalErr");
                    Log.LogToFile(pathName + "CheckingCondition_INAReqLogBySer.log", DateTime.Now.ToString() + " " + ex.Message);
                    Log.LogToFile(pathName + "CheckingCondition_INAReqLogBySer", "StackTrace: " + ex.StackTrace);
                    Log.LogToFile(pathName + "CheckingCondition_INAReqLogBySer", "Source " + ex.Source);
                }
                finally
                {
                    db.closeDB();
                }
    		}

    feel headche and no idea how to solve it.

    Thank you.

    I'm newbie in enterprise as developer.

    Thursday, April 13, 2017 1:34 AM

All replies

  • Hello,

    If there is an issue with the generated SQL then write out the SQL to the IDE Output window then try executing it in either SQL Server Management Studio or in a SQL file in your project. To create an sql file in your project add a new text file, name it myquery.sql then insert the sql from the output window and execute it.

    In the sql file in Visual Studio (as per above), press the connect button, 5th button from left, connect then use the first button to execute the query. If there are errors they will show up here. Correct the query and then figure out from the corrected query how to fix what has been done in the code above.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 13, 2017 3:07 AM
    Moderator
  • Hi eng teong,

    Thank you for posting here.

    For your question, could you provide more information about your question?

    Where you get the issue in your code? And some error message would be helpful.

    Could you provide demo code for us to repeat your issue?

    We are waiting for your update.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 8:34 AM
    Moderator