-2

I am doing a little project and I got stuck at a certain point (I am new to C# WPF). What I want to do is I have some data tables called item, issue_note & items_in_Issue_Note. I want to get all the issue note details into a datagrid & after selecting a row and click view button, I want to display the items in that issue note. I can get the data using

dgISNDetails.ItemsSource = db.Issue_Note.ToList();

but when I am going to use

dgISNDetails.ItemsSource = db.Database.SqlQuery<Issue_Note>("select Issue_No,Created_Date,R_Name,R_Dep,R_Desig,Issued_Date,UpdatedAt from Issue_Note").ToList();

the code throws a NullReferenceException (I want to use the SQL query, because I want to search issue notes by no and date).

I will add my code for reference.

Thank you!

public PnlISNDetails_SK()
{
        InitializeComponent();
        dgISNDetails.ItemsSource = db.Database.SqlQuery<Issue_Note>("select Issue_No,Created_Date,R_Name,R_Dep,R_Desig,Issued_Date,UpdatedAt from Issue_Note").ToList();
        dgISNDetails.ItemsSource = db.Issue_Note.ToList();
        datagrid = dgISNDetails;
}

private void btnSearch_Click(object sender, RoutedEventArgs e)
{
        dt = new DataTable();
        addIssueNoteLogic = new AddIssueNoteLogic();

        if(cmbSearchBy.Text== "ISSUE NOTE NO")
        {
            addIssueNoteLogic.ViewISNFromISNNo(txtSearchBox.Text).Fill(dt);
            dgISNDetails.ItemsSource = dt.DefaultView;
            datagrid = dgISNDetails;
        }
        else if (cmbSearchBy.Text == "CREATED DATE")
        {
            addIssueNoteLogic.ViewISNFromCreatedDate(Convert.ToDateTime(dpSearchDatePicker.Text)).Fill(dt);
            dgISNDetails.ItemsSource = dt.DefaultView;
            datagrid = dgISNDetails;
        }
        else if (cmbSearchBy.Text == "ISSUED DATE")
        {
            addIssueNoteLogic.ViewISNFromIssuedDate(Convert.ToDateTime(dpSearchDatePicker.Text)).Fill(dt);
            dgISNDetails.ItemsSource = dt.DefaultView;
            datagrid = dgISNDetails;
        }
}

Class code for search issue notes:

public SqlDataAdapter ViewISNFromISNNo(string searchText)
{
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandText = "select * from Issue_Note where Issue_No like '%" + searchText + "%'";
        cmd.Connection = con;
        da = new SqlDataAdapter(cmd);
        con.Close();
        return da;
}

public SqlDataAdapter ViewISNFromCreatedDate(DateTime searchText)
{
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandText = "select * from Issue_Note where created_date = '" + searchText + "'";
        cmd.Connection = con;
        da = new SqlDataAdapter(cmd);
        con.Close();
        return da;
}

public SqlDataAdapter ViewISNFromIssuedDate(DateTime searchText)
{
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandText = "select * from Issue_Note where Issued_date = '" + searchText + "'";
        cmd.Connection = con;
        da = new SqlDataAdapter(cmd);
        con.Close();
        return da;
}

public SqlDataAdapter ViewISNDetails(string isnNo)
{
        con.Open();
        cmd = new SqlCommand();
        cmd.CommandText = "select Item.ItemCode,Item.itemName,Item.Unit,Items_In_Issue_Note.Issued_Qty,Issue_Note.Issue_No from ((Item inner join Items_In_Issue_Note on Item.ItemCode= " +
            "Items_In_Issue_Note.ItemCode) inner join Issue_Note on Issue_Note.Issue_No = Items_In_Issue_Note.Issue_No)where Issue_Note.Issue_No = '"+isnNo+"'; ";
        cmd.Connection = con;
        da = new SqlDataAdapter(cmd);
        con.Close();
        return da;
}

This is the code for displaying items in issue note:

public void LoadGrid()
{
        dt = new DataTable();

        string isnNo = (PnlISNDetails_SK.datagrid.SelectedItem as Issue_Note).Issue_No; //Exception is thrown in here
        addIssueNoteLogic = new AddIssueNoteLogic();
        addIssueNoteLogic.ViewISNDetails(isnNo).Fill(dt);
        dgItemsInISN.ItemsSource = dt.DefaultView;
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tharindu
  • 21
  • 6
  • It looks like LoadGrid is called when there is no selected item. You can't access a property (Issue_Note) on a null reference (PnlISNDetails_SK.datagrid.SelectedItem). Code it so that it only does that stuff if there is a selected item. – Crowcoder Jun 17 '22 at 19:25
  • 1
    [Why should you use parameters](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) – HoneyBadger Jun 17 '22 at 19:30
  • @Crowcoder But eventhough I selected an item, it throws the exception. – Tharindu Jun 17 '22 at 19:44
  • Maybe SelectedItem cannot be converted `as Issue_Note`. – Crowcoder Jun 18 '22 at 10:32
  • @Crowcoder Thank you! I found a way. using `dgISNDetails.ItemsSource = db.Issue_Note.Where(Issue_Note => DbFunctions.Like(Issue_Note.Issue_No, "%"+txtSearchBox.Text+"%")).ToList();` for searching I can get it. – Tharindu Jun 18 '22 at 10:53
  • @HoneyBadger I should thankful for you, because with link you have shared, I learnt very improtant thing about sql injection attacks. again thank you very much! – Tharindu Jun 18 '22 at 10:57

1 Answers1

-1

Debug and verify that the connection to the database in your datacontext is not null or closed. specifically this part

db.Database
Eliezer V.M.
  • 1
  • 1
  • 1