Database

Bit_Flipper wrote:

I know how to do everything. Any comments are greatly appreciated.

Sub WriteToExcel(objAdoResultSet, strTemplateFile, strNewFilePath)
   Dim objFSO, objExcelConn, objExcelRS, arrData, lngRow, intField

   'Copy the template spreadsheet, and open a recordset.
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Call objFSO.CopyFile(strTemplateFile, strNewFilePath, True)
   Set objExcelConn = CreateObject("ADODB.Connection")
   Call objExcelConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strNewFilePath & ";" & _
         "Extended Properties=""Excel 8.0;HDR=NO""")
   Set objExcelRS = CreateObject("ADODB.RecordSet")
   Call objExcelRS.Open("SELECT * FROM [Sheet1$]", objExcelConn, _
         adOpenKeyset, adLockOptimistic)
   Call objExcelRS.MoveLast() 'Position cursor on formatting row.

   'Write data to the spreadsheet recordset.
   arrData = objAdoResultSet.GetRows()
   With (objExcelRS)
      For lngRow = 0 To UBound(arrData, 2)
         Call .AddNew()
         For intField = 0 To UBound(arrData, 1)
            .Fields(intField).Value = arrData(intField, lngRow)
         Next
         Call .Update()
      Next
   End With

   'Tidy up.
   Call objExcelRS.Close()
   Set objExcelRS = Nothing
End Sub

adnanrafiq wrote:

first i m getting these two errors

adnanrafiq wrote:

when i try to call the from my DAL layer i got exception

select id, measurement_01, measurement_02, measurement_03,
      case
         when measurement_01 >= measurement_02 and measurement_01 >= measurement_03
            then measurement_01
         when measurement_02 >= measurement_03
            then measurement_02
         else  measurement_03
      end as maximum
   from #temp_ajh
   order by id

SELECT
    m.ID,
    m.measurement_01,
    m.measurement_02,
    m.measurement_03,
    MAX (FlatTable.measurement_01)
FROM
    MeasurementTable m
INNER JOIN
    (SELECT ID, measurement_01 
     FROM MeasurementTable
    UNION
     SELECT ID, measurement_02 
     FROM MeasurementTable
    UNION
     SELECT ID, measurement_03 
     FROM MeasurementTable) AS FlatTable
    ON (m.ID = FlatTable.ID)
GROUP BY
    m.ID,
    m.measurement_01,
    m.measurement_02,
    m.measurement_03

create procedure MyRecordInsert
@Prefix varchar(3),
@OtherData varchar(20)
as begin
   set nocount on
   declare @PrimaryKey varchar(15), @MaxKey int
   Begin tran

   --Create first part of primary key (prefix + date).
   set @PrimaryKey = @Prefix + Convert(varchar(8), GetDate(), 112)

   --Find the higher number that has been allocated today.
   --Use locking to prevent anyone else inserting until we have finished
   --our transaction.
   select @MaxKey = Convert(Int, Substring(Max(PrimaryKey), 12, 4))
      from MyTable with (tablock, holdlock)
      where PrimaryKey like @PrimaryKey + '%'

   --Increment the highest number, and then append to key.
   set @MaxKey = IsNull(@MaxKey, 0) + 1
   set @PrimaryKey = PrimaryKey +
         Right('0000' + Convert(varchar(4), @MaxKey), 4)

   --Insert record into the database.
   insert into MyTable (PrimaryKey, OtherData)
      values (@PrimaryKey, @OtherData

   Commit tran
end

INSERT INTO myTable(ID, Item1, Item2)
SELECT 'sa0' + CONVERT(DATETIME, GETDATE(), 112) + COUNT(*),
@Item1, @Item2
FROM myTable