Retrieving data from MS Access database using timestamps i.e. starting and ending date on a user form

Question

I had a question about retrieving data from MS Access database using timestamps i.e. starting and ending date on a user form. Kindly let me know if and what's the process to do that. I'm using Microsoft Visual Studio 2015. Website user interface will be in Asp.net with VB. Although I have tried passing parameters I am not understanding why it is not reflecting on the Grid View

ASPX.VB Code

Partial Class _Default

Inherits System.Web.UI.Page

Public dad As OleDbDataAdapter

Protected Sub Page_Load(sender As Object, e As EventArgs)

End Sub

Protected Sub TextBox2_TextChanged(sender As Object, e As EventArgs) Handles TextBox2.TextChanged

End Sub

Protected Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles TextBox3.TextChanged

End Sub
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

    Dim connectionString As [String] = "Provider=Microsoft.ACE.OLEDB.12.0;Data" + " Source=App_Data/VQT_GL Testing.mdb"
    Dim ds As New DataSet()
    Dim conn As New OleDbConnection(connectionString)
    conn.Open()


    Dim cmd As New OleDbCommand("SELECT AVG(POLQA_Score) AS MEAN FROM VQTPOLQA WHERE VQT_Timestamp BETWEEN ? AND ?", conn)
    cmd.Parameters.AddWithValue("@StartDate", (TextBox3.Text))
    cmd.Parameters.AddWithValue("@EndDate", (TextBox2.Text))

    GridView1.DataSource = ds

End Sub



End Class

ASPX CODE





    MOS FORM HOME
      
      
     
    
    
    
    
    
    




    
    

                                                                              MOS FORM HOME PAGE
        
            START DATE   
            

        
        
            END DATE

Jonathan Willcock · Accepted Answer

At first sight, your problem seems to be with your parameters to your OleDbCommand. You are using cmd.Parameters.AddWithValue. This is something I tend to avoid, as the datatype of the Parameter is inferred from the value passed. In this case you are passing text. This means that the parameter is treated as a string parameter not a DateTime. This is almost certainly going to fail! Instead you should set the type of the parameter yourself. To do this use something like:

Dim param as OleDbParameter
param = cmd.CreateParameter
param.OleDbType = OleDbType.Date
param.Value = DateTime.Parse(TextBox1.Text)
param.ParameterName = "@myDate"
cmd.Parameters.Add(param)

Note for Access OleDbType needs to be Date even though the field is DateTime.

It may be that I have a syntax error in the above - it is a very long time since I used VB, and I cannot remember when you need round brackets and when you don't, but it should be something like this (I could give it to you exactly in c# ). I am sure you can correct my VB.

Secondly you are not actually executing your OleDbCommand! Here there are different possiblilities. You can use an OleDbDataAdapter or an OleDbDataReader. Personally I prefer to use OleDbDataReader and I then build my own DataTable from the result to use as the DataSource, but you may find an Adapter easier. Please google for examples of how to do this! It should be something like:

Dim adapter as New OleDbDataAdapter(cmd)
adapter.Fill(ds)

Again my VB is not upto date!

Hope this helps

Retrieving data from MS Access database using timestamps i.e. starting and ending date on a user form

Answers (1)

Related Questions