You must Sign In to post a response.
  • Category: .NET

    Filter data from sql server by 2 DateTimePicker in vb.net2008

    I have a project to take data from database by month. I do this program in vb.net which I have 2 datetimepicker so that I can display only data equals or same as the datetimpicker 1 & datetimpicker2. The coding as below:
    Dim con As New SqlConnection
    Dim dat As String = DateTimePicker1.Value.ToShortDateString
    Dim dat2 As String = DateTimePicker2.Value.ToShortDateString
    Dim conStr As String = "Server = ICT-RONOER\RONOER;" & "initial Catalog = CMC;" & " Trusted_Connection=Yes"
    con.ConnectionString = conStr
    'Check if Connection is open-----RON----
    If con.State = ConnectionState.Closed Then
    con.Open()
    Dim cmd As New SqlCommand("SELECT * FROM CMC_FaultInfoTemp where Tarikh BETWEEN @dt AND @ds", con)
    cmd.Connection = con
    cmd.Parameters.AddWithValue("@dt", dat)
    cmd.Parameters.AddWithValue("@ds", dat2)
    Dim myReader As SqlDataReader = cmd.ExecuteReader()
    Try
    'get data from TblPunca Table and display in txt----Ron----
    Using streamWriterObject As New StreamWriter("C:\Users\NTEP0049\Desktop\cmc\cmc.txt")
    Dim i As Integer = 1 ' initial number of data row***added by ron***
    streamWriterObject.WriteLine("Bil" & "|" & "No Aduan" & "|" & "Tarikh" & " | " & "Nama") 'Header
    While (myReader.HasRows)
    myReader.Read()
    streamWriterObject.WriteLine(i & "|" & myReader("NoAduan").ToString() & "|" & myReader("Tarikh").ToString() & " | " & myReader("Nama").ToString())
    i = i + 1 ' indicate number of data row***added by ron***
    End While
    myReader.Close()
    streamWriterObject.Close()
    End Using
    Catch ex As Exception
    If myReader.HasRows = True Then
    MessageBox.Show("Successful Read and Display CMC_FaultInfoTemp Table")
    Else
    MessageBox.Show("Error in reading Data")
    End If
    con.Close()
    End Try
    End If

    This coding is return no error but also return no values. Hope anyone can help me to trace problem.
  • #764083
    1. Are you getting records from the data base

    2. Are you selecting the proper dates

    3. Is the data is there in the table. Check using normal sql query using sql server management studio

    4. If you are getting the records but not able to write in the txt. Try to use File.Create for creating the text file.

    By Nathan
    Direction is important than speed

  • #764088
    Hi,

    Rather than asking our help, better to debug the program and trace out the solution.

    We are not in your situation , to identify what difficulties you are facing. So, first debug the program and check the command line what it returns and execute the same query in SQL server management studio and check the result.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #764092
    Thank for reply Nathan,
    Actually my problem here is cannot filter the data using WHERE and BETWEEN Clause by datetimepicker
    This is the prob it cannot give me the value when i put the condition in the sql clause
    "SELECT * FROM CMC_FaultInfoTemp where Tarikh BETWEEN @dt AND @ds"
    If i change the coding to
    "SELECT * FROM CMC_FaultInfoTemp" without any condition I can write the data without problem into txt
    The prob that it cannot write any data when I put condition into my sql CLause.
    WHEN I try in sql management studio I still can get the answer by this code:
    "SELECT * FROM CMC_FaultInfoTemp where Tarikh BETWEEN '2015-10-06' AND '2015-10-07'"
    only when i change the static date to the variable I assign to my datetimepicker it will write no data.

  • #764094
    Solved already get from other forum thanks.
    With this solution:
    Dim dat As String = DateTimePicker1.Value.ToShortDateString
    Dim dat2 As String = DateTimePicker2.Value.ToShortDateString

    Dim cmd As New SqlCommand("SELECT * FROM CMC_FaultInfoTemp where Tarikh BETWEEN @dt AND @ds", con)
    cmd.Connection = con
    cmd.Parameters.AddWithValue("@dt", dat)
    cmd.Parameters.AddWithValue("@ds", dat2)

    Changed into
    Dim dat = DateTimePicker1.Value.ToString("yyyy-MM-dd")
    Dim dat2 = DateTimePicker2.Value.ToString("yyyy-MM-dd")

    Dim sql = "SELECT * FROM CMC_FaultInfoTemp WHERE [Tarikh] BETWEEN @dt AND @ds"
    Dim cmd = New SqlCommand(sql, con)
    cmd.Parameters.AddWithValue("@dt", dat)
    cmd.Parameters.AddWithValue("@ds", dat2)

    The datetimepicker format default with dd/MM/yyyy and the my db is yyyy-MM-dd so have to convert format eventhou I already change the custom format in the datetimepicker properties.


Sign In to post your comments