I'm doing a little reservation grid and I need two columns
In the first one I got the hours and in the second one I got the 'state'

For example:

HOUR | STATE
-------------------------
10:00  |  Available
10:30  |  Reserved
11:00  |  Reserved
11:30  |  Available

The problem is that I fill this gridview from differents tables in my BD.
So, the only way I can make this is binding the state items while the
gridview is being generated, do I explain myself?
But, I don't know how to do this :S

In this tutorial http://www.asp.net/learn/dataaccess/tutorial11vb.aspx?tabid=63 
is explained how you can modified the column values under a certain criteria.
I need something similar, but inserting data from a table.
I tried to understand that code, but they use classes and stuff and to be honest
I got lost real soon :(

I hope I was clear in my explanation... I'm in a hurry with this and I need to do it ASAP!!!

 Thanks!

I dont know if i got you 100%.

I think you mean that you want to bind your Gridview with two tables ( from Database) which is not possible.

But you can get vaules from many tables at the same time in a Gridview. the only thing you have keep in mind that these Data tables must  have som relation to each other( I exactly did not mean exactly what actully Relation mean SQL, let me explian wid an example thi thing).

For example "any field of one Table must be also located in other Table"

Then you can create Functions ( i mean VB or C# ones, i.g static functions ) and access the data.

see one exact example below the way can you do after;

 ( code is trimed for brevity of clarity)

You can see this below line is accessing a function for every "Topic ID" accessed through our "SqlDatasource". We are accessing it through a function because this does not lie in the "myMessage" Table.

<%# ForumData.GetViewsCount(Convert.ToInt32(Eval("TopicID"))).ToString()%>

Regards,

Omer Kamal

I appreciate your help very much, but I still can't see a solution :(

 Let's see....

I have two tables: Modules and Reservation

In MODULE I have every module from a doctor with the day and the hours that he goes to work, for example:

doctor | module |   hour   | day
--------------------------------------------
  1          1         10:00      1
  1          2         10:30      1
  1          3         11:00      1
  1          4         09:00      2
  1          5         09:30      2

This says that the Doctor 1 works from 10:00 until 11:00 on Monday, from 9 to 9:30 on Tuesday

And in RESERVATION I got:

    date       |  hour  |  doctor
-----------------------------------------
25/12/06       10:00      1          
25/12/06       11:00      1

This says that Doctor 1 has a reservation for day 25 (wich is Monday = 1) at 10:00 and at 11:00

I HAVE to fill my gridview from de MODULE table with ALL the hours available for that doctor in certain date (I got covered that with a calendar control).
So, for default that would look like this:

HOUR | STATE
-------------------------
10:00  |  Available
10:30  |  Available
11:00  |  Available
11:30  |  Available

But in the case that one o more of the hours are reserved (making a query in the RESERVATION table), then would look like this:

 HOUR | STATE
-------------------------
10:00  |  Reserved
10:30  |  Available
11:00  |  Reserved
11:30  |  Available

I hope is clear now.... please please help me with this!!!

Today was Xmas and a Busy day.

But, I had the question in ma mind and got some time for solving it and then posted it at www.dotnet-friends.com

Please, see this link below,

http://www.dotnet-friends.com/Tutorials/ASP/TUTinASP4c187aed-f46c-41f0-9064-1c7e539c8d2d.aspx

I was looking up the code and I get an error on

if (IsReserved(doc, reader["Hour"].ToString(), thisDate))

{ dt.Rows.Add(new Object[] { reader["Hour"].ToString(), "Reserved" });

It says something about "Make sure that the maximum index on a list is less than the list size".

can you post your code with changings?

The code was only an idea. Hour and Date Fields are "String" or "Nvarchar".

 For your need you have to change them according to your need. ( in DataTable Creation and in DataTable filling)

1    Public Class Test
2        Private myConnStr As String = "data source=(local); integrated security=SSPI; initial catalog=CMI"
3    
4        Public Function MakeDataTable(ByVal doc As Integer, ByVal thisDate As String) As DataTable
5            ' Using 
6            Dim connection As SqlConnection = New SqlConnection(myConnStr)
7            Try
8                Dim Query As String = "SELECT horainicio FROM [Modulo] WHERE idespecialidadmedico=@doc"
9                Dim command As SqlCommand = New SqlCommand(Query, connection)
10               command.CommandType = CommandType.Text
11               command.Parameters.Add("@Doc", SqlDbType.Int).Value = doc
12               connection.Open()
13               Dim reader As SqlDataReader = command.ExecuteReader
14               Dim dt As DataTable = New DataTable("Result")
15               dt.Columns.Add("Hour", GetType(DateTime))
16               dt.Columns.Add("State", GetType(String))
17               While reader.Read
18   
19                   If IsReserved(doc, reader("Hour"), thisDate) Then
20                       dt.Rows.Add(New Object() {reader("Hour"), "Reserved"})
21                   Else
22                       dt.Rows.Add(New Object() {reader("Hour"), "Available"})
23                   End If
24               End While
25               dt.AcceptChanges()
26               Return dt
27           Finally
28               CType(connection, IDisposable).Dispose()
29           End Try
30       End Function
31   
32       Public Function IsReserved(ByVal thisdoc As Integer, ByVal hour As DateTime, ByVal xDate As Integer) As Boolean
33           Dim Query As String = "SELECT * FROM [RESERVA] WHERE idespecialidadmedico=1 AND fechareserva=@Date AND hireserva=@Hour"
34           ' Using 
35           Dim connection As SqlConnection = New SqlConnection(myConnStr)
36           Try
37               Dim command As SqlCommand = New SqlCommand(Query, connection)
38               command.CommandType = CommandType.Text
39               command.Parameters.Add("@Doc", SqlDbType.Int).Value = thisdoc
40               command.Parameters.Add("@Date", SqlDbType.NVarChar).Value = xDate
41               command.Parameters.Add("@Hour", SqlDbType.DateTime).Value = hour
42               connection.Open()
43               Dim reader As SqlDataReader = command.ExecuteReader
44               Dim isthere As Boolean = reader.HasRows
45               Return isthere
46           Finally
47               CType(connection, IDisposable).Dispose()
48           End Try
49       End Function
50   End Class

 I was debugging a little and the error appears in line 19.
I think this is passing the whole reader to the "IsReserved" function, not only a value.

The other thing, in the lines 20 and 22, should be {reader("State"),  right?

Thank for your help :)

In line #20 and 22

reader("Hour") need to be changed to DateTime

Date is  SqlDbType.NVarChar type then  make xDate  as string. Or change SqlDbType.NVarChar to DateTime

1    Public Class Test
2        Private myConnStr As String = "data source=(local); integrated security=SSPI; initial catalog=CMI"
3    
4        Public Function MakeDataTable(ByVal doc As Integer, ByVal thisDate As DateTime) As DataTable
5            ' Using 
6            Dim connection As SqlConnection = New SqlConnection(myConnStr)
7            Try
8                Dim Query As String = "SELECT horainicio FROM [Modulo] WHERE idespecialidadmedico=@doc and iddia=@iddia"
9                Dim command As SqlCommand = New SqlCommand(Query, connection)
10               command.CommandType = CommandType.Text
11               command.Parameters.Add("@Doc", SqlDbType.Int).Value = doc
12               command.Parameters.Add("@iddia", SqlDbType.Int).Value = thisDate.Date.DayOfWeek
13               connection.Open()
14               Dim reader As SqlDataReader = command.ExecuteReader
15               Dim dt As DataTable = New DataTable("Result")
16   
17               dt.Columns.Add("Hour", GetType(DateTime))
18               dt.Columns.Add("State", GetType(String))
19   
20               While reader.Read
21   
22                   If IsReserved(doc, reader("Hour"), thisDate) Then
23                       dt.Rows.Add(New Object() {reader("Hour"), "Reserved"})
24                   Else
25                       dt.Rows.Add(New Object() {reader("Hour"), "Available"})
26                   End If
27   
28               End While
29               dt.AcceptChanges()
30               Return dt
31           Finally
32               CType(connection, IDisposable).Dispose()
33           End Try
34       End Function
35   
36       Public Function IsReserved(ByVal thisdoc As Integer, ByVal hour As DateTime, ByVal xDate As DateTime) As Boolean
37           Dim Query As String = "SELECT * FROM [RESERVA] WHERE idespecialidadmedico=1 AND fechareserva=@Date AND hireserva=@Hour"
38           ' Using 
39           Dim connection As SqlConnection = New SqlConnection(myConnStr)
40           Try
41               Dim command As SqlCommand = New SqlCommand(Query, connection)
42               command.CommandType = CommandType.Text
43               command.Parameters.Add("@Doc", SqlDbType.Int).Value = thisdoc
44               command.Parameters.Add("@Date", SqlDbType.DateTime).Value = xDate
45               command.Parameters.Add("@Hour", SqlDbType.DateTime).Value = hour
46               connection.Open()
47               Dim reader As SqlDataReader = command.ExecuteReader
48               Dim isthere As Boolean = reader.HasRows
49               Return isthere
50           Finally
51               CType(connection, IDisposable).Dispose()
52           End Try
53       End Function
54   End Class

I've changed what you said, but I still have the same error :(

 I think that can be the date format, 'cause when I make a query I make it like this

 
select * from reserva
where fechareserva = '2006-12-20'
 

1. In line # 23 and # 25 use

Since the SQL server uses only datetime types, one have to split the component of it
So, this query:

 

SELECT * FROM [RESERVA] WHERE idespecialidadmedico=1 AND fechareserva=@Date AND hireserva=@Hour

SELECT * FROM [RESERVA] WHERE idespecialidadmedico=1 AND fechareserva=@Date AND hireserva - CAST(FLOOR(CAST(hireserva AS float)) AS datetime) =@Hour

 Now it works.... but I'm not done yet ;)

Thanks a lot!!!!!!

1    Public Class Test
2        Private myConnStr As String = "data source=(local); integrated security=SSPI; initial catalog=CMI"
3    
4        Public Function MakeDataTable(ByVal doc As Integer, ByVal thisDate As Date) As DataTable
5            ' Using 
6            Dim connection As SqlConnection = New SqlConnection(myConnStr)
7            Try
8                Dim Query As String = "SELECT horainicio FROM [Modulo] WHERE idespecialidadmedico=@doc and iddia=@iddia"
9                Dim command As SqlCommand = New SqlCommand(Query, connection)
10               Dim iddia As Integer
11               Dim mifecha As New DateTime
12               iddia = thisDate.Date.DayOfWeek
13               mifecha = thisDate.Date.ToShortDateString
14               command.CommandType = CommandType.Text
15               command.Parameters.Add("@Doc", SqlDbType.Int).Value = doc
16               command.Parameters.Add("@iddia", SqlDbType.Int).Value = iddia
17               connection.Open()
18               Dim reader As SqlDataReader = command.ExecuteReader
19               Dim dt As DataTable = New DataTable("Result")
20   
21   
22               dt.Columns.Add("Hora", GetType(DateTime))
23               dt.Columns.Add("Estado", GetType(String))
24   
25               While reader.Read
26   
27                   If IsReserved(doc, reader(0), mifecha) Then
28                       dt.Rows.Add(New Object() {reader(0), "Reservada"})
29                   Else
30                       dt.Rows.Add(New Object() {reader(0), "Disponible"})
31                   End If
32   
33               End While
34               dt.AcceptChanges()
35               Return dt
36           Finally
37               CType(connection, IDisposable).Dispose()
38           End Try
39       End Function
40   
41       Public Function IsReserved(ByVal thisdoc As Integer, ByVal hour As DateTime, ByVal xDate As Date) As Boolean
42           Dim Query As String = "SELECT * FROM [RESERVA] WHERE idespecialidadmedico=@Doc  AND hireserva =@Hour and fechareserva=@Date"
43           ' Using 
44           Dim connection As SqlConnection = New SqlConnection(myConnStr)
45           Dim isthere As Boolean
46           Try
47               Dim command As SqlCommand = New SqlCommand(Query, connection)
48               command.CommandType = CommandType.Text
49               command.Parameters.Add("@Doc", SqlDbType.Int).Value = thisdoc
50               command.Parameters.Add("@Date", SqlDbType.DateTime).Value = xDate
51               command.Parameters.Add("@Hour", SqlDbType.DateTime).Value = hour
52               connection.Open()
53               Dim reader2 As SqlDataReader = command.ExecuteReader
54               isthere = reader2.HasRows
55    Return isthere
56           Finally
57               CType(connection, IDisposable).Dispose()
58           End Try
59   
60       End Function
61   End Class

 I just have 2 more questions:

1) How can I format the data when the HOUR is displayed in the gridview?

HOUR: 12/30/1899 10:00:00 AM
I need HOUR: 10:00

2) Can I put a link instead of a "Available" string? And could I send values to another page?

 Thanks for this abuse of help