SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly
SQL Help needed, Desperatly SQL Help needed, Desperatly
SQL Help needed, Desperatly
Go Back  Xtreme Visual Basic Talk > > > SQL Help needed, Desperatly


Reply
 
Thread Tools Display Modes
  #1  
Old 02-06-2013, 01:48 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default SQL Help needed, Desperatly


Hi there

I need some help with 2 SQL statements that don't seem to be working for me

im really stuck on this one, and REALLY need to get this sorted ASAP, im not the most advance VB user, so it could be something really stupid, if it is, im sorry lol *Face Palm*

Ok so, Statement 1

essentially, there are 2 date pickers on one form. The user fills them both out, then when they click search, it exports them into 2 other datepickers on a second form, and runs the search sub.

Code:
Sub search()
        Dim SearchCommand As OleDbCommand = Nothing
        Dim SearchAdapter As New OleDbDataAdapter
        Dim SearchTable As New DataTable
        Dim recordcount As Integer

        Searchbydate.searchbydatein.Format = DateTimePickerFormat.Short
        Searchbydate.searchbydateout.Format = DateTimePickerFormat.Short

        Try
            'establish command object and data adapter
            SearchCommand = New OleDbCommand("SELECT * FROM Booking  WHERE DateIn BETWEEN 'Searchbydate.searchbydatein.Value ' AND  ' Searchbydate.searchbydateout.Value ' OR Dateout BETWEEN 'Searchbydate.searchbydatein.Value' AND  ' Searchbydate.searchbydateout.Value' ", searchcon)
            SearchAdapter.SelectCommand = SearchCommand
            SearchAdapter.Fill(SearchTable)
            'bind grid view to data table
            Searchbydate.searchgrd.DataSource = SearchTable
            recordcount = SearchTable.Rows.Count.ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        SearchCommand.Dispose()
        SearchAdapter.Dispose()
        SearchTable.Dispose()


        If recordcount < 1 Then
            MsgBox("No Results " & recordcount)


        Else
            MsgBox("RESULT " & recordcount)
        End If

    End Sub
What should happen, is it should show any results where by EITHER the DateIn in the access database falls between the 2 dates defined in the datepicker, OR the Dateout falls between the two dates, essentially showing if the room for the hotel is available during the designated dates.

The error I get is "data type mismatch" .... Any suggestions?




Second issue.

Same Situation, but I added the room number aswell,

Code:
Dim ResultsCommand As OleDbCommand = Nothing
        Dim ResultsAdapter As New OleDbDataAdapter
        Dim ResultsTable As New DataTable
        Dim recordcount As Integer
        Dim textresult As String

        Try
            'establish command object and data adapter
            ResultsCommand = New OleDbCommand("SELECT * FROM Booking  WHERE RoomNumber = '" & RoomNumberTextBox.Text & "' AND Datein >= InDateField AND Datein <= OutDateField OR Dateout >= InDateField AND Dateout <= OutDateField ", verifybooking)
            ResultsAdapter.SelectCommand = ResultsCommand
            ResultsAdapter.Fill(ResultsTable)
            'bind grid view to data table
            bookinggrd.DataSource = ResultsTable
            recordcount = ResultsTable.Rows.Count.ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        ResultsCommand.Dispose()
        ResultsAdapter.Dispose()
        ResultsTable.Dispose()


        If recordcount < 1 Then
            txtrecords.BackColor = Color.Green
            textresult = "Avalible"
            txtrecords.Text = textresult
            confirmdates.Enabled = True


        Else
            txtrecords.Text = "All Ready Booked"
            txtrecords.BackColor = Color.Red
            confirmdates.Enabled = False


        End If
Error I get here, is "No value given for one or more of the required parameters"

PLEASE HELP ME, im on a really short deadline here and just cant work out whats wrong

Thanks
Luke
Reply With Quote
  #2  
Old 02-06-2013, 02:41 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

First one...
Code:
SearchCommand = New OleDbCommand("SELECT * FROM Booking  WHERE DateIn BETWEEN 'Searchbydate.searchbydatein.Value ' AND  ' Searchbydate.searchbydateout.Value ' OR Dateout BETWEEN 'Searchbydate.searchbydatein.Value' AND  ' Searchbydate.searchbydateout.Value' ", searchcon)
I never know how to best explain these ones.

VB will not look inside double quoted strings and replace variable/control values named in the string with the values.

If you looked at what SQL statement you were trying to execute it would look like..
Quote:
SELECT * FROM Booking
WHERE DateIn BETWEEN 'Searchbydate.searchbydatein.Value ' AND ' Searchbydate.searchbydateout.Value ' OR Dateout BETWEEN 'Searchbydate.searchbydatein.Value' AND ' Searchbydate.searchbydateout.Value'
You cannot be too surprised that the database does not know what 'Searchbydate.searchbydatein.value' is or that a date can't be compared to it.

The database thinks you're trying to compare a date field with the string value "Searchbydate.searchbydatein.value' NOT the value that was in that control.

You have to concatenate variables/control values into strings yourself. Like...
Code:
"Select * FROM table Where StringField='" & myStringVariable & "' AND IntegerField=" & myIntegervariable.ToString
Note that the variables are outside of the quoted strings.

Your second problem is the same kind of thing.

But lets also draw attention to the fact you are using dates here. Dates are a problem when having to take formats into account (US and UK formats are different). Therefore just chucking a Date value into a string might cause a problem. You can always convert it into Chinese (yyyy/mm/dd) format because there's never an issue of format if the year is the first field.

OR you can use parameters in your query as I think I have mentioned before.
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 02-06-2013, 04:10 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

So essentially. I should convert the datepicker format into custom. then make it "YYYY/MM/DD"

Then I need to say that variable1 = datepicker.value

then use the variable1 inplace of the datepicker... and assign it as a string ?
Reply With Quote
  #4  
Old 02-06-2013, 04:24 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Not really, you can have the format in the date control however you want. But the .Value you get out of the control will be a Date type.

A Date type is not a String. In order to put the Date into a String it has to be converted to a string.

VB.NET with Option Strict Off will do a lot of the converting for you, so you can just concatenate a Date type into a String and VB goes "that's a Date in a String, I need to convert that to a String". Most of the time that's fine, but as mentioned dates can be funny because their formats can be different so it's best to format it yourself.

So, where you build your query string it should look like...
Code:
cmd.CommandText = "SELECT * FROM myTable WHERE DateField = '" & Format(dateControl.Value, "yyyy-mm-dd") & "'"
Even there you are asking the database to compare a date field with a string and the database has to go "that's a string trying to compare a date, I must try and convert that String to a Date".

You can use parameters in your query to avoid all of this Date to String, String to Date stuff.
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 02-06-2013, 04:35 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I'll also add that if you are comparing between dates you often want to include times so that you check between the start of one day and the end of the other day.
Code:
"WHERE DateField BETWEEN '" & format(firstDate, "yyyy-mm-dd") & " 00:00:00" & "' AND '" & format(secondDate, "yyyy-mm-dd") & " 23:59:59" & "'"
__________________
There are no computers in heaven!
Reply With Quote
  #6  
Old 02-06-2013, 04:47 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

Im slightly lost here to be honest

so are you saying this should be ok? I don't need time to work, if anything I want to avoid time

Code:
("SELECT * FROM Booking  WHERE DateIn OR Dateout BETWEEN '" & Format(Searchbydate.searchbydatein, "dd-mm-yyyy") AND '" & Format(Searchbydate.searchbydateout, "dd-mm-yyyy") , searchcon)
Note sure if I can use OR between the first two fields? as I say im really stuck, I could move it onto the other form so that it doesn't have to refer to searchbydate.searchbydatein if that helps
Reply With Quote
  #7  
Old 02-06-2013, 04:57 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I don't think you can use the Or there.

You're also missing some inverted commas after the parameters. And some double quotes too.

I'm trying to explain as best I can, I'm sorry if I'm confusing you. I'd rather you try and understand what's going on than just give you the answer. What we are dealing with here is just building strings. Nothing SQL as such.

This is how I think the first query you posted should really look in your code. I've tried to make it more readable but am worried I've made it harder to understand.
Code:
"SELECT * " & _
"FROM Booking " & _
"WHERE DateIn BETWEEN '" & Format(FoamSearchbydate.searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & Format(Searchbydate.searchbydateout.Value, "yyyy-mm-dd") & "' " & _
"OR Dateout BETWEEN '" & format(Searchbydate.searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & format(Searchbydate.searchbydateout.Value, "yyyy-mm-dd" & "'"
Using simpler examples to try and explain the concepts seemed to be the best way to go.

I hope I've got all the quotes correct there. Difficult to tell. By heck, parameters would be a LOT simpler.
__________________
There are no computers in heaven!
Reply With Quote
  #8  
Old 02-06-2013, 05:05 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

I really don't understand parameters im afraid

Ill give that a go, one sec lol.

I really do appreciate your help, thank you, lets see how this one goes.
Reply With Quote
  #9  
Old 02-06-2013, 05:12 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by brown View Post
I really don't understand parameters im afraid
But they aren't difficult and you shouldn't be scared to learn how to use them.

Consider that query from above in code...
Code:
SearchCommand = New OleDBCommand("SELECT * " & _
"FROM Booking " & _
"WHERE DateIn BETWEEN '" & Format(Searchbydate.searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & Format(Searchbydate.searchbydateout.Value, "yyyy-mm-dd") & "' " & _
"OR Dateout BETWEEN '" & format(Searchbydate.searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & format(Searchbydate.searchbydateout.Value, "yyyy-mm-dd" & "'")
Ugly and hard to know if all the quotes and double quotes are correct. Can be difficult to debug the query, in fact it's often necessary to print it out to the screen to see if you've got it right.

Using parameters instead...
Code:
SearchCommand = New OleDBCommand("SELECT * FROM Booking WHERE DateIn BETWEEN @dateIn AND @dateOut Or DateOut BETWEEN @dateIn AND @dateOut")
' Instead of having to convert the dates to strings they are just named parameters in the query. All you have to do then is add those parameters to the Command
SearchCommand.Parameters.Add("@dateIn", OleDb.OleDbType.Date).Value = SearchByDate.SearchByDateIn.Value
SearchCommand.Parameters.Add("@dateOut", OleDb.OleDbType.Date).Value = SearchByDate.SearchByDateOut.Value
Mmmmmmmmm, so much tidier and easy to see what's going on.
__________________
There are no computers in heaven!
Reply With Quote
  #10  
Old 02-06-2013, 05:16 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

how does that stand with the date in the datebase as that takes the format of a short date.... "dd,mm,yyyy"
Reply With Quote
  #11  
Old 02-06-2013, 05:20 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

ok so lets try parameters as that's not working with the strings.

So my code would look something like.....

Code:
SearchCommand.Parameters.Add("@dateIn", OleDb.OleDbType.Date).Value = SearchByDate.SearchByDateIn.Value
SearchCommand.Parameters.Add("@dateOut", OleDb.OleDbType.Date).Value = SearchByDate.SearchByDateOut.Value



"SELECT * FROM Booking WHERE DateIn BETWEEN '" @datein") & "' AND '" & @dateout") & "'  OR Dateout BETWEEN '" @datein") & "' AND '" @dateout'", searchcon)
Would that work?
Reply With Quote
  #12  
Old 02-06-2013, 05:23 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Quote:
Originally Posted by brown View Post
how does that stand with the date in the datebase as that takes the format of a short date.... "dd,mm,yyyy"
Dates don't have formats. Ultimately they are just a very big number.

The operating system has regional settings that tell it how to display dates.

Programs outputting dates for humans to read should use those regional settings. For example the results of...
Code:
Dim d as date
messagebox.show(d.ToLongDateString)
... will depend on the Regional settings of the PC. The person will see the date as they have set their computer to show it. You shouldn't really use Format() for dates unless it's to get into a particular format you need.

For those reasons it's best to try and keep Dates as Dates inside the program.
__________________
There are no computers in heaven!
Reply With Quote
  #13  
Old 02-06-2013, 05:54 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

ok so in which case if I do

Code:
  SearchCommand.Parameters.Add("@dateIn", OleDb.OleDbType.Date).Value = searchbydatein.Value
        SearchCommand.Parameters.Add("@dateOut", OleDb.OleDbType.Date).Value = searchbydateout.Value

        Try
            SearchCommand = New OleDbCommand("SELECT * FROM Booking WHERE DateIn BETWEEN '@datein' AND   '@dateout'  OR Dateout BETWEEN  '@datein' AND  '@dateout', searchcon)
Reply With Quote
  #14  
Old 02-06-2013, 05:58 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

The parameters don't need the inverted commas around them in the SQL query.

The database would complain that it can't convert the string "@dateIn" into a date and hopefully you understand why.
__________________
There are no computers in heaven!
Reply With Quote
  #15  
Old 02-06-2013, 06:03 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

when I click the search, it causes an error.

NullReferanceException was unhandled
Object reference not set to an instance of an object.

That is on the line that makes the parameter for the date in
Reply With Quote
  #16  
Old 02-06-2013, 06:06 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

In the code you posted above, you are trying to add the parameters to SearchCommand before you have created SearchCommand as a New OleDbCommand.

So it is moaning that SearchCommand is the object not set to an instance.

Move the adding of parameters after the creating the object. Like in my example.
__________________
There are no computers in heaven!
Reply With Quote
  #17  
Old 02-06-2013, 06:16 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

Ok didn't think about that, I assumed the parameters would be needing to set up first.

I changed that and now I get a different error

Error in processing SQL
Fill: Selectcommand.connection property has not been initialized

This is my code

Code:
Sub Search ()
 Dim SearchCommand As OleDbCommand = Nothing
        Dim SearchAdapter As New OleDbDataAdapter
        Dim SearchTable As New DataTable
        Dim recordcount As Integer


        Try
            SearchCommand = New OleDbCommand("SELECT * FROM Booking WHERE DateIn BETWEEN @datein AND  @dateout  OR Dateout BETWEEN  @datein AND  @dateout", searchcon)
            SearchCommand.Parameters.Add("@dateIn", OleDb.OleDbType.Date).Value = searchbydatein.Value
            SearchCommand.Parameters.Add("@dateOut", OleDb.OleDbType.Date).Value = searchbydateout.Value

            'establish command object and data adapter
            ' SearchCommand = New OleDbCommand("SELECT * FROM Booking WHERE DateIn BETWEEN '" & Format(searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & Format(searchbydateout.Value, "yyyy-mm-dd") & "'  OR Dateout BETWEEN '" & format(searchbydatein.Value, "yyyy-mm-dd") & "' AND '" & format(searchbydateout.Value, "yyyy-mm-dd" & "'", searchcon)
            'SearchCommand = New OleDbCommand("SELECT * FROM Booking  WHERE DateIn OR Dateout BETWEEN '" & Format(searchbydatein, "dd-mm-yyyy") And "'" & Format(searchbydateout, "dd-mm-yyyy"), searchcon)
            SearchAdapter.SelectCommand = SearchCommand
            SearchAdapter.Fill(SearchTable)
            'bind grid view to data table
            searchgrd.DataSource = SearchTable
            recordcount = SearchTable.Rows.Count.ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error in Processing SQL", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        SearchCommand.Dispose()
        SearchAdapter.Dispose()
        SearchTable.Dispose()


        If recordcount < 1 Then
            MsgBox("No Results" & recordcount)


        Else
            MsgBox("RESULT" & recordcount)
        End If

    End Sub
Reply With Quote
  #18  
Old 02-06-2013, 06:28 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

That's moaning about SearchCon that you're passing to SearchCommand in the construct.

You don't show any of the setup of SearchCon in that code so it's difficult to determine the problem.
__________________
There are no computers in heaven!
Reply With Quote
  #19  
Old 02-06-2013, 06:30 AM
brown brown is offline
Freshman
 
Join Date: Jan 2013
Posts: 39
Default

I just have

Dim searchcon As OleDbConnection

in the public class then

Dim SearchCommand As OleDbCommand = Nothing

in the search sub
Reply With Quote
  #20  
Old 02-06-2013, 06:33 AM
DrPunk's Avatar
DrPunkSQL Help needed, Desperatly DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Connection objects require Connection Strings to tell the connection where it should be connecting to, and OleDb connections also need to know what type of connection it is.

www.connectionstrings.com is useful for finding out what your connection string should be.
__________________
There are no computers in heaven!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly SQL Help needed, Desperatly
SQL Help needed, Desperatly
SQL Help needed, Desperatly
 
SQL Help needed, Desperatly
SQL Help needed, Desperatly
 
-->