|
Ok. NetWorkDays[^] is an excel function to work out the number of working days between two dates, taking into account (optional) holiday dates provided.
There is this article on CodeProject - Excel NetworkDays for C#[^] ... yes, it's C# but there should be enough information there to get you started. Come back if you get stuck
[Edit - an alternative, sorry it's C# again] Business Dates Calculation[^]
|
|
|
|
|
Private Sub ttlLate()
Dim datardrr As MySqlDataReader
Try
conn.Open()
strSql = "SELECT dates between '" & dtpstart.Text & "' and '" & dtpend.Text & "', SUM(late) as SumTotalLate from tbl_attendance where employeeNumber='" & textempNoSrch.Text & "'"
cmd = New MySqlCommand(strSql, conn)
datardrr = cmd.ExecuteReader
TextLateDeduct.Text = datardrr("SumTotalLate").ToString
datardrr.Close()
conn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
conn.Dispose()
End Try
End Sub
it gives me a 0 as output instead of 2.40. Late field is decimal in datatypes in mysql database.
|
|
|
|
|
I'm pretty sure you intend the "date-range" to be a WHERE-predicate, right? I don't know how exactly MySQL interpretes the statement that you have there currently; apparently it's no syntax error but it most certainly won't be interpreted as a WHERE-predicate.
Also you should use SQL-parameters (MySqlParameter in this case) instead of concantenating your SQL-statement. Concatenating values into the statement has several(!) disadvantages, the most prominent one being the risk of SQL-injection, potentially leading to inadvertant or purposeful destruction of your database (Google SQL-injection).
Also you should use using-statements for your MySql****-objects like the connection, command, datareader, etc., which will ensure those are properly disposed in any case.
Also I recommend to not keep any of those objects as class variables but only as local variables.
And since you only want one single value, you don't need a DataReader; instead you can execute your command with ExecuteScalar().
Dim startDate As DateTime
Dim endDate As DateTime
If Not DateTime.TryParse(dtpstart.Text, startDate) Then
End If
If Not DateTime.TryParse(dtpend.Text, endDate) Then
End If
Dim sql As String = "SELECT SUM(late) AS SumTotalLate FROM tbl_attendance WHERE employeeNumber=@empNo AND dates BETWEEN @startDate AND @endDate;"
Try
Using conn As New MySqlConnection(ConnectionString)
Using cmd As New MySqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@empNo", employeeNumber)
cmd.Parameters.AddWithValue("@startDate", startDate)
cmd.Parameters.AddWithValue("@endDate", endDate)
conn.Open()
TextLateDeduct.Text = cmd.ExecuteScalar().ToString()
End Using
End Using
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
(I'm no VB.NET 'guy', maybe there's a small syntax error in there somewhere.)
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
modified 27-Mar-16 11:34am.
|
|
|
|
|
Dim startDate As DateTime
Dim endDate As DateTime
If Not DateTime.TryParse(dtpstart.Text, startDate) Then
' error indication and return
End If
If Not DateTime.TryParse(dtpend.Text, endDate) Then
' error indication and return
End If
Dim sql As String = "SELECT dates BETWEEN @startDate and @endDate, SUM( late ) AS SumTotalLate FROM tbl_attendance WHERE employeeNumber = @empno"
Try
Using conn1 As New MySqlConnection("server=localhost;userid=root;password=;database=amwps") ' ConnectionString from somewhere central
Using cmd As New MySqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@empNo", TextEmpNo)
cmd.Parameters.AddWithValue("@startDate", startDate)
cmd.Parameters.AddWithValue("@endDate", endDate)
conn.Open()
Dim val As Integer = cmd.ExecuteScalar("SumTotalLate")
TextLateDeduct.Text = val.ToString()
End Using
End Using
Catch ex As MySqlException
MessageBox.Show(ex.Message)
End Try
sumtotallate can't display in textbox, it having an error "
No default member found for type 'DBNull'.
but when i've try it to my mysql localhost it gives accurate result. 2.40
|
|
|
|
|
Real Corks wrote: No default member found for type 'DBNull'.
On which line does this exception occur?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Dim val As Integer = cmd.ExecuteScalar("SumTotalLate")
|
|
|
|
|
Apparently the result of the query is null (DBNull) because there were no records matching the criteria. So your code attempted to assign DBNull to an Integer, which doesn't work. Try this:
Dim val As Object = cmd.ExecuteScalar("SumTotalLate")
If TypeOf val Is DBNull Then
TextLateDeduct.Text = "0"
Else
TextLateDeduct.Text = val.ToString()
End If
It will most certainly result in "0" being displayed, which, as you say, is not the result that you expect. But you also reversed the SQL command back to your first version, which, I think, is not correct. Did you try it with my modified SQL command?
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
yes, i already tried your sql query but it gives me same output 0. Ok thanks, ill try to revised the sql query again. thanks
|
|
|
|
|
i am using vb.net and mysql database and creating crystal report using xml file.
<pre lang="VB.NET">
Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
conn.ConnectionString = "server=localhost;userid=root;password=;database=amwps"
If ToolStripComboBox1.SelectedItem = "Employee Information" Then
Dim daset As New DataSet
strSql = "SELECT * FROM tbl_employeeinfo"
conn.Open()
dadapter.SelectCommand = New MySqlCommand(strSql, conn)
dadapter.Fill(daset)
daset.WriteXml(CurDir() & "\xmlEmpSummary.xml", XmlWriteMode.WriteSchema)
conn.Close()
ElseIf ToolStripComboBox1.SelectedItem = "Attendance" Then
Dim daset As New DataSet
strSql = "SELECT * FROM tbl_attendance where dates = '" & ToolStripTextBox1.Text & "'"
conn.Open()
dadapter.SelectCommand = New MySqlCommand(strSql, conn)
dadapter.Fill(daset)
daset.WriteXml(CurDir() & "\xmlAttSummary.xml", XmlWriteMode.WriteSchema)
conn.Close()
ElseIf ToolStripComboBox1.SelectedItem = "Payroll" Then
Dim daset As New DataSet
strSql = "SELECT * FROM tbl_payroll where empNumber = '" & ToolStripTextBox1.Text & "'"
conn.Open()
dadapter.SelectCommand = New MySqlCommand(strSql, conn)
dadapter.Fill(daset)
daset.WriteXml(CurDir() & "\xmlPayrollSummary.xml", XmlWriteMode.WriteSchema)
conn.Close()
Else
MsgBox("Choose Filter!", MsgBoxStyle.Information, "Summary Report")
End If
End Sub</pre>
|
|
|
|
|
Do you plan to explain what the error is ?
Asking questions is a skill[^]
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
nothing error, my problem is it cannot create another xml file
|
|
|
|
|
Why can't it create another xml file?
|
|
|
|
|
|
It was a question from me to you ... you said "I cannot make another xml file" and I wanted to know why.
|
|
|
|
|
daset.WriteXml(CurDir() & "\xmlEmpSummary.xml", XmlWriteMode.WriteSchema)
I do not think you can concatenate a string to the return value of a method.
modified 21-Mar-16 8:55am.
|
|
|
|
|
So long as that method returns a string, yes you can. It's just never a good idea to do it like this because you can't see the resulting value in the debugger and you can't reuse the value without rebuilding it again.
Plus, I think he should be using Path.Combine instead of string concatenation.
|
|
|
|
|
Thanks, never thought to try it for myself.
|
|
|
|
|
In a VB.NET 2010 application, I am trying to change the t-sql 2012 that is commented out for 'sql' to use the 'sql' that is not commented about below in the code. My goal is to use the original applications logic as much as possible.
I am trying to use a basic cte so that I can eventually list more columns in the original select statement and logic
after data is selected from the cte to list a sort column value. I know the code listed below does not work since the logic ends up in the try catch block. When I look at the 'sql' in the debugger, i so that there is no space in from of the FRom statement. The sql looks like
'STUDENTNAMEFROM'.
Thus could you tell me the following:
1. Could you show me how to modify the sql I listed below so that the CTE works in VB.NET 2010?
2. Could you tell me what I can do so the catch block will tell me what the exact error message is that is occuring?
#Region "Public Function SelectAttendanceLetters(ByVal schoolid As Integer, ByVal schoolyear As Integer) As DataTable"
Public Function SelectAttendanceLetters(ByVal schoolid As Integer, ByVal schoolyear As Integer, ByVal milestone As Integer, ByVal term As Integer) As DataTable
Dim dt As DataTable = New DataTable()
'Const sql As String = "SELECT alm.SCHOOLYEAR, RTRIM(als.PERMNUM) AS PERMNUM, alm.SCHOOLNUM, alm.STULINK, alm.MILESTONE_CODE " _
' & ", RTRIM(als.LASTNAME) + ', ' + RTRIM(als.FIRSTNAME) + CASE WHEN MIDDLENAME IS NULL THEN '' WHEN RTRIM(MIDDLENAME) = '' THEN '' ELSE ' ' + SUBSTRING(RTRIM(MIDDLENAME), 1, 1) END AS STUDENTNAME " _
' & ", als.GRADE , alm.MILESTONE_DATE, alm.ABSENCES, alm.TARDIES, als.HOMELNGCOR, alm.SEMESTER " _
' & "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK " _
' & "WHERE alm.SCHOOLNUM = @schoolnum " _
' & "AND alm.SCHOOLYEAR = @schoolyear " _
' & "AND alm.MILESTONE_CODE = @milestone " _
' & "AND alm.SEMESTER = @semester " _
' & "AND (alm.PRINTED <> 'Y' OR alm.PRINTED IS NULL) " _
' & "AND (alm.PRINTED <> 'N' OR alm.PRINTED IS NULL) " _
' & "ORDER BY als.HOMELNGCOR, alm.MILESTONE_DATE, als.LASTNAME, als.FIRSTNAME, MIDDLENAME; "
Const sql As String = ";WITH CTE_ASTU (SCHOOLYEAR,PERMNUM,SCHOOLNUM,STULINK,MILESTONE_CODE,STUDENTNAME,LASTNAME,FIRSTNAME,MIDDLENAME,GRADE,MILESTONE_DATE,ABSENCES,TARDIES,HOMELNGCOR,SEMESTER) AS ( " _
& "SELECT alm.SCHOOLYEAR as SCHOOLYEAR, RTRIM(als.PERMNUM) AS PERMNUM, alm.SCHOOLNUM as SCHOOLNUM, alm.STULINK as STULINK, alm.MILESTONE_CODE as MILESTONE_CODE " _
& ", RTRIM(als.LASTNAME) + ', ' + RTRIM(als.FIRSTNAME) + CASE WHEN MIDDLENAME IS NULL THEN '' WHEN RTRIM(MIDDLENAME) = '' THEN '' ELSE ' ' + SUBSTRING(RTRIM(MIDDLENAME), 1, 1) END AS STUDENTNAME " _
& ", als.LASTNAME as LASTNAME,als.FIRSTNAME as FIRSTNAME, CASE WHEN MIDDLENAME IS NULL THEN '' ELSE MIDDLENAME END AS MIDDLENAME " _
& ", als.GRADE as GRADE , alm.MILESTONE_DATE as MILESTONE_DATE, alm.ABSENCES as ABSENCES, alm.TARDIES as TARDIES, als.HOMELNGCOR as HOMELNGCOR, alm.SEMESTER as SEMESTER" _
& "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK " _
& "WHERE alm.SCHOOLNUM = @schoolnum " _
& "AND alm.SCHOOLYEAR = @schoolyear " _
& "AND alm.MILESTONE_CODE = @milestone " _
& "AND alm.SEMESTER = @semester " _
& "AND (alm.PRINTED <> 'Y' OR alm.PRINTED IS NULL) " _
& "AND (alm.PRINTED <> 'N' OR alm.PRINTED IS NULL) " _
& ") " _
& "SELECT * " _
& " FROM CTE_ASTU " _
& "ORDER BY HOMELNGCOR, MILESTONE_DATE, LASTNAME, FIRSTNAME, MIDDLENAME; "
Using con As SqlConnection = DB.OpenConnectionCampusOPS()
Using da As SqlDataAdapter = New SqlDataAdapter(sql, con)
Try
da.SelectCommand.Parameters.Add("@schoolnum", SqlDbType.Int).Value = schoolid
da.SelectCommand.Parameters.Add("@schoolyear", SqlDbType.Int).Value = schoolyear
da.SelectCommand.Parameters.Add("@milestone", SqlDbType.Int).Value = milestone
da.SelectCommand.Parameters.Add("@semester", SqlDbType.Int).Value = term
da.Fill(dt)
Catch ex As Exception
End Try
End Using
End Using
Return dt
End Function
#End Region
|
|
|
|
|
dcof wrote: alm.SEMESTER as SEMESTER" _
& "FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK " _
There is no space before this from!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
that answered the question
|
|
|
|
|
how do i design a simple currency converter which accept user input and also stored user values a database?
|
|
|
|
|
Create a winforms or WPF project
put the control on a form/window
collect the values and operations from the user
perform the calculations required by the user
make a connection to the database
store the results in the database.
Take a look at the directions how to get an answer to your question (hint 2nd message in list) and try with a question that can be answered sensibly.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How would you do with a sheet of paper and a pencil ?
What do you need to complete the task ?
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
Hi there
i want to make an application which does this:
a person has the application and a database on their computer. They select a user who has the same application on their computer and they send message "Hi" and that message is received on that users application.
I am not sure if it is possible to do such thing with VB and if it is, where to start??
Thanks in advance!
modified 16-Mar-16 8:59am.
|
|
|
|
|
Sounds like you want a chat application but I don't know of any calculator that uses the internet!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
