Results 1 to 3 of 3

Thread: Using a Parameter with DataGridView

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2015
    Posts
    111

    Using a Parameter with DataGridView

    I have a question about using parameters with a datagridview. So I probably need to start out by describing what my project is. There is a miniatures game for Star Wars X-Wing. So basically you build a squadron of ships from Star Wars and fight it out. So you could say put Luke Skywalker in an X-Wing and then add upgrades to the ship such as putting R2-D2 in it too. I know there are other squad builders online, but I wanted to see if I could build one myself. I think it's fun and I am enjoying the challenge.
    Their website is: https://www.fantasyflightgames.com/en/products/x-wing/

    So that brings me to my problem. Some ships have a title (like the Millennium Falcon) that can only be added to one type of ship. So I do is first select a ship, choose a pilot, and click a button for the appropriate upgrade. That should populate a datagridview with that info. It works, but I'm not using parameters to get the datagridview with the 4 to populate. Here's a screen shot of my form.
    Name:  XWing1.jpg
Views: 693
Size:  32.1 KB

    And my code:

    Code:
        Private Sub dgvUpgradeList_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvUpgradeList.CellContentClick
    
            Try
    
                'Load the data grid with from qrySquadBuild
                Dim connetionUpgrades As String = Nothing
                Dim OleDBconnectionUpgrades As OleDbConnection
                Dim commandUpgrades As OleDbCommand
                Dim adapterUpgrades As New OleDbDataAdapter()
                Dim dsUpgrades As New DataTable
                Dim sqlUpgrades As String = Nothing
    
                dgvUpgrades.Columns.Clear()
    
                connetionUpgrades = strXWingConnections
    
                'If the button clicked was astromech, load the data grid for Astromechs.
                If e.RowIndex = 0 Then
                    sqlUpgrades = "SELECT * FROM qryAstromechs"
           
                   'If the button clicked was Title, load the data grid for Title.
                ElseIf e.RowIndex = 14 Then
                    ' sqlUpgrades = "SELECT ModificationName, ModificationText, ModificationCost FROM qryTitle WHERE ShipID = @ShipID"
                    sqlUpgrades = "SELECT ModificationName, ModificationText, ModificationCost FROM qryTitle WHERE ShipID = " & dgvCurrentShip.CurrentRow.Cells("ShipID").Value
    
    
                End If
    
                OleDBconnectionUpgrades = New OleDbConnection(connetionUpgrades)
    
                OleDBconnectionUpgrades.Open()
                commandUpgrades = New OleDbCommand(sqlUpgrades, OleDBconnectionUpgrades)
                commandUpgrades.Parameters.AddWithValue("@FactionID", dgvCurrentShip.CurrentRow.Cells("FactionID").Value)
                commandUpgrades.Parameters.AddWithValue("@ShipID", dgvCurrentShip.CurrentRow.Cells("ShipID").Value)
                adapterUpgrades.SelectCommand = commandUpgrades
                adapterUpgrades.Fill(dsUpgrades)
                adapterUpgrades.Dispose()
                commandUpgrades.Dispose()
                OleDBconnectionUpgrades.Close()
                dgvUpgrades.DataSource = dsUpgrades
                'Set the datagrid to wrap
                dgvUpgrades.Columns("ModificationText").DefaultCellStyle.WrapMode = DataGridViewTriState.True
                dgvUpgrades.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
    
                Dim column As DataGridViewColumn = dgvUpgrades.Columns("ModificationText")
                column.Width = 240
    
                Dim btn As New DataGridViewButtonColumn
                dgvUpgrades.Columns.Add(btn)
                btn.HeaderText = "Add Upgrade"
                btn.Text = "Add Upgrade"
                btn.Name = "btn"
                btn.UseColumnTextForButtonValue = True
    
            Catch ex As Exception
                'Display the error
                'Console.WriteLine(ex.Message)
                MessageBox.Show("Error loading data grid: " & ex.Message)
                'Log the error into LogException table
    
                Exit Sub
            End Try
    
        End Sub
    So, like I said I'm not using a parameter, which I know is bad. But if I try to use one and use the line
    Code:
    sqlUpgrades = "SELECT ModificationName, ModificationText, ModificationCost FROM qryTitle WHERE ShipID = @ShipID"
    I don't get any results in my datagridview. It doesn't throw any errors. Here's a screen shot of that:

    Name:  XWing2.jpg
Views: 616
Size:  28.8 KB

    I'm probably missing something simple, but I can't find what that is. Any help would be appreciated.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Using a Parameter with DataGridView

    When you use parameters you need to somehow specify what data type to use.

    If you use Parameters.Add then you need to explicitly specify the data type you want, but when you use Parameters.AddWithValue it automatically guesses based on the data type of the value you passed in... but it turns out that the data type of .Cells().Value isn't the data type you want.

    Based on your working code, it looks like it should be an Integer, so try this:

    Code:
                commandUpgrades.Parameters.AddWithValue("@ShipID", CInt(dgvCurrentShip.CurrentRow.Cells("ShipID").Value))

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Using a Parameter with DataGridView

    also, the SQL that does use parameters only has one, but you're creating two:
    Code:
                commandUpgrades.Parameters.AddWithValue("@FactionID", dgvCurrentShip.CurrentRow.Cells("FactionID").Value)
                commandUpgrades.Parameters.AddWithValue("@ShipID", dgvCurrentShip.CurrentRow.Cells("ShipID").Value)
    Which, depending on the database you're using could be an issue.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width