Display data in data gridview

Question

0.00/5 (No votes)

See more:

I stored first database value to array and trying to use as a search filter in database 2 all working fine but only last value is getting printed

Here is my code:

C#

//method1:

using System.Data.SqlClient;

// .....

string queryString = "Select productID from Table1 where custid="+ textbox1.text; 

List TempproductID = new List() ;


using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
Tempproductid.add( reader[0].ToString());


}
}
reader.Close();
tempproductid.toarray();
}

//method 2:
String stringprovider = "@database connection string "; 
for ( int I = 0; I < tempproductid.count; I++)
{
Sqlconnection con2 = new sqlconnection(stringprovider); Con2.open();
Cmd2.connection=con2; Cmd2.commandType = new commandType.text; Cmd2.commandText = "select * from Table2 where Productid = @productid"; Cmd2.parameters.addwithvalue("@productid",Tempproductid[i].to string());

Dataset Ds = new dataset(); sqldataadaptaer da1 = new sqldataadapter(cmd2); Datatable Table2 = new Data table(); Da1.fill(table2); 
Datagridview2.source = table2; 
} 
}

In that for loop only last one value is getting in datagridview not getting all?

Posted 24-Sep-15 22:29pm

Member 12005342

Updated 24-Sep-15 22:36pm

Leo Chapiro

v3

Add a Solution

Comments

John C Rayan 25-Sep-15 5:16am

The reason why you are getting the last value because you are executing the DB query inside the loop and not saving the results anywhere , and in every iteration previous result overwritten by the current value so in the end you will have the result in DataSet for the last value. Try to rewrite the code.

Member 12005342 25-Sep-15 13:20pm

Hi john can you please help me in writing that code

I am not getting how to save data inside loop without overwrite and display outside

John C Rayan 28-Sep-15 8:54am

Hi
sorry for the delay in getting back to you. Are you still looking for the code. I think you have already got the code in solutions. Let me know

Member 12005342 28-Sep-15 10:30am

Yes john still searching for the code

Could you please help in the code

John C Rayan 28-Sep-15 11:10am

Did you try this code. I have changed slightly from the original code.
As you can see , I have moved the commandText assignment after parameters string array populated.

Sqlconnection con2 = new sqlconnection(stringprovider); Con2.open();
Cmd2.connection=con2; Cmd2.commandType = new commandType.text;

var parameters = new string[tempproductid.count];
for ( int I = 0; I < tempproductid.count; I++)
{

parameters[i] = string.Format("@Productid{0}", i);
Cmd2.Parameters.AddWithValue(parameters[i], tempproductid.items[i]);
}
Cmd2.commandText = string.Format("select * from Table2 where Productid in ({0})", string.Join(", ", parameters));

Dataset Ds = new dataset(); sqldataadaptaer da1 = new sqldataadapter(cmd2);
Datatable Table2 = new Data table(); Da1.fill(table2);
Datagridview2.source = table2

Member 12005342 28-Sep-15 13:52pm

Yes I tried and I am getting run time error @ da1.fill(table 2).

Stating " in operator without () in querry expression 'Productid in @productid' "

Member 12005342 28-Sep-15 14:46pm

Thank you soo much john for your help

Code is working and I am able to display all the data but only problem is next time when I search for another customer it is displaying both previous and this customer details.

Member 12005342 28-Sep-15 15:36pm

Thank you john

Code worked perfect

Thanks again

John C Rayan 29-Sep-15 3:57am

Glad to know it worked. Can you mark my solution below as accepted.

Sinisa Hajnal 25-Sep-15 5:16am

First things first. NEVER CONCATENATE USER INPUT INTO YOUR SQL! I cannot say this strong enough. Google SQL Injection and change as soon as you can to parametrized query or stored procedure.

Instead of reader, could you use ExecuteDataset? You get the set with datatable which you can change to array directly if that is needed.

Finally, instead of reader[0].ToString try using reader.GetString("productID").

Good luck!

4 solutions

Solution 2

Because you are binding gridview each time in loop. Add one by one data in datatable in loop and after whole loop gets over, bind gridview out side loop.

Posted 24-Sep-15 23:11pm

Member 12003400

Comments

Member 12005342 25-Sep-15 6:35am

Can you please help in this how to add one by one data inside loop
And display all values??

Not getting how to write code foe that

Member 12003400 25-Sep-15 7:06am

Or you will need to add the values in the one array one at a time.

Sqlconnection con2 = new sqlconnection(stringprovider); Con2.open();
Cmd2.connection=con2; Cmd2.commandType = new commandType.text;

Cmd2.commandText = string.Format("select * from Table2 where Productid in ({0})", string.Join(", ", parameters));

var parameters = new string[tempproductid.count];
for ( int I = 0; I < tempproductid.count; I++)
{

parameters[i] = string.Format("@Productid{0}", i);
Cmd2.Parameters.AddWithValue(parameters[i], tempproductid.items[i]);
}
Dataset Ds = new dataset(); sqldataadaptaer da1 = new sqldataadapter(cmd2);
Datatable Table2 = new Data table(); Da1.fill(table2);
Datagridview2.source = table2;

Member 12005342 25-Sep-15 14:58pm

In this code I am getting runtime error stating " syntax value error in query" @ Da1.fill(table2);

John C Rayan 28-Sep-15 8:58am

Debug your code to see the number of parameters matching the values and also each value has got some value. Look for any comma related issues.

Solution 1

Well (ignoring the reasons that won't compile - C# is case sensitive, so TempproductID is not the same as Tempproductid, and ingnoring the dangerous code you are using) the first thing to look at is that you are searching for an exact match on your product / customer id value - and unless there is something very, very amiss in your system, there will only ever be one exactly match on an ID - if you have two or more customers with the same id, who are you going to bill? If you have two or more products with the same ID, which are you going to send?

Try a LIKE query:

SQL

SELECT * FROM MyTable WHERE MyTextColumn LIKE '%Paul%'

Will return all values that contain the word "Paul" - '%' is a wildcard character in SQL LIKE comparisons.

But seriously, never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead:

SQL

SELECT * FROM MyTable WHERE MyTextColumn LIKE '%' + @SEARCH + '%'

And provide the text via a parameter @SEARCH

Posted 24-Sep-15 23:05pm

OriginalGriff

Comments

John C Rayan 25-Sep-15 5:19am

From the query by the OP , it looks like he is reading all the product ids for a given customer. So your solution doesn't really solve the OP's issue.

OriginalGriff 25-Sep-15 5:30am

Depends what kind of IDs he's using - he is passing a textbox value to his existing queries, so we really can't tell what his DB looks like.
And I've seen a load of Customer ID's that start with the initials of the company name, followed by four digits, and product codes like "PCMON24LG004"

Member 12005342 25-Sep-15 6:37am

As John said here I am reading all productid for given customerid

And in my DB customer I'd is alphanumeric

Solution 3

The first question is why are you iterating, and why you bind the datagridview inside iterating.?

If you want to bind your datagrid then simply use below sample

C#

SqlConnection con=new SqlConnection(connectionstring);
con.Open();
SqlCommand cmd=new SqlCommand("your select query",con)
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);

dgv.DataSource=ds;
dgc.DataBind();

Posted 25-Sep-15 3:21am

Naveen.Sanagasetti

Add a Solution

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

John C Rayan · Accepted Answer · 2015-09-28T21:56:00

Sqlconnection con2 = new sqlconnection(stringprovider); Con2.open();
Cmd2.connection=con2; Cmd2.commandType = new commandType.text;

var parameters = new string[tempproductid.count];
for ( int I = 0; I < tempproductid.count; I++)
{

parameters[i] = string.Format("@Productid{0}", i);
Cmd2.Parameters.AddWithValue(parameters[i], tempproductid.items[i]);
}
Cmd2.commandText = string.Format("select * from Table2 where Productid in ({0})", string.Join(", ", parameters));

Dataset Ds = new dataset(); sqldataadaptaer da1 = new sqldataadapter(cmd2);
Datatable Table2 = new Data table(); Da1.fill(table2);
Datagridview2.source = table2

Display data in data gridview

4 solutions

Solution 2

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0

Display data in data gridview

4 solutions

Solution 2

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us