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`));

Use `COALESCE(columnname, '') AS columnname` instead of single `columnname` in the output list for nullable column of string type.\ — Akina, Mar 02 '21 at 11:04
Side but very important note. This code is very unsecure. Concatenating strings to form an sql command is a well known problem. You could get a syntax error if your strings contains quotes but you are also exposed to an [Sql Injection attack](https://xkcd.com/327/). The consequences of this vulnerability are fatal. Look at how to use a [_parameterized query_](https://stackoverflow.com/questions/4712037/what-is-parameterized-query#:~:text=A%20parameterized%20query%20%28also%20known%20as%20a%20prepared,as%20a%20means%20of%20preventing%20SQL%20injection%20attacks.) — Steve, Mar 02 '21 at 11:09
Better link for parameterized query https://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Steve, Mar 02 '21 at 11:17
@Akina i have tried using that and it moved the error to the line with Phealth but now its only inserting the value of 0 into level is the way I have done this correct: sql = New MySqlCommand("Select COALESCE(Level, '') AS Level, COALESCE(Health, '') AS Health, COALESCE(Score, '') AS Score, COALESCE(PlayerTime, '') AS time FROM savedata WHERE `UserName` = '" & UserName & "' AND `Game` = '" & game & "'", MySqlConn) do you know why this is happening — Charlie, Mar 02 '21 at 12:22

score 0 · Answer 1 · answered Mar 03 '21 at 08:20

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

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

1 Answers1