Pls help!

Hi all

i am a new user to .NET comunity and i need help in the following problem

1. I have a table called student_master which has rollno a PK and various other fields which are used to get the corresponding details.

2. I also have a table called attendance with the following fields rollno an FK, date and hrs

3. what i need to do is, on selection of a day, i need to copy all the roll numbers from te student_master table and the date to the selected date and hrs as default 0 by just one transaction

example

student_master table

rollno |name

1 | xxx

2 | xxx

what i should get in attendance table is somwwhat like this

rollno|date|hrs

1 |12 |0

2 |12 |0

1 |13 |0

2 |13 |0

Thanks in advance

Hi Grietcap

Not sure I understand you completely - especiallywhy you seem to have two dates in your returned table (12th and 13th)if you are selecting by a date. But in any case can I offer thefollowing select statement which may be of help:-

"SELECT[student_master].[rollno], [attendance].[date], [attendance].[hours]FROM [student_master] INNER JOIN [attendance] ON[student_master].[rollno] = [attendance].[rollno] WHERE(([attendance].[date] = @.date) AND ([attendance].[hours] = 0))"

You can run this select in the following VB.net function

Function MyQueryMethod(ByVal [date] As Date) As System.Data.SqlClient.SqlDataReader
Dim connectionString AsString = "server='XXX'; user id='XXXX'; password='XXXX';Database='XXXX'"
Dim sqlConnection AsSystem.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String ="SELECT [student_master].[rollno], [attendance].[date],[attendance].[hours] FROM "& _
"[student_master] INNER JOIN [attendance] ON [student_master].[rollno]= [attendance].[rollno] WHERE (([attendance].[date] = @.date) AND([attend"& _
"ance].[hours] = 0))"
Dim sqlCommand AsSystem.Data.SqlClient.SqlCommand = NewSystem.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@.date", System.Data.SqlDbType.DateTime).Value = [date]

sqlConnection.Open
Dim dataReader AsSystem.Data.SqlClient.SqlDataReader =sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Hope this helps

Regards

Mike