Question how to select records of today from access table

[Wasim Sayyed King]

how to select records of today from access table???????????????? for creating crystal report for printing bills of today

 

[Wasim Sayyed King]

hello please help me fast i want this query in my annual collage project

 

[Wasim Sayyed King]

ya done,, than x forum members for not helping me range: if any one having same problem i will help you contact me by posting reply

 

[jmcilhinney]

Maybe you should just post your solution, so people can see it when they arrive here rather than having to ask you for help and wait for you to reply and risk that it might not happen.

 

[Wasim Sayyed King]

to select today's record from access database,,,,here is the solution,,,i performed this query for selecting and filling today's records in data grid view,,,,and then i used data grid view for making crystal report,,,

Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\My Project\My Project\Stone Crusher\myproject.accdb")
con.Open()
Dim cmd As New OleDb.OleDbCommand("select * from Transaction_table where vehicle_number='" + frmprintoptionsforbills.Label1.Text + "' and Date_transaction=@date1", con)
cmd.Parameters.AddWithValue("@date1", SqlDbType.Date).Value = frmprintoptionsforbills.Label2.Text
Dim Adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
Dim ds As DataSet = New DataSet()
Try
Adapter.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
cmd.ExecuteNonQuery()
Catch ex As Exception
End Try
con.Close()
label1 and label2 are lables of different forms which stores the value for vehicle_number and todays date
and to using this data grid view in crystal report i used youtube

 

[jmcilhinney]

Hmmm... if I'd know that it was just a matter of populating a DataTable then I would have answered. I thought it was something specific to CR and I don't use CR so I didn't think I could be of help. There are some issues with your code so i will address them.

1. You are inserting two values into your SQL code and you are using string concatenation for one and a parameter for the other. You should be using parameters for both.
2. You should not be getting a date value from a Label. You must have had a DateTime in the first place to populate the Label so it's that DateTime that you should be using. I would do the same thing for the vehicle number, i.e. use the original source. Basically, I never use the Text of a Label as a source because the user can't edit that data so you must already have it in your code.
3. You've butchered the addition of the parameter. You call AddWithValue and pass a data type as the value and then you proceed to set the Value separately. That means that you're setting the Value twice, once with the wrong data, and you never set the data type except to the wrong type implicitly
4. Your DataSet is pointless. You're only using one DataTable so you should just be creating one DataTable.
5. You are executing your query twice. You're calling Fill to populate the DataTable so what's the ExecuteNonQuery call for? It's a SELECT statament so it IS a query.
6. There's no point explicitly opening and closing the connection to execute a single data adapter command because Fill and Update both implicitly open and close the connection.
7. You are apparently catching exceptions and completely ignoring them. I'm not sure whether you have removed the error handling code or you intend to add some at some point but you should almost never simply ignore exceptions.
8. Last but not least, you're not even doing what you said that you wanted to do, i.e. getting records with today's date.

Here's that code redone and improved:

Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLED B.12.0;Data Source=D:\My Project\My Project\Stone Crusher\myproject.accdb")
Dim adapter As New OleDbDataAdapter("SELECT * from Transaction_table WHERE vehicle_number = @vehicle_number AND Date_transaction = @Date_transaction", connection)

With adapter.SelectCommand.Parameters
    .AddWithValue("@vehicle_number", vehicleNumber) 'vehicleNumber is a String variable containing the vehicle number.
    .AddWithValue("@Date_transaction", Date.Today)
End With

Dim table As New DataTable

Try
    adapter.Fill(table)
    DataGridView1.DataSource = table
Catch ex As Exception
    'Error handling code goes here.
End Try

That assumes the the Date_transaction column contains just dates and no times. If it contains times as well then you'll need to specify a range, e.g.

Dim connection As New OleDbConnection("Provider=Microsoft.ACE.OLED B.12.0;Data Source=D:\My Project\My Project\Stone Crusher\myproject.accdb")
Dim adapter As New OleDbDataAdapter("SELECT * from Transaction_table WHERE vehicle_number = @vehicle_number AND Date_transaction >= @Date_transaction1 AND Date_transaction < @Date_transaction2", connection)

With adapter.SelectCommand.Parameters
    .AddWithValue("@vehicle_number", vehicleNumber) 'vehicleNumber is a String variable containing the vehicle number.
    .AddWithValue("@Date_transaction1", Date.Today)
    .AddWithValue("@Date_transaction2", Date.Today.AddDays(1))
End With

Dim table As New DataTable

Try
    adapter.Fill(table)
    DataGridView1.DataSource = table
Catch ex As Exception
    'Error handling code goes here.
End Try