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

Nebula

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.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nebula7y ago
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);
Nebula7y ago
1. 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 ( ");
2. for (int i = 0; i < dt.Rows.Count; i++)
3. {
4. if (i != 0) sb.Append(",");
5. string name = "@P" + i;
6. cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]);
7. sb.Append(name);
8. }
9. sb.Append(")");
10. cmd.CommandText = sb.ToString();
11. SqlDataAdapter da = new SqlDataAdapter(cmd);
12. 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 Kumar7y ago

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

Nebula7y ago

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 Kumar7y ago

Hi Nebula,

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

Nebula7y ago

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 Kumar7y ago

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

Nebula7y ago

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 <= workSheet.Dimension.End.Column; col++)
{
dt.Columns.Add(col.ToString());
}
for (int row = 12; row <= 26; row++)
{
DataRow newRow = dt.NewRow(); //Create a row
int i = 0;
for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.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 < dt.Rows.Count; 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;
}