trying to input data into my .NET program through a mySQL database

Question

Hi I am trying to import data from a database into variable in my program but I keep on getting the error:

System.InvalidCastException: 'Conversion from type 'DBNull' to type 'String' is not valid.'

       Dim value As Integer
       Dim MySqlConn As MySqlConnection
       Dim sql As New MySqlCommand

       Dim dataAdapter As New MySqlDataAdapter
       Dim dataFromDB As New DataSet
       Dim numrows As Integer

       MySqlConn = New MySqlConnection
       MySqlConn.ConnectionString = "server=localhost;user id=root;database=gamedata;"

       Try
           MySqlConn.Open()

           sql = New MySqlCommand("SELECT Ccost FROM cards WHERE `UserName` = '" & UserName & "' AND `Game` = '" & game & "'", MySqlConn)

           dataAdapter = New MySqlDataAdapter(sql)
           dataAdapter.Fill(dataFromDB)
           numrows = dataFromDB.Tables(0).Rows.Count

           For counter = 1 To numrows - 1
               value = dataFromDB.Tables(0).Rows(counter).Item(0)
           Next

           sql = New MySqlCommand("Select Level,Health,Score,PlayerTime FROM savedata WHERE `UserName` = '" & UserName & "' AND `Game` = '" & game & "'", MySqlConn)

           dataAdapter = New MySqlDataAdapter(sql)
           dataAdapter.Fill(dataFromDB)
           numrows = dataFromDB.Tables(0).Rows.Count
           
           'For counter = 0 To 1
           level = dataFromDB.Tables(0).Rows(0).Item(0)
           Phealth = dataFromDB.Tables(0).Rows(0).Item(1)
           score = dataFromDB.Tables(0).Rows(0).Item(2)
           time = dataFromDB.Tables(0).Rows(0).Item(3)

          

       Catch ex As MySqlException
           MsgBox("Error " & ex.Message)
       End Try

database code:

use `Gamedata`;

create table `SaveData`
(`GameCode` int AUTO_INCREMENT not null,`Game` enum('1','2','3','4') not null,`UserName` varchar(20) not null,`level` int not null, `Health` int not null,`Score` int not null,`PlayerTime` time not null,

foreign key(`UserName`) REFERENCES `player` (`UserName`),
primary key(`GameCode`));

Answer 1

Here is a slightly different approach. This code requires the following Imports at the top of the file.

Imports System.Data.SqlTypes

You need to declare your database objects with Using blocks so they will be closed and disposed even if there is an error. Queries should always be written using parameters. Not it only does it avoid sql injection but it is easier to read and write.

A database integer can be null but not so in .net. The SqlInt32 has an .IsNull property that we can use for testing. If you try to convert a null string to a string by, for example calling .ToString, you will get an error.

Private ConStr As String = "server=localhost;user id=root;database=gamedata;"

Private Sub OpCode(UserName As String, Game As String)
    Dim value As Integer

    Dim dt As New DataTable

    Dim strSql = "SELECT Ccost FROM cards WHERE `UserName` = @UserName AND `Game` = @Game"
    Try
        Using Sql As New MySqlCommand(strSql, New MySqlConnection(ConStr))
            Sql.Connection.Open()
            Sql.Parameters.Add("@UserName", MySqlDbType.VarChar).Value = UserName
            Sql.Parameters.Add("@Game", MySqlDbType.VarChar).Value = Game
            Dim obj = Sql.ExecuteScalar
            value = If(obj Is Nothing, 0, CInt(obj))
            Sql.CommandText = "Select Level,Health,Score,PlayerTime FROM savedata WHERE `UserName` = @UserName AND `Game` = @Game;"
            Using reader = Sql.ExecuteReader
                dt.Load(reader)
            End Using
        End Using
        Dim level = If(CType(dt.Rows(0)(0), SqlString).IsNull, "", dt.Rows(0)(0).ToString)
        Dim Phealth = If(CType(dt.Rows(0)(1), SqlString).IsNull, "", dt.Rows(0)(1).ToString)
        Dim score = If(CType(dt.Rows(0)(2), SqlInt32).IsNull, 0, CInt(dt.Rows(0)(2)))
        Dim time = If(CType(dt.Rows(0)(3), SqlInt32).IsNull, 0, CInt(dt.Rows(0)(3)))
    Catch ex As MySqlException
        MsgBox("Error " & ex.Message)
    End Try
End Sub