How to get CheckedListBox values from DataBase?

Question

Dear All,

Greetings of the day..

 

Please take a look at my problem. I want to get the CheckedListBox items Checked according to the database when I select an option from a DataGridView.

 

In a WinForm, I have a DataGridView contains User names (XYZ, ABC etc.) and a CheckedListBox contains different rights (Billing, Purchase, Stores, Edit, Delete etc.) to the user. a user (e.g. XYZ) rights saved (e.g. Purchase, Billing etc.) to MS Access database in a table...

UserName   UserRights

XYZ              Billing

XYZ              Purchase

XYZ              EditBills

 

Now, i want to see all the rights given to XYZ in the CheckedListBox when I select name XYZ from the DataGridView.

 

I got almost result but there is something missing that compares the db to CheckedListBox.

Please see the screen shots attached to get it more clearly. in this code (The Code.jpg) I only get first few items checked regardless, the actual rights.

 

Thank You.

 

Mayank Jani

Answer 1

Do not execute this query directly first understand all statements and thing about your requirement... here is basic queries, let me know next queries later after understanding this...

--get current stock of product
Select ProductID, ProductName, ClosingStockQTy, ClosingStockRs from ProductMaster where Product = 1

--on purchase add stock
insert into Purchases (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)
values
(
    'CP1',
    getdate(),
    @ProductID,
    @Rate,
    @Discount,
    @Qty,
    @TotalAmount
)

declare @StockQTy as int=0;
set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);
set @StockQTy = @StockQTy + @Qty;

Update ProductMaster
set
    ClosingStockQTy = @StockQTy
Where
    ProductID = 1


--on sale decrease stock
insert into Sales (BillNo, BillDate, ProductID, Rate, Discount, Qty, TotalAmount)
values
(
    'CS1',
    getdate(),
    @ProductID,
    @Rate,
    @Discount,
    @Qty,
    @TotalAmount
)

declare @StockQTy as int=0;
set @StockQTy = (select top 1 ClosingStockQty from ProductMaster where ProductID = 1);
set @StockQTy = @StockQTy - @Qty;

Update ProductMaster
set
    ClosingStockQTy = @StockQTy
Where
    ProductID = 1

Answer 2

You can use inner join and left join to get the result.

select PM.ProductID, PM.ProductName, PM.MRP,
Purchase.BillDate as PurchaseBillDate,Purchase.Rate as PurchaseRate,
Sales.BillDate as SalesBillDate,Sales.Rate as SalesRate
from Productmaster PM
inner join Purchase on PM.ProductID=Purchase.ProductID
left join Sales on PM.ProductID = Sales.ProductID
where PM.ProductID= 1000