Query not returning anything

Posted by RohiK under Sql Server on 12/29/2010 | Points: 10 | Views : 3813 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi all,

I want to fetch Primary key column name from Table into my project so I am using following code


public const string strGetPK = "select t1.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2,INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 where  t2.CONSTRAINT_TYPE='PRIMARY KEY' and t2.TABLE_NAME=@TableName and t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME";
        /// <summary>
        /// Retrives Primary key column name
        /// </summary>
        /// <returns>dataset</returns>
        /// <exception cref="MaxnetDBProviders.DBConnectionException">Thrown when connection to database fails</exception>
        /// <exception cref="MaxnetDBProviders.DBOpException">Thrown when select fails</exception>
        public DataSet GetKeyColumn(string TableName)
        {

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            OpenConnection();
            SqlCommand cmd = new SqlCommand(strGetPK, conn);
            //cmd.Parameters.AddWithValue("@TableName", TableName);

            DataSet dt = new DataSet();
            try
            {

                cmd.Prepare();
                dataAdapter.SelectCommand = cmd;
                // Reads a recordset and fill data into the dataset
                dataAdapter.Fill(dt);


            }
            catch (SqlException sqlex)
            {
                throw new DBOpException(UserFriendlyErrorMessages.ConnectionFailed, sqlex);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                CloseConnection();
            }
            return dt;
        }

the query GetPK is executed in Sql server management studio and returing primary key column name. but using above code it returns nothing.
I am surprised ...
plz help to execute it.

Happy Programming!!
Rohi

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: PandianS on: 12/29/2010 [Member] [MVP] Silver | Points: 25

0	Hi Seems to be you have used one Variable named "@TableName" in query, But you not passed any table name to that variable/parameter... You have commented the following line....!, So it will not be assigned any Table name to the varibale :) //cmd.Parameters.AddWithValue("@TableName", TableName); Cheers www.sqlserverbuddy.blogspot.com Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

Seems to be you have used one Variable named "@TableName" in query, But you not passed any table name to that variable/parameter...

You have commented the following line....!, So it will not be assigned any Table name to the varibale :)

//cmd.Parameters.AddWithValue("@TableName", TableName);

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

RohiK, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathya4260 on: 12/29/2010 [Member] Starter | Points: 25

0	just remove the comment line, it ill work perfectly ... as mr pandian said..., Kindly mark answers if your question got solved... Sathish Kumar S RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: RohiK on: 12/30/2010 [Member] Starter | Points: 25

0	hi actually I haven't put comment over there in my code. when i was trying to do this without passing parameters( mention it in query ) that time i comment that line. otherwise it is not commented. plz tell me another solution. Happy Programming!! Rohi RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: T.saravanan on: 12/30/2010 [Member] [MVP] Silver | Points: 25

0	Hi Rohik, I had try your code. Small changes in your code... public const string strGetPK = "select t1.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2,INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 where t2.CONSTRAINT_TYPE='PRIMARY KEY' and t2.TABLE_NAME=@TableName and t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME"; public DataSet GetKeyColumn(string TableName) { SqlDataAdapter dataAdapter = new SqlDataAdapter(); OpenConnection(); SqlCommand cmd = new SqlCommand(strGetPK, conn); cmd.Parameters.AddWithValue("@TableName", TableName); //uncomment this line DataSet dt = new DataSet(); try { //cmd.Prepare(); comment this line because i face a problem to add this line.. dataAdapter.SelectCommand = cmd; // Reads a recordset and fill data into the dataset dataAdapter.Fill(dt) } catch (SqlException sqlex) { throw new DBOpException(UserFriendlyErrorMessages.ConnectionFailed, sqlex); } catch (Exception ex) { } finally { CloseConnection(); } return dt; } i had change your code in my way...Try this way.... public const string strGetPK = "select t1.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2,INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 where t2.CONSTRAINT_TYPE='PRIMARY KEY' and t2.TABLE_NAME='tbl_Test' and t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME"; // Here i am add the TableName in above string. public DataSet GetKeyColumn() { SqlDataAdapter dataAdapter = new SqlDataAdapter(strGetPK,conn); DataSet dt = new DataSet(); try { dataAdapter.Fill(dt) } catch (Exception ex) { } return dt; } Cheers :) Thanks, T.Saravanan RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Rohik,

I had try your code. Small changes in your code...


public const string strGetPK = "select t1.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2,INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 where  t2.CONSTRAINT_TYPE='PRIMARY KEY' and t2.TABLE_NAME=@TableName and t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME";        



        public DataSet GetKeyColumn(string TableName)

        {

            SqlDataAdapter dataAdapter = new SqlDataAdapter();

            OpenConnection();

            SqlCommand cmd = new SqlCommand(strGetPK, conn);

            cmd.Parameters.AddWithValue("@TableName", TableName);    //uncomment this line

            DataSet dt = new DataSet();

            try

            {

                //cmd.Prepare();    comment this line  because i face a problem to add this line..

                dataAdapter.SelectCommand = cmd;

                // Reads a recordset and fill data into the dataset

                dataAdapter.Fill(dt)

            }

            catch (SqlException sqlex)

            {

                throw new DBOpException(UserFriendlyErrorMessages.ConnectionFailed, sqlex);

            }

            catch (Exception ex)

            {

            }

            finally

            {

                CloseConnection();

            }

            return dt;

        }

i had change your code in my way...Try this way....


public const string strGetPK = "select t1.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2,INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 where  t2.CONSTRAINT_TYPE='PRIMARY KEY' and t2.TABLE_NAME='tbl_Test' and t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME";        

// Here i am add the TableName in above string.

        public DataSet GetKeyColumn()

        {

            SqlDataAdapter dataAdapter = new SqlDataAdapter(strGetPK,conn);            

            DataSet dt = new DataSet();

            try

            {                               

                dataAdapter.Fill(dt)

            }            

            catch (Exception ex)

            {

            }            

            return dt;

        }

Cheers :)

Thanks,
T.Saravanan

RohiK, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: RohiK on: 12/31/2010 [Member] Starter | Points: 25

0	Hi T.Saravanan, I need to execute this query to find out primary key of different tables depends on condition, so I cant replace parameter with any fixed table name. Happy Programming!! Rohi RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: T.saravanan on: 12/31/2010 [Member] [MVP] Silver | Points: 25

0	Hi Rohik, Your code also working fine for passing different table name.Have you get any error in execution time? If yes means comment a line cmd.Prepare(); and then execute. Thanks, T.Saravanan RohiK, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts