Looping through object items

Acidtrash

Member
Joined
Jul 23, 2012
Messages
5
Programming Experience
1-3
I have a piece of cose that cycles through a table of RSS file locations for blogs, opens up each file in an xml reader and then cycles through the items to get the date of the latest post - then fire that date into my databse to create a dynamic blogroll.

There are a couple of problems with this piece of code in that it cycles through all items which isn't needed. I just need it to get the first pubdate from the RSS file. I am using "For Each objItem In objitems" when I just want the first item. I don't know how to phrase this.

The second problem is that the sql insert doesn't seem to like my date variable due to conversion problems. I'm not too hot with those sorts of problems. Would be very grateful for any hints.


PHP:
Protected Sub Button2_Click(sender As Object, e As System.EventArgs) Handles Button2.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim cellcount As Integer
        Dim objRoot As Object
        Dim objitems As Object
        Dim SQL As String = "select * from tbl_blogroll where rssloc is not null"
        Dim brid As Integer
        Dim objXML As Object

        connetionString = ConfigurationManager.ConnectionStrings("cerberusConnectionString").ConnectionString
        connection = New SqlConnection(connetionString)
        connection.Open()
        command = New SqlCommand(SQL, connection)
        adapter.SelectCommand = command
        adapter.Fill(ds)

        For i = 0 To ds.Tables(0).Rows.Count - 1
            Dim rss As String = ds.Tables(0).Rows(i).Item(3)
            Dim thefeed As String = rss
            brid = ds.Tables(0).Rows(i).Item(0)

            objXML = Server.CreateObject("MSXML2.DOMDocument.4.0")
            objXML = Server.CreateObject("Microsoft.XMLDOM")
            objXML.Async = False
            objXML.SetProperty("ServerHTTPRequest", True)
            objXML.ResolveExternals = True
            objXML.ValidateOnParse = True
            objXML.Load(thefeed)
            cellcount = 0

            If (objXML.parseError.errorCode = 0) Then
                objRoot = objXML.documentElement
                objitems = objRoot.getElementsByTagName("item")
                For Each objItem In objitems
                    Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("cerberusConnectionString").ConnectionString)
                        Dim thedate As String = objItem.selectSingleNode("pubDate").Text
                        Dim sql1 As String = "update tbl_blogroll set Lupdate='" + thedate + "' where brid=" + brid
                        Dim myCommand1 As New SqlCommand(sql1, Conn)
                        myCommand1.ExecuteNonQuery()
                    End Using
                Next
            End If

            objXML = Nothing

            
        Next i

        adapter.Dispose()
        command.Dispose()
        connection.Close()
        Response.Redirect("Default.aspx")

    End Sub
 
You should be able to directly reference the individual objects with objItems.item(x).

With regards to your SQL query not working.... switch over to using parametized queries to start with - it's more secure and means you can be stricter with the types you're passing (without having to convert to strings). Your code would change to :
VB.NET:
Dim sql1 As String = "update tbl_blogroll set Lupdate=@thedate where brid=@brid"
Dim myCommand1 As New SqlCommand(sql1, Conn)
                        
myCommand1.Parameters.addWithValue("@thedate", cDate(thedate))
myCommand1.Parameters.addWithValue("@brid", cInt(brid))

This assumes your table is expecting a datetime to be passed to it. You can also go a step further and parse the date string to tie it down even further.
 
You should be able to directly reference the individual objects with objItems.item(x).

With regards to your SQL query not working.... switch over to using parametized queries to start with - it's more secure and means you can be stricter with the types you're passing (without having to convert to strings). Your code would change to :
VB.NET:
Dim sql1 As String = "update tbl_blogroll set Lupdate=@thedate where brid=@brid"
Dim myCommand1 As New SqlCommand(sql1, Conn)
                        
myCommand1.Parameters.addWithValue("@thedate", cDate(thedate))
myCommand1.Parameters.addWithValue("@brid", cInt(brid))

This assumes your table is expecting a datetime to be passed to it. You can also go a step further and parse the date string to tie it down even further.

Yes, that is much more sensible. I will do that. Thank you.
 
Back
Top