different button same function

head
body
form id="form1" runat="server">
div>
LinkButton
LinkButton
LinkButton

Private Sub Confirrmshipment_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
While True

DispatchOrders()
FnUpdateEbayStatus()
FnUpdateBuyComStatus()
UnshipedReports()
toCheckManualOrders()

Try

Dim sleepTimeCal As Integer = 0
sleepTimeCal = 1000 * 60 * Convert.ToInt32(ConfigurationManager.AppSettings.Get("SleepInterval"))
Thread.Sleep(sleepTimeCal)
Catch ex As Exception

Finally

End Try

End While


End Sub

Different types of date show

different types of show date

SELECT CONVERT(VARCHAR(100),GETDATE(),100) --Feb 8 2011 5:43PM
SELECT CONVERT(VARCHAR(100),GETDATE(),101) --02/08/2011
SELECT CONVERT(VARCHAR(100),GETDATE(),102) --2011.02.08
SELECT CONVERT(VARCHAR(100),GETDATE(),103) --08/02/2011
SELECT CONVERT(VARCHAR(100),GETDATE(),104)--08.02.2011
SELECT CONVERT(VARCHAR(100),GETDATE(),105)-- 08-02-2011
SELECT CONVERT(VARCHAR(100),GETDATE(),106) --08 Feb 2011
SELECT CONVERT(VARCHAR(100),GETDATE(),107) --Feb 08, 2011
SELECT CONVERT(VARCHAR(100),GETDATE(),108) --17:41:36
SELECT CONVERT(VARCHAR(100),GETDATE(),109) --Feb 8 2011 5:41:57:530PM
SELECT CONVERT(VARCHAR(100),GETDATE(),112) --20110208
SELECT CONVERT(VARCHAR(100), GETDATE(),113) --08 Feb 2011 17:43:44:903
SELECT CONVERT(VARCHAR(100), GETDATE(),114) --17:44:04:450
SELECT CONVERT(VARCHAR(100), GETDATE(),110) --ANSWER :02-08-2011

SELECT CONVERT(VARCHAR(100),GETDATE(),120) --201102082011-02-08 17:45:17

SELECT CONVERT(VARCHAR(100),GETDATE(),111) --2011/02/08

SELECT CONVERT(VARCHAR(100),GETDATE(),121)--2011-02-08 17:45:43.513

Read Xml format

« SQL SERVER – Simple Example of Creating XML File Using T-SQL
SQL SERVER – Restore Master Database – An Easy Solution »
SQL SERVER – Simple Example of Reading XML File Using T-SQL
February 13, 2009 by pinaldave
In one of the previous article we have seen how we can create XML file using SELECT statement SQL SERVER – Simple Example of Creating XML File Using T-SQL. Today we will see how we can read the XML file using the SELECT statement.
Following is the XML which we will read using T-SQL:
Following is the T-SQL script which we will be used to read the XML:
DECLARE @MyXML XMLSET @MyXML = 'WhiteBlueBlackGreenRedApplePineappleGrapesMelon'
SELECTa.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’++a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4FROM @MyXML.nodes(‘SampleXML’) a(b)

INSERT DATA FROM TEXTFILE TO DATABASE

Private Sub readfile()
'Dim delimiter As String() = {vbTab}
'Dim headers As String() = Split(lines(1), delimiter(0))

Dim tabSize As Integer = 0
Dim arInfo As String()
Dim objstreamreader As StreamReader
objstreamreader = File.OpenText(Ge_Filename + filename)
' ReadAllText("E:\vengatesh\USADD@12!20110130163507-1_4.txt")
Dim a As String()
a = File.ReadAllLines(Ge_Filename + filename)
' contents = a.Replace(("").PadRight(tabSize, " "c), vbTab)
Dim textdelimiter As String() = {vbTab}
For count As Integer = 1 To 12
arInfo = Split(a(count), textdelimiter(0))
For i As Integer = 0 To arInfo.Length
' row = Table.NewRow()
If i < arInfo.Length Then
vendor = arInfo(i).ToString()
End If
If i + 1 < arInfo.Length Then
user = arInfo(i + 1).ToString()
End If
If i + 2 < arInfo.Length Then
product = arInfo(i + 2).ToString()
End If
If i + 3 < arInfo.Length Then
itemcondition = arInfo(i + 3).ToString()
'Table.Rows.Add(row)
End If
If i + 4 < arInfo.Length Then
itemnode = arInfo(i + 4).ToString()
'Table.Rows.Add(row)
End If
If i + 5 < arInfo.Length Then
price = arInfo(i + 5).ToString()
End If
If i + 6 < arInfo.Length Then
seller = arInfo(i + 6).ToString()
End If
If i + 7 < arInfo.Length Then
quantity = arInfo(i + 7).ToString()
End If
If i + 8 < arInfo.Length Then
adddelete = arInfo(i + 8).ToString()
End If
If i + 9 < arInfo.Length Then
shipinternelly = arInfo(i + 9).ToString()
End If
If i + 10 < arInfo.Length Then
exiptedship = arInfo(i + 10).ToString()
End If
If i + 11 < arInfo.Length Then
mrp = arInfo(i + 11).ToString()
Dim get_Value = obj.Insert_data(vendor, user, product, itemcondition, itemnode, price, seller, quantity, adddelete, shipinternelly, exiptedship, mrp).ToString()
End If
i = i + 12
Next

Next










End Sub

CSV file format tab delimiter

Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE PROCEDURE venkattext @FILENAME varchar(200) AS DECLARE @cmd varchar(5000) DECLARE @F_Name varchar(8000) DECLARE @tempCOUNT int BEGIN CREATE TABLE ##TempFileGen2(isbn varchar(500) ,title varchar(300),authorname varchar(200),manufacturerid bigint,imageurl varchar(300),binding varchar(200),sku varchar(300),price int,name varchar(300))
Insert into ##TempFileGen2(isbn,title,authorname,manufacturerid,imageurl,binding,sku,price,name)
select top 100 P.ISBN,P.title,P.AuthorName,p.manufacturerid,P.ImageURL,p.binding,s.sku,pr.usprice as Price,m.namefrom product p join stock s on p.productid=s.productid join price pr on pr.sku=s.sku joinmanufacturer m on p.manufacturerid=m.manufacturerid and p.AmazonSync=1 and s.stockInHand>0 and pr.active=1 and s.active=1
SET @F_Name = 'E:\vengatesh\'+@FILENAME+'.txt' SET @Cmd= 'bcp "SELECT * FROM gmimagedb..[##TempFileGen2] " queryout "' -- SET @Cmd = @Cmd + @F_Name + '" -U -P -c -U sa -P @r@bm0r3' SET @Cmd = @Cmd + @F_Name + '" -c -T -S TESTSERVER\SQL2008TEST' print @cmd EXEC master..xp_cmdshell @Cmd END

sql server

2) Write small procedure for automatic backup

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor


3)HOW TO COVERT DATE TO VARCHAR


SET @VARIABLE NAME=CONVERT(VARCHAR(100),GETDATE(),112)

SQL QUERES

CURSOR EXAMPLES:

1) Write the cursor program examples for select top most 100 author name from product table and print with serial no

Answer:

SET NOCOUNT ON
DECLARE @name VARCHAR(100),@COUNT INT=0
DECLARE OPENCURSOR CURSOR FOR
SELECT TOP 100 AUTHORNAME FROM PRODUCT
OPEN OPENCURSOR

FETCH NEXT FROM OPENCURSOR INTO @name

WHILE @@FETCH_STATUS=0
BEGIN
SET @COUNT=@COUNT+1
PRINT LTRIM(STR(@COUNT)+')'+ @NAME)+','
FETCH NEXT FROM OPENCURSOR INTO @name
END
CLOSE OPENCURSOR
DEALLOCATE OPENCURSOR

Output:

1)Frances Hodgson Burnett,
2)Rudyard Kipling,
3)Kenneth Grahame,
4)Colin Hawkins,Jacqui Hawkins,
5)Jane Kemp,Clare Walters,
6)Jane Kemp,Clare Walters,
7)Enid Blyton,
8)Enid Blyton,
9)Nick Butterworth,
10)Judith Kerr,
11)Judith Kerr,
12)Dr. Seuss,
13)Dr. Seuss,
14)Dr. Seuss,
15)Dr. Seuss,
16)Elizabeth Laird,John Richardson,
17)Enid Blyton,
18)Enid Blyton,