Click here to Skip to main content
14,872,784 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My program can read excel into datatable and there's a barcode column with data. I need to get the bookcode from sql server based on data in barcode column. How the sql query able to get each cell data from the barcode column? I am confused to get the data by column or row. I managed to get the sql connection and retrieve the general records so far. Hopefully you guys are able to assist me in this matter. thank you

What I have tried:

C#
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 = workSheet.Dimension.Start.Row; row <= workSheet.Dimension.End.Row; row++)
           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);

            }

            //// dataGridView2.ColumnHeadersVisible = false; // dataGridView1.Rows.Remove(dataGridView1.Rows[12]);

            dt.Columns.RemoveAt(0); //remove No 
            dt.Columns.RemoveAt(0); //remove article
            
n            //Get BookCode
            SqlConnection conn = new SqlConnection("Server=10.0.0.10;Database=;UserID=;Trusted_Connection=False");
            string query = "SELECT TOP 14 BookCode FROM Inventory";            

            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            dataGridView2.DataSource = dt;

            conn.Close();
            da.Dispose();
}
Posted
Updated 28-Jan-19 4:59am
v2
Comments
Member 14127871 25-Jan-19 2:00am
   
UPDATE, I managed to get the Bookcode using below query
string query = "SELECT BookCode FROM Inventory WHERE Barcode='" + dt.Rows[1]["3"].ToString().Trim() + "'"; //3 is column for barcode

And now i think i have to do looping to get all the bookcode for all the barcode column data but I'm not sure with the sequence and sql connection
Richard Deeming 28-Jan-19 10:52am
   

Use the SQL WHERE clause or IN clause, see: SQL IN Operator[^]

Before using the barcode value, you should first check if it is NULL:
var barcode = dt.Rows[0]["Barcode"];

if (barcode != NULL)
{
  // your code ...
}
   
v2
Comments
Member 14127871 24-Jan-19 19:21pm
   
Like this? I'm not sure how to get the barcode column in ()

string query1 = "SELECT Bookcode FROM Inventory WHERE Barcode IN (Barcode column in datatable here?)";

I thought it something to do like foreach(DataRow dr in datatable.Rows) to get barcode?
RickZeeland 25-Jan-19 2:13am
   
See updated solution, you can use WHERE Barcode IN (1, 2, 3, ...)
Something like this should work, and will avoid the SQL Injection vulnerability:
C#
using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand(null, conn))
{
    StringBuilder sb = new StringBuilder("SELECT BookCode FROM Inventory WHERE 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(")");
    
    cmd.CommandText = sb.ToString();
    
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    
    dataGridView2.DataSource = dt;
}
   
v2
Comments
Member 14127871 28-Jan-19 19:58pm
   
Thank you I got it. May I ask why the data I receive doesnt follow the order in barcode column? And there supposedly a blank cell or null since theres a data in barcode that doesnt have the bookcode, how I am supposed to add the null value?
Richard Deeming 29-Jan-19 6:14am
   
You don't have an ORDER BY clause on your query, so the order of the results is not guaranteed.

If you want to exclude records without a BookCode, you'll need to add And BookCode Is Not Null to the WHERE clause.
Member 14127871 29-Jan-19 19:30pm
   
I did order by barcode and still the order doesnt match the bookcode with barcode. And I want if there's no bookcode that match with barcode in sql, it will write 'no bookcode found' in the cell. Not to exclude it. I really appreciate ur help thank you
Member 14127871 30-Jan-19 3:58am
   
Currently this is my code to get the 'no data' cell if no bookcode found but still Im not able to get.

StringBuilder sb = new StringBuilder("WITH cte AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer WHERE 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(") ");
sb.Append(")) SELECT InvtID,BOOKCODE = coalesce(InvtID, 'nodata') FROM cte WHERE rid = 1 UNION SELECT InvtID='', BOOKCODE='nodata' WHERE NOT EXISTS (SELECT 1 FROM CTE)");
create a comma seperated string of barcodes and

like
C#
string str = "1111, 11212, 2155";


this can be done by

C#
foreach(DataRow dr in dt.Rows)
{
  str += dr["barcode"].ToString() + ",";
}


use query

C#
string query = "SELECT TOP 14 BookCode FROM Inventory where barcode in ('"+ str + "')"; 
   
v2
Comments
Member 14127871 28-Jan-19 20:34pm
   
I tried ths method too. Same problem happened which the order doesnt follow the barcode column data and there no cell blank or Null as some of barcode have no bookcode.

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