|
Hi,
I want to read a cell-value from an Excel-worksheet in VB.NET 2005 with the following function:
Private Function strCellValue(ByVal strFilname As String, ByVal strWorksheet As String, ByVal strCell As String) As String
Dim dtDataTable As New DataTable
Dim dbConnection As OleDb.OleDbConnection
Dim dbDataAdapter As OleDb.OleDbDataAdapter
dbConnection = New OleDb.OleDbConnection
dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & strFilename & ";" & "Extended Properties='Excel 8.0;HDR=NO'"
dbConnection.Open()
dbDataAdapter = New OleDb.OleDbDataAdapter("Select * From " & "[" & strWorksheet & "$]", dbConnection)
dbDataAdapter.Fill(dtDataTable)
strCellValue = dtDataTable.Rows(intRow(strCell)).Item(intColumn(strCell)).ToString
dbConnection.Close()
End Function
with
Private Function intRow(ByVal strCell As String) As Integer
intRow = CInt(System.Text.RegularExpressions.Regex.Replace(strCell, "[A-Z]", "")) - 1
End Function
and
Private Function intColumn(ByVal strCell As String) As Integer
Dim intI As Integer
Dim strColumn As String
strColumn = System.Text.RegularExpressions.Regex.Replace(strCell, "[0-9]", "")
intColumn = 0
For intI = 0 To strColumn.Length - 1
intColumn += (Asc(Mid(strColumn, strColumn.Length - intI, 1)) - 64) * CInt(Math.Pow(26, intI))
Next
intColumn -= 1
End Function
The last 2 functions converts only the "cell-address" in Excel (e.g. "C4") to the column and row for the data-table (e.g. C==2 and 4==3).
Problem: If a cell-value in Excel is formated as date and the value in this cell is not a date (e.g. the string "Hello"), the value is assumed correct, means strCellValue="Hello". If the value is a number and so, the cell-value is a valid date (e.g. "2009.03.21"), an empty string is assumed, means strCellValue=""
Any idea why? Thank you very much.
Christian
|
|
|
|
|
Hello,
I have solved the problem myself:
The ConnectionString must modified like this:
dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & strDateiname & ";" & "Extended Properties='Excel" & _
"8.0;HDR=NO;IMEX=1;'"
The "IMEX=1;" string tells the driver, that it should "convert" mixed formated cell-values (e.g. number, string, date,...) as text.
That's it.
Regards,
Christian
modified on Sunday, March 22, 2009 4:02 AM
|
|
|
|
|
how to make the form as MDI child in visual studio 2008?
|
|
|
|
|
You first need to have an MDI Parent form. set a forms IsMdiContainer property to True (Form1)
From the Form1, set the Child forms (Form2) parent and show it e.g.
<br />
Dim ChildForm as new Form2<br />
ChildForm.MdiParent = Me<br />
ChildForm.show()<br />
The easiest way to see this is start a new project and add MDI Parent Form, inspect the code and see how its done.
|
|
|
|
|
first of all set form1 property of ismdicontainer to true
then place menustrip
in that write menu items as form2
now double click on form2 menu item write there below code..:-
form2.MdiParent = Me
form2.Show()
|
|
|
|
|
hello every one
I want to insert the chekbox.text value like(YES/NO) on click of checkbox in listview..
for that my code is :-
If Me.chbox1.Checked = True Then
Me.chbox1.Text = "Yes"
Else
Me.chbox1.Text = "No"
End If
but this changes the value of checkbox.text every time...
so please help so that the value of text is Yes when i click checkbox and No when nothing is clicked....
|
|
|
|
|
In the listview itemchecked event put;
<br />
if e.item.checked then e.item.text = "Yes" else e.item.text = "No"<br />
|
|
|
|
|
hi
i have done the same thing
f Me.chbox1.Checked = True Then
Me.chbox1.Text = "Yes"
Else
Me.chbox1.Text = "No"
End If
but this is not working every time the text value of checkbox changes
when again i go in edit mode...
so please help....
|
|
|
|
|
The below code worked for me, are you specifying in your code that this should run when the check box is changed?
Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
If CheckBox1.Checked = True Then
CheckBox1.Text = "Yes"
Else
CheckBox1.Text = "No"
End If
End Sub
Hope that helps
|
|
|
|
|
He is talking about a check box in a listview.
I am a bit confused about exactly what his issue is.
Both the code you supplied for check boxes and the code i supplied for check boxes in listitems does as he has requested.
So need clarity on his issue.
|
|
|
|
|
hello
for clearence i m sending my code:-
i had set checkbox text property to "YES/NO"
Me.chbox1.Checked = True Then
Me.chbox1.Text = "Yes"
Else
Me.chbox1.Text = "No"
End If
Dim str As String = "insert into spoke_mast(area_numb,area_code,area_name,area_alias,area_select,area_tpemct) values ('" + Me.txt3.Text + "','" + Me.txt4.Text + "','" + Me.txt1.Text.ToUpper + "','" + Me.txt2.Text.ToUpper + "','" + Me.chbox1.Text + "','" + Me.cmbbox1.Text + "')"
obj.strexecutesql(str)
where obj.strexecutesql is a function in class
but this code doesnt work it changes the text from "YES/NO" to either "YES" or "NO"
whic should happen...
so please suggest me what to do??
|
|
|
|
|
i m sorry the last few lines were written wrong it should be
which should not happen...
so please suggest me what to do??
Smile Smile Smile
instead
whic should happen.....
so please suggest me what to do??
|
|
|
|
|
Ok,
The code is doing what you are asking to do, but i think i now know what you are trying to do.
You want to insert 'Yes' or 'No' into the SQL string based on whether the checkbox is checked or not.
If that is the case then;
<br />
Dim tempString as String = ""<br />
If Me.chbox1.Checked Then tempString = "Yes" else tempString = "No"<br />
<br />
Dim str As String = "insert into spoke_mast(area_numb,area_code,area_name,area_alias,area_select,area_tpemct) values ('" + Me.txt3.Text + "','" + Me.txt4.Text + "','" + Me.txt1.Text.ToUpper + "','" + Me.txt2.Text.ToUpper + "','" + tempString + "','" + Me.cmbbox1.Text + "')"<br />
<br />
obj.strexecutesql(str) <br />
|
|
|
|
|
hello
the solution you gave works well and good for add..
thanks for all..
but when i want to update the items in listview i use to double click on that particular record of listview...and update it
for that my code is:-
Me.chbox1.Text = Me.lst1.SelectedItems(0).SubItems(4).Text
this brings the value of listview to checkbox
now what happens is the value of checkbox.text changes from "Yes/No" to "Yes" or "No"
instead it should not change...and should remain "Yes/No" only
please suggest what to do....
|
|
|
|
|
Again, your question is not good and it is not clear what you are trying to do.
Of course the checkbox text is changing, that is what the code is telling it to do. i.e. it is being set to whatever subitem(4).text value is.
Can't help you without you starting to write better questions!
modified on Sunday, March 22, 2009 4:25 AM
|
|
|
|
|
hello
i m sending u the screen shoot of my project please have a look
help me..
|
|
|
|
|
hello
simply speaking when we click on any check box the text should remain unchanged...
but in my case when i double click on listview the text in chckbox changes
for eg:-
at form load event the text in check box is "Yes/No"
now lsitview contains some items like area name,area number etc along with area select which is having value of checkbox either "Yes" or "No"
which is well and good working...
but when i double click on listview the text changes
to either "Yes" or "No"
so what shall i do so that when i double click on listview the text in checkbox.text remains "Yes/No" like it was at form load event....
hope u got me...
please help....
|
|
|
|
|
If you are setting the checking box at design time to "Yes/No" and it should never change, then you should not have any code that has 'checkbox.text ='
Search your code/step through in debug, and find all instance of code that change the text value and remove.
I think you should have enough info from this thread to work that out..............
|
|
|
|
|
If you are still having problems, please answer this.
When you double-click the listview, do you want the checkbox to be checked, if you get a yes, and unchecked if you get a no?
If this is not what you want, then I am afraid that your question is too poorly worded, to be understood.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
how about updating DATABASE with checkbox ? can u post some example codes for it .. btw ur codes helps a lot .. thanks
|
|
|
|
|
How should I pass 2 parameters from a vb.net 2005 windows application to a crystal report that is written in version 11?
I am working on a Visual Basic.NET 2005 windows application. This application needs to call an existing crystal report written in version 11 and needs to pass two (2) parameters to the crystal report.
When I step though the code I think I am passing the first parameter, and I know that I am not passing the second parameter. I am getting an error message on the second when I step though the code.
My code is basically the following (except I do not know how to code the second parameter)
mports CrystalDecisions.Shared
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Windows.Forms
Imports System.Data.SqlClient
Public Class vbCls
Dim cryRpt As New ReportDocument
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cryRpt As New ReportDocument
Dim sql As String
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
'located crystal report from
Dim strReportPath As String = "C:\TstReport.rpt"
connetionString = "Data Source=server3;Initial
Catalog=ProgTest;User ID=xxx;Password=xxxx"
connection = New SqlConnection(connetionString)
sql = "select * from test_table"
Try
connection.Open()
adapter = New SqlDataAdapter(sql, connection)
adapter.Fill(ds)
connection.Close()
cryRpt.Load(strReportPath)
Dim crParameterFieldDefinitions As ParameterFieldDefinitions
Dim crParameterFieldDefinition As ParameterFieldDefinition
Dim crParameterValues As New ParameterValues
Dim crParameterDiscreteValue As New ParameterDiscreteValue
crParameterDiscreteValue.Value = 12345
crParameterFieldDefinitions = _
cryRpt.DataDefinition.ParameterFields
crParameterFieldDefinition = _
crParameterFieldDefinitions.Item("@Id")
crParameterValues = crParameterFieldDefinition.CurrentValues
crParameterValues.Clear()
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
CrystalReportViewer1.ReportSource = cryRpt
CrystalReportViewer1.Refresh()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class
The error message I am getting every time I try to code a second parameter is pass is the following:
crystaldecisions.crystalreports.engine.ParameterFieldCurrentValueException: Missing parameter values
-->System.Runtime.InteropServices.ComException (0x8004100E): Missing paramter values.
Thnaks!
|
|
|
|
|
Hi all
kindly help me to handle the followings.
1)I have Created a Group Name MasterGroup
2)Created an other Group Name ChildGroup Under MasterGroup
3) Created an other Group Name ChildGroup_A Under ChildGrup
Doing like above i have created a Group and under Group hierarchy say up to 6 step deep.
Now i have Created some account using group and under group.
like:-
Id Name UnderGroup
1 abc MasterGroup
2 Bzx ChildGroup
3 Kml ChildGroup
4 Axv ChildGroup_A
Up to this there is no problem, But i want to fetch Records group wise.
I want result using following sql statements
Select * from Tablename where UnderGroup = 'MasterGroup'
in response it will show me all records having under group mastergroup and the records having relation with mastergroup.
similarly when i use following sql statement
Select * from tablename where groupname='Childgroup'
in response it will show me only records having the groupname childgroup and records having relation upto Childgroup.
I am working in vb2005 and mssql. kindly send me code if posible.
a help would be highly appreciated.
Thanks
rm
rmshah
Developer
|
|
|
|
|
Firstly it is not very clear what it is that you want to do.
Nor is there any mention of what errors or results, if any, you are getting when you try the SQL statements you have shown in your post.
However, one thing springs to mind, given that you mention grouping. Have you looked at the GROUP BY clause of SQL?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I have an application that does maintenence tasks on the Access database.
Most of the functionality works fine but when I want to use a function that resides in one of the module of the database the query does not find the function.
All dates on the source tables of the database are held as YYYYMMDD. I have a function called fromsysdate('YYYYMMDD') which converts this into date format for users to view.
Here is an example of the code that I'm submitting using ExecuteNonQuery through a OLEDb connection.
"Select forename, surname, fromsysdate(dob) as dateofbirth
into birthdays from main "
The same functions also are included in my VB app. I wondered about exporting the access functions to a DLL and then adding the DLL back int ot he database - would it be recognised then? Or is this all just a syntax thing?
Cheers for any thoughts...
rob
|
|
|
|
|
Bad news 
Select forename, surname,dob as dateofbirth
into birthdays from main The OleDb connection doesn't have access to the Acces modules, and cannot call the function. I'd wait with the formatting until your showing the date to the user. You can use VB.NET to apply the same formatting.
I are troll
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
