Monday, May 18, 2009
Save Word Document to Database
set rs=server.CreateObject("ADODB.RecordSet")
set rs1=server.CreateObject("ADODB.RecordSet")
set binObj =server.CreateObject("ADODB.Stream")
set rs=cnn.execute("exec usp_docinsert '1.doc'")
if not rs.EOF then
fileid=rs.Fields(0)
end if
set rs=nothing
sql = "SELECT * FROM doc WHERE fileid=" & fileid
rs1.Open sql, cnn,1, 2, adCmdTableDirect
If Not (rs1.BOF And rs1.eof) Then
binObj.Type = 1
binObj.Open
binObj.LoadFromFile (server.MapPath(".") & "\1.doc")
rs1.Fields("filecontent") = binObj.Read
rs1.Update
binObj.Close
Set binObj = Nothing
End If
set rs1=server.CreateObject("ADODB.RecordSet")
set binObj =server.CreateObject("ADODB.Stream")
sql = "SELECT * FROM doc WHERE fileid=18"
rs1.Open sql, cnn,1, 2, adCmdTableDirect
if Not (rs1.BOF And rs1.eof) Then
binObj.Type = 1
binObj.Open
binObj.Write rs1.Fields("filecontent")
binObj.SaveToFile ("C:docs\2.doc") , 2
binObj.Close
Set binObj = Nothing
End if
set rs1=server.CreateObject("ADODB.RecordSet")
set binObj =server.CreateObject("ADODB.Stream")
set rs=cnn.execute("exec usp_docinsert '1.doc'")
if not rs.EOF then
fileid=rs.Fields(0)
end if
set rs=nothing
sql = "SELECT * FROM doc WHERE fileid=" & fileid
rs1.Open sql, cnn,1, 2, adCmdTableDirect
If Not (rs1.BOF And rs1.eof) Then
binObj.Type = 1
binObj.Open
binObj.LoadFromFile (server.MapPath(".") & "\1.doc")
rs1.Fields("filecontent") = binObj.Read
rs1.Update
binObj.Close
Set binObj = Nothing
End If
set rs1=server.CreateObject("ADODB.RecordSet")
set binObj =server.CreateObject("ADODB.Stream")
sql = "SELECT * FROM doc WHERE fileid=18"
rs1.Open sql, cnn,1, 2, adCmdTableDirect
if Not (rs1.BOF And rs1.eof) Then
binObj.Type = 1
binObj.Open
binObj.Write rs1.Fields("filecontent")
binObj.SaveToFile ("C:docs\2.doc") , 2
binObj.Close
Set binObj = Nothing
End if
Monday, April 6, 2009
Script to automatically reindex all tables in a database
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
exec sp_updatestats
DEALLOCATE TableCursor
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
exec sp_updatestats
DEALLOCATE TableCursor
Wednesday, March 4, 2009
HOW TO DELETE BLANK CELLS IN A COLUMN (WITHOUT DELETING ENTIRE ROW) and SHIFT CELLS UP
Dim rDataArea As Range
Set rDataArea = ActiveCell.CurrentRegion
Dim iTotalRows As Integer
iTotalRows = rDataArea.Rows.Count
Dim i As Integer
i = 3 'set to row you wish to start with..looked like row 3 from your post
Cells(i, 2).Select '2 is the column, B
Do Until ActiveCell.Row >= iTotalRows Or i >= iTotalRows
If ActiveCell.Text = "" Then
Selection.Delete Shift:=xlUp
i = i + 1
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Set rDataArea = ActiveCell.CurrentRegion
Dim iTotalRows As Integer
iTotalRows = rDataArea.Rows.Count
Dim i As Integer
i = 3 'set to row you wish to start with..looked like row 3 from your post
Cells(i, 2).Select '2 is the column, B
Do Until ActiveCell.Row >= iTotalRows Or i >= iTotalRows
If ActiveCell.Text = "" Then
Selection.Delete Shift:=xlUp
i = i + 1
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Wednesday, February 11, 2009
Product Of a Field
select Product, EXP(sum(log(abs(nullif(Value, 0))))) * (1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
from @PRODUCT group by Product
select Exp(Sum((case Abs(column) when 0 then 0 else Log(Abs(column)) end)))*(case Min(abs(column)) when 0 then 0 else 1 end)*(1-2*(Sum( ( case when column>=0 then 0 else 1 end) ) % 2)) column
from @PRODUCT group by Product
select Exp(Sum((case Abs(column) when 0 then 0 else Log(Abs(column)) end)))*(case Min(abs(column)) when 0 then 0 else 1 end)*(1-2*(Sum( ( case when column>=0 then 0 else 1 end) ) % 2)) column
Tuesday, January 20, 2009
dropdown menu onclick show div hide div
<html>
<head>
<title>Script Demo Gops</title>
<style>
#d1, #d2, #d3{display:none;}
</style>
<script language="javascript">
var oldD="";
function show(o){
if(oldD!="") oldD.style.display='none';
if(o.selectedIndex>0){
var d=document.getElementById(o[o.selectedIndex].value);
d.style.display='block';
oldD=d;
}
}
</script>
</head>
<body>
<select onchange="show(this)">
<option>--Choose--</option>
<option value="d1">Layer 1</option>
<option value="d2">Layer 2</option>
<option value="d3">Layer 3</option>
</select>
<div id="d1">
this is a div1<br>
this is a div1<br>
this is a div1<br>
this is a div1<br>
this is a div1<br>
</div>
<div id="d2">
this is a div2<br>
this is a div2<br>
this is a div2<br>
this is a div2<br>
this is a div2<br>
</div>
<div id="d3">
this is a div3<br>
this is a div3<br>
this is a div3<br>
this is a div3<br>
this is a div3<br>
</div>
</body>
</html>
Tuesday, December 2, 2008
Convert Number To Words
Option Explicit
' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************
Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
Place(0) = " Thousand "
Place(2) = " Lakh "
Place(4) = " Crore "
Place(6) = " Arab "
Place(8) = " Kharab "
On Error Resume Next
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paisa
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paisa = " and " & ConvertTens(Temp) & " Paisa"
' Strip off paisa from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
' Convert last 3 digits of MyNumber to ruppees in word.
Hundreds = ConvertHundreds(Right(MyNumber, 3))
' Strip off last three digits
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
iCount = 0
Do While MyNumber <> ""
'Strip last two digits
Temp = Right(MyNumber, 2)
If Len(MyNumber) = 1 Then
Words = ConvertDigit(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 1)
Else
Words = ConvertTens(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
End If
iCount = iCount + 2
Loop
CurrencyToWord = "Rupees " & Words & Hundreds & Paisa
End Function
' Conversion for hundreds
'*****************************************
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
' Conversion for tens
'*****************************************
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
' Function for conversion of a Currency to words
' Parameter - accept a Currency
' Returns the number in words format
'*************************************************
Function CurrencyToWord(ByVal MyNumber)
Dim Temp
Dim Rupees, Paisa As String
Dim DecimalPlace, iCount
Dim Hundreds, Words As String
ReDim Place(9) As String
Place(0) = " Thousand "
Place(2) = " Lakh "
Place(4) = " Crore "
Place(6) = " Arab "
Place(8) = " Kharab "
On Error Resume Next
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paisa
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paisa = " and " & ConvertTens(Temp) & " Paisa"
' Strip off paisa from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
' Convert last 3 digits of MyNumber to ruppees in word.
Hundreds = ConvertHundreds(Right(MyNumber, 3))
' Strip off last three digits
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
iCount = 0
Do While MyNumber <> ""
'Strip last two digits
Temp = Right(MyNumber, 2)
If Len(MyNumber) = 1 Then
Words = ConvertDigit(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 1)
Else
Words = ConvertTens(Temp) & Place(iCount) & Words
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
End If
iCount = iCount + 2
Loop
CurrencyToWord = "Rupees " & Words & Hundreds & Paisa
End Function
' Conversion for hundreds
'*****************************************
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
' Conversion for tens
'*****************************************
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function
Subscribe to:
Posts (Atom)