APP. FOR INSERT XML DATA TO DATABASE

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Timer1.Interval = 15000
Call actualWeatherData()
Call worldWeatherData()
'Call indiaWeatherData()
MsgBox("Record inserted")

End Sub
Private Sub actualWeatherData()
Dim Date1 As String
Dim LastupdatedTime1 As String

Dim CountryID1 As Integer
Dim Country1 As String

Dim CityID1 As Integer

Dim City1 As String

Dim Max1 As Integer

Dim Min1 As Integer

Dim CurrentTemp1 As Integer

Dim SkyConditionID1 As Integer
Dim SkyCondition1 As String

Dim Rain1 As Integer

Dim Humidity1 As Integer

Dim Wind1 As String

Dim Sunrise1 As String

Dim Sunset1 As String
Dim Pressure1 As Integer
connection = New SqlConnection(connetionString)



xmlFile = XmlReader.Create("C:\Documents and Settings\developer\Desktop\network18\network18\actual\actual.xml", New XmlReaderSettings())

ds.ReadXml(xmlFile)

Dim i As Integer

connection.Open()
sql = "delete from WeatherData"
command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()
connection.Close()
connection.Open()
For i = 0 To ds.Tables(0).Rows.Count - 1

'product_ID = Convert.ToInt32(ds.Tables(0).Rows(i).Item(0))

'Product_Name = ds.Tables(0).Rows(i).Item(1)

'product_Price = Convert.ToDouble(ds.Tables(0).Rows(i).Item(2))


Date1 = ds.Tables(0).Rows(i).Item(0)
LastupdatedTime1 = ds.Tables(0).Rows(i).Item(1)
CountryID1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(2))
Country1 = ds.Tables(0).Rows(i).Item(3)
CityID1 = ds.Tables(0).Rows(i).Item(4)
City1 = ds.Tables(0).Rows(i).Item(5)
Max1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(6))
Min1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(7))

CurrentTemp1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(8))
SkyConditionID1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(9))
SkyCondition1 = ds.Tables(0).Rows(i).Item(10)
Rain1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(11))
Humidity1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(12))
Wind1 = ds.Tables(0).Rows(i).Item(13)
Sunrise1 = ds.Tables(0).Rows(i).Item(14)

Sunset1 = ds.Tables(0).Rows(i).Item(15)
Pressure1 = Convert.ToInt32(ds.Tables(0).Rows(i).Item(16))
'sql = "insert into Product values(" & product_ID & ",'" & Product_Name & "'," & product_Price & ")"
'Dim Weather_ID As Integer
'Weather_ID = 0
'Weather_ID = Weather_ID + 1

'Dim Weather_ID As Integer
sql = "INSERT INTO WeatherData(Weather_currentTime,Date,LastupdatedTime,CountryID,Country,CityID,City,Max,Min,CurrentTemp,SkyConditionID,SkyCondition,Rain,Humidity,Wind,Sunrise,Sunset,Pressure) VALUES('" & Now & "','" & Date1 & "','" & LastupdatedTime1 & "','" & (CountryID1).ToString & "','" & Country1 & "','" & CityID1 & "','" & City1 & "','" & Max1 & "','" & Min1 & "','" & CurrentTemp1 & "','" & SkyConditionID1 & "','" & SkyCondition1 & "','" & Rain1 & "','" & Humidity1 & "','" & Wind1 & "','" & Sunrise1 & "','" & Sunset1 & "','" & Pressure1 & "')"
'Weather_ID = Weather_ID + 1
command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()

Next
ds.Clear()


connection.Close()

End Sub
Private Sub worldWeatherData()

Dim Date2 As String
Dim CityID2 As Integer>>>>>>>>>>this line give me an error
Dim Max2 As Integer

Dim Min2 As Integer
Dim SkyCondition2 As String

Dim Rain2 As String

'connetionString = "Data Source=192.168.26.220;Initial Catalog=TEMPLATEDB;User ID=sa;Password=cnbctv18"

connection = New SqlConnection(connetionString)



xmlFile = XmlReader.Create("C:\Documents and Settings\developer\Desktop\network18\network18\forecast\world\WEATHER_30-May-2013.xml", New XmlReaderSettings())

ds.ReadXml(xmlFile)

Dim j As Integer
connection.Open()
sql = "delete from WeatherData2"
command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()
connection.Close()

connection.Open()

For j = 0 To ds.Tables(0).Rows.Count - 1

Date2 = ds.Tables(0).Rows(j).Item(0)
CityID2 = Convert.ToInt32(ds.Tables(0).Rows(j).Item(1))
Max2 = Convert.ToInt32(ds.Tables(0).Rows(j).Item(2))
Min2 = Convert.ToInt32(ds.Tables(0).Rows(j).Item(3))
SkyCondition2 = ds.Tables(0).Rows(j).Item(4)
Rain2 = ds.Tables(0).Rows(j).Item(5)

sql = "INSERT INTO WeatherData2(Weather_CurrentTime,Date,CityID,Max,Min,SkyCondition,Rain) VALUES('" & Now & "','" & Date2 & "','" & CityID2 & "','" & Max2 & "','" & Min2 & "','" & SkyCondition2 & "','" & Rain2 & "')"

command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()

Next
ds.Clear()
connection.Close()
End Sub
Private Sub indiaWeatherData()
Dim cityid As Integer
Dim city_name As String

Dim latitude As String
Dim longitude As String

Dim day_sequence As Integer

Dim date1 As String

Dim high_temp As String

Dim low_temp As String

Dim rainfall As Integer

Dim wind_speed As Integer
Dim wind_dir As String

Dim wind_short As String

Dim wind_long As String

Dim max_rh As Integer

Dim min_rh As Integer

Dim sky As String
Dim precip_prob As Integer
connection = New SqlConnection(connetionString)
xmlFile = XmlReader.Create("C:\Documents and Settings\developer\Desktop\network18\network18\forecast\india\280156.xml", New XmlReaderSettings())

ds.ReadXml(xmlFile)

Dim k As Integer
connection.Open()
sql = "delete from WeatherData3"
command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()
connection.Close()
connection.Open()

cityid = Convert.ToInt32(ds.Tables(0).Rows(k).Item(1))

city_name = ds.Tables(0).Rows(k).Item(2)
latitude = ds.Tables(0).Rows(k).Item(3)
longitude = ds.Tables(0).Rows(k).Item(4)
'For d As Integer = 1 To 5
For k = 0 To ds.Tables(1).Rows.Count - 1
day_sequence = Convert.ToInt32(ds.Tables(1).Rows(k).Item(0))
date1 = ds.Tables(1).Rows(k).Item(1)
high_temp = ds.Tables(1).Rows(k).Item(2)
low_temp = ds.Tables(1).Rows(k).Item(3)

rainfall = Convert.ToInt32(ds.Tables(1).Rows(k).Item(4))
wind_speed = Convert.ToInt32(ds.Tables(1).Rows(k).Item(5))
wind_dir = ds.Tables(1).Rows(k).Item(6)
wind_short = ds.Tables(1).Rows(k).Item(7)
wind_long = ds.Tables(1).Rows(k).Item(8)
max_rh = Convert.ToInt32(ds.Tables(1).Rows(k).Item(9))
min_rh = Convert.ToInt32(ds.Tables(1).Rows(k).Item(10))

sky = ds.Tables(1).Rows(k).Item(11)
precip_prob = Convert.ToInt32(ds.Tables(1).Rows(k).Item(12))
'sql = "insert into Product values(" & product_ID & ",'" & Product_Name & "'," & product_Price & ")"
sql = "INSERT INTO WeatherData3(Weather_Current_Date,City_ID,City_Name,Llatitude,Longitude,Day_Sequence,date,High_Temp,Low_Temp,Rainfall,Wind_Speed,Wind_Dir,Wind_Short,Wind_Long,Max_RH,Min_RH,Sky,Precip_Prob) VALUES('" & Now & "','" & cityid & "','" & city_name & "','" & latitude & "','" & longitude & "','" & day_sequence & "','" & date1 & "','" & high_temp & "','" & low_temp & "','" & rainfall & "','" & wind_speed & "','" & wind_dir & "','" & wind_short & "','" & wind_long & "','" & max_rh & "','" & min_rh & "','" & sky & "','" & precip_prob & "')"
command = New SqlCommand(sql, connection)

adpter.InsertCommand = command

adpter.InsertCommand.ExecuteNonQuery()

Next
ds.Clear()
connection.Close()
End Sub