I wanted to develop a code in VB inside MS Excel that allows the user to select his name from a drop down menu placed in an Excel Cell. Then the code asks the user for a password against his username and further unlock selected columns for him to edit.
I wrote this a month and it seemed to work fine. Perhaps I didn't have enough data to test it against at the time.
So I finally finished my program conversion to SQL Linq and Entity Framework 6
But Now I have a few statements that are failing.
This one is suppose to sum all the invoices on a single day into 5 columns for a report and bring back 1 row of data, but I get a row for each invoice. I thought I had the groups right for the sum, but it doesn't sum the columns. So It gets the count of invoices, and then the totals.
I've been messing with it for over 2 hours now and I can't figure out how to write it correctly.
Dim m_startDate As DateTime = New DateTime(2015, 9, 1, 0, 0, 0)
Dim m_stopDate As DateTime = New DateTime(2015, 9, 1, 23, 59, 59, 999)
Dim context AsNew hx5Context()
Dim pResult = _
From oh In context.Order_History
Where oh.OrderDate >= m_startDate _
And oh.OrderDate <= m_stopDate _
And oh.OrderStatus = "COMPLETED"
Group By oh = NewWith
} Into Group
You're creating a group for each unique set of values in the SubTotal, Shipping, SalesTax and GrandTotal columns. Since multiple invoices are unlikely to have the same values in those fields, you're going to get a group for each invoice.
I ended up grabbing the records I needed into a result, and ran functions against each value I needed into the target.
You do realize that's the only valid answer on the internet right now?
I'll give that a spin today and see how it goes in Fiddler first and answer back.
I finished my conversion and published it last weekend. I had so many that didn't work that I had to rethink over the last 7 days. But I'm glad I made the change. I think I have eliminated 99.9% of all my database errors now.
I have a problem when I'm returned masked number in datagrid it show like this in columns http://i58.tinypic.com/x4qf6q.jpg
I want to show Like this (000) -(0000-0000) And I'm using vb.net Thanks
i used the 2 grid in my project..
the dgv1 and dgv2
suppose the dgv1 line no is 1 and (dgv2 has contain line 10)dgv2 line is 1. so that dgv1 line details are viseble,another line deatails are hide how is it possible pls help me..
I have 5 datatables which having each 3 columns (ID, Brand, Quanitiy) to derive the opening, purchase, sales and closing stock. The First 3 datatables should be merged and sum up to derive the opening stock as on given period. 4 th datatable is for Purchases done for the given period. The last one Datatable is for Sales done for the given period.
Here is my question: 1. How can i merge the 3 datatables and using the first 2 columns and summing the values? 2. How can i use the tables in crystal reports?
Dim con AsNew ClassConnection
If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()
'To get Opening Stock in Full Quantity
Dim sql AsString = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
" Sum (tblOp_Details.Net_Qty) AS Quantity FROM tblOp_Stock INNER JOIN (tblBrand INNER JOIN" & _
" tblOp_Details ON tblBrand.B_ID = tblOp_Details.B_ID) ON tblOp_Stock.Stk_ID = tblOp_Details.Stk_ID" & _
" WHERE tblOp_Stock.God_ID = @GID GROUP BY tblBrand.B_ID, tblBrand.B_Name"'To get Purchases < start date and adding to the opening stock
Dim sql1 AsString = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
" Sum (tblPur_Details.Net_Qty) AS Quantity FROM tblPurchase INNER JOIN (tblBrand INNER JOIN" & _
" tblPur_Details ON tblBrand.B_ID = tblPur_Details.B_ID) ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID" & _
" WHERE tblPurchase.God_ID = @GID AND tblPurchase.Rec_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"'To get Sales < Start date and subtracting to the above
Dim sql2 AsString = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
" Sum (tblSales_Details.Net_Qty) AS Quantity FROM tblSales INNER JOIN (tblBrand INNER JOIN" & _
" tblSales_Details ON tblBrand.B_ID = tblSales_Details.B_ID) ON tblSales.Sale_ID = tblSales_Details.Sale_ID" & _
" WHERE tblSales.God_ID = @GID AND tblSales.Sale_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"'The above 3 condition is for deriving opening stock as on given date
'To get Purchases >= Start Date and <= Start Date
Dim sql3 AsString = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
" Sum(tblPur_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblPurchase INNER JOIN" & _
" tblPur_Details ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID) ON tblBrand.B_ID = tblPur_Details.B_ID" & _
" WHERE tblPurchase.God_ID = @GID And tblPurchase.Rec_Date >= @SDate And tblPurchase.Rec_Date" & _
" <= @EDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"'The above condition is for deriving Purchases as on given date
'To get Sales >= Start Date and <= Start Date
Dim sql4 AsString = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
" Sum(tblSales_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblSales INNER JOIN" & _
" tblSales_Details ON tblSales.Sale_ID = tblSales_Details.Sale_ID) ON tblBrand.B_ID = tblSales_Details.B_ID" & _
" WHERE tblSales.God_ID = @GID And tblSales.Sale_Date >= @SDate And tblSales.Sale_Date <= @EDate" & _
" And tblSales_Details.S_Active = @SAct GROUP BY tblBrand.B_ID, tblBrand.B_Name"'The above condition is for deriving Sales as on given date
Dim da AsNew OleDb.OleDbDataAdapter(sql, con.Conn)
Dim da1 AsNew OleDb.OleDbDataAdapter(sql1, con.Conn)
Dim da2 AsNew OleDb.OleDbDataAdapter(sql2, con.Conn)
Dim da3 AsNew OleDb.OleDbDataAdapter(sql3, con.Conn)
Dim da4 AsNew OleDb.OleDbDataAdapter(sql4, con.Conn)
Dim dt AsNew DataTable
Dim dt1 AsNew DataTable
Dim dt2 AsNew DataTable
Dim dt3 AsNew DataTable
Dim dt4 AsNew DataTable
I think, that you want to remove the row if found=True (not =False as you coded).
What you should know is, that with this command also the rows.count of the dt_grid1 is decreased. Now your loop will do wrong from this point.
In case of found=True you should break your loop, Remove the Row and restart the loop again. If the loop is finished and you got no found=True then you don't need to repaet this.
Whenever you're removing items from a collection within a For loop, you should start and the end of the collection and work backwards. Otherwise, you end up skipping every element following a removed item.
For i AsInteger = dt_grid1.Rows.Count - 1To0Step -1
You'll also need to specify the row you're looking at when you read the column value:
If dt_grid1.Rows(i)("BarCode").ToString() = dtserialleft.Rows(j)("SerialNo").ToString() Then
found = TrueExitFor' No need to continue looking once we've found a match.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
First a quick description of the problem. A piece of software written in VB handles a lot of stuff. 6-8 active users at a time. One module deals with planning and got 2-3 users which use this.
Up until recently only one user was actively using this module but structural changes within the workforce has changed this.
This has brought on a problem where changes to the planning seems to disappear for those that plan. My problem when fixing this is that I'm not 100% exactly what does what so I want to create a solution that is as small and clean as possible to fix the issues as we are moving to another system sometime within the next 4month-2years.
The module queries an SQLDatabase and then renders the used table using some fancy VB6 graphics. Changes are made and then saved.
When a save is made a lot of rows are deleted and then remade with the new data. I think the problem might be that what is deleted isn't just those entries that are changed but a large chunks of rows fit inside the selection criteria. This means that if 50 rows are selected and 5 changed by user A which saves if user A got the data before user B made it's changes user B's changes are overwritten if their are included inside the selection criteria.
Those who use this module swear that they use the reload function correctly to make sure they got the latest data and then swiftly make their changes and saves and that in order to avoid the problem they make sure only one is editing at the time but you know... ...users.
One of the things I'm working on adding is a check where I look if someone else has changed the data before saving and compare. If user A's and user B's changes doesn't overlap I will merge these instead of overwriting everything and if there are conflicts I will promt the user as to which change they want to use.
This should fix it because even if the query selection returns the same set of data in a majority of cases they are working on different parts of this set.
That's what I've been doing so far and will implement. Even if it doesn't fit 100% what they want this will be what they get.
That should hopefully describe the current situation. Since they actually try to avoid working at the same time my boss also suggested to add a lock/semaphore to enforce the idea that only one user can edit the data. And I just wanted to get some input on a quick way of doing it.
I was thinking of adding three controls/buttons and a status window. Status window will show if and who is currently editing if its locked or if its free to take the position as editor.
Lock and unlock button as well as force unlock.
Super easy to add this as well as the logic behind this that I figured.
I'll add a table which I plan to use as my lock. This will contain one entry which says if the table should be locked. If so, who is currently owning the lock and time of locking.
Add some queries to the buttons and a few other places in the code and bam, only one user can edit.
I'm interesting in viewpoints on my solution as well as other ways to do it. What I like with this is the simplicity to implement for me and simple way of making sure that the users always can force an unlock if something should happen.
Reading through the description of the system it is quite obvious that it is not suited to multiple access - particularly the deletion and re-insertion of data rather than updates.
There is no manner in which you can 'fix' things that will not cause you a world of pain in the future.
I would say you have 2 options:
(1)Rewrite the underlying database update mechanism so that it does not makes use of deletions and inserts.
(2)Leave things as they are an only allow one user to access the application at a time.
Even if you do go with option (1) there is no solution to the update conflict issue.
Quite simply if two users grab the same record, the person who makes the second update wins - there is no way around this no matter how much clever code you write.
Given that the best minds in computer science have not yet solved this problem there is little chance that you or I will.
“That which can be asserted without evidence, can be dismissed without evidence.”
I went with a simple lock that relies a lot on the fact that the users use it the correct way but there are only 3 of them and I only need to reduce the amount of conflicts. Time constraints where I've got to priorities what I do kind of forced me to do what I found the easiest atm.
Now the user wont be able to edit unless they own the lock so to speak and will be prompted to do this and I've gone over all the available functions and added checks to verify correct usage.
Hopefully this will reduce the amount of conflicts. Their problem seems to be compounded by stress when a conflict happens because of their time constraints. If there is one they usually make more shortly there after.
At this point rewriting the entire updating mechanism isn't interesting because in the not so far off future we will change systems and my work now is more to make sure it keeps afloat until that time.
What happens if a user is booted out of the system while the have the lock?
I guess you have to go in and manually unlock the row(s).
Member 11683251 wrote:
relies a lot on the fact that the users use it the correct way
Good luck with that because in my experience systems need to be written to either guide users or to block them from breaking a system. Relying on users to do things 'the correct way' will lead to the sort of trouble you currently find yourself in.
“That which can be asserted without evidence, can be dismissed without evidence.”
The guy that worked here before me had basically given up on user interactions. One part of the system deals with users reporting produced goods and despite numerous checks and controls people still managed to mess it up. One common mistake was that people wrote their userid where you should put the number of goods produced so headed a comparison with their userid was and if it matched with reported goods prompted user to answer if they wanted to proceed. People ended up just clicking yes anyway so instead he just added a big nope can do if they tried to do it.
But I'll put my faith in to that these three can manage a few months.
There is a force unlock available to avoid that and even if that doesn't work this system isn't very safe when it comes to credentials so they could just solve it by exiting and entering again and say that they are the person that owns the lock.
If I was in your position I would do this:
I would create a separate application that allows me to unlock specific rows - a force unlock on all rows could cause issues if some of those rows still need to be locked.
It sounds like you have inherited something of a nightmare to support.
“That which can be asserted without evidence, can be dismissed without evidence.”
It can be a bit of a headache at some times. No documentation at all. The way it's build is at least fairly compartmentalized and everything follows the same flow but certain parts like this module is a bit more complex. A lot of time gets eaten up just by understanding how things are supposed to work.
It doesn't help that I got a lot of duties that are quite spread out which means that when something pops up in a part which I haven't familiarized myself with it's so much harder.
And lastly the entire system is a classic case of a program or suit of programs in this case which has grown in scope as different needs has come up.
For example there are several types of "orders" which are stored in different tables. There are customer orders, which is the entire order of what they want and how much, shipping orders which specify when and what things are to be delivered, these are related to the customer order. Then we have at least two types of internal productions order which are there to specify when we need to produce what. All complete with poor name conventions and lack of documentation.
The upside is that people see me as some sort of magician when there is a problem and quickly figures it out.
I'm a good problem solver but average programmer with zero database experience before I started working here and originally was hired to program robots.
Hi, I am using Vb.net 2010. I have 2 data tables, which have different set of columns. I would like to copy the rows from one data table to another . How to loop through each of the rows in the first data table and set the values in the second data table. Thanks
One possibility is that you create a LINQ query that queries the source datatable and fetches the columns that match in the target datatable. When looping through the results, add a new row as an object array into the target table or create a new table and use that.
I'm using the example provided earlier in a previous post of mine.
I want to convert this sql linq statement into a queryable, so I can adjust the take and skip,
But I can't figure out how to Join and do Multiple contains using IQueryable.
So I wrote this which works, but now I only want to take the data I need, and not the whole table that matches. I thought I needed to go IQueryable for the Take and Skip, not being to add that to the code below. If I'm wrong, please let me know.
Dim p_pageIndex asinteger = 1Dim p_pageOffset asinteger = 10Dim p_SearchQuery asString = "Kneepads"Using context AsNew DBContext()
Dim pResults = _
From pi In context.ProductInfo
Join pik In context.ProductInfo_Keywords On pik.ProductID Equals pi.ProductID
Where pik.Keywords.Contains(p_SearchQuery.Trim) _
Or pik.Stream.Contains(p_SearchQuery.Trim) _
Order By pi.PartNumber, pi.SDescription, pi.Description Descending
In the example above, I have multiple contains from 2 tables that are Joined, I can't figure out the wording for the Join or multiple contains. the multiple OrderBy's was from a previous version in TSQL where I used ORDER By RANK
Dim context AsNew DBContext()
Dim items As IQueryable(Of PRODUCTINFO) = context.ProductInfo
items = items.OrderByDescending(Function(m) m.PartNumber).OrderByDescending(Function(m) m.SDescription).OrderByDescending(Function(m) m.Description)
'Filter by Rank
items = items.Where(Function(m) m.PartNumber.Contains(p_SearchQuery))
'Get a Single Page of Data
If (p_pageIndex = 1) Then
items = items.Take(p_pageOffset)
items = items.Skip((p_pageIndex - 1) * p_pageOffset).Take(p_pageOffset)
pResults = _
items.AsEnumerable().Select(Function(pi) New searchIndex With
With ExcelSheet For i = 1 To Me.DataGridViewGCL.RowCount .cells(i, 1) = Me.DataGridViewGCL.Rows(i - 1).Cells("GclNo").Value For j = 1 To DataGridViewGCL.Columns.Count - 1 .cells(i, j + 1) = DataGridViewGCL.Rows(i - 1).Cells(j).Value Next Next End With
Thanks for the response. I have excel 2013 and the code executes well in vb 2008 and excel 2007. From the references tab, Microsoft Excel Object Library 15.0 exists. Is there anything I have to do again, please? If you could link me to any code concerning that, i will be glad. Thanks
After that, run the code again and see if any values are printed to the output-screen. If there's no data, then you either have no data at that point, data might get converted from the wrong type, or you might be looking at another file than you think you are.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]