-
Jan 12th, 2019, 04:47 PM
#1
Thread Starter
Junior Member
[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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|