Data Presentation Controls

Re: Colum values depending in another column

Saintcorp — Wed, 27 Dec 2006 16:26:31 GMT

It's finally working... this is the code:

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

Re: Colum values depending in another column

Saintcorp — Wed, 27 Dec 2006 01:33:49 GMT

I made the changes, but I think I've found the error. It was in the query :)

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

Becomes:

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!!!!!!

Re: Colum values depending in another column

omerkamal — Tue, 26 Dec 2006 23:00:20 GMT

1. In line # 23 and # 25 use

Convert.ToDateTime(reader("Hour"))

2. By Using Parenters you not accessing the Date like string so that is not the error.

3. I am not using VB but check that if you function is returning any Value. because the Return statment should be the last statment in the function

Change the Function like this;

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)

Dim isthere As Boolean = false

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

                      isthere = reader.HasRows
49
50           Finally
51               CType(connection, IDisposable).Dispose()
52           End Try

Return isthere
53 End Function

Re: Colum values depending in another column

Saintcorp — Tue, 26 Dec 2006 22:05:35 GMT

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'

So, I think it can be causing a conflict :S

Re: Colum values depending in another column

omerkamal — Tue, 26 Dec 2006 19:44:00 GMT

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

Re: Colum values depending in another column

Saintcorp — Tue, 26 Dec 2006 19:10:53 GMT

Forget about the 20 and 22 line :P

Re: Colum values depending in another column

Saintcorp — Tue, 26 Dec 2006 19:07:56 GMT

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 :)

Re: Colum values depending in another column

omerkamal — Tue, 26 Dec 2006 12:27:13 GMT

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)

Re: Colum values depending in another column

Saintcorp — Mon, 25 Dec 2006 23:19:11 GMT

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

Re: Colum values depending in another column

omerkamal — Sun, 24 Dec 2006 23:57:25 GMT

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

Re: Colum values depending in another column

Saintcorp — Sun, 24 Dec 2006 05:32:08 GMT

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:

Re: Colum values depending in another column

omerkamal — Sat, 23 Dec 2006 22:27:45 GMT

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;

asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"

PageSize="50" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" Width=100%>

This member still did not share any ideas with the Community members!

</EmptyDataTemplate>

<asp:TemplateField ItemStyle-Width=7% HeaderText="Views" ItemStyle-HorizontalAlign=Center>

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

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDb %>"

SelectCommand="SELECT DISTINCT [myTopicID] FROM [myMessages] ">

<asp:QueryStringParameter Name="CreatedBy" QueryStringField="name" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

( 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

Colum values depending in another column

Saintcorp — Sat, 23 Dec 2006 19:26:54 GMT

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'

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!