Click here to Skip to main content
15,902,893 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi guys ..

I have a problem with this code.I Have an error
"Conversion from type 'DBNull' to type 'String' is not valid."
I just cant figure it out wheres the problem.
Please help me...

heres the code:
VB
Private Sub BackupMySql()

        Dim localDir As String = "C:/"
        Dim strDate As String = Date.Now.ToShortDateString   'Prepend file with date for dated backups
        'Dim fileName As String = strDate.Replace("/", "-") & "_" & ftphost & ".sql"
        Dim fileName As String = strDate.Replace("/", "-") & "_" & "test" & ".sql"
        Dim saveFile As String = localDir & fileName
        Dim DBServer As String = "localhost"
        Dim DBServerPort As String = "3306"
        Dim Database As String = "dtr"
        Dim DBUser As String = "root"
        Dim DBPass As String = "pinoy"
        Dim MyConString As String = "SERVER=" & DBServer & ";DATABASE=" & Database & ";UID=" & DBUser & ";PASSWORD=" & DBPass & ";PORT=" & DBServerPort & ";charset=utf8"
        Dim connection As New MySqlConnection(MyConString)
        Dim tablesCommand As MySqlCommand = connection.CreateCommand()
        Dim rowsCommand As MySqlCommand = connection.CreateCommand()
        Dim tablesReader As MySqlDataReader
        Dim rowsReader As MySqlDataReader

        tablesCommand.CommandText = "SHOW TABLES FROM " & Database
        connection.Open()
        tablesReader = tablesCommand.ExecuteReader()

        Dim query As String = ""
        Dim tablesArray As New ArrayList()

        While tablesReader.Read()
            tablesArray.Add(tablesReader.GetValue(0).ToString())
        End While

        tablesReader.Close()
        For i = 0 To tablesArray.Count - 1
            query += "DROP TABLE IF EXISTS `" & tablesArray(i) & "`;" & vbCrLf '& Database & "."
            'query += "DROP TABLE IF EXISTS `" & tablesArray(i) & "`;" & vbCrLf '& Database & "."
            query += vbCrLf & "CREATE TABLE `" & tablesArray(i) & "` (" & vbCrLf
            rowsCommand.CommandText = "DESCRIBE " & tablesArray(i)
            rowsReader = rowsCommand.ExecuteReader()

            Dim temp As String = ""
            While rowsReader.Read()
                query += "`" & rowsReader.GetString("Field") & "` " & rowsReader.GetString("Type")
                If Not rowsReader.GetString("Null") = "YES" Then
                    query += " NOT NULL"
                End If
                If IsDBNull(rowsReader.Item("Default")) = False Then
                    query += " DEFAULT '" & rowsReader.GetString("Default") & "'"
                End If
                If Not rowsReader.GetString("Extra") = Nothing Then
                    query += " " & rowsReader.GetString("Extra").ToUpper()
                End If
                If rowsReader.GetString("Key") = "PRI" Then
                    temp = "primary key(" & rowsReader.GetString("Field") & ")"
                End If
                query += "," & vbCrLf

            End While
            query += temp & vbCrLf & ");" & vbCrLf & vbCrLf
            rowsReader.Close()
            rowsCommand.CommandText = "SELECT * FROM " & tablesArray(i)
            rowsReader = rowsCommand.ExecuteReader()

            While rowsReader.Read()
                query += "INSERT INTO `" & tablesArray(i) & "` ("
                Dim count As Integer = rowsReader.FieldCount - 1
                Dim keys(count) As String
                Dim values(count) As String
                For n = 0 To count
                    keys(n) = rowsReader.GetName(n)
                    values(n) = rowsReader.Item(n)
                Next
                query += Join(keys, ", ") & ")" & vbCrLf & "VALUES ('" & Join(values, "', '") & "');" & vbCrLf
            End While
            rowsReader.Close()
            query += vbCrLf & vbCrLf

        Next
        connection.Close()
        connection.Dispose()

        If File.Exists(saveFile) Then
            File.Delete(saveFile)
        End If
        Dim objWriter As New System.IO.StreamWriter(saveFile)

        objWriter.Write(query)
        objWriter.Close()

    End Sub


Please help me...
Thanks in advanced.!
Posted
Updated 24-Jun-10 7:10am
v2
Comments
William Winner 24-Jun-10 13:12pm    
The error should tell you what line it happens on...that would keep us from having to scan all of your code for you...more specifics are always better.

I suspect your error lines here;

jleonorlane wrote:
values(n) = rowsReader.Item(n)


You will need to test for type of System.DBNull and then .ToString() it.

Search google for "DBNull VB.Net" there are a million examples there.
 
Share this answer
 
What's happening is that a column in one of the rows in your database is empty. When a cell is empty, SQL doesn't send "", it sends the value DBNull. If your table can have empty cells, then you need to check each cell for DBNull. You can use IsDBNull to check that and if it isn't, then you can test your values.

You should look at the documentation for each object your using, specifically MySqlDataReader.GetString()[^]

It says, "Call IsDBNull to check for null values before calling this method."

Instead of:
VB
If Not rowsReader.GetString("Null") = "YES" Then
     query += " NOT NULL"
End If

try:
VB
If Not rowsReader.IsDBNull("Null") Then
    If Not rowsReader.GetString("Null") = "YES" Then
        query += " Not Null"
    End If
End If


Looking at some resources, I believe you could also write
VB
If (If(Not rowsReader.IsDBNull("Null"), Not rowsReader.GetString("Null") = "YES", False)) Then
  query += " Not Null"
End If
 
Share this answer
 
v2
Comments
jleonorlane 25-Jun-10 0:16am    
I still got an Error "Conversion from String "NULL" to type 'Integer' is not valid."
jleonorlane 25-Jun-10 0:22am    
i tried this one :
If Not rowsReader.IsDBNull("Null").ToString Then
If Not rowsReader.GetString("Null").ToString = "YES" Then
query += " NOT NULL"
End If
End If

but still, i got the same error from the top.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900