Between operator in MS Access

Question

4.00/5 (1 vote)

See more:

Hi Everyone,

I am writing a code insert a date in access db and then to fetch data from MS access db range between fromdate(Datetimepicker) to todate(Datetimepicker). But it is showing blank output, can anyone help me.

Here is the code to insert date in access db
Private Sub Save()
Try
Dim maxnum As Integer
Dim dateofintro As String
maxnum = cDS.GetMaxCounters("SELECT Max (CandidateID) FROM CandidateMaster;")
maxnum = maxnum + 1
dateofintro = DateTimePicker1.Value
conn.Open()
sqlcmd = "insert into CandidateMaster (CandidateID, CandidateName,DateOfIntro,ContactNo1,ContactNo2,EmailID1,EmailID2,Address1, Address2, VisaType) values(" + maxnum.ToString() + "," + "'" + TextBox1.Text + "','" + dateofintro.Substring(0, 10) + "','" + Con1.ToString() + "','" + Con2.ToString() + "','" + Email1.ToString() + "','" + Email2.ToString() + "','" + Add1.ToString() + "','" + Add2.ToString() + "','" + ComboBox1.Text.ToString() + "')"
cmd.CommandText = sqlcmd
cmd.ExecuteNonQuery()
conn.Close()
ds.Clear()
conn.close()

This is the code for fetching data from access db

conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "CI.mdb;Jet OLEDB:Database Password= 123456")
conn.Open()
Dim fromdate, todate As String
Dim Cand_name, dat1, Dateofintro, visatype, Step_1, Step_2, Step_3, Step_4, Step_5, Step_6, Step_7, Step_8, Step_9, Step_10, Step_11, Step_12, Step_13, Step_14, Step_15 As String

fromdate = DateTimePicker1.Value.Date
todate = DateTimePicker2.Value.Date
sqlcmd = "select * from CandidateMaster where DateOfIntro between " & fromdate.Substring(0, 10) & " and " & todate.Substring(0, 10) & ""

cmd = New OleDbCommand(sqlcmd, conn)
dr = cmd.ExecuteReader

Thanks in advance

[edit]Code block added - OriginalGriff[/edit]

Posted 21-May-11 2:05am

Saumyavemula

Updated 22-May-11 20:58pm

v3

Add a Solution

Comments

Christian Graus 21-May-11 8:17am

I suggest posting the result of your string mashing code so we can see the output. I suspect your date may need to be in quotes, have you tried that ?

Saumyavemula 23-May-11 2:03am

Yes i have tried with date in quotes but its showing an error, if i dont add quotes then error is not coming but its showing blank output. Below is the complete code

Public Function csvfile()
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "CI.mdb;Jet OLEDB:Database Password= 123456")
conn.Open()
Dim fromdate, todate As String
Dim Cand_name, dat1, Dateofintro, visatype, Step_1, Step_2, Step_3, Step_4, Step_5, Step_6, Step_7, Step_8, Step_9, Step_10, Step_11, Step_12, Step_13, Step_14, Step_15 As String
fromdate = DateTimePicker1.Value.Date
todate = DateTimePicker2.Value.Date
sqlcmd = "select * from CandidateMaster where DateOfIntro between " & fromdate.Substring(0, 10) & " and " & todate.Substring(0, 10) & ""
cmd = New OleDbCommand(sqlcmd, conn)
dr = cmd.ExecuteReader

sqlprocess = "select * from Process where Process.CandidateID = CandidateMaster.CandidateID"
cmd = New OleDbCommand(sqlprocess, conn)
dtre = cmd.ExecuteReader
maxsrno = 1
createHfile = "CanadianImmgration_Report" & ".csv"
If File.Exists("C:\Users\Mil-Dev2\Desktop\" & createHfile) = False Then
Try
Dim SrHeader As StreamWriter = File.CreateText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
mstrHeaderdata = "Srno,Candidate's Name,Date of Intro,VisaType,Mailed details with AF,Received AF and CV from Candidate,Forwarded AF to Canada,Received Retainer from Canada,Sent Retainer back to Canada,Received Invoice from Canada,Made 1st Payment to Canada,Received Receipt of 1st Payment,Received Contract Paper from Employer,Sent contract Paper back to Employer duly signed,Received LMO from Canada,Get sponsorship and statenomination,Made 2nd payment to Canada,Received Receipt of 2nd Payment,Candidate applied to Ambesy for visa"
SrHeader.WriteLine(mstrHeaderdata)
SrHeader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
ElseIf File.Exists("C:\Users\Mil-Dev2\Desktop\" & createHfile) = True Then
Try
Dim SrHeader As StreamWriter = File.CreateText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
mstrHeaderdata = "Srno,Candidate's Name,Date of Intro,VisaType,Mailed details with AF,Received AF and CV from Candidate,Forwarded AF to Canada,Received Retainer from Canada,Sent Retainer back to Canada,Received Invoice from Canada,Made 1st Payment to Canada,Received Receipt of 1st Payment,Received Contract Paper from Employer,Sent contract Paper back to Employer duly signed,Received LMO from Canada,Get sponsorship and statenomination,Made 2nd payment to Canada,Received Receipt of 2nd Payment,Candidate applied to Ambesy for visa"
SrHeader.WriteLine(mstrHeaderdata)
SrHeader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If

Try
Dim sr As StreamWriter = File.AppendText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
Do While dr.Read = True And dtre.Read = True
Cand_name = dr("CandidateName").ToString
Dateofintro = dr("DateOfIntro").ToString
'Dateofintro = dat1.Substring(0, 10)
visatype = dr("VisaType").ToString
Step_1 = dtre("Step1").ToString
Step_2 = dtre("Step2").ToString
Step_3 = dtre("Step3").ToString
Step_4 = dtre("Step4").ToString
Step_5 = dtre("Step5").ToString
Step_6 = dtre("Step6").ToString
Step_7 = dtre("Step7").ToString
Step_8 = dtre("Step8").ToString
Step_9 = dtre("Step9").ToString
Step_10 = dtre("Step10").ToString
Step_11 = dtre("Step11").ToString

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-05-21T02:23:00

Solution 1

Instead of playing around with strings, convert your code to use the DateTime values directly:

sqlcmd = "select * from CandidateMaster where DateOfIntro between @START and @END" 
cmd = New OleDbCommand(sqlcmd, conn)
cmd.Parameters.AddWithValue("@START", DateTimePicker1.Value)
cmd.Parameters.AddWithValue("@END", DateTimePicker2.Value)
dr = cmd.ExecuteReader

It's cleaner, more readable, and it will probably solve your problem, all in one go!

Posted 21-May-11 2:23am

OriginalGriff

Comments

Saumyavemula 23-May-11 2:10am

Hi,

Even i tried this but its in vain, please find my completed code.

Public Function csvfile()
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "CI.mdb;Jet OLEDB:Database Password= 123456")
conn.Open()
Dim fromdate, todate As String
Dim Cand_name, dat1, Dateofintro, visatype, Step_1, Step_2, Step_3, Step_4, Step_5, Step_6, Step_7, Step_8, Step_9, Step_10, Step_11, Step_12, Step_13, Step_14, Step_15 As String
fromdate = DateTimePicker1.Value.Date
todate = DateTimePicker2.Value.Date
sqlcmd = "select * from CandidateMaster where DateOfIntro between " & fromdate.Substring(0, 10) & " and " & todate.Substring(0, 10) & ""
cmd = New OleDbCommand(sqlcmd, conn)
dr = cmd.ExecuteReader

sqlprocess = "select * from Process"
cmd = New OleDbCommand(sqlprocess, conn)
dtre = cmd.ExecuteReader
maxsrno = 1
createHfile = "CanadianImmgration_Report" & ".csv"
If File.Exists("C:\Users\Mil-Dev2\Desktop\" & createHfile) = False Then
Try
Dim SrHeader As StreamWriter = File.CreateText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
mstrHeaderdata = "Srno,Candidate's Name,Date of Intro,VisaType,Mailed details with AF,Received AF and CV from Candidate,Forwarded AF to Canada,Received Retainer from Canada,Sent Retainer back to Canada,Received Invoice from Canada,Made 1st Payment to Canada,Received Receipt of 1st Payment,Received Contract Paper from Employer,Sent contract Paper back to Employer duly signed,Received LMO from Canada,Get sponsorship and statenomination,Made 2nd payment to Canada,Received Receipt of 2nd Payment,Candidate applied to Ambesy for visa"
SrHeader.WriteLine(mstrHeaderdata)
SrHeader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
ElseIf File.Exists("C:\Users\Mil-Dev2\Desktop\" & createHfile) = True Then
Try
Dim SrHeader As StreamWriter = File.CreateText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
mstrHeaderdata = "Srno,Candidate's Name,Date of Intro,VisaType,Mailed details with AF,Received AF and CV from Candidate,Forwarded AF to Canada,Received Retainer from Canada,Sent Retainer back to Canada,Received Invoice from Canada,Made 1st Payment to Canada,Received Receipt of 1st Payment,Received Contract Paper from Employer,Sent contract Paper back to Employer duly signed,Received LMO from Canada,Get sponsorship and statenomination,Made 2nd payment to Canada,Received Receipt of 2nd Payment,Candidate applied to Ambesy for visa"
SrHeader.WriteLine(mstrHeaderdata)
SrHeader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If

Try
Dim sr As StreamWriter = File.AppendText("C:\Users\Mil-Dev2\Desktop\" & createHfile)
Do While dr.Read = True And dtre.Read = True
Cand_name = dr("CandidateName").ToString
Dateofintro = dr("DateOfIntro").ToString
visatype = dr("VisaType").ToString
Step_1 = dtre("Step1").ToString
Step_2 = dtre("Step2").ToString
Step_3 = dtre("Step3").ToString
Step_4 = dtre("Step4").ToString
Step_5 = dtre("Step5").ToString
Step_6 = dtre("Step6").ToString
Step_7 = dtre("Step7").ToString
Step_8 = dtre("Step8").ToString
Step_9 = dtre("Step9").ToString
Step_10 = dtre("Step10").ToString
Step_11 = dtre("Step11").ToString
Step_12 = dtre("Step12").ToString
Step_13 = dtre("Step13").ToString
Step_14 = dtre("Step14").ToString
Step_15 = dtre("Step15").ToString