|
Hi
Using VS2005 VB
I am having Problems when importing data from an Excel sheet using oledb
The sheets being opened as always use the following naming convention
P_W03-29M40MH-S16A-1235296-XFA-
The problem seems to be the dash at the end.
If I rename the sheet and replace the dash with an underscore, it works fine
I dont want to have to open the book first an rename the sheets every time.
The code below throws an error when cmd.Execute reader executes.
The error states:
System.Data.OleDb.OleDbException = {"The Microsoft Jet database engine could not find the object 'P_W03-29M40MH-S16A-1235296-XFA-$.A:A'.
Make sure the object exists and that you spell its name and the path name correctly."}
This is the code I am using:
<br />
<br />
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br />
"Data Source=C:\myFile.xls;" & _<br />
"Extended Properties=""Excel 8.0;HDR=NO"""<br />
<br />
Dim conn As New System.Data.OleDb.OleDbConnection(strConn)<br />
conn.Open()<br />
<br />
Dim strSheet As String = "P_W03-29M40MH-S16A-1235296-XFA-"<br />
Dim cmd As New System.Data.OleDb.OleDbCommand("Select * from [" & strSheet & "$A:A]", conn)<br />
Dim rdr As OleDbDataReader = cmd.ExecuteReader
|
|
|
|
|
Just off the top of my head: have you tried concatenating a ! in between the sheet name and the range?
"Select * from [" & strSheet & "!$A:A]"
or you could try one of the following alternative syntaxes:
"Select * from ['" & strSheet & "$A:A]"'"
"Select * from ['" & strSheet & "'$A:A]"
"Select * from ['" & strSheet & "'!$A:A]"'"
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thanks, tried them but no luck.
The '$' sign is eqivalent to the '!' usually used to indicate a sheet in an Excel Formula.
I tried the same query in MS Query from Excel, and it gave the similar syntax and it works with the '-' at the end.
The only difference was it the Filed name was as follows
SELECT * FROM mysheetName$.F1
Do you know how to use an escape character for '-'. Mabee I can get the sheet name, strip off the dash, and insert a dash with an escape character sequence?
|
|
|
|
|
The problem with Excel programming (in my experience) is that any code is rather sensitive to (MS Excel) version differences.
I don't know if there is any kind of special escape character you could use, but one thing you can try is the "add quotes till it works" method. Something like this:
"SELECT * FROM ['" & mysheetName- & "'$.F1]"
next "SELECT * FROM [" & "'" & " & mysheetName- & " & "'" & "$.F1]"
or "SELECT * FROM [''" & mysheetName- "''$.F1]"
next "SELECT * FROM ['''" & mysheetName- "'''$.F1]"
etc. (and you can try variations of the location of the quotes)
One other option that came to mind was to try and use the sheets index instead of its name. I found this article that may be helpful: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm[^]
and a third option that came to mind is to first programmatically change the sheet name before you run the query against it (a bit clumsy, but once it runs, who cares right?).
And in conjunction to this idea: the sheet (name) seems to also get created by some sort of automatic process. Do you have access to this process?
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi
I have tried various options with the quotes with no sucess.
Once I have selected the workbook from which I want to import the sheet, I load the sheets in the workbook into a combo. The user can then select which sheet they want to import. I do this as follows:
<br />
For Each sheet As Excel.Worksheet In xlPacklistBook.Sheets<br />
Me.cboPackListSheetSelect.Items.Add(sheet.Name)<br />
Next<br />
Regarding the second option, I did try using the sheet index, but that did not work. I suspect it is because in the following code, the command data type is a string:
<br />
Dim cmd As New System.Data.OleDb.OleDbCommand("Select * from ['" & strSheet & "$.A:A]'", conn)<br />
In Excel VBA if you use Sheet(1) as a ref to a sheet, the one is an int value, not a string as in Sheet("mySheet")
I'm trying th e third option at the moment, but having hassles - I'll let you know if I get it right..
|
|
|
|
|
hi...
i developed a win application in vb.net..in that i had some report files, images , & other files.. all these are kept in seperate folders like reports in reportfolder, images in Imagefolder.... i created set up for the application... the installed application is working properly in "All users", and in network tooo...
but when i install the application in domain its not printing the report.... the printer is shared from one of the domain Pc.
And also if i remove the reports folder from the installed directory.. still the report is previewing....
do any idea..
|
|
|
|
|
Greettings again;
I am working on POS software in visual basic 2005/08 and have run into a problem that I just can't seem to wrap my mind around. This POS is designed for Taverns and as thus I need to setup various happy hour specials. Right now I have a working POS setup and functioning in a very basic environment but I am not at all happy with how the happy hour routine works. Specifically how I deal with the days of the week that specials apply.
Right now for each happy hour special I have an individual variable that indicates if the special runs on that specific day. For example HHMon(x), HHTue(x), HHWed(x) etc as boolean variables. True means the special is on that day, false it is off. Some specials run on Monday and Thursday, others run one day a week, others run weekdays, so on and so on. I just feel it is such a waste and inefficient to use seven variables for each special.
So my question is, does anyone have an suggestions on how to better approach dealing with days of the week in this fashion? Could a single variable represent every possible combination of week days? Is there a built in function that would do this? I am very new to VB.NET and programming in general and I would appreciate any assistance.
Thank You,
Ryan
modified on Thursday, January 31, 2008 3:08:55 AM
|
|
|
|
|
Ryan - I hope that is an Irish Ryan and working in a pub - some people do have the luck.
This problem is not a VB or coding problem, it is a data structure issue. Put out of your mind the idea of having 1 variable to cater for all types, it aint gonna happen.
I would start with some?
Do you have multiple HHs in a day?
Do you have different types of HH?
Do the HHs change time and length from 1 day to the next?
Do prices vary for different HH types?
What about ad hoc HHs (public hols)?
Can management cancel an HH at short notice?
How many pints can I drink in 1 HH - oops
The answer to these (and there will probably be more I have not thought of) will determine the shape of your data structure. And if you think some of them are silly in Singapore they have all of the above and some products have different discount rates for AM/PM HHs.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello every one,
I am using DataGridView in 2-3 forms in my application
In one of my forms I have 3 girds and if I alter any thing in the design mode then the default cell header and default cell style changes. But the alternate row style remains what I had saved as.
Dose any one has a idea of how to get rid of this problem
Regards
Nshkarsh
|
|
|
|
|
What do you mean by "problem"? From what you've described, this is normal behavior, depending on what you changed and how your code is written. You haven't said anything about what you expect it to do, or what you changed in the Properties of the DataGridView.
|
|
|
|
|
Hi Dave
I think I had not described the issue properly…
I will try to define it again it again
In one of my forms I am suffering with the following issue
- Once I change the font and sizes of the rows in a datagridview save it then it works fine
- Once I change even the location of any other object, say a label, on that form all the properties changes (fonts and size) done in datagridview are set back to the default
Id there a way out?
|
|
|
|
|
I have an application called "MEDGIDS". In that the main EXE file accessing the folder called "images".
I want to lock the "images" folder for the external view(It shouldn't show the files inside the folder).
Assume the "images" folder is locked then how can i access the folder from my main EXE file.
I need to lock the "images" folder, and if i locked then how to access the "images" folder from main EXE.
Thanks & Regards
Kumaran
|
|
|
|
|
I would user a DirectoryInfo to change the hidden attribute.
I am not aware that you can "lock" a folder, there is only hidden and read only and of course access permiisions controlled by active directories.
Attribute changes allow your exe to get at the files and can be done in VB. AD changes are much more stringent.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you really want to secure your image files, you perhaps should consider storing them in a database like an SQL Server database.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
You cannot "lock" a folder beyond the security provided by NTFS. You also cannot change the security so just an application can get to the folder. This is because when you launch an application, it inherits the security token of the user that launched it. In other words, the application is impersonating the user and has the same security permissions that the user does. So, if you change the security so a person cannot see what's inside the folder, your application won't be able to see it either.
|
|
|
|
|
Hi,
I have this code. Which is the beginning of class. The last line
objSI = new TBRSendDocInfo
has an error when this code
executes objSI is nothing.
What would cause a object not be able to instantiate it???
The object TBRSendDocInfo definition is below this code.
<transaction(transactionoption.requiresnew)> Public Class TransTimesheetWeekly
Inherits ServicedComponent
Public objSI As TBRSendDocInfo
Private CapType As String
Private CapID As Integer
Private justTimesheet As Boolean
Private SickDefault As Date = "01/01/1980"
Private HolidayDefault As Date = "01/01/1981"
Private LWOPDefault As Date = "01/01/1982"
Private Const WorkedDefault As String = "01/01/1984"
Private Const NotWorkedDefault As String = "01/01/1985"
Public Function ProcessTimesheetWeekly(ByVal tswID As Integer) As Integer
objSD = clsServiceDefaults.Load("C:\Windows\TBRServices.xml", GetType(clsServiceDefaults))
Connstr = objSD.TBRConnCap
WebConnstr = objSD.TBRWebConnCap
gTestEmail = objSD.TestEmailCap
Dim objCI As New tblCapturedInvoices(mdsDatabaseV3.mdsDbType.dbSQL)
Dim objTW As New tblwebTimesheetWeekly(mdsDatabaseV3.mdsDbType.dbSQL)
Dim objDB As New mdsDatabaseV3.cMDSTableData(Connstr, mdsDatabaseV3.mdsDbType.dbSQL)
Dim objWDB As New mdsDatabaseV3.cMDSTableData(WebConnstr, mdsDatabaseV3.mdsDbType.dbSQL)
Dim objDR As SqlClient.SqlDataReader
Dim objPR As SqlClient.SqlDataReader
Dim SQL, s As String
Dim i, j, wk, newID, wksInv As Integer
Dim objR As DataRow
Dim submitted As Date
Try
objSI = New TBRSendDocInfo
etc
etc
Imports System.Xml.Serialization
Imports System.IO
Public Class PDFDocInfo
Public InputFile As String
Public DocType As String
Public OutputFile As String
End Class
Public Class TBRSendDocInfo
Public ID As Integer
Public FirstName As String
Public LastName As String
Public Email As String
Public RecipientName As String
Public Subject As String
Public Body As String
Public LesterUser As String
Public FromEmail As String
<xmlignore()> Public PDFDocs As New ArrayList
Public Sub AddPDFDocInfo(ByVal pdfinfo As PDFDocInfo)
PDFDocs.Add(pdfinfo)
End Sub
Public Function AddNewPDFDocInfo() As PDFDocInfo
Dim newPDFDocInfo As New PDFDocInfo
PDFDocs.Add(newPDFDocInfo)
Return newPDFDocInfo
End Function
Public Property SendPDFDocs() As PDFDocInfo()
Get
Dim numPDFDocs As Integer
If PDFDocs.Count = 0 Then
numPDFDocs = 0
Else
numPDFDocs = PDFDocs.Count - 1
End If
Dim PDFDocInfoArray(numPDFDocs) As PDFDocInfo
PDFDocs.CopyTo(PDFDocInfoArray)
Return PDFDocInfoArray
End Get
Set(ByVal Value As PDFDocInfo())
PDFDocs.Clear()
If Not Value Is Nothing Then
Dim PDFDocInfo As PDFDocInfo
For Each PDFDocInfo In Value
PDFDocs.Add(PDFDocInfo)
Next
End If
End Set
End Property
Public Function Save(ByVal filename As String)
Dim tempFilename As String
tempFilename = filename & ".tmp"
Dim tempFileInfo As New FileInfo(tempFilename)
If tempFileInfo.Exists = True Then tempFileInfo.Delete()
Dim stream As New FileStream(tempFilename, FileMode.Create)
Try
Save(stream)
stream.Close()
tempFileInfo.CopyTo(filename, True)
tempFileInfo.Delete()
Catch ex As Exception
MsgBox("Error on Save of file " & filename & ". " & vbCrLf & ex.Message)
End Try
End Function
Public Function Save(ByVal stream As Stream)
Dim serializer As New XmlSerializer(Me.GetType)
serializer.Serialize(stream, Me)
End Function
Public Shared Function Load(ByVal filename As String, ByVal newType As Type) As Object
Dim fileInfo As New FileInfo(filename)
If fileInfo.Exists = False Then
Return System.Activator.CreateInstance(newType)
End If
Dim stream As New FileStream(filename, FileMode.Open)
Dim newObject As Object = Load(stream, newType)
stream.Close()
Return newObject
End Function
Public Shared Function Load(ByVal stream As Stream, ByVal newType As Type) As Object
Dim serializer As New XmlSerializer(newType)
Dim newObject As Object = serializer.Deserialize(stream)
Return newObject
End Function
End Class
|
|
|
|
|
You should post only relavent code. No one is going to take the time to read all that. Post only relavent lines, and also include the full, detailed error message you are getting. Without both of those things being done, your chances of getting helped are slim.
I'm going to become rich when I create a device that allows me to punch people in the face over the internet.
"If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer
|
|
|
|
|
In vb 6.0 we can use adodc by using connectionstring and recordsource properties and we can use that datasource and datafield properties to bind the controls with adodc.
But in vb.net there is no property to bind controls with adodc control.In vb.net we can only use databinding.add method to bind the controls but it does not allow to bind control with the adodc control.
Any idea how I can use adodc in vb.net 2005?
Thanks in advance
Ejaz
|
|
|
|
|
I suggest you look into OLEDB as a datalayer option, I beleive the datacontrol idea has been deprecated in .net
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Holmes for the reply.
I never use oledb as data layer.Any artical or sample for this?
Ejaz
|
|
|
|
|
|
Adodc is not used anymore.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
haw im using control.invoke and why?
|
|
|
|
|
What is your question? It makes absolutely no sense.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
plz can someone try to help me how i can use DataGrid with dataset????
voila mon msn 
cheikh_arabe@hotmail.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
