Click here to Skip to main content
15,917,652 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am getting double data (double columns) from my query on a table.
I am unable to fathom why this is happening.
Have tried all kinds of methods but no change.
The original table only has a single row of data in each row.

C#
	// Now let's try to get the data
     DataTable dt = new DataTable();
     BindingSource SBind = new BindingSource();
     List<object> rows = new List<object>();
     rows = (List<object>)Get_All_Items_For_A_Table(SelectedTableName);
     SBind.DataSource = rows;
     dataGridView1.DataSource = SBind;
    // Why the hell am I getting double data in each row of the DataGridView?

The data row is too long to post here.

public static object Get_All_Items_For_A_Table(string tableName)
{
    object output = null;
    string dBaseName = ConfigurationManager.AppSettings.Get("dBaseName");
    using (IDbConnection connection = new           System.Data.SqlClient.SqlConnection(ConnectionStringHelper.CnnVal(dBaseName)))
    {
        output = connection.Query<object>("dbo.Select_All_From_Table_By_Table_Name", new 
        {
            Table_Name = tableName }, commandType: CommandType.StoredProcedure);
            return output;
        }
    }

I was expecting that as usual, I would get a single row for each row in the datagridview.
The only difference between this query and all the others that work fine is the use of a specific data model to collect the data from the server.
This time I wanted to use a generic model (object) and use a variable for the table name to make the query against.
It all goes well until binding to the Datagridview.

What I have tried:

I have tried all the suggested methods on here and other sites but none of them solve the problem.

Just thought I should post the stored procedure as well:
SQL
CREATE PROCEDURE [dbo].[Select_All_From_Table_By_Table_Name] 
	@Table_Name nvarchar (128)
AS
BEGIN
SET NOCOUNT ON;

SET @table_Name = @Table_Name 
DECLARE @sql NVARCHAR(100) = 'SELECT * FROM ' + @table_name;
EXECUTE (@sql);

END
GO

Update: I have tested the stored procedure and it does what is expected. I have watched the value of TableName and it is as expected.
I guess I will have to abandon my attempt to solve this one for a while and develop multiple queries using the data models for each table.
Cheers and thanks for responding.
Posted
Updated 8-Dec-22 3:07am
v4

We can't tell what exactly is happening: but the most likely problem is that tableName does not contain what you think it does - so start there.
Use the debugger to examine exactly what you pass into it, and single step the method to see exactly what the query returns.

Sorry, but we can't do any of that for you!
 
Share this answer
 
That's an extremely dangerous stored procedure. If your tableName parameter can be influenced in any way by the end-user, then your code is vulnerable to SQL Injection[^].

At the very least, you need to validate the table name in the stored procedure:
SQL
CREATE PROCEDURE [dbo].[Select_All_From_Table_By_Table_Name] 
	@Table_Name nvarchar (128)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RealTableName sysname, @SchemaName sysname;
    SELECT @RealTableName = name, @SchemaName = SCHEMA_NAME(schema_id) FROM sys.tables WHERE name = @Table_Name;
    
    DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@RealTableName);
    EXECUTE (@sql);
END
GO
Ideally, you'd want to validate that the table name came from a specific list of tables which were meant to be used with this stored procedure, not just any random table in the database.
 
Share this answer
 
Comments
PIEBALDconsult 8-Dec-22 9:39am    
Can also use the OBJECT_ID function to validate the table name.
Ozzie Mozzie 8-Dec-22 17:01pm    
Thank you. I appreciate your advice and have updated the procedure.
The previous procedure worked ok but as you have pointed out it was leaky. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900