Starting out with vb.net and sqlite3...

[zehd]

"Is nothing" in vb.net is equivalent to "== null"

And "if (not object is nothing) then" is the not clause.

But sometimes DB stuff doesn't recognise null, and needs the DBNull. Which is probably why sub has done it his way.

Can't get the affirmative or negative going. Searching everywhere for inline check. Might have to build a errhandler...

Code:

If dataReader.GetString(dataReader.GetOrdinal("argb_color")) Is Nothing Then
                myGenre.argb = ""
            Else
                myGenre.argb = dataReader.GetString(dataReader.GetOrdinal("argb_color"))
            End If

[zehd]

From memory its something like:

Object argb = dataReader.GetObject(dataReader.GetOrdinal("argb"));
if (Convert.IsDBNull(argb) == false)
{
argb = Convert.ToInt32(argb);
}

So I guess I could set the

Code:

Public Class Genre
        Public oid As Integer
        Public genreName As String
        Public argb As [B]Object[/B]
End Class

And then test with isdbnull

[zehd]

How's this? No errors. But then is the argb_color suppsoed to be string type?

Code:

Public Class Genre
        Public oid As Integer
        Public genreName As String
        Public argb As String
    End Class


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        SQL_connection.ConnectionString = "Data Source=" & GBPVRdb3 & ";Version=3;New=True;"
        SQL_connection.Open()

        Dim command As DbCommand = DbProviderFactory.CreateCommand()
        command.Connection = SQL_connection
        command.CommandText = "SELECT * from GENRE"
        Dim dataReader As DbDataReader = command.ExecuteReader()


        While dataReader.Read() 'iterates all fileds
            Dim myGenre = New Genre()
            myGenre.oid = dataReader.GetInt32(dataReader.GetOrdinal("oid"))
            myGenre.genreName = dataReader.GetString(dataReader.GetOrdinal("genre_name"))
            If (Not Convert.IsDBNull(dataReader.GetValue(dataReader.GetOrdinal("argb_color")))) Then
                myGenre.argb = CStr(dataReader.GetString(dataReader.GetOrdinal("argb_color")))
            End If
            genreList.Add(myGenre)
        End While
        dataReader.Close()

    End Sub

[zehd]

Totally got it. Thanks guys..

[HtV]

Well if you go on like this you'll have 10.000 posts in 2 weeks.
Good luck with it.

Hans

[zehd]

Well if you go on like this you'll have 10.000 posts in 2 weeks.
Good luck with it.

Hans

Now we know how sub has so many posts. He was just winging it...

[Fatman_do]

Now we know how sub has so many posts. He was just winging it...

Just think if he threw in a poll about chicken !!!eleven!!!!!!1111!!??teen!!


And NO!.. this isn't a useless post on my part in a thread I have nothing to do with just to get my post count up.

[zehd]

Rtflmol

[zehd]

Thought I'd resurrect this thread... What the heck

I've been searching for ways to reduce some code clutter and I wanted to see if I could concatenate a variable. I have serached on the net quite a bit, so maybe I don't know what I'm asking... here's the scopp

This is for getting and setting info into an SQLITE3 Table...

Code:

Public Class Programme
    Public oid As Integer
    Public name As String
    Public sub_title As String
    Public description As String
    Public start_time As DateTime
    Public end_time As DateTime
    Public channel_oid As Integer
    Public unique_identifier As String
    Public rating As String
End Class

I create an instance and populate the variables.

Code:

(some snipped)

        While dataReader.Read()

            myDB_Programme = New Programme

            myDB_Programme.oid = dataReader.GetInt32(dataReader.GetOrdinal("oid"))
            myDB_Programme.name = dataReader.GetString(dataReader.GetOrdinal("name"))
            myDB_Programme.sub_title = dataReader.GetString(dataReader.GetOrdinal("sub_title"))
            myDB_Programme.description = dataReader.GetString(dataReader.GetOrdinal("description"))
            myDB_Programme.start_time = dataReader.GetString(dataReader.GetOrdinal("start_time"))
            myDB_Programme.end_time = dataReader.GetString(dataReader.GetOrdinal("end_time"))
            myDB_Programme.channel_oid = dataReader.GetInt32(dataReader.GetOrdinal("channel_oid"))
            myDB_Programme.unique_identifier = dataReader.GetString(dataReader.GetOrdinal("unique_identifier"))
            If (Not Convert.IsDBNull(dataReader.GetValue(dataReader.GetOrdinal("rating")))) Then myDB_Programme.rating = dataReader.GetString(dataReader.GetOrdinal("rating"))

            ProgrammeList.Add(myDB_Programme)

        End While

Due to my query, I actually only have one record filled with values. myDB_Programme is public, so I can get and set the value outside. I change one property

Code:

myDB_Programme.description = "Test2"

So far so good.

Now I want to update the table with the new data. I Know how to do it, if I select the properties...

Here's the section where I build the SQL statement to update the fileds. I have it set to update the whole record, (though in this section, only one field has been changed.

Code:

With myDB_Programme
            'theis record is indexed on oid, so there's no changing it...
            command.CommandText = "UPDATE OR REPLACE PROGRAMME SET " & _
                    "name = '" & .name & "', " & _
                    "sub_title = '" & .sub_title & "', " & _
                    "description = '" & .description & "', " & _
                    "start_time = '" & .start_time & "', " & _
                    "end_time = '" & .end_time & "', " & _
                    "channel_oid = '" & .channel_oid & "', " & _
                    "unique_identifier = '" & .unique_identifier & "', " & _
                    "rating = '" & .rating & "' " & _
                    "WHERE oid =" & param_oid
        End With
        MsgBox(command.CommandText)

Sure I could shorten up the SQL if I only set the 'description' but this is supposed to be more versatile.

I call the set procedure, by passing the oid I want to single out, and the property that I want to update

Code:

Set1_Programme_by_oid("2975", "description", "Test2")

Code:

Sub Set1_Programme_by_oid(ByVal param_oid As Integer, ByVal paramTag As String, ByVal SetValue As Object)


        'first query and fill the mydb_programme object
        Get1_Programme_by_oid(param_oid)

        With myDB_Programme
            Select Case paramTag
                Case "name"
                    .name = SetValue
                Case "sub_title"
                    .sub_title = SetValue
                Case "description"
                    .description = SetValue
                Case "start_time"
                    .start_time = SetValue
                Case "end_time"
                    .end_time = SetValue
                Case "channel_oid"
                    .channel_oid = SetValue
                Case "unique_identifier"
                    .unique_identifier = SetValue
                Case "rating"
                    .rating = SetValue
            End Select
        End With

What I want to do is THIS:

Code:

With myDB_Programme
         "." & paramTag = SetValue
End with

Of course I know that this won't work. I just create a string ("." & paramTag) but I want to turn that string into a class property

[psycik]

Hmm, I think you do what you want, but it's not in one line....

This takes the object, and figures out the public (browsable) properties of that class/object. From there it does stuff with them. Sorry that it's C#

Code:

private static System.Collections.Hashtable EnumerateProperties(object objectToEnumerate)
        {

            PropertyInfo[] properties = (objectToEnumerate.GetType()).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            System.Collections.Hashtable newProperties = new System.Collections.Hashtable();
            for (int i = 0; i < properties.Length; i++)
            {
                //check if there is set accessor
                if (null != properties[i].GetSetMethod())
                {
                    object name = properties[i].Name;
                    object value = "";
                    if (properties[i] != null && properties[i].GetValue(objectToEnumerate, null) != null)
                        value = properties[i].GetValue(objectToEnumerate, null);
                    
                    AttributeCollection attributes = TypeDescriptor.GetProperties(objectToEnumerate)[name.ToString()].Attributes;
                    if (attributes[typeof(BrowsableAttribute)].Equals(BrowsableAttribute.Yes))
                    {
                        //Added so you can now put in nested properties by using the [XmlElement("level1/elemeet")] option on properties.
                        //See UpdateURL for example
                        if (attributes[typeof(XmlElementAttribute)] != null)
                        {
                            XmlElementAttribute tempAttr = (XmlElementAttribute)attributes[typeof(XmlElementAttribute)];
                            if (tempAttr.ElementName.Length > 0)
                                name = tempAttr.ElementName;

                        }
                        newProperties.Add(name, value);
                    }



                }
            }
            return newProperties;
        }

So not sure if you'd be able to use it. I wrote this as a settings class where I gave it an object but wanted more control over its serialisation. So you may be able to adapt it to using a DB....