Get data from Sql Server but not in order & no data found

Question

I create another post as i can't edit the previous one https://www.c-sharpcorner.com/forums/retrieve-null-data-from-sql-server-into-datatable. Right now I managed to get rid the duplicate barcode. And the problems now i. The data I receive is not in order that match with barcode ("3") column ii. If no data found it will print 'no bookcode found' in the cell I attach photo the order i receive and the actual order i want. I really appreciate your help, thank you.

Nebula · Answer

Even when I specified the barcode that 1 have and 1 dont have bookcode, it just print that 1 bookcode only and theres no 'Nodata' cell row var barCodesstring = string.Format("'{0}'", string.Join("','", 9789830093819,9789830094205)); SqlCommand cmd = new SqlCommand("WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID = '' THEN 'Nodata' ELSE InvtID END AS InvtID, Barcode, ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid = 1 and Barcode In (" + barCodesstring + ")", conn);

Nebula · Answer

StringBuilder sb = new StringBuilder( "WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'Nodata' ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In ( " ); for ( int i = 0; i { if (i != 0) sb.Append( "," ); string name = "@P" + i; cmd.Parameters.AddWithValue(name, dt.Rows[i][ "3" ]); sb.Append(name); } sb.Append( ")" ); cmd.CommandText = sb.ToString(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); Hi Sir, thats the query tht I changed and the results still the same. My 'no data' is not include in the data column. Where did i do wrong

Jignesh Kumar · Answer

Please change as below and put your all coulcmn after this Case When, still you face issue then please drop your code with changes you tried WITH cte AS ( SELECT case when BOOKCODE IS NULL or BOOKCODE = '' then 'No Bookcode Found' else BOOKCODE end as BOOKCODE, OtherCoulmnName which you want to select..... ,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc ) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1

Nebula · Answer

Sorry sir for asking again as I still blur, does I have to replace the whole cte select or how am I supposed to add statement with 1 select with select case in the same query. union? ( SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer ), replace SELECT case when InvtID IS NULL or InvtID = '' then 'No Bookcode Found' else InvtID end as BOOKCODE

Jignesh Kumar · Answer

Hi Nebula, You need to change in your CTE select statement not in inner query. Please change in first query. it will resolve.

Nebula · Answer

Thank you sir, I tried exactly as your suggestion as below WITH cte AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT case when InvtID IS NULL or InvtID = '' then 'No Bookcode Found' else InvtID end as BOOKCODE FROM cte WHERE rid=1 and Barcode In ("); But I still dont receive the 'No Bookcode Found' in the data column :(

Jignesh Kumar · Answer

Hi Nebula, Please try below things in your select query( SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer ), -- If you are using SQL 2012 or above SELECT IF(BOOKCODE IS NULL or BOOKCODE = '' , 'No Bookcode Found' , BOOKCODE) as BOOKCODE from InventoryCustomer or --If you are using lower version then SQL 2012 SELECT case when BOOKCODE IS NULL or BOOKCODE = '' then 'No Bookcode Found' else BOOKCODE end as BOOKCODE from InventoryCustomer

Nebula · Answer

Here is my code public void filldatagridview(ExcelWorksheet workSheet) { DataTable dt = new DataTable(); //Create the data column for ( int col = workSheet.Dimension.Start.Column; col { dt.Columns.Add(col.ToString()); } for ( int row = 12; row { DataRow newRow = dt.NewRow(); //Create a row int i = 0; for ( int col = workSheet.Dimension.Start.Column; col { newRow[i++] = workSheet.Cells[row, col].Text; } dt.Rows.Add(newRow); } dt.Columns.RemoveAt(0); //remove No dt.Columns.RemoveAt(0); //remove article //Get BookCode using (SqlConnection conn = new SqlConnection( "Server=10" )) using (SqlCommand cmd = new SqlCommand( null , conn)) { StringBuilder sb = new StringBuilder( "WITH cte AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In (" ); for ( int i = 0; i { if (i != 0) sb.Append( "," ); string name = "@P" + i; cmd.Parameters.AddWithValue(name, dt.Rows[i][ "3" ]); sb.Append(name); } sb.Append( ") ORDER BY Barcode" ); cmd.CommandText = sb.ToString(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); dt.Columns[ "BOOKCODE" ].SetOrdinal(0); dataGridView2.DataSource = dt; }