[2005] Querying A Table

[ewankirky]

Hi, i'm using the date picker to try and allow the user to select two dates and the let them run a table query to find all the records between the two dates.

I got the date picker outputing the strings date1 & date2 and these are getting displayed in text boxes.

1.) How do i get rid of the time that is thrown in with the date picker? only need the date.

2.) how can i create a query that allows me to have the String 'date1' included in it without defining any values.

e.g.
SELECT BookingID, [Date Of Booking], [Date of Arrival], [Date of Departure], CustomerID
FROM [Booking Table]
WHERE Date_Of_Booking >= date1 AND Date_Of_Booking <= date2

Thanks for the any help,

Ewan

[stimbo]

You can use:

TextBox1.Text = Me.DateTimePicker1.Value.Date.ToShortDateString

Not eactly sure what you mean on point 2. Where do you define Date1 & date2?

When I'm taking values from textboxes I use parameters:

' Set the SELECT statement for the Command object
                cmdUserSelect.CommandText = "SELECT myID, " _
                    & "firstName, secondName " _
                    & "FROM newTbales WHERE firstName = @newName"
 
                cmdUserSelect.Parameters.AddWithValue("@newName", Me.searchField.Text)

[ewankirky]

thanks for the help got it displaying the short date now, probabily a few more questions to come.

[stimbo]

I think you can use the BETWEEN keyword in SQL statements.

Which would roughly look like:

...WHERE Date_Of_Booking BETWEEN Date1 AND Date2

[ewankirky]

thanks i'll give it a try.


Sorry forgot to answer your question about point 2.
Just threw this "Dim date1 As String" at the start of the class and then read the value of the datetimepicker and made date1 = datetimepicker.value

could you tell me how i'd do a very simple text box search?

Say type a name into textbox1 and the click a search button and that would return all the records FirstName = search.

pretty new to VB and trying to create a CRM system for uni.

also is there any way to get back into a query after it has been created on the toolstrip or as a button?

[stimbo]

The SQL query would probably be something along the lines of:

mySQLstatement.CommandText = "SELECT first_Name FROM MyTable WHERE first_Name = '" & Me.name.Text & "'"

Or:

cmdUserSelect.CommandText = "SELECT first_Name FROM MyTable WHERE first_Name = @firstName"

'And then add the parameter to your command
cmdUserSelect.Parameters.AddWithValue("@firstName", Me.searchField.Text)

[ewankirky]

The SQL query would probably be something along the lines of:

SELECT first_Name FROM MyTable WHERE first_Name = '" & Me.name.Text & "'"

Or as shown above:

SELECT first_Name FROM MyTable WHERE first_Name = @firstName"
 
'And then add the parameter to your command
cmdUserSelect.Parameters.AddWithValue("@firstName", Me.searchField.Text)

what do i put in place of cmdUserSelect?

saying its not declared.

thanks alot,

really not great at this program at all!

[stimbo]

That's the name of MY SQL declaration command.

You will have your own I would imagine... might look something like this?

Dim mySQLcommand As New SqlClient.SqlCommand()

[ewankirky]

right got that bit working, but now it doesnt show the name i type in when i click search, just displays a blank table....

any chance you would have a look at it for me if your not too busy?

[stimbo]

Can't right now I'm afraid (actually working and leaving for meeting). Post it on here anyway and I'm sure someone will respond.

I know if sounds stupid but the name definitely exists that you are searching for...

[ewankirky]

Can't right now I'm afraid (actually working and leaving for meeting). Post it on here anyway and I'm sure someone will respond.

I know if sounds stupid but the name definitely exists that you are searching for...

Yeh tried a few that existed. no luck.

thanks for all your help today anyway.

[stimbo]

If it's any consolation I only started SQL a couple of weeks ago... it does get easier (a little)

Here's a link to one of the VB members. Check out his signature. It has the best SQL/ADO tutorials I've read. Really simple to follow. Just take it slow:
http://www.vbforums.com/member.php?u=30656

[ewankirky]

Hi, still cant get this working..... doesnt seem to be displaying anything in the tables when i type in the name of a person....

[ewankirky]

Anyone know why this wont work?

SELECT BookingID, [Date Of Booking], [Date of Arrival], [Date of Departure], CustomerID
FROM [Booking Table]
WHERE ([Date of Arrival] >= Me.DateTimePicker3.Value)


DateTimePicker
Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
date1 = DateTimePicker3.Value
TextBox3.Text = Me.DateTimePicker3.Value.Date.ToShortDateString
End Sub

EDIT: BUMP!

[ewankirky]

Anyone?

[jmcilhinney]

I'm not going to read this entire thread but there should be NO strings involved at all. Do NOT convert dates to strings unless you want to display them. In all other circumstances keep then as binary date objects:

Dim cmd As New SqlCommand("SELECT * FROM MyTable WHERE DateColumn BETWEEN @StartDate AND @EndDate", con)
 
cmd.Parameters.AddWithValue("@StartDate", startDatePicker.Value.Date)
cmd.Parameters.AddWithValue("@EndDate", endDatePicker.Value.Date)

[ewankirky]

I'm not going to read this entire thread but there should be NO strings involved at all. Do NOT convert dates to strings unless you want to display them. In all other circumstances keep then as binary date objects:

Dim cmd As New SqlCommand("SELECT * FROM MyTable WHERE DateColumn BETWEEN @StartDate AND @EndDate", con)
 
cmd.Parameters.AddWithValue("@StartDate", startDatePicker.Value.Date)
cmd.Parameters.AddWithValue("@EndDate", endDatePicker.Value.Date)

thanks this helps, what does the con bit mean. saying its not declared.

[jmcilhinney]

What is the second parameter to the SqlCommand constructor? It's an SqlConnection object. YOUR SqlConnection object.

[Evy124]

Hi

I have a data base, Ms Access, with forms and tables and macro... and all.

But I want to create a search button, using VB, to bring specific data from one of my tables to be viewable in a form I already created.
For example:
Example.mdb
FirstTable has:
ID, EMployeeName, EmployeeNumber, EmployeeAddress
The Form has to have a text box and the search button. It's purpose is to the User to enter the number of the employee and search it in the database. This will bring the Show-Employee-Information Form with the desire employee information.

I create the command button and selected the OnClick option...but that's it.
I'm kind of stuck there... I'm new at this and would really appreciate any advise.

Thanks