|
Ms Chart is a pain in the behind, first I had an issue where when binding or manually adding dates to my X axis it would force add every single date in between even though that data didn't exist, my SQL code is setup with a date dimension table which makes it so I only return exactly the number of rows I want (4 weeks 4 values, 5 months, 5 values)
"Fixed" that by instead adding generic counter for X axis and then setting the label to the date, or so I thought...
[Now that I've added code to do multiple series the damn thing is adding extra items to the X axis again]
[The dates in each series are exactly the same.]
My counter gets reset to 0 before every series and when I loop through the code to see the data points in debug the labels and data are set properly for every series. but ms chart still decides it needs 12 billion extra lines on the X axis for no goddamn explainable reason.
Private Sub LoadChart()
Dim oDT As New DataTable
Dim sqlCmd As New SqlCommand
Dim sSQLDateCTE As String = "WITH Date_CTE as ( " & _
"SELECT {0} DDim.[Date] as SaleDate " & _
"FROM [DateDimension] as DDim {1} "
Dim sWHEREDateCTE As String = "WHERE [Date] BETWEEN DATEADD({0},-{1},GETDATE()) AND GETDATE() {2} "
Dim sSQLSUM As String = "), SUM_CTE as ( " & _
"SELECT {0}" & _
"FROM {1} " & _
"WHERE F1034 = 3 AND F64 <> 0 AND F01 = @UPCCode " & _
"AND F254 IN (SELECT saledate FROM Date_CTE) " & _
"GROUP BY {2}) "
Dim sSQLFinalPart = "SELECT {0} DC.SaleDate ,ISNULL(SC.UnitsSold,0) as UnitsSold " & _
"FROM Date_CTE as DC " & _
"LEFT OUTER JOIN SUM_CTE as SC " & _
"ON SC.SaleDate = DC.SaleDate {1}" & _
"ORDER BY DC.SaleDate ASC "
Dim sFieldList As String = ""
Dim sGroupBy As String = ""
Dim sStorelist As String = ""
Dim sWhereIN As String = ""
Dim sWhereINDateCTE As String = ""
Dim sSQL As String = ""
Try
If oAppEnv.sSource = "H" Then
For Each CheckedItem In clbStores.CheckedItems
If sStorelist <> "" Then
sStorelist = sStorelist & ", "
End If
sStorelist = sStorelist & CheckedItem.key
Next
End If
If sStorelist <> "" Then
sFieldList = "F01 as UPCCode, F1056 as Store, F254 as SaleDate, SUM(F64) as UnitsSold "
sGroupBy = "F01,F1056,F254"
sSQLDateCTE = String.Format(sSQLDateCTE, "SL.Store, ", "CROSS JOIN Newbuck.dbo.tblStoreList as SL ")
sWhereIN = "AND {0} in ({1}) "
sWhereINDateCTE = String.Format(sWhereIN, "SL.Store", sStorelist)
sSQLFinalPart = String.Format(sSQLFinalPart, "DC.Store, ", "AND SC.Store = DC.Store ")
Else
sFieldList = "F01 as UPCCode, F254 as SaleDate, SUM(F64) as UnitsSold "
sGroupBy = "F01,F254"
sSQLDateCTE = String.Format(sSQLDateCTE, "", "")
sWhereINDateCTE = ""
sSQLFinalPart = String.Format(sSQLFinalPart, "", "")
End If
Dim PeriodRow As DataRow = oAppEnv.dtPeriods.Select(String.Format("DPPeriodID = {0}", cboPeriods.SelectedValue)).FirstOrDefault
Dim DurationRow As DataRow = oAppEnv.dtDurations.Select(String.Format("DDDurationID = '{0}'", cboDuration.SelectedValue)).FirstOrDefault
Select Case PeriodRow.Item("DPPeriodAbbrev")
Case "D"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "DD", DurationRow.Item("DDDurationLength"), sWhereINDateCTE)
sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_D", sGroupBy)
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
Case "W"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "WW", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND Weekday = 1 "
sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_W", sGroupBy)
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
Case "M"
sWHEREDateCTE = String.Format(sWHEREDateCTE, "MM", DurationRow.Item("DDDurationLength"), sWhereINDateCTE) & " AND [DAY] = DATEPART(Day,getdate()) "
sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_M", sGroupBy)
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
Case "Y"
sWHEREDateCTE = String.Format("WHERE [Date] BETWEEN '01-01-2009' AND GETDATE() AND DayOfYear = 1 {0} ", sWhereINDateCTE)
sSQLSUM = String.Format(sSQLSUM, sFieldList, "RPT_ITM_Y", sGroupBy)
sSQL = sSQLDateCTE & sWHEREDateCTE & sSQLSUM & sSQLFinalPart
End Select
sqlCmd.CommandText = sSQL
sqlCmd.Parameters.AddWithValue("@UPCCode", sUPCCode)
oDT = oAppEnv.oLogiDM.GetSQLData(sqlCmd)
ctSalesData.ChartAreas.Clear()
ctSalesData.ChartAreas.Add("Sales")
ctSalesData.Series.Clear()
If oDT IsNot Nothing Then
'//Get the Date range.
Dim oSortDV As DataView = oDT.DefaultView
oSortDV.Sort = "UnitsSold DESC"
'ctSalesData.DataSource = oDT
Dim iCnt As Integer = 0
ctSalesData.AlignDataPointsByAxisLabel()
If oAppEnv.sSource = "H" And sStorelist <> "" Then
For Each CheckedItem In clbStores.CheckedItems
Dim oStoreRows() As DataRow = oDT.Select("Store = " & CheckedItem.key)
iCnt = 0
Dim cSeries As New Charting.Series(CheckedItem.key)
cSeries.ChartType = Charting.SeriesChartType.Bar
cSeries.IsXValueIndexed = True
cSeries.CustomProperties = "PixelPointWidth = 20"
cSeries.IsValueShownAsLabel = True
For Each row In oStoreRows
cSeries.Points.AddXY(iCnt, row.Item("UnitsSold"))
cSeries.Points(iCnt).AxisLabel = row.Item("SaleDate")
iCnt += 1
Next
ctSalesData.Series.Add(cSeries)
Next
Else
Dim cSeries As New Charting.Series("Sales")
cSeries.ChartType = Charting.SeriesChartType.Bar
cSeries.IsXValueIndexed = True
cSeries.CustomProperties = "PixelPointWidth = 15"
cSeries.IsValueShownAsLabel = True
For Each row In oDT.Rows
cSeries.Points.AddXY(iCnt, row.item("UnitsSold"))
cSeries.Points(iCnt).AxisLabel = row.item("SaleDate")
iCnt += 1
Next
ctSalesData.Series.Add(cSeries)
End If
ctSalesData.ChartAreas(0).AxisX.Minimum = 0
ctSalesData.ChartAreas(0).AxisX.Maximum = oDT.Rows.Count + 1
If oDT.Rows.Count > 21 Then
Dim ZoomDate As Date = oDT.Rows(14).Item("SaleDate")
ctSalesData.ChartAreas(0).AxisX.ScaleView.Zoom(0, 22)
End If
ctSalesData.ChartAreas(0).AxisX.Interval = 1
ctSalesData.ChartAreas(0).AxisY.Minimum = 0
ctSalesData.ChartAreas(0).AxisY.Interval = 1
ctSalesData.ChartAreas(0).AxisY.Maximum = Math.Ceiling(oSortDV(0).Item("UnitsSold") / 10) * 10
If ctSalesData.ChartAreas(0).AxisY.Maximum = oSortDV(0).Item("UnitsSold") Then
ctSalesData.ChartAreas(0).AxisY.Maximum += 10
End If
ctSalesData.ChartAreas(0).CursorX.AutoScroll = True
ctSalesData.ChartAreas(0).AxisX.ScaleView.SizeType = Charting.DateTimeIntervalType.Number
ctSalesData.ChartAreas(0).AxisX.ScrollBar.ButtonStyle = Charting.ScrollBarButtonStyles.SmallScroll
ctSalesData.ChartAreas(0).AxisX.ScaleView.SmallScrollSize = 21
ctSalesData.ChartAreas(0).AxisX.ScrollBar.IsPositionedInside = False
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
|
|
|
|
|
I think I found the answer by walking the Parent of Msg.HWnd until I find the containing 'Control' window in which FromHandle(hwnd) returns a Control object.
In a custom control I have implemented an IMessageFilter to process the WM_MOUSEMOVE message to determine when the mouse is over a particular control.
I then try to convert the hWnd to a control with Control.FromHandle(Msg.HWnd). This works for common controls.
When the mouse is over as WebBrowser control I get Nothing returned from FromHandle().
I can use api GetClassName() to get the class name which returns either "shell embedding" or "internet explorer_server".
The problem is I have more than one WebBrowser instance in the app. and need to distinguish between them.
My question is, is there some value I can retrieve using an api with Msg.HWnd that will be unique to the particular instance object?
WebBrowser does not have a Text property so GetWindowText() returns "".
WebBrowser does have a Tag and Name property but I don't know if that is accessable through a windows api.
Thank you
-- modified 25-Nov-19 10:39am.
|
|
|
|
|
|
This has nothing to do with Visual Basic. The answer is most likely in the manual that came with your router.
|
|
|
|
|
Need help...
Table Name - cashbook
Field name - pname & amount
Examble
pname amount
Antony 1500
Jose 3000
Antony 3500
need to display in msflexgrid like belo
Antony - 5000
Jose - 3000
below my code is not working....can anybody help me
Rec.open "select from pname,sum(amount) as amt from cashbook group by pname order by pname,var,adopendynamic,adlockoptimist
While rec.eof=false
Pymnt.rows=pymnt.rows+1
Pymnt.textmatrix(pymnt.rows -1,0)=rec!pname
Pymnt.textmatrix(pymnt.rows -1,1)=amt
Rec.movenext
Wend
Rec.close
|
|
|
|
|
Read your SELECT statement again. An SQL SELECT is of the form
SELECT <fieldlist>
FROM <table>
WHERE condition
GROUP BY <<fieldlist>
ORDER BY <fieldlist>
You have a FROM where it does not belong.
SELECT FROM <fieldlist>
FROM cashbook
GROUP BY pname
ORDER BY pname
And you might want to get off of VB6. It's been dead for quite a long time now and getting into VB.NET development is free!
|
|
|
|
|
ineed code project show report data from data base by vb
|
|
|
|
|
If you're asking for a sample project on how to show data in a report in VB, then Google should be your first stop.
Search for "VB.NET Show report in " and whatever report engine you're using.
|
|
|
|
|
Hi,
Yesterday I ran into the exception mentioned in the title. In the mean time I have found a solution for it but I wonder why it happened, why the solution works and most importantly if someone else has run in to this sort of problem as well.
Below are some declarations and a few lines of code. The two lines of code are located in two separate functions: one to send an XML formatted message and one to receive the XML formatted response. The Write operation sends a 74 byte data block to the server. The Read operation receives a 105 byte data block sent back from the server to the client.
When I first tested the software I got the Exception mentioned in the title and it simply did not work. Using WireShark I could clearly see that both data blocks were sent back and forth as expected but on trying to read the supplied answer I always got the exception.
I finally managed to solve it by introducing a 20 millisecond delay before executing the Read operation whereas originally both operations executed very fast after each other, presumably well under a millisecond.
That makes me suspect that the problem is caused by calling the Read operation while the earlier Write operation is still ongoing in the background.
Both operations are asynchronous and I think it is quite possible that although the Write operation immediately returns there may very well be some ongoing background activity related to it so that when the Read operation is performed too fast it interferes and the Exception occurs.
Any thoughts, comments, remarks or bits of enlightenment would be very much appreciated.
Shared ClientSocket As System.Net.Sockets.TcpClient
Shared ClientStream As System.Net.Sockets.NetworkStream
ClientStream.Write(TxBuffer, 0, bytesToTx)
ClientStream.Read(RxBuffer, bytesRxed, RxBuffer.Length - bytesRxed)
|
|
|
|
|
You should never do it like that. Reading messages from a remote device will fail if that device has not yet sent any data. You should either test the connection to see if data is waiting to be read, or use an event handler that gets called when the message arrives.
|
|
|
|
|
Yes: excellent idea. If I check for DataAvailable first it will probably avoid the problem. I'll do some testing to see if it does.
|
|
|
|
|
Thanks Richard, it was an excellent suggestion indeed. This works quite well now.
Do
If ClientStream.DataAvailable Then
bytesRxed = bytesRxed + ClientStream.Read(RxBuffer, bytesRxed, RxBuffer.Length - bytesRxed)
If IsStringInBuffer(XmlEndString, RxBuffer) = True Then
Return bytesRxed
End If
End If
System.Threading.Thread.Sleep(RxTimeout)
rxLoopCount += 1
Loop While rxLoopCount < MaxRxLoops
When the loop executes the first time it skips the Read operation because there is not any available data yet. 10 milliseconds later ( RxTimeout ) there is and it all works.
|
|
|
|
|
Error in exporting SHEET2
code to populate datagrid:
'For x = 0 To ds.Tables.Count - 1
' Dim tp As New TabPage("TabPage" & x)
' TabControl1.TabPages.Add(tp)
' DataGridView(x) = New DataGridView
' 'Dim tabPagex As System.Windows.Forms.TabPage
' tp.Controls.Add(DataGridView(x))
' DataGridView(x).DataSource = ds.Tables(x)
' DataGridView(x).Dock = DockStyle.Fill
'Next
Export to excel：
Dim xlApp, xlBook, xlSheet As Object
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets()
xlApp.Visible = True
xlSheet = xlBook.Sheets("sheet1")
Dim Cols As Integer
For Cols = 1 To DataGridView1.Columns.Count
xlApp.Cells(1, Cols) = DataGridView1.Columns(Cols - 1).HeaderText
Next
Dim i As Integer
For i = 0 To DataGridView1.RowCount - 1
Dim j As Integer
For j = 0 To DataGridView1.ColumnCount - 1
If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then
xlApp.Cells(i = 2, j = 1) = ""
Else
xlApp.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString
End If
Next
Next
xlApp.Sheets.Add(xlApp.Sheets("sheet1"))
xlApp.Sheets("sheet2").Select()
For Cols = 1 To DataGridView2.Columns.Count
xlApp.Cells(1, Cols) = DataGridView2.Columns(Cols - 1).HeaderText
Next
For i = 0 To DataGridView2.RowCount - 1
Dim j As Integer
For j = 0 To DataGridView2.ColumnCount - 1
If Me.DataGridView2(j, i).Value Is System.DBNull.Value Then
xlApp.Cells(i = 2, j = 1) = ""
Else
xlApp.Cells(i + 2, j + 1) = DataGridView2(j, i).Value.ToString
End If
Next
Next
xlApp.Sheets.Add(xlApp.Sheets("sheet2"))
xlApp.Sheets("sheet3").Select()
For Cols = 1 To DataGridView3.Columns.Count
xlApp.Cells(1, Cols) = DataGridView3.Columns(Cols - 1).HeaderText
Next
For i = 0 To DataGridView3.RowCount - 1
Dim j As Integer
For j = 0 To DataGridView3.ColumnCount - 1
If Me.DataGridView3(j, i).Value Is System.DBNull.Value Then
xlApp.Cells(i = 2, j = 1) = ""
Else
xlApp.Cells(i + 2, j + 1) = DataGridView3(j, i).Value.ToString
End If
Next
Next
|
|
|
|
|
What error, and where does it occur? And why is the code that is supposed to populate the datagrid all commented out?
|
|
|
|
|
This code uses TabControl loop to add TabPage and DGV, and then exports them all to excel.
Terminate and report error when generating SHEET2,"system.NullReferenceException:The object reference is not set to the case of the object"
|
|
|
|
|
The only way to find out which variable is causing the exception is to step through the code with the debugger. When the exception occurs you can look back in the stack trace to see why.
I suspect this may be your problem.
xlApp.Sheets.Add(xlApp.Sheets("sheet2"))
xlApp.Sheets("sheet3").Select()
|
|
|
|
|
Dear all,
Hope you can help me out, i am new to VB scripting and i get the question from a customer to help them out with there VB script, They have the script below that is working fine on a windows 7 + Office 2013 computer but now they are migrated to a Windows 10 device with Office 365 and the script is stoped working.
It is a VB script in Excel/Access that can save the selected e-mail in outlook to a *.msg file:
<pre>Sub SlaMailOp()
Dim olApp As Outlook.Application, olNs As Namespace, Item As Object, eFldr As Object
Dim MailItem As Variant
Dim Onderwerp As String
On Error GoTo ErrorHandler
Set olApp = GetObject(, "Outlook.Application")
tmpOpenTextInvoer = "C:\temp\OpenTextInvoer"
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
End If
Set olNs = olApp.GetNamespace("MAPI")
Set eFldr = olNs.Application.ActiveExplorer.Selection
For Each MailItem In eFldr
If Dir(tmpOpenTextInvoer, vbDirectory) = "" Then
MkDir tmpOpenTextInvoer
End If
Onderwerp = PasGeldigheidBestandsnaamAan(Left(MailItem.Subject, 120)) & "_" & Format(Now, "YYMMDDHHMMSS")
MailItem.SaveAs tmpOpenTextInvoer & "\" & Onderwerp & ".msg"
Next
Set olApp = Nothing
Set olNs = Nothing
Set eFldr = Nothing
Exit Sub
ErrorHandler:
MsgBox "Er is een fout opgetreden bij het ophalen van de e-mail informatie! Is Outlook wel opgestart?" & vbCr & Err.Description, vbCritical
End Sub
Function PasGeldigheidBestandsnaamAan(Naam As String)
Naam = Replace(Naam, ":", "_")
Naam = Replace(Naam, "\", "_")
Naam = Replace(Naam, "/", "_")
Naam = Replace(Naam, "*", "_")
Naam = Replace(Naam, "?", "_")
Naam = Replace(Naam, "<", "_")
Naam = Replace(Naam, ">", "_")
Naam = Replace(Naam, "|", "_")
Naam = Replace(Naam, """", "_")
Naam = Replace(Naam, " ", "_")
Naam = Replace(Naam, "€", "EUR")
Naam = Replace(Naam, """, "")
Naam = Replace(Naam, "
Naam = Replace(Naam, "’", "")
Naam = Replace(Naam, "Ë", "E")
Naam = Replace(Naam, "ë", "e")
Naam = Replace(Naam, "Ö", "O")
Naam = Replace(Naam, "ö", "o")
Naam = Replace(Naam, "Ü", "U")
Naam = Replace(Naam, "ü", "u")
Naam = Replace(Naam, "@", "_AT_")
Naam = Replace(Naam, "#", "_")
Naam = Replace(Naam, "___", "_")
Naam = Replace(Naam, "__", "_")
PasGeldigheidBestandsnaamAan = Naam
End Function
Function Ampersand(Naam As String)
Ampersand = Replace(Naam, "&", "&")
End Function
|
|
|
|
|
And?
What does it do that it didn't used to, or not do that it did?
Where does it do that?
What have you tried to find out where and why it fails? What happened?
Are there any messages?
What have you tried?
Where are you stuck?
What help do you need?
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with. We can't even run your code under the same conditions you do as we have no access to your systems or the sheet it's a part of...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
sorry, users are getting the error:
application-defined or object-defined error
What is should do is save the selected e-mail in outlook to a folder (*.msg)
|
|
|
|
|
At the risk of repeating myself, what have you done to find out where and why?
What has the debugger shown you?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
If i run the debug it give me the error after the step:
MailItem.SaveAs tmpOpenTextInvoer & "\" & Onderwerp & ".msg"
This is the last step where it should save the email in the folder,
For the test is renamed the folder "OpenTextInvoer" and the script did create the folder after i run it for the first time so in my opinion the autorizations are fine.
Please i am sorry if it sounds really amateurish but i am really new with vb scripting and trying really hard to understand it..
If you have any arguments about things that i need to test please feel free to say me so
|
|
|
|
|
So us eteh debugger and put a breakpoint on that line. Then start looking at what the variables contain, and so what you pass to the SaveAs function. Then check that path and make sure it exists and can be written to by the user.
We can't do any of that for you - finding and fixing script bugs is just the same process as you use in application debugging!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
thanks! i understand,
if i breakpoint the SaveAs function and go/point over the "tmpOpenTextInvoer" i see the folder where it needs to be saved so and that is OK.. if i point over the part "Onderwerk" (Onderwerp is Dutch for Subject) i see the subject from the e-mail that i selecteed in outlook. so that part is working fine
i did remove the folder and run the script and the script created the folder like it should be and for the test i put "everyone" on the group with full access but same error is there...
|
|
|
|
|
So you need to look at all the data in MailItem, and check that you can write a file with that name to that folder manually (i.e. not using SaveAs, but using the same filename).
It may be worth setting up an "all users full access" folder and trying to write your file to that under a "nondescript name": "MySafeFolder\AAA.txt" perhaps.
One of the major changes between Win7 and Win10 is a tightening up of security over the file system, and it's possible that the user that the SaveAs is running under (which is conceivably not the same as your user ID) doesn't have the right permissions over the folder.
But this is speculation - this needs carefully thinking about on the actual system(s) it's failing on, and we have no access to that!
Good luck!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I have a Listbox with items. Some reports are limited to 1 item, and others are limited to 24 items.
Say a report limited to 24 items is selected, and the user chooses 200 items. The event seems to fires 17 times, incrementing the selectedItems count 1 a time. So my MessageBox fires 17 times.
I really only need to fire the MessageBox once. Is there a way to wait for the last event in queue to cycle and then I run my code in the event?
[Edit]
From reading even more, seems to be no cure and I should can the message box, and replace it with a huge label in red that states the message.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 24-Oct-19 14:00pm.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
