[wanabavbcoder]

I have edited it due to some reading and assistance given by the sql forum. So now the SQL code is:

Alter proc allvalid
@Venue_Name char(30),@Start_Date nvarchar(8),@End_Date nvarchar(8),@Venue_Rent nvarchar(8) OUT
as 
select Venue_Details.Venue_id,Venue_Details.Venue_Name,Upcoming_event.Start_Date,Upcoming_event.End_Date,Venue_Details.Venue_Rent
from Upcoming_event 
inner join  Venue_Details 
on Venue_Details.Venue_Name=Upcoming_event.Venue_Name
where Venue_Details.Venue_Name = @Venue_Name and
Upcoming_event.Start_Date = @Start_Date and
Upcoming_event.End_Date = @End_Date and 
Venue_Details.Venue_Rent= @Venue_Rent

But for some reason, it does not display in the data reader. it skips that part completely and displays the venue is available even when its not.
The vb.net code is:

cperson = TextBox1.Text
        phone = Val(TextBox2.Text)
        company = TextBox3.Text
        address = TextBox4.Text
        noatt = Val(TextBox5.Text)
        costatt = Val(TextBox6.Text)
        cat = ComboBox1.Text
        dae1 = DateTimePicker1.Format = DateTimePickerFormat.Custom
        DateTimePicker1.CustomFormat = "dd,MM,yy"
        DateTimePicker1.Format = DateTimePickerFormat.Custom
        dae2 = DateTimePicker2.Format = DateTimePickerFormat.Custom
        DateTimePicker2.CustomFormat = "dd,MM,yy"
        DateTimePicker2.Format = DateTimePickerFormat.Custom
        Dim connetionString As String
        Dim connection As SqlConnection
        connetionString = "workstation id=RIKESH;packet size=4096;user id=sa;data source=RIKESH;persist security info=False;initial catalog=Merrymeetings"
        connection = New SqlConnection(connetionString)
        Dim sqlComm As New SqlCommand
        Dim reader As SqlDataReader
        sqlComm.Connection = connection
        connection.Open()
        sqlComm.CommandType = CommandType.StoredProcedure
        sqlComm.CommandText = "allvalid"
        sqlComm.Parameters.Add("@Venue_Name", SqlDbType.Char, 30).Value = ComboBox2.Text
        sqlComm.Parameters("@Venue_Name").Direction = ParameterDirection.Input
        sqlComm.Parameters.Add("@Start_Date", SqlDbType.NVarChar, 8).Value = DateTimePicker1.Text
        sqlComm.Parameters("@Start_Date").Direction = ParameterDirection.Input
        sqlComm.Parameters.Add("@End_Date", SqlDbType.NVarChar, 8).Value = DateTimePicker2.Text
        sqlComm.Parameters("@End_Date").Direction = ParameterDirection.Input
        sqlComm.Parameters.Add("@Venue_Rent", SqlDbType.NVarChar, 8)
        sqlComm.Parameters("@Venue_Rent").Direction = ParameterDirection.Output
        sqlComm.Parameters.Add("@Venue_id", SqlDbType.Int)
        sqlComm.Parameters("@Venue_id").Direction = ParameterDirection.Output


        Try
            reader = sqlComm.ExecuteReader()
            If reader.Read Then
                MessageBox.Show(reader.Item(0) & "Is booked  from:   " & reader.Item(1) & "   Till- the   " & reader.Item(2) & " Rent is =" & reader.Item(3), "WHOOPS", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Button1.Hide()
            ElseIf MessageBox.Show("Venue is available", "HOORAY!!!", MessageBoxButtons.OK, MessageBoxIcon.Information) Then
                Button1.Show()

            End If
        Catch sqlex As SqlException
            MessageBox.Show(sqlex.Message)
        End Try
        connection.Close()

I left out the label part because it is more convenient to just display it.

[modi123_1]

Do as it says - you have a mismatch of parameters for the procedures and for what you are providing. Example.. where's this parameter at on the incoming?

32	        sqlComm.Parameters.Add("@Venue_id", SqlDbType.Int)
33	        sqlComm.Parameters("@Venue_id").Direction = ParameterDirection.Output


2	@Venue_Name char(30),@Start_Date nvarchar(8),@End_Date nvarchar(8),@Venue_Rent nvarchar(8) OUT

[wanabavbcoder]

okay, that was my error. I removed it from the vb code. I thought that i needed it. BUT I'm now lost. I need to check if the Venue name, Start date, end date are located in my table. When it is found, then it should display a message saying that the venue is either booked or available.
If the venue is booked then it should display ,in a messagebox, the start date and end date also the Venue rent. if it is available then it should just say the venue is available and display the rent in one messagebox.
1) is it the sql part?
2) is the vb part?
because of the very little knowledge of both sql and vb i dont have a clue.
Please dont laugh but 90% of my coding is learned from tutorials on youtube.

[modi123_1]

I have no idea what you were trying to ask there with the number points.. run that by me again.

[wanabavbcoder]

wanabavbcoder, on 10 October 2012 - 08:34 AM, said:

LOL forget the points. My problem is below:


okay, that was my error. I removed it from the vb code. I thought that i needed it. BUT I'm now lost. I need to check if the Venue name, Start date, end date are located in my table. When it is found, then it should display a message saying that the venue is either booked or available.
If the venue is booked then it should display ,in a messagebox, the start date and end date also the Venue rent. if it is available then it should just say the venue is available and display the rent in one messagebox.

[modi123_1]

Okay.. and the question is what? I see a few veiled and passive demands, but I am at a loss on what you need specific help on.

[wanabavbcoder]

okay lets start with the sql coding. Im not sure if the stored proc is what im looking for it to do.

This code is going to select the Venue_Name,Start_Date,End_Date from the Upcoming_event table.
Then it needs to select Venue_Name,Venue_Rent from Venue_Details so that it can display the Venue_Name,Start_Date,End_Date,Venue_Rent

Does my code do this???

Create proc allvalid

@Venue_Name char(30),@Start_Date nvarchar(8),@End_Date nvarchar(8),@Venue_Rent nvarchar(8) OUT
select Venue_Details.Venue_id,Venue_Details.Venue_Name,Upcoming_event.Start_Date,Upcoming_event.End_Date,Venue_Details.Venue_Rent
from Upcoming_event
inner join  Venue_Details
on Venue_Details.Venue_Name=Upcoming_event.Venue_Name
where Venue_Details.Venue_Name = @Venue_Name and
Upcoming_event.Start_Date = @Start_Date and
Upcoming_event.End_Date = @End_Date and
Venue_Details.Venue_Rent= @Venue_Rent

I am matching the value of Venue_Name from the Venue_Details to the value of Venue_Name from the Upcoming_event

This post has been edited by wanabavbcoder: 10 October 2012 - 11:53 AM

[modi123_1]

Typically string comparisons are less trustworthy than say a number. How about just use the id column to match up the same sets of data?

07	on Venue_Details.Venue_Name=Upcoming_event.Venue_Name

Regarding the rest - what about your fringe cases? What happens if a venue is booked the 26, 27, and the 28, but a person needs it 25 and 26? The venue date over laps.. does that count? Instead of searching for exact dates why not try a range and the 'greater than/less than' logic?

09	Upcoming_event.Start_Date = @Start_Date and
10	Upcoming_event.End_Date = @End_Date and

The same goes with the rent.. so the person putting in puts in the max they want, but you only have it checking for an exact value. As in if the client will pay up to $801 your code only checks that.. wouldn't you want to find venues that are 801 or less in cost? This means if you have a venue open on the right days and is 300 bones it won't be a problem.

11	Venue_Details.Venue_Rent= @Venue_Rent