Query not returning anything

Posted by RohiK under Sql Server on 12/29/2010 | Points: 10 | Views : 1638 | Status : [Member] | Replies : 6
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response