|
Hello
I am a non-programmer who has been asked to maintain Excel tools. I used Visual Studio Community 2017 to edit a vb file. In my organization, every 5th year is designated as having 53 weeks for financial reporting purposes. One of the tools contained code for the previous time we had a year with 53 weeks (2012). Two lines in the code contained a reference to 2012. I changed them to 2017 and and build the executable file. Now, when I run the executable, I get the following .NET error message
Can anyone see why changing the 53 week year value from 2012 to 2017 would cause this to fail, and what steps I would need to take to fix it?
Quote: ************** Exception Text **************
System.IndexOutOfRangeException: Index was outside the bounds of the array.
at VGProfilesAP9.dlgProfiler.Build_FromToLists() in C:\Users\3811\Desktop\AP9 40 STORES TWO\AP9\VGProfilesAP9\VGProfilesAP9\Profiler.vb:line 156
at VGProfilesAP9.dlgProfiler.Profiler_Load(Object sender, EventArgs e) in C:\Users\3811\Desktop\AP9 40 STORES TWO\AP9\VGProfilesAP9\VGProfilesAP9\Profiler.vb:line 84
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Here is an excerpt of the vb code that contains the two lines referenced above.
Line 84 is the "Profiler_Load" subroutine: Build_FromToLists()
Line 156 is in "Build_FromToLists()" subroutine: wkList(wkIndex) = CStr(yr) & " Wk" & Format(wk, "00")
<pre>
Public Class dlgProfiler
Dim storeCount As Integer
Dim IsInit As Boolean = False
Dim nmeArr(0 To 999) As String
Dim importVGArr(0 To 999) As String
Dim totSales As Double = 0
Dim nodeFound As Boolean
Dim nodecount As Long
Dim WK1, WKL, bWK1, bWKL As Int32
Dim webSales As Double
Dim EditMode As Boolean = False
Dim SavedProfID As Integer = 0
Dim HNRID_IN As String
Private Sub Profiler_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim defaultVGDB9 = GetSetting("AsstPlan", "Settings", "DefaultVGP9db", "")
Select Case UCase(Mid(System.Environment.GetEnvironmentVariable("SessionName"), 1, 3))
Case "CON"
MY_AP9_PATH = Application.StartupPath
If File.Exists(defaultVGDB9) Then
If defaultVGDB9 = MY_AP9_PATH & "\MyVGP9.accdb" Then
Me.optProfileDB_ThisComputer.Checked = True
SharedProfileDatabase = GetSetting("AsstPlan", "Settings", "SharedVGP9db", "")
If File.Exists(SharedProfileDatabase) Then
optProfileDB_Shared.Text = "Shared AP9 Profiles (from " & SharedProfileDatabase & ")"
Else
optProfileDB_Shared.Text = "Shared AP9 Profiles (currently not linked - click to search)"
SaveSetting("AsstPlan", "Settings", "SharedVGP9db", "")
End If
Else
Me.optProfileDB_Shared.Checked = True
optProfileDB_Shared.Text = "Shared AP9 Profiles (from " & defaultVGDB9 & ")"
SaveSetting("AsstPlan", "Settings", "SharedVGP9db", defaultVGDB9)
optProfileDB_Shared.Text = "Shared AP9 Profiles (from " & defaultVGDB9 & ")"
SharedProfileDatabase = defaultVGDB9
End If
cmdSharedDBFind.Left = optProfileDB_Shared.Right + 10
cmdSharedDBCreate.Left = optProfileDB_Shared.Right + 100
CurrentProfileDatabase = defaultVGDB9
Else
If File.Exists(MY_AP9_PATH & "\MyVGP9.accdb") Then
CurrentProfileDatabase = MY_AP9_PATH & "\MyVGP9.accdb"
SaveSetting("AsstPlan", "Settings", "DefaultVGP9db", CurrentProfileDatabase)
Else
If MsgBox("An AP9 profiles database file has not been installed this computer yet. A new blank file will be opened in the asst planning " &
"folder." & vbCrLf & vbCrLf & "If you click cancel, the profiler application will terminate without creating that file.",
vbOKCancel + vbExclamation, "AP9 Profile Datafile Not Found") = vbOK Then
If File.Exists(AP_PATH & "MyVGP9.accdb") = True Then
File.Copy(AP_PATH & "MyVGP9.accdb", MY_AP9_PATH & "\MyVGP9.accdb")
CurrentProfileDatabase = MY_AP9_PATH & "\MyVGP9.accdb"
SaveSetting("AsstPlan", "Settings", "DefaultVGP9db", CurrentProfileDatabase)
Else
MsgBox("Cannot connect to public drive to create file. Application will end", vbOKOnly + vbCritical, "Not connected")
End
End If
Else
End
End If
End If
End If
Case "ICA"
If File.Exists(defaultVGDB9) Then
CurrentProfileDatabase = defaultVGDB9
Else
If MsgBox("When working through Citrix, only 'Shared' profiles database files are available. (Shared files are stored on a network drive like the I:\ drive.) " & _
"Have you already created a shared database on the I:\ drive?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Shared VG Profiles Database") = vbYes Then
If FindSharedProfileDB() = False Then End
Else
If MsgBox("Do you want to create one? (Yes to create a new file, No toexit the application)", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Create New Shared VG9 Database") = vbYes Then
CreateSharedVGDB()
Else
End
End If
End If
End If
End Select
cnMID = New ADODB.Connection
cnMID.Open("DRIVER={SQL SERVER};SERVER=Mid.sqldb.prod.myco.com;DATABASE=MID", "User", "user")
InitStoreNames()
BuildVersionList()
Build_SeasonYearLists()
Build_FromToLists()
BuildUserList()
GR3.RowTemplate.Height = 18
SetGrids(300)
SetToolTips()
LoadMyProfiles()
End Sub
Public Sub SetToolTips()
Dim toolTips As New ToolTip()
toolTips.AutoPopDelay = 8000
toolTips.InitialDelay = 1000
toolTips.ReshowDelay = 500
toolTips.ShowAlways = True
toolTips.SetToolTip(cmdImport_Excel, "Create and open an Excel sheet with 3 colums with Store, Sales, and optionally Vgroup letter A-O")
toolTips.SetToolTip(cmdCopyVG, "Allows you to copy profile to/ from a shared profiles file to the file on your computer.")
End Sub
Private Sub dlgProfiler_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
cnMID.Close() : cnMID = Nothing
End Sub
Private Sub InitStoreNames()
Dim RSS As New ADODB.Recordset
Dim SQL1 As String = "SELECT ST_ID, STORE_NAME FROM dbo.STORES ORDER BY ST_ID ASC"
RSS.Open(SQL1, cnMID, 3, 1, 1)
Do Until RSS.EOF
If Val(RSS.Fields("ST_ID").Value) > 0 Then
nmeArr(RSS.Fields("ST_ID").Value) = RSS.Fields("STORE_NAME").Value
End If
RSS.MoveNext()
Loop
RSS.Close()
End Sub
Private Sub BuildVersionList()
Dim RSV As New ADODB.Recordset
Dim SQL1 As String = "SELECT dbo.FORECAST_VERSION.FV_RID, dbo.FORECAST_VERSION.DESCRIPTION From dbo.FORECAST_VERSION WHERE (((dbo.FORECAST_VERSION.FV_ID) IN('A','C','Z'))) ORDER BY dbo.FORECAST_VERSION.DESCRIPTION ASC"
RSV.Open(SQL1, cnMID, 3, 1, 1)
Dim vList As New ArrayList
Dim vListB As New ArrayList
Do Until RSV.EOF
vList.Add(New MIDVersion(RSV.Fields(1).Value, RSV.Fields(0).Value))
vListB.Add(New MIDVersion(RSV.Fields(1).Value, RSV.Fields(0).Value))
RSV.MoveNext()
Loop
cboH_PlanVersion.DataSource = vList
cboH_PlanVersion.DisplayMember = "Version_Name"
cboH_PlanVersion.ValueMember = "Version_Code"
RSV.Close() : RSV = Nothing
cboH_PlanVersion.SelectedIndex = 2
End Sub
Private Sub Build_FromToLists()
Dim wkList(0 To 207) As String
Dim year1 As Integer = Year(Now) - 2
Dim wkIndex As Integer = 0
Dim currFweek As Long = MID_GetCurrFiscalWeek()
Dim currFweekIndex As Integer = 0
For yr = year1 To year1 + 3
If yr = 2017 Then
For wk = 1 To 53
wkList(wkIndex) = CStr(yr) & " Wk" & Format(wk, "00")
If (yr * 100) + wk = currFweek Then currFweekIndex = wkIndex
wkIndex += 1
Next wk
Else
For wk = 1 To 52
wkList(wkIndex) = CStr(yr) & " Wk" & Format(wk, "00")
If (yr * 100) + wk = currFweek Then currFweekIndex = wkIndex
wkIndex += 1
Next wk
End If
Next yr
cboTime_From.Items.AddRange(wkList)
cboTime_To.Items.AddRange(wkList)
If currFweekIndex > 0 Then
cboTime_From.SelectedIndex = currFweekIndex - 1
cboTime_To.SelectedIndex = currFweekIndex
End If
End Sub
|
|
|
|
|
Member 13693556 wrote:
Dim wkList(0 To 207) As String
You've declared an array of 208 elements, but you're trying to store 209 elements in it.
- 2016: 52 elements;
- 2017: 53 elements
- 2018: 52 elements
- 2019: 52 elements
- 52 + 53 + 52 + 52 = 209
Change the array declaration:
Dim wkList(0 To 208) As String
Also, rather than recompiling the code every five years, you can calculate whether the year needs 53 weeks:
If (yr - 2012) Mod 5 = 0 Then
Else
End If Mod Operator (Visual Basic) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello Richard
Thank you so much for figuring this out, and for the very useful explanation, and for the idea on how to eliminate the manual edit every time there is a 53 week year. I made then edit, then decided I better confirm my "every 5 years" comment. Looking at the National Retail Federation website where the 4-5-4 calendar is published, I saw that the frequency of 53 week years is not necessarily every 5 years. Most recent examples of 53 week years were:
1995
2000
2006
2012
2017
Because it is not a sure thing for a 53 week every five years, I restored the old code. But thanks for the suggestion, and thanks so much for sorting out the issue, and explaining how to calculate what would be needed going forward. I really appreciate the help.
One question, from the lines in the error message, how did you know to target the array declaration as being the source of the problem?
Kenn
modified 23-Feb-18 21:34pm.
|
|
|
|
|
OK, it looks like you're using the "first full week" rule, with weeks starting on Sunday.
Private Shared Function NumberOfWeeksInYear(ByVal year As Integer) As Integer
Dim lastDayOfYear As New Date(year, 12, 31)
Return System.Globalization.CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(lastDayOfYear, System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Sunday)
End Function
Private Sub Build_FromToLists()
Dim wkList(0 To 208) As String
Dim year1 As Integer = Year(Now) - 2
Dim wkIndex As Integer = 0
Dim currFweek As Long = MID_GetCurrFiscalWeek()
Dim currFweekIndex As Integer = 0
For yr = year1 To year1 + 3
Dim weeksInYear As Integer = NumberOfWeeksInYear(yr)
For wk = 1 To weeksInYear
wkList(wkIndex) = CStr(yr) & " Wk" & Format(wk, "00")
If (yr * 100) + wk = currFweek Then currFweekIndex = wkIndex
wkIndex += 1
Next wk
Next yr
cboTime_From.Items.AddRange(wkList)
cboTime_To.Items.AddRange(wkList)
If currFweekIndex > 0 Then
cboTime_From.SelectedIndex = currFweekIndex - 1
cboTime_To.SelectedIndex = currFweekIndex
End If
End Sub
Member 13693556 wrote: One question, from the lines in the error message, how did you know to target the array declaration as being the source of the problem?
The error message said "Index was outside the bounds of the array". The line it was thrown from only had a single array access, to wkList. From there, it was just a case of working out the maximum value for wkIndex, and comparing it to the declared length of the array. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks again for the explanation. It seems the tool was written with the understanding that the array value would require intermittent maintenance.
The author of the tool had been planning on a 3 year transition and knowledge transfer to coincide with his planned departure from the company. When his departure was accelerated by three years, the (unwritten) system documentation walked out the door with him - so I really appreciate the help.
|
|
|
|
|
I populate a list box with files from a file search. Then I can select a file from the list box and open it with a certain program. If I select a second file from the list box and open it, it starts another instance of the program. These are text files I am opening in a text editor. What I want to do is if the text editor is already running just open the new file in the already opened program. The extensions can be anything so I cannot associate them to this file editor. (too numerous) I know how to find out if the program is running, just not how to add another file to it. Here is code that I have been using to find out if the program is open and if not start the file in the program. This works fine but what I want to do is instead of showing message box if the process is running, is open the file in it without staring another instance of it.
Dim ProcessIsRunning As Boolean = Process.GetProcessesByName("editcnc").Length > 0
If ProcessIsRunning = True Then
MessageBox.Show("EditCNC already Running")
Else
openf = """" & openf & """"
Process.Start(editFile.Text, openf)
End If
modified 22-Feb-18 15:55pm.
|
|
|
|
|
You have a problem. Not every application can handle opening another document while it's already running, like Notepad can't do that.
Not every application exposes any kind of interface to be able to programatically support opening multiple documents. Word, Excel, and most other Office apps, supports doing this as they expose COM interfaces to be able to communicate with them. Most applications don't support this. You'd also have to write code specifically against every application you'd want to support. There is no such thing as a "one interface works with all" here.
Some applications may support opening other documents through command line arguments. Launch another copy of the application and it will automatically talk to the already running copy to tell it to open the new document. Not every application supports this and, again, there is no "one command line works for all apps" here either.
There is no way to support every app generically. The only workable solution you have is to simple launch the document you're trying to open. The application associated with that file extension will open the document however it is supported, either by launching another copy of the application or by telling the the already running copy of the app to open it.
YOU WILL HAVE NO CONTROL OVER HOW THIS PROCESS WORKS, NOR CAN YOU GET CONTROL OVER IT!
modified 23-Feb-18 8:04am.
|
|
|
|
|
Thanks for your reply, Dave. The program I am working with, "EditCNC" does support opening multiple documents at one time. In my program I am giving the option to open the files with EditCNC, Word, or Wordpad. I am not as concerned about opening multiple files with Word or Wordpad, just with EditCNC. I am new at this but am wondering if you could elaborate more on the COM interface you had mentioned.
Thanks.
|
|
|
|
|
You have to contact the vendor on that app and see if it even exposes a COM interface as most apps don't.
You can try looking in Visual Studio though to see if anything stands out. Start a new Console or Windows Forms application, go to your project in Solution Explorer and right-click References. Click Add Reference. In the dialog that shows up, click on the COM tab and start looking through the list for anything that might be related to EditCNC. Warning: The list is going to be quite long.
|
|
|
|
|
Dave Kreskowiak wrote: Now every application exposes ...
"Not every application", surely? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Whoops! Fixed. 
|
|
|
|
|
Hi,
I would like to know how to read, modify and write an Excel file in a Windows form.
Does anyone has any experience and sample code?
I am using Visual Basic 2017.
Thanks!
Frank
modified 22-Feb-18 9:01am.
|
|
|
|
|
There are the Microsoft.Office.Interop libraries for interacting with Office products, and lots of sample code on the net if you google it. Here[^] tells you how to add the Excel one to your project.
Note though that this is only a workable solution for Winforms - if you're writing Webforms, you'll need a third-party solution (which may be easier to work with anyway, if you or whoever you're working for can afford it - they're generally not cheap.)
|
|
|
|
|
Thanks for your info!
I will look into it.
Frank
|
|
|
|
|
Syncfusion provides a set of non-UI tools for this purpose. They have a Community version if you don't wish to spend thousands of dollars for the commercial version. You can create, read and write Excel and Word files and integrate it into a WinForms or WPF application. Bit of a learning curve, but it's a fairly powerful set of tools. Thorough documentation and responsive live help even with the free version. There also are 3 grid controls included if you wish to build your own Excel clone.
Syncfusion XlsIO[^]
Sometimes the true reward for completing a task is not the money, but instead the satisfaction of a job well done. But it's usually the money.
|
|
|
|
|
Hello !
I'm using vb.net 2013
Is there any way to open a form full screen? ( I mean the same effect that I can have on youtube when choose to play full screen , or in games that can show full screen ) ?
Thank you !
|
|
|
|
|
Sure, open a form with a windowstate of WS_MAXIMIZED and make it borderless. If you want to improve on youtube, you open your own desktop-object
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello !
I'm using vb.net 2013 , sql server 2008 R2.
I'm using SMO to backup and restore databases.
The problem is that this working only on SQL server 2008R2.
If I try my application with other Sql server versions it doesn't work.
Is there any way to make it work with all sql server versions ?
Thank you !
|
|
|
|
|
Different versions of Sql Server have their own respective SMO, as it is bound to the capabilities of that version. To "make it work" with all versions, you'd have to make a facade that loads the correct version of SMO and manipulates the target for you.
Still doesn't mean that you can restore a 2012 database on a 2008 server, so limits as described on MSDN still apply.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
And there is another way to do that , I mean without SMO ?
|
|
|
|
|
Issue Sql-commands for what you want to achieve; the backup-command is supported by most versions of SQL Server.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I've read somewhere that if I use SMO files from the latest SQL server version , these can work with lower versions too.
Is this true ?
|
|
|
|
|
Yes
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello !
I'm using vb.net 2013 , SQL server 2008 R2 and entity Frameowrk 6.
I have a situation where on the database , I need to have a foreign key refer the primary key on the same Table.
Employe Table
------------
ID
Name
Position
Manager ID
ID is the primary key , Manager ID is the foreign key , and as the manager is also an employee on the same table , this foreign key refer to the primary key ID on the table employee. But several employees can have the same Manager ID.
I want to know if this is possible and safe to use on sql server and also to work with it on Entity Framework.
Thank you !
|
|
|
|
|
desanti wrote:
I want to know if this is possible and safe to use on sql server and also to work with it on Entity Framework.
That's a rather common thing to do. Without knowing much EF, I'm going to say that it is possible and safe.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
