Click here to Skip to main content
15,347,383 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This may be my last problem to solve before the program is finished and it seems to be a common problem but with different parameters, it's usually seen when pulling dates from a database.

My program is simple, it has 2 numupdown controls, a button, and three labels. The user inputs the probability of success in the first numupdown (BoolNum), they put the drop group number in the second numupdown(GroupNum), and they click the button to be told what item they are getting.

When they click the button the program connects to a database containing a list of items and their drop groups. The program first decides whether the player will get an item. If it comes up false the program will tell the player it's false, but if it's true then it searches the database for all items that are in the specified drop group and adds each item to the ItemArray, then chooses one item from the ItemArray at random and displays it in the third label (ItemReceived). The code I am using is presented below.

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Dim DbConnection As New OleDbConnection
    Dim DbCommand As New OleDbCommand
    Dim DbInsert As New OleDbCommand
    Dim DbUpdate As New OleDbCommand
    Dim DbDelete As New OleDbCommand
    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    System.Environment.CurrentDirectory & "\Game Database.mdb"

    Public Function GetRandom(ByVal Min As Integer, ByVal Max As Integer) As Integer
        ' by making Generator static, we preserve the same instance '
        ' (i.e., do not create new instances with the same seed over and over) '
        ' between calls '
        Static Generator As System.Random = New System.Random()
        Return Generator.Next(Min, Max)
    End Function

    Private Sub BoolBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BoolBtn.Click

        If GetRandom(0, 101) < BoolNum.Value Then

            'Try Again was added to ItemArray because it needed an initial value and because
            'GetRandom function does not accept negative values, and is non-inclusive, so it
            'can never generate 0)
            Dim ItemArray() As String = New String(0) {"Try Again"}

            DbConnection = New OleDbConnection(strConnectionString)

                ' Open connection

                'Create the SQL Query that draws out the data
                Dim SqlQry As String = "SELECT [Item Name] FROM [Item List] WHERE [Drop Group] = '" & GroupNum.Value & "'"

                'create data adapter
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(SqlQry, DbConnection)

                'create dataset
                Dim ds As DataSet = New DataSet

                'fill dataset
                da.Fill(ds, "Item List")

                'get data table
                Dim dt As DataTable = ds.Tables("Item List")

                'display data
                Dim row As DataRow

                For Each row In dt.Rows
                    Dim NewItem As New String("Item Name")

                    Array.Resize(ItemArray, ItemArray.Length + 1)
                    ItemArray(ItemArray.Length - 1) = newItem
                Next row
            Catch ex As OleDbException
                MsgBox("Error: " & ex.ToString & vbCrLf)
                ' Close connection

                ItemReceived.Text = GetRandom(0, ItemArray.Length)
            End Try
        End If

    End Sub
End Class

1 solution

Don't concatenate strings and build the sql statement. use Parameters, read DataAdapter Parameters[^]
sample code:

Dim selectSQL As String = _
  "SELECT CustomerID, CompanyName FROM Customers " & _
  "WHERE CountryRegion = ? AND City = ?"
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter 

Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add( _
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add( _
  "@City", OleDbType.VarChar, 15).Value = "London"

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")
KitsunePhoenix 4-Jun-15 1:23am
Thank you, that got me past my problem. I'll have to remember that for next time.
DamithSL 4-Jun-15 1:59am
You are welcome!

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