What is the best method

amina89

Hello I work on an intranet site management fees. management part of review tired me, I chose to work with Excel for simplicity That's what I want to do: I have a listbox containing the modules , for a list of students registered in a module, the responsable for this must complete an authentication form. I want that after authenticating the excel file containing the student opens (I do not know if this is the right method, you comments are welcome) to export data from sqlserver to excel I used this code:

cn.Open();
SqlDataAdapter da = new SqlDataAdapter(@"DECLARE @MATIERE nvarchar(max)
SELECT @MATIERE =
STUFF(
(
select distinct ',[' + LIBELLE_MATIERE + ']'
from MATIERE where CODE_MODULE =11
for xml path('')
),
1,1,'')

DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select
*
from (

SELECT ETUDIANT.NUM_INSCRIPTION, ETUDIANT.NOM_PRENOM_ETUDIANT_ARABE,EXAMEN.CODE,
EXAMEN.NOTE,
MATIERE.LIBELLE_MATIERE
FROM ETUDIANT INNER JOIN
EXAMEN ON ETUDIANT.NUM_INSCRIPTION = EXAMEN.NUM_INSCRIPTION INNER JOIN
CAPITALISE_MODULE ON
ETUDIANT.NUM_INSCRIPTION = CAPITALISE_MODULE.NUM_INSCRIPTION INNER JOIN
MATIERE ON EXAMEN.CODE_MATIERE = MATIERE.CODE_MATIERE INNER JOIN
MODULE ON CAPITALISE_MODULE.CODE_MODULE = MODULE.CODE_MODULE
AND MATIERE.CODE_MODULE = MODULE.CODE_MODULE
WHERE (CAPITALISE_MODULE.CODE_MODULE = 11) AND
(CAPITALISE_MODULE.CODE_TYPE_RELATION_MODULE = 1)
) Data
PIVOT (
SUM(NOTE)
FOR LIBELLE_MATIERE
IN (
' + @MATIERE + '
)
) PivotTable
'

exec sp_executesql @SQL

", cn);
DataTable dt = new DataTable();
da.Fill(dt);
cn.Close();

   Response.Clear();
   Response.ContentType = "application/vnd.ms-excel";
   string sep = "";
   foreach (DataColumn dc in dt.Columns)
   {
          Response.Write(sep + dc.ColumnName);
          sep = "\\t";
   }
   Response.Write("\\n");

   int i;
   foreach (DataRow dr in dt.Rows)
   {
          sep = "";
          for (i = 0; i < dt.Columns.Count; i++)
          {
                 Response.Write(sep + dr\[i\].ToString());
                 sep = "\\t";
          }
          Response.Write("\\n");
   }

}

this code works with sql queries simple but with my query i had an em

Viral Upadhyay

I think you should debug your code first and see what you get in DataTable. if it is working fine then you can think of other option. :rose:

Viral My Site
Save Our Tigers

amina89

Thank you very much Viral Upadhyay, :) My code work I, it gives me a dialog box to open or register the excel file .but it is in the server or the client? if the file is in the server how to send the client by simple download or??? Thanks

Viral Upadhyay

Actually i am taking about is your datatable which fetch data from database has record or not? but its look like you have done every thing right. By the way the dialog box you see is for download or open file from server to client.

Viral My Site
Save Our Tigers

amina89

Thank you very much Viral Upadhyay:) my english is bad i undrstand that the dialog box will be open in the pc of teachers and they will record notes and then send files to servers or directly to the database. thank you.