[wanabavbcoder]

I have posted this code before. It was giving me other errors, but now that all that is sorted. I want to perform a second action to query my database with a variable used in the first query"@Venue_Name" and display the column"Venue_Rent" from table"Venue_Details". I would like to display the value of the rent in a label so that a user cannot modify the value. This is my code

   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 = "valid"
        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
        Dim da As New SqlClient.SqlDataAdapter("Select @Venue_Name,Venue_Rent from Venue_Details", connection)
        Dim dt As New DataTable
        da.Fill(dt)
        Label23.DisplayMember = "Venue_Rent"
        Label23.DataSource = dt



        ProgressBar1.Show()
        Timer1.Start()

        Try

            reader = sqlComm.ExecuteReader()
            If reader.Read Then
                MessageBox.Show(reader.Item(0) & " is booked  from:" & reader.Item(1) & " Till-" & reader.Item(2), "WHOOPS!!!", MessageBoxButtons.OK, MessageBoxIcon.Information)
            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 am not sure if I am doing this correctly

[trevster344]

What's your error? What have you tried? We don't write code for you, you give us your best shot, that includes you giving it a shot not just "I got to this point, don't know where to go from here", otherwise if you need ideas on how to go about it,you need to ask.

[CharlieMay]

The error is telling you that "DisplayMember" isn't available in a label
You are trying to use it and there's not a property by that name

You could access the information you want by using
Label23.Text = dt.Rows(0)("Venue_Rent").ToString()

but here's the issue, that would only give you the top row returned (row 0) and since your SQL statement doesn't use a WHERE clause to limit the information to a single row, it may not be the value you're looking for.

I would suggest a WHERE clause that pulls the row based on a primary key so that you know that the venue_rent column holds the information for the particular venue you're checking.

Otherwise, you need to loop through the DT with an index identifier variable and when the venue is found, then use that index for the row in place of 0 to get the proper venue_rent value.

The rest of the code with the reader is not needed as the DataAdapter handles filling the DT with the result of the Query so you can remove that (unless there is more code somewhere and the reader is not for this particular problem).

[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.

This post has been edited by wanabavbcoder: 10 October 2012 - 07:58 AM