|
Thanks, I got the answer.
|
|
|
|
|
I have the following fields in a database: NurseLastName, NurseFirstName, NurseTitle
I have an SQL statement that retrieves this information into a DataTable("ReportingNurse").
What I want to do is concatenate the fields from the database into a single combo box that will show the data like this:
"NurseFirstName NurseLastName, NurseTitle".
I tried to do it in the .DisplayMember of the control but that does not work, or at least I could not get it to work.
I was thinking that I might need to access the DataTable like this: dtReportingNurse.Columns[1] (for the second column in the DataTable) but this doesn't work either.
Any suggestions?
Thanks,
Dan
|
|
|
|
|
The easiest way I can think of is doing the concatenation in the SQL:
<br />
SELECT NurseFirstName + ' ' + NurseLastName + ', ' + NurseTitle AS NurseData<br />
FROM ...<br />
and then you can set the DisplayMember to NurseData.
|
|
|
|
|
Thanks. That did the trick.
Dan
|
|
|
|
|
I am trying to rid myself of the problem of different version being used for excel on my customers machines. I have several programs the have a feature that allows the data sets to be save into excel. Using the interop and early binding gives me a problem with the office version, on the users machine. So I know to fix this I should use late binding. I don't really understand how to do this. I understand the concept, but can't quite get the method of execution right. Here is an example of my code:
Private Sub createSpreadSheet()
Dim EXL As Object
Dim wsheet As New Worksheet
Dim da As New SqlDataAdapter
EXL = CreateObject("excel.application")
wsheet = EXL.Workbooks.Add.Worksheets.Add
wsheet = EXL.Workbooks.Item(1).Worksheets("Sheet1")
Dim str1 As String
Dim intCount As Integer
intCount = Label57.Text - 1
Dim checkHeader As String
Dim I As Integer
For I = 0 To 5
checkHeader = strHead(I)
Select Case checkHeader
Case "wo_num"
strHead(I) = "Work Order"
strCwidth(I) = 100
Case "status_description"
strHead(I) = "Status"
strCwidth(I) = 100
Case "priority_description"
strHead(I) = "Priorty"
strCwidth(I) = 100
Case "category_description"
strHead(I) = "Category"
strCwidth(I) = 100
Case "location_description"
strHead(I) = "Location"
strCwidth(I) = 130
Case "wo_user"
strHead(I) = "Requested By"
strCwidth(I) = 130
Case "wo_in_date"
strHead(I) = "Date Entered"
strCwidth(I) = 130
Case "wo_date_needed"
strHead(I) = "Date Needed"
strCwidth(I) = 130
Case "wo_fixed_date"
strHead(I) = "Date Repaired"
strCwidth(I) = 140
Case "wo_description"
strHead(I) = "Description"
strCwidth(I) = 560
Case "wo_detailed_location"
strHead(I) = "Detailed Location"
strCwidth(I) = 330
Case "wo_est_time"
strHead(I) = "Est. Time"
strCwidth(I) = 130
Case "wo_act_time"
strHead(I) = "Act. Time"
strCwidth(I) = 130
Case "wo_resolution_notes"
strHead(I) = "Notes"
strCwidth(I) = 560
Case "wo_internal_comments"
strHead(I) = "Internal Comments"
strCwidth(I) = 560
End Select
wsheet.Cells(2, I + 1) = strHead(I)
Dim Q As Integer
For Q = 0 To intCount
wsheet.Cells(Q + 3, I + 1) = strP(I, Q)
Next Q
Next I
With wsheet
.Cells(1, 1) = "Custom Report"
End With
wsheet.SaveAs("C:\FM\FM.XLS")
EXL.Workbooks(1).Close(SaveChanges:=False)
If you can help I would be truly greatful.
|
|
|
|
|
I don't do Office Interop, but I seem to remember something about using the lowest Primary Interop Assemblies, like Office 2000, and they'll work with the higher versions of Office, so long as you don't want to use any of the new functionality that those versions offer.
For example, write your application using the Office 2000 PIA's and it should work with Office 2000, XP, and 2003.
I could be wrong though.
|
|
|
|
|
I am trying to initialize a form that has 7 combo boxes and 1 list box. I want to bind the selection data to various tables in an access database. I currently have done two of the combo boxes but feel that there should be a way to do this a little bit cleaner. I am new to .NET and am open to any suggestions that would lean towards best practice ideas. I have inserted my code below:
Function sqlDataInitialize(ByVal connectionString as String)<br />
'Retreive data for dropdowns<br />
<br />
'Disease List<br />
Dim strSQLDiseaseList As String = "Select * From Disease"<br />
Dim daDisease As New OleDbDataAdapter(strSQLDiseaseList, connectionString)<br />
Dim dsDisease As New DataSet<br />
<br />
daDisease.Fill(dsDisease, "Disease")<br />
<br />
Dim dtDisease As New DataTable<br />
dtDisease.Columns.Add("DiseaseName", GetType(System.String))<br />
dtDisease.Columns.Add("DiseaseCode", GetType(System.String))<br />
<br />
Dim drDSRow As DataRow<br />
Dim drNewRow As DataRow<br />
<br />
For Each drDSRow In dsDisease.Tables("Disease").Rows()<br />
drNewRow = dtDisease.NewRow()<br />
drNewRow("DiseaseName") = drDSRow("DiseaseName")<br />
drNewRow("DiseaseCode") = drDSRow("DiseaseCode")<br />
dtDisease.Rows.Add(drNewRow)<br />
Next<br />
<br />
cboDiseaseName.DropDownStyle = ComboBoxStyle.DropDownList<br />
<br />
With cboDiseaseName<br />
.DataSource = dtDisease<br />
.DisplayMember = "DiseaseName"<br />
.ValueMember = "DiseaseCode"<br />
'.SelectedIndex = 0<br />
.SelectedIndex = cboDiseaseName.FindStringExact("")<br />
End With<br />
'End Disease List<br />
<br />
'Sex<br />
Dim strSQLSex As String = "Select * From Sex"<br />
Dim daSex As New OleDbDataAdapter(strSQLSex, connectionString)<br />
Dim dsSex As New DataSet<br />
<br />
daSex.Fill(dsSex, "Sex")<br />
<br />
Dim dtSex As New DataTable<br />
dtSex.Columns.Add("Sex", GetType(System.String))<br />
<br />
For Each drDSRow In dsSex.Tables("Sex").Rows()<br />
drNewRow = dtSex.NewRow()<br />
drNewRow("Sex") = drDSRow("Sex")<br />
dtSex.Rows.Add(drNewRow)<br />
Next<br />
<br />
cboSex.DropDownStyle = ComboBoxStyle.DropDownList<br />
<br />
With cboSex<br />
.DataSource = dtSex<br />
.DisplayMember = "Sex"<br />
.ValueMember = "Sex"<br />
'.SelectedIndex = 0<br />
.SelectedIndex = cboSex.FindStringExact("")<br />
End With<br />
'End Sex<br />
End Function<br />
Thanks,
Dan
|
|
|
|
|
HurricaneDan wrote: 'Disease List
Dim strSQLDiseaseList As String = "Select * From Disease"
Dim daDisease As New OleDbDataAdapter(strSQLDiseaseList, connectionString)
Dim dsDisease As New DataSet
daDisease.Fill(dsDisease, "Disease")
Dim dtDisease As New DataTable
dtDisease.Columns.Add("DiseaseName", GetType(System.String))
dtDisease.Columns.Add("DiseaseCode", GetType(System.String))
Dim drDSRow As DataRow
Dim drNewRow As DataRow
For Each drDSRow In dsDisease.Tables("Disease").Rows()
drNewRow = dtDisease.NewRow()
drNewRow("DiseaseName") = drDSRow("DiseaseName")
drNewRow("DiseaseCode") = drDSRow("DiseaseCode")
dtDisease.Rows.Add(drNewRow)
Next
cboDiseaseName.DropDownStyle = ComboBoxStyle.DropDownList
With cboDiseaseName
.DataSource = dtDisease
.DisplayMember = "DiseaseName"
.ValueMember = "DiseaseCode"
'.SelectedIndex = 0
.SelectedIndex = cboDiseaseName.FindStringExact("")
End With
'End Disease List
From the looks of this, you're getting all the columns from a table called Disease, then you're just picking out two columns from that table and building another table with them. WHY?? Why not just get the two columns you need in the first place and be done with it?
'Disease List
Dim strSQLDiseaseList As String = "Select DiseaseCode, DiseaseName From Disease"
Dim daDisease As New OleDbDataAdapter(strSQLDiseaseList, connectionString)
Dim dtDisease As New DataTable("Disease")
daDisease.Fill(dtDisease)
With cboDiseaseName
.DropDownStyle = ComboBoxStyle.DropDownList
.DataSource = dtDisease
.DisplayMember = "DiseaseName"
.ValueMember = "DiseaseCode"
.SelectedIndex = cboDiseaseName.FindStringExact("")
End With
'End Disease List
|
|
|
|
|
Dave,
The reason behind I was building another table is that I did not know any better.
I have gone in and cleaned up the code to look like this:
<br />
'Disease List<br />
Dim strSQLDiseaseList As String = "Select DieaseName, DiseaseCode, SurveillaneFormFlag, 24HrReportFlag, CausativeOrganismFlag, SerotypeFlag, SiteOfInfectionFlag From Disease"<br />
Dim daDisease As New OleDbDataAdapter(strSQLDiseaseList, connectionString)<br />
Dim dtDisease As New DataTable("Disease")<br />
<br />
daDisease.Fill(dtDisease)<br />
<br />
With cboDiseaseName<br />
.DropDownStyle = ComboBoxStyle.DropDownList<br />
.DataSource = dtDisease<br />
.DisplayMember = "DiseaseName"<br />
.ValueMember = "DiseaseCode"<br />
.SelectedIndex = cboDiseaseName.FindStringExact("")<br />
End With<br />
'End Disease List<br />
But I am getting the following error when I build the project:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
It occurs on the line: daDisease.Fill(dtDisease)
Also, let me see if I understand this. If I create a data adapter and a data table, I do not need a data set...if I fill the data table with data obtained throught he data adapter. Is that right? I was under the impression that I would have to have a data set to fill the data table, almost as a go between for the data adapter and the data table.
Thanks for the help so far,
Dan
|
|
|
|
|
HurricaneDan wrote: Also, let me see if I understand this. If I create a data adapter and a data table, I do not need a data set...if I fill the data table with data obtained throught he data adapter. Is that right? I was under the impression that I would have to have a data set to fill the data table, almost as a go between for the data adapter and the data table.
A DataSet is a collection of one or more DataTables and any DataRelation objects. If you're just filling a single table, then a DataTable is all you need.
HurricaneDan wrote: But I am getting the following error when I build the project:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
It occurs on the line: daDisease.Fill(dtDisease)
What does the Message, or InnerException property of this exception say? It looks like it's complaining about some problem with the SQL statement.
|
|
|
|
|
In fact it was the SQL statement. I was just coming to update my post. I had misspelled two words in the SQL statement.
I am running into a syntax error when I am running the SQL statement with this column, 24HRReportFlag. I have changed it in my SQL statement to [24HRReportFlag]. I guess it did not like the number at the beginning of the column name.
Dave, you have been a great help so far and I am grateful. What about the fact that I have to populate the 7 combo boxes? Do you have a better recommendation or would you also put them all into a single sub or function?
Thanks again,
Dan
|
|
|
|
|
Yep, it's a bad idea to have column names start with a non-letter character.
HurricaneDan wrote: Dave, you have been a great help so far and I am grateful. What about the fact that I have to populate the 7 combo boxes? Do you have a better recommendation or would you also put them all into a single sub or function?
If it were me writing this, I'd have a seperate sub to fill each combobox, fetching only the columns of data that each needs. If you need to refresh all the ComboBox's, you can create another sub that calls all of the ComboBox refresh subs.
|
|
|
|
|
Dave,
I went ahead and changed my database table to have the column name TwentyFourHourReportFlag so as to not have to use a work around.
I appreciate your time today,
Dan
|
|
|
|
|
I have written some subs where you pass in the dropdown, the dataset, the table name and the columns. Then you can re-use the sub for loading any dropdown.
Ben
|
|
|
|
|
Thanks for that idea and including what is needed to be passed.
Dan
|
|
|
|
|
I need to build a desktop vb.net application that requires database connectivity. I used to write applications that connects to sql servers installed on separate machines. In this application i need to make the whole application mobile (the application and the database).. How can I do that?
Can the database be included in a setup file with the application its self?
Akram..
|
|
|
|
|
You might want to look at using sql express. It is a local sql server engine that is free. You could add it to your install and everything would be local.
Hope that helps.
Ben
|
|
|
|
|
You can use either SQL Express or Access as the database. You do NOT need Access installed to use the database. Just the MDAC components need to be installed.
|
|
|
|
|
Hallo
I am dealing with a problem in my project. I am using Visual Basic 2005. I am able to create a .txt file in my PDA but i don't know how to save it to a network drive. The idea is to press a SAVE button in the form (PDA) and the file to be saved in a network drive (e.g. c:\output.txt). I know how to do it only from a network computer to another, using a mapped network drive. Is there something like <<dim out="" as="" streamwriter="New" streamwriter("\\192.168.0.10\c\output.txt")="">> or <<\\computer_name\c\output.txt>> ????
Any given code sample or link to the necessary reading ( e.g socket? ) would be much appreciated!
Thank you in advance for your time
|
|
|
|
|
k.giannis_1980 wrote: Is there something like <<dim out="" as="" streamwriter="New" streamwriter("\\192.168.0.10\c\output.txt")="">> or <<\\computer_name\c\output.txt>> ????
Yes. What you described are the UNC paths to the server, share name, and file system path to a file. Saving the file is no different than if the path was C:\something. The code is exactly the same, just a different way of describing the filepath.
\\servername\sharename\folder\folder\filename
or
\\ipAddress\sharename\folder\folder\filename
|
|
|
|
|
Thank you so much!
It worked perfectly.
Thank you again for your fast reply
|
|
|
|
|
I have an application that has worked happily for many years under VB2003.
Today I upgraded it to VB2005 with no upgrade issues or changes.
The following line now fails
dim datestr as string = "7thNovember1993"
dim datedate as date
datedate = System.DateTime.Parse(datestr)
VB2005 throws
The string was not recognized as a valid DateTime. There is a unknown word starting at index 1.
TOPSie
|
|
|
|
|
You now have to use DateTime.ParseExact[^] and supply the format string that describes the format in which it should expect the date. Read the link I supplied, near the bottom, for an in-depth description of how ParseExact works and creating custom format strings to tell the parser what to expect.
|
|
|
|
|
Thanks for this - what a giant leap - backwards.
If I understand this then 7 November 1993 has a format string of "d MMMM YYYY"
But all my dates (and the data has LOTS of them) are in the format
7th November 1993. I can find nothing in your link that shows me a format to cope with "th" (or "st" or "rd"). I presume I am going to have to strip these out of the input string myself - being careful with AuguST etc.
As I said a great leap forward - one line of code turns in to 10 or more!!!
STOP PRESS
The original error was in DateTime.Parse coping with "7th November 1993"
Following your link I tried ParseExact with a format string of "d MMMM YYYY"
after stripping the "th" out. But ParseExact returned an error.
However going back to just Parse with no "th" in the date string now seems to work.
Thanks again
TOPSie
-- modified at 13:25 Friday 29th June, 2007
TOPSie
|
|
|
|
|
TotalTops1972 wrote: Thanks for this - what a giant leap - backwards.
Not really. This method is more flexible than what it used to be.
TotalTops1972 wrote: But all my dates (and the data has LOTS of them) are in the format
7th November 1993.
This is a really bad format. Sure it's human readable, but it makes it a PITA to use in automated data processing. Forget searching the database between date ranges. This is a string of characters that looks like a date. It's not actually a date value.
|
|
|
|