|
I must apologize for sending you down the wrong path.
When I wrote that, it did not even occur to me that you had the Office Primary Interop Assemblies (PIA) installed in the GAC. You would have gotten a reference not found or something like that if they were missing.
The error message is pretty clear about the circumstances; at some point your code must be trying to delete/hide the last visible worksheet. This is easy to reproduce by calling the Delete method on the Workbook.WorkSheets object.
You need to inspect your code and find what causes this condition to occur.
As to why does it work fine on your development machine, but not on another? This is hard to say without seeing your code, but perhaps you are deleting the default WorkSheets (Sheet1 thru Sheet3) as some type of cleanup in your code and no other visible worksheets were added prior to this action on the subject installation.
Edit: corrected typo's
modified 10-Jan-14 17:16pm.
|
|
|
|
|
Thank you and now I think I know what I have to do.
For a long time, starting a new workbook in Excel caused the creation of 3 blank worksheets. As you guessed, my code starts by deleting 2 of these blank sheets as part of a pre-cleanup, before it added the variable number of sheets the user needs.
Excel 2013 apparently starts you off with only 1 blank sheet. Thus, either when I try and delete a non-existent sheet, or when my program assumes 1 sheet exists when it doesn't, I get the error.
So I will programmatically count the number of active sheets before I do any clean up, and this should give me a program that behaves regardless of the Excel version.
Thank you for focusing me on the right thing. (Fingers crossed.)
|
|
|
|
|
Thank you TnTinMn. I can now report this problem is solved. Indeed, I had a loop to remove 2 blank worksheets; I rewrote the loop to count the number of worksheets (would be 3 for older versions of excel, 1 for newer), to subtract 1 from this count, and to loop from 1 to this count deleting worksheets. In practice, this loops from 1 to 2 for older excel (how I had it hard-coded) and 1 to 0 in new excel. Should have done that in the first place!
--Steve
|
|
|
|
|
good day everyone i need help to my project i'm newbie in programming.
i want to make an file downloader using visual basic 2012.
i have a code for file downloader but i need some button to add.
i only have download button1 for (download)to download file.
now i need to add another button2 for (cancel) to stop the download file.
here's the code
Imports System.Net
Public Class Form1
Private Sub downloadFile(ByVal srcPath As String, ByVal destPath As String)
Dim wClient As New System.Net.WebClient()
AddHandler wClient.DownloadProgressChanged, AddressOf downloadFile_ProgressChanged
wClient.DownloadFileAsync(New System.Uri(srcPath), destPath)
End Sub
Private Sub downloadFile_ProgressChanged(ByVal sender As Object, ByVal e As System.Net.DownloadProgressChangedEventArgs)
ProgressBar1.Maximum = e.TotalBytesToReceive
ProgressBar1.Value = e.BytesReceived
Label7.Text = e.ProgressPercentage.ToString + "%"
Application.DoEvents()
If e.ProgressPercentage = 100 Then
Label5.Text = "Download Complete :"
DownloadBtn1.Enabled = True
End If
end sub
Private Sub DownloadBtn1_Click(sender As Object, e As EventArgs) Handles DownloadBtn1.Click
If Pm1.SelectedItem = ("item") Then
downloadFile("http://", "Location")
ProgressBar1.Visible = True
Label5.Visible = True
Label7.Visible = True
DownloadBtn1.Enabled = False
End If
end Sub
end class
anybody can edit the code for me
thaks.
|
|
|
|
|
Have you seen the WebClient.CancelAsync() method??
In your case you would just drop a button on the form and in the Click event handler put:
wClient.CancelAsync()
Nobody is going to edit your code for you. Nobody is doing it for us, so why would we do it for anyone else??
|
|
|
|
|
Hi friends,
I have a question. In form, If we maximize the form, if all the controls in the form is to be centered, we give ANCHOR->NONE to all the controls. But, If I gave ANCHOR->NONE to any one of the controls, it should affect others also.. How can I acheive this??
|
|
|
|
|
Since no control knows about the existence of any other (exactly the way it should be!), you can't do that.
You'd have to write a method that sets/unsets the Anchor property of all your controls by iterating through all the affected controls and setting each control individually.
|
|
|
|
|
Im trying to get this if then statement to work, but I'm new to VB and programming in general and I'mnot sure what's wrong, or even if this is the best way to do what i want to do. I see there is a select case method, but I'm not sure the best way to do this. Thanks in advance. When strOwnerCode = "M", my strTitle is not getting populated correctly. I'm not sure where my flaw is(probably my whole statement).
strOwnerCode=sess0.screen.GetString(10, 78, 01)
If strOwnerCode = "C" then
sess0.screen.sendkeys("<PF2>")
strTitle=sess0.screen.GetString(10, 13, 01)’ check the code for officer title
if strTitle = "S" then
strTitle = "Secretary"
elseif strTitle = "P" then
strTitle = "President"
elseif strTitle = "T" then
strTitle = "Treasurer"
elseif strTitle = "V" then
strTitle = "Vice President"
end if
elseIf strOwnerCode = "D" then
sess0.screen.sendkeys("<PF2>")
strTitle=sess0.screen.GetString(10, 13, 01)’ check the code for officer title
if strTitle = "M" then
strTitle = "Member"
elseif strTitle = "G" then
strTitle = "Manager"
end if
elseif strOwnerCode = "M" then
sess0.screen.sendkeys("<PF2>")
strTitle=sess0.screen.GetString(10, 13, 01)’ check the code for officer title
if strTitle = "M" then
strTitle = "Owner Receiving Mail"
elseif strTitle = "O" then
strTitle = "Et Al"
end if
elseif strOwnerCode = "S" then
strTitle = "Owner"
end if
modified 7-Jan-14 18:21pm.
|
|
|
|
|
I'd suggest to remove the if-elseif-series and use a Select Case statement instead (see e.g. http://msdn.microsoft.com/en-us/library/cy37t14y.aspx[^]).
Furthermore, you should consider the possibility that none of your cases was found - in the if-elseif world, that means another else clause (without an if); with select-case, it is the Case Else clause.
|
|
|
|
|
Ok, thanks for the suggestion, I'll go ahead and do that.
|
|
|
|
|
Bear in mind that string comparisons are by default case sensitive so always convert both side of the comparison to upper or lowercase(ToUpper or ToLower).
If an "m" comes through your comparison will return a false with your current code.
Clickety for how I messed up on this[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I'm writing this for attachmate, and our interface only uses uppercase. But that is definetley a good suggestion to keep in mind.
|
|
|
|
|
Hi all,
My MySQL table structure is ...
(I am giving you from SQL Query)
DROP TABLE IF EXISTS `expenditure`;
CREATE TABLE IF NOT EXISTS `expenditure` (
`ExpId` int(11) NOT NULL AUTO_INCREMENT,
`ExpDate` date DEFAULT NULL,
`ExpPurpose` varchar(350) DEFAULT NULL,
`ExpRefNo` varchar(50) DEFAULT NULL,
`Expenditure` double(10,2) DEFAULT '0.00',
`ExpFlag` char(10) DEFAULT NULL,
PRIMARY KEY (`ExpId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
;
INSERT INTO `expenditure` (`ExpId`,`ExpDate`,`ExpPurpose`,`ExpRefNo`,`Expenditure`,`ExpFlag`) VALUES
(1,'2014-01-06','Purchase','ttyu76',15750,'P');
;
My Code is...
Private Sub Generate()
Dim DateFrom As Date = Convert.ToDateTime(DtpFrom.Value).ToString("yyyy-MM-dd")
Dim DateTo As Date = Convert.ToDateTime(DtpTo.Value).ToString("yyyy-MM-dd")
Try
OpenConnection()
Dim sb As New StringBuilder
sb.Append("SELECT ExpDate,ExpPurpose,Expenditure FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
Dim dbcommand As New MySqlCommand
Dim dbadapter As New MySqlDataAdapter
Dim stdata As New DataTable
dbcommand.Connection = conn
dbcommand.CommandText = sb.ToString
dbadapter.SelectCommand = dbcommand
dbadapter.Fill(stdata)
DgvExp.DataSource = stdata
Catch ex As Exception
MsgBox(ex.Message)
Finally
CloseConnection()
End Try
End Sub
After the execution of the code, my Datagridview is not displaying any Data.
What is wrong ?
|
|
|
|
|
Your query didn't return anything, so the datatable is empty and, therefore, your grid.
Do NOT user string concatenation to build your query, especially with dates. Google for, and use, "vb.net MySQL parameterized queries".
|
|
|
|
|
Try changing this:
Dim sb As New StringBuilder
sb.Append("SELECT ExpDate,ExpPurpose,Expenditure FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "'")
Dim dbcommand As New MySqlCommand
Dim dbadapter As New MySqlDataAdapter
Dim stdata As New DataTable
dbcommand.Connection = conn
dbcommand.CommandText = sb.ToString
dbadapter.SelectCommand = dbcommand
To this:
String sb = "SELECT ExpDate,ExpPurpose,Expenditure FROM expenditure WHERE ExpDate BETWEEN @StartDate AND @EndDate")
Dim dbcommand As New MySqlCommand
Dim dbadapter As New MySqlDataAdapter
Dim stdata As New DataTable
dbcommand.Connection = conn
dbcommand.CommandText = sb;
dbcommand.Parameters.AddWithValue("@StartDate", DateFrom)
dbcommand.Parameters.AddWithValue("@EndDate", DateTo)
dbadapter.SelectCommand = dbcommand
The problem is that the date strings are not properly formatted to return the right data in your SQL, since you are using the default ToString method instead of an actual format like yyyy-MM-dd. Using parameters will force the underlying data adapter to insert the correct strings for the values without you having to worry about formatting them. Additionally, this is preferred to avoid SQL injection attacks and cleaner code.
|
|
|
|
|
Hi Ron,
Thank you for your answer. I changed my code exactly how you said but no result. Should I Change this ?
Dim DateFrom As Date = Convert.ToDateTime(DtpFrom.Value).ToString("yyyy-MM-dd")
Dim DateTo As Date = Convert.ToDateTime(DtpTo.Value).ToString("yyyy-MM-dd")
If yes, then in which format ?
Actually I am trying to create an Income & Expenditure Statement. I have another table namely "income". All income data stored here. I have created a crystal report with two sub report inside. I got the result. But last two days it failed to retrieve any records. I searched and found it retrieve any record that is in previous year i.e. 2013 but failed to retrieve any record which date is after 31/12/2013. I don't know why it is doing so. But if I searched the date from 01/01/2013 to 31/12/2013, then it displays 2/1/2014 date result. I am totally confused.
What should I do ?
Please help me...
modified 6-Jan-14 14:54pm.
|
|
|
|
|
Did you debug and see if your select statement is actually returning data?
|
|
|
|
|
Hi Ron,
I think something is wrong. My Income and Expenditure table both have 1 record each but while executing the code, Crystal report shows 10 records in income subreport(December 2013 records) but no records in expenditure subreport while I am searching the records between 06/01/2014 to 07/01/2014.
Let me re-check my code and generated XML files for CrystalReport Datasource and then I can tell you.
Thank you for helping me, I will get back to you after checking the code.
Thanks again
Biplob
|
|
|
|
|
Hi Ron,
I have tried parameterized queries as stated but facing some problems. Sometimes it displays data and sometimes not (May be it is wrong in my code). However, I've changed a field in my MySQL Database. I changed the Date field from Date datatype to Char(10) in both Income and Expenditure Table. Below I am giving the structure(SQL) of both the tables with data.
DROP TABLE IF EXISTS `expenditure`;
;
;
CREATE TABLE `expenditure` (
`ExpId` int(11) NOT NULL AUTO_INCREMENT,
`ExpDate` char(10) DEFAULT NULL,
`ExpPurpose` varchar(350) DEFAULT NULL,
`ExpRefNo` varchar(50) DEFAULT NULL,
`Expenditure` double(10,2) DEFAULT '0.00',
`ExpFlag` char(10) DEFAULT NULL,
PRIMARY KEY (`ExpId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
;
LOCK TABLES `expenditure` WRITE;
;
INSERT INTO `expenditure` VALUES (1,'06-01-2014','Purchase','ttyu76',15750.00,'P');
;
UNLOCK TABLES;
DROP TABLE IF EXISTS `income`;
;
;
CREATE TABLE `income` (
`IncId` int(11) NOT NULL AUTO_INCREMENT,
`IncDate` char(10) DEFAULT NULL,
`IncPurpose` varchar(350) DEFAULT NULL,
`IncRefNo` varchar(50) DEFAULT NULL,
`IncIncome` double(10,2) DEFAULT '0.00',
`IncFlag` char(2) DEFAULT NULL,
PRIMARY KEY (`IncId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
;
LOCK TABLES `income` WRITE;
;
INSERT INTO `income` VALUES (1,'06-01-2014','Sale','GE-INV/13-14/0102',1199.00,'S'),(2,'25-12-2013','Sale','GE-INV/13-14/0103',2875.00,'S'),(3,'09-01-2014','Sale','GE-INV/13-14/0104',343.00,'S'),(4,'08-01-2014','Sale','GE-INV/13-14/0105',118.00,'S');
;
UNLOCK TABLES;
Now I am giving the VB.NET Code for report generation...
Private Sub GenerateReport()
Dim DateFrom As String = Convert.ToDateTime(DtpFrom.Value).ToString("dd-MM-yyyy")
Dim DateTo As String = Convert.ToDateTime(DtpTo.Value).ToString("dd-MM-yyyy")
Try
OpenConnection()
Try
Dim sb As New StringBuilder
sb.Append("SELECT * FROM income WHERE IncDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "' ORDER BY IncDate ASC")
Dim dbcommand As New MySqlCommand
Dim dbadapter As New MySqlDataAdapter
Dim stdata As New DataSet()
dbcommand.Connection = conn
dbcommand.CommandText = sb.ToString
dbadapter.SelectCommand = dbcommand
dbadapter.Fill(stdata)
stdata.WriteXml(Application.StartupPath & "\ReportXml\Income.xml", XmlWriteMode.WriteSchema)
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
Dim sb1 As New StringBuilder
sb1.Append("SELECT * FROM expenditure WHERE ExpDate BETWEEN '" & DateFrom & "' AND '" & DateTo & "' ORDER BY ExpDate ASC")
Dim dcommand As New MySqlCommand
Dim dadapter As New MySqlDataAdapter
Dim sdata As New DataSet()
dcommand.Connection = conn
dcommand.CommandText = sb1.ToString
dadapter.SelectCommand = dcommand
dadapter.Fill(sdata)
sdata.WriteXml(Application.StartupPath & "\ReportXml\Expenditure.xml", XmlWriteMode.WriteSchema)
Dim objRpt1 As New InEx
objRpt1.SetDataSource(sdata.Tables(0))
FormReport.RptViewer.ReportSource = objRpt1
FormReport.ShowDialog()
FormReport.RptViewer.RefreshReport()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Catch ex As Exception
MsgBox(ex.Message)
Finally
CloseConnection()
End Try
End Sub
As you can see the Income table has 4 data and Expenditure table has just 1 data.
Amazingly, while I am generating the report from 01/12/2013 to 10/01/2014 [^], the report shows only 3 data (January 2014) from income table and 1 data from Expenditure table (It has only one data). But if I generate the report from 01/12/2013 to 31/12/2014 [^], the report shows all the data from the tables.
I am Completely Confused and unable to find the solution. I debug and found my select statements are returning data that are showing in the report.
Please help me.
|
|
|
|
|
BETWEEN is a mathematical comparison, when used on strings it simply selects, alphabetically, if the row is between the two values. Because you changed your date column to a char column, its no longer a date and between doesn't mean between the two dates, it means between the two strings alphabetically.
You really need to have that be a date column to use the BETWEEN correctly.
|
|
|
|
|
Hi Ron,
Thank you for your reply. I understood the BETWEEN comparison. I would like to say that, if I make the datatype of the field "date", it never displays any data. I don't know why. Is there any fault in my code ? Another thing is, if I convert the string in to date then is it possible to calculate ?
|
|
|
|
|
Sorry, it is not possible I think because how can I retrieve the database datatype string in to datetime. So what should I do, make the field datatype "Date" or "DateTime" ?
|
|
|
|
|
Ok, now I changed the DataType in to Date and changed the dates in to "yyyy-MM-dd" format in the database and changed my code for MySQL Date format...
Dim DateFrom As String = Convert.ToDateTime(DtpFrom.Value).ToString("yyyy-MM-dd")
Dim DateTo As String = Convert.ToDateTime(DtpTo.Value).ToString("yyyy-MM-dd")
Now it retrieving the data correctly in between two dates but a new problem arises. The report date field not showing any date.
While viewing the datasource of crystal report (XML), the schema shows...
<xs:element name="IncDate" msdata:DataType="MySql.Data.Types.MySqlDateTime, MySql.Data, Version=6.6.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" type="xs:anyType" minOccurs="0" />
and the data shows (in XML)
<IncDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<TimezoneOffset>0</TimezoneOffset>
<Year>2013</Year>
<Month>12</Month>
<Day>25</Day>
<Hour>0</Hour>
<Minute>0</Minute>
<Second>0</Second>
<Millisecond>0</Millisecond>
</IncDate>
What should I do ?
Please note that in my ConnectionString the Allow Zero Datetime is set to "True". Is this the problem ?
modified 9-Jan-14 13:19pm.
|
|
|
|
|
Biplob Singha Shee wrote: If yes, then in which format ?
That's the entire point behind using a PARAMETERIZED QUERY. You don't have to worry about the format AT ALL!! It's automatically handled by the Parameter object.
|
|
|
|
|
Hi Dave,
Thank you for your answer. I am trying to solve this problem with parameterized queries however there is something wrong in my code I think. Let me check this first.
Thanks again,
Regards,
Biplob
|
|
|
|
|