PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] VB.Net, MySQL, TextBoxes and Parameters-VBForums
Results 1 to 8 of 8

Thread: [RESOLVED] VB.Net, MySQL, TextBoxes and Parameters

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    17

    Resolved [RESOLVED] VB.Net, MySQL, TextBoxes and Parameters

    Hi Guys,

    I have been struggling for a number of hours with using parameters when querying MySQL database.

    I have a basic form with TextBoxes that send and retrieve data from the database and I understand the need to use parameters to make sure I do not cause any issues with regards to SQL Injection.

    The sending of data from TextBoxes to MySQL server is working fine.. but I cannot figure out how to pull the data from the server into the TextBoxes. I have "Find Record" function that works (but is not using parameters, so I am getting a warning/error is an apostrophe ( ' ) is with the data that is being pulled.

    Here is my working code for "Add" / INSERT Command (with parameters):

    Code:
    Public Function addRecord()
            Dim iReturn As Boolean
            Using SQLConnection As New MySqlConnection(My.Settings.ConnString)
                Using sqlCommand As New MySqlCommand()
                    With sqlCommand
                        .CommandText = "INSERT INTO addresses & _
                            "(`deceased_id`," & _
                            "`deceased_name`," & _
                            "`deceased_surname`," & _
                            "`deceased_address1`," & _
                            "`deceased_address2`," & _
                            "`deceased_address3`," & _
                            "`deceased_pc`)" & _
                            "values " & _
                            "(@did," & _
                            "@dname," & _
                            "@dsurname," & _
                            "@daddress1," & _
                            "@daddress2," & _
                            "@daddress3," & _
                            "@dpc,)"
    
                        .Connection = SQLConnection
                        .CommandType = CommandType.Text
                        '.CommandTimeout = 500000
                        .Parameters.AddWithValue("@did", TextBox_DId.Text)
                        .Parameters.AddWithValue("@dname", TextBox_DName.Text)
                        .Parameters.AddWithValue("@dsurname", TextBox_DSurname.Text)
                        .Parameters.AddWithValue("@daddress1", TextBox_DAddress1.Text)
                        .Parameters.AddWithValue("@daddress2", TextBox_DAddress2.Text)
                        .Parameters.AddWithValue("@daddress3", TextBox_DAddress3.Text)
                        .Parameters.AddWithValue("@dpc", TextBox_DPC.Text)
                    End With
    
                    Try
                        SQLConnection.Open()
                        sqlCommand.ExecuteNonQuery()
                        iReturn = True
                        MessageBox.Show("Record Saved", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                        Button_Save.Enabled = False
    
                    Catch ex As MySqlException
                        MsgBox(ex.Message.ToString)
                        iReturn = False
                    Finally
                        SQLConnection.Close()
                    End Try
                End Using
            End Using
            Return iReturn
    
        End Function
    This works fine and I believe is using the parameters correctly.

    What I am hoping someone can help me with is assisting with the code that would pull the data from the server and populate the TextBoxes?

    Below is my code for the "Find" / Select Command, which works but not with parameters:

    Code:
    Public Function findRecord()
            Dim iReturn As Boolean
            Dim dt As DataTable
            Dim READER As MySqlDataReader
    
            Using SQLConnection As New MySqlConnection(My.Settings.ConnString)
                Using sqlCommand As New MySqlCommand()
                    With sqlCommand
                        .CommandText = "SELECT * FROM addresses WHERE" & _
                            "`deceased_id`= " & _
                            "@did"
                        .Connection = SQLConnection
                        .CommandType = CommandType.Text
                        .Parameters.AddWithValue("@did", TextBox_DId.Text)
                    End With
    
                    Try
                        SQLConnection.Open()
    
                        READER = sqlCommand.ExecuteReader()
                        dt = New DataTable()
                        dt.Load(READER)
                        If (dt.Rows.Count > 0) Then
                            'If Not READER.IsDBNull(0) Then
                            TextBox_DId.DataBindings.Add("Text", dt, "deceased_id")
                            TextBox_DName.DataBindings.Add("Text", dt, "deceased_name")
                            TextBox_DSurname.DataBindings.Add("Text", dt, "deceased_surname")
                            TextBox_DAddress1.DataBindings.Add("Text", dt, "deceased_address1")
                            TextBox_DAddress2.DataBindings.Add("Text", dt, "deceased_address2")
                            TextBox_DAddress3.DataBindings.Add("Text", dt, "deceased_address3")
                            TextBox_DPC.DataBindings.Add("Text", dt, "deceased_pc")
                        Else
                            MessageBox.Show("No Record with ID: " & TextBox_DId.Text & " found", "Error")
                        End If
    
                        iReturn = True
    
                    Catch ex As MySqlException
                        MsgBox(ex.Message.ToString)
                        iReturn = False
                    Finally
                        SQLConnection.Close()
                    End Try
                End Using
            End Using
            Return iReturn
    
        End Function
    Any assistance with turning this into a parameter'd version would be greatly appreciated!

    Thanks,
    James

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: VB.Net, MySQL, TextBoxes and Parameters

    Um, that second code snippet IS using parameters. Given that you have this line:
    Code:
    .Parameters.AddWithValue("@did", TextBox_DId.Text)
    that's a bit of a giveaway.

    My question would be, what data type is the 'deceased_id' column? When you use AddWithValue, the data type of the parameter is inferred from the value you provide. You are providing a String so the data will be inferred to be a text data type of some sort. That's bad if your column has a numeric data type. It's not even good if your column is a text data type because it will always infer 'nvarchar' with a size equal to the Length of the String. That will often not be an issue but there are times that it will. If you're going to use AddWithValue than at least make sure that the value you provide is an appropriate data type, e.g. if you are getting a numner from a TextBox, be sure to actually convert the text to a number. Better still, don't use AddWithValue at all. Use Add and specify the data type explicitly, along with the size if appropriate. You can then set the Value property of the parameter, e.g.
    vb.net Code:
    1. .Parameters.Add("@did", SqlDbType.Int).Value = CInt(TextBox_DId.Text)
    2. .Parameters.Add("@dname", SqlDbType.VarChar, 50).Value = TextBox_DName.Text
    There are a number of other improvements that could be made to that code but I don't have time to address them right now.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    17

    Re: VB.Net, MySQL, TextBoxes and Parameters

    Hi jmcilhinney,

    Thanks for your response and guidance. I understand my code is not great, but works for the purpose I created it, (well.. kinda not, since I am here!)

    I realise I am using a parameter for the DId value only (in my SELECT Statement), but not for my other values.

    The issue I am receiving is that any of the Address fields may contain the apostrophe.. so I need to be using parameters for them too. I just can't get my head around how to assign the database data to the TextBox.Text values.

    With just implementing the code you suggested, I am getting an error of "Unknown data type" and I think the reason is because I am not assigning the database values to the parameters anywhere?

    Is this something to do with my SELECT SQL Statement?

    Code:
    .CommandText = "SELECT * FROM addresses WHERE `deceased_id` = @did, `)"
    Code:
    .Parameters.Add("@dname", SqlDbType.VarChar, 50).Value = TextBox_DName.Text
    .Parameters.Add("@daddress1", SqlDbType.VarChar, 50).Value = TextBox_DAddress1.Text
    .Parameters.Add("@daddress2", SqlDbType.VarChar, 50).Value = TextBox_DAddress2.Text
    .Parameters.Add("@daddress3", SqlDbType.VarChar, 50).Value = TextBox_DAddress3.Text
    .Parameters.Add("@dpc", SqlDbType.VarChar, 50).Value = TextBox_DPC.Text
    Do I need to assign values to the parameters within the SELECT Statement first?

    Thanks again for you help,
    James

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: VB.Net, MySQL, TextBoxes and Parameters

    There's a lot wrong here. I suggest that you follow the Blog link in my signature and check out my post on Parameters In ADO.NET and see if things are any clearer after that. Keep in mind that parameters are generally used for getting values INTO SQL code. They can be used to get data out but that is much less common and there's no call for it here. When you execute a SELECT statement and get a result set, that has nothing do with parameters. Read my blog post and then, if you are still having issues, post back with a FULL and CLEAR explanation of EXACTLY what you're trying to achieve. Don't ask about using parameters for address values and the like without explaining what the relevance is, i.e. are you trying to filter the query by those values or are they the values you expect to be output as a result of the query?

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    17

    Re: VB.Net, MySQL, TextBoxes and Parameters

    Hi jmcilhinney,

    Thanks for the reference, your blog explained the useof parameters well.. I understand more now.

    From the initial research I did before posting here, I saw the best way to handle data is with parameters but did not realise that this was mainly used for INSERT statements.

    My find function is designed to select a record from the database filtered by the ID with is also the PK of the record. There are actually close to 40 fields that are associated to each record and once found these should populate into the TextBoxes/ComboBoxes. Please see image below:

    Name:  1.png
Views: 26
Size:  34.1 KB

    The error occurs when I input a value that contains an apostrophe, see below:

    Name:  2.png
Views: 24
Size:  38.0 KB

    As I do with the AddRecord function of sending the data in the TextBoxes/ComboBoxes to the database, with the FindRecord function I am just trying to read them back.

    I hope this makes sense?

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: VB.Net, MySQL, TextBoxes and Parameters

    Quote Originally Posted by jazimo View Post
    I saw the best way to handle data is with parameters but did not realise that this was mainly used for INSERT statements.
    That's not true. They are and should be used with ANY SQL statement that needs variable values. With an INSERT statement, you use them to specify any field values that are not constant and the same goes for UPDATE statements. For SELECT statements, you use them to specify any filter values in the WHERE clause. They can be used in any other statement where a value is required too.
    Quote Originally Posted by jazimo View Post
    My find function is designed to select a record from the database filtered by the ID with is also the PK of the record.
    So you only need one parameter, which you already have, so what's the actual problem?
    Quote Originally Posted by jazimo View Post
    There are actually close to 40 fields that are associated to each record and once found these should populate into the TextBoxes/ComboBoxes.
    And those field values are output, not input, so they have nothing at all to do with parameters. You execute the query either by calling ExecuteReader on the command and then getting the field values from the data reader returned or you call Fill on a data adapter to populate a DataTable. Either way, not a parameter issue.
    Quote Originally Posted by jazimo View Post
    The error occurs when I input a value that contains an apostrophe
    Then you're doing it wrong but you've never actually shown us the code that is causing this error.
    Quote Originally Posted by jazimo View Post
    As I do with the AddRecord function of sending the data in the TextBoxes/ComboBoxes to the database, with the FindRecord function I am just trying to read them back.
    So just read them back then. An apostrophe can only be an issue if you're putting data into a SQL statement, not if you're getting data out of a result set. You might benefit by following the CodeBank link in my signature below and checking out my thread on Retrieving & Saving Data, which provides code examples of the most common ADO.NET scenarios.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2014
    Posts
    17

    Re: VB.Net, MySQL, TextBoxes and Parameters

    Hi jmcilhinney,

    Your responses prompted me to look again at my functions for pulling in data. I had been so fixated on the apostrophe in the ComboBox I had assumed (wrongly) that the apostrophe in combination with retrieving of the data was the issue and therefore concluded (wrongly) that the solution required the use of parameters for all data.

    It turns out I was not using a parameter in a different function that is also called when the Find Button was clicked!

    After updating this function I no longer receive the error!

    Thank you for your assistance and patience with me.


    Best regards,
    James

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: VB.Net, MySQL, TextBoxes and Parameters

    All's well that ends well.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width