如何在datagridview中插入图片并将其转换为数据库VB.NET而不使用参数 [英] How to insert picture in the datagridview and converted it into the database VB.NET without using parameter

问题描述

嗨！我这里有一个代码，可以添加，搜索，删除，清除和插入用户信息的图片。问题是我不知道应该如何以及使用什么语法将图片添加到添加按钮中以将其保存在数据库中并在数据网格视图中显示图片以及用户信息...我希望并提前谢谢对那些可以帮助我的人...谢谢



PS这是我在代码中唯一缺少的，所有这些都是可执行的。它已经可以出现在图片框中，但我还没有在我的数据库中连接，这就是问题所在。我将根据我已经制作的所有代码粘贴到这里，希望这会有所帮助。



我尝试了什么：

Hi! I have a code here which can add,search,delete,clear and insert a picture of users information. The problem is I don't know how and what syntax should I make the picture add in the add button to save it in the database and show the picture in the data grid view along with the users informations...I hope and advance thanks to people that can help me ...Thanks

P.S this is the only thing that i'm missing in my code, all of them is executable. It can already appear in the picture box but I haven't connected yet in my database which that is the problem. I will paste here under all the codes i already made, hope this will help.

What I have tried:

'Imports Microsoft.Win32
Imports System.Data.OleDb
Imports System.IO
'Imports System
'Imports System.Data
'Imports System.Drawing
'Imports System.Drawing.Imaging
'Imports System.Windows.Forms
'Imports Microsoft.VisualBasic
Imports System.DBNull

Public Class ProjectTwo
    Dim cnn As New OleDb.OleDbConnection
    Dim imgName As String
    Dim cmd As New OleDb.OleDbCommand
    Private Sub ProjectTwo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnn = New OleDb.OleDbConnection
        cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\ProjectTwo\ProjectTwoEmployment.accdbProvider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\ProjectTwo\ProjectTwoEmployment.accdb"
        '
        'get data into list
        Me.RefreshData()
    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        Dim cmd As New OleDb.OleDbCommand
        If Not cnn.State = ConnectionState.Open Then
            'open connection if it is not yet open
            cnn.Open()
        End If

        cmd.Connection = cnn
        'check whether add new or update
        If Me.TxtEID.Tag & "" = "" Then
            'add new 
            'add data to table
            cmd.CommandText = "INSERT INTO PersonalInfo (EmploymentID, EmploymentName, DateOfBirth, PlaceOfBirth, Address , Phone , Sex) " & _
                            " VALUES(" & Me.TxtEID.Text & ",'" & Me.TxtEName.Text & "','" & Me.TxtEDate.Text & "','" & _
                            Me.TxtEPlace.Text & "','" & Me.TxtEAdd.Text & "','" & Me.TxtEPhone.Text & "','" & _
                            Me.TxtESex.Text & "')"
            cmd.ExecuteNonQuery()
        Else
            'update data in table
            cmd.CommandText = "UPDATE PersonalInfo " & _
                        " SET EmploymentID =" & Me.TxtEID.Text & _
                        ", EmploymentName='" & Me.TxtEName.Text & "'" & _
                        ", DateOfBirth='" & Me.TxtEDate.Text & "'" & _
                        ", PlaceOfBirth='" & Me.TxtEPlace.Text & "'" & _
                        ", Address='" & Me.TxtEAdd.Text & "'" & _
                        ", Phone='" & Me.TxtEPhone.Text & "'" & _
                        ", Sex='" & Me.TxtESex.Text & "'" & _
                        " WHERE EmploymentID=" & Me.TxtEID.Tag
            cmd.ExecuteNonQuery()
        End If

        'refresh data in list
        Me.RefreshData()
        'clear form
        Me.BtnClear.PerformClick()

        'close connection
        cnn.Close()
    End Sub

    Private Sub RefreshData()
        If Not cnn.State = ConnectionState.Open Then
            'open connection
            cnn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter("SELECT EmploymentID as [EmploymentID], " & _
                                             "EmploymentName as [EmploymentName], DateOfBirth, PlaceOfBirth, Address, Phone , Sex , Photo" & _
                                             " FROM PersonalInfo ORDER BY EmploymentID", cnn)

        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.DataGridView1.DataSource = dt

        'close connection
        cnn.Close()
    End Sub

    Private Sub BtnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear.Click
        Me.TxtEID.Text = ""
        Me.TxtEName.Text = ""
        Me.TxtEDate.Text = ""
        Me.TxtEPlace.Text = ""
        Me.TxtEAdd.Text = ""
        Me.TxtEPhone.Text = ""
        Me.TxtESex.Text = ""
        Me.PictureBox1.Image = Nothing

        Me.RefreshData()

        'enable button edit
        Me.BtnUpdate.Enabled = True
        'set button add to add label
        Me.BtnAdd.Text = "Add"
        '
        Me.TxtEID.Focus()
    End Sub

    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intEID As Integer = Me.DataGridView1.SelectedRows(0).Cells("EmploymentID").Value
                'get data from database followed by employment id
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If
                'get data into datatable
                Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM PersonalInfo " & _
                                                     " WHERE EmploymentID=" & intEID, cnn)
                Dim dt As New DataTable
                da.Fill(dt)

                Me.TxtEID.Text = intEID
                Me.TxtEName.Text = dt.Rows(0).Item("EmploymentName")
                Me.TxtEDate.Text = dt.Rows(0).Item("DateOfBirth")
                Me.TxtEPlace.Text = dt.Rows(0).Item("PlaceOfBirth")
                Me.TxtEAdd.Text = dt.Rows(0).Item("Address")
                Me.TxtEPhone.Text = dt.Rows(0).Item("Phone")
                Me.TxtESex.Text = dt.Rows(0).Item("Sex")

                '
                'hide the id to be edited in TAG of TxtEID in case id is changed
                Me.TxtEID.Tag = intEID
                'change button add to update
                Me.BtnAdd.Text = "UPDATE"
                'disable button edit
                Me.BtnUpdate.Enabled = False
                'close connection
                cnn.Close()
            End If
        End If
    End Sub

    Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        'check for the selected item in list
        If Me.DataGridView1.Rows.Count > 0 Then
            If Me.DataGridView1.SelectedRows.Count > 0 Then
                Dim intEID As Integer = Me.DataGridView1.SelectedRows(0).Cells("EmploymentID").Value
                'open connection
                If Not cnn.State = ConnectionState.Open Then
                    cnn.Open()
                End If

                'delete data
                Dim cmd As New OleDb.OleDbCommand
                cmd.Connection = cnn
                cmd.CommandText = "DELETE FROM PersonalInfo WHERE EmploymentID=" & intEID
                cmd.ExecuteNonQuery()
                'refresh data
                Me.RefreshData()

                'close connection
                cnn.Close()
            End If
        End If
    End Sub

    Private Sub BtnPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPicture.Click
     
        Dim opf As New OpenFileDialog
        opf.Filter = "Choose Image(*.jpg;*.png;*.gif)|*.jpg;*.png;*.gif"

        If opf.ShowDialog = DialogResult.OK Then
            PictureBox1.Image = Image.FromFile(opf.FileName)
        End If
    End Sub

    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click

        'for searching the record in the database 
        Try
            'open the connection
            cnn.Open()
            Dim data = New DataTable
            Dim dataT As New OleDbDataAdapter
            'set your commands for holding the data
            With cmd
                .Connection = cnn
                .CommandText = "Select * from PersonalInfo where EmploymentID like '" & TxtEID.Text & "%'"
            End With
            'filling the table in the database.
            dataT.SelectCommand = cmd
            dataT.Fill(data)
            'put your datasource in the datagridview
            DataGridView1.DataSource = data

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'close the connection
        cnn.Close()

    End Sub

End Class

推荐答案

永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

引用：

是否可以插入图片？不使用参数？

is there possible way that it can be inserted a picture? without using parameters?

不，参数是唯一的解决方案。

No, parameters is the only solution.

Quote：

那么我可以为照片制作一个特殊参数，其余参数不在参数中吗？

Then can I just make a special parameter for the photo and the rest is not in parameters?

您的选择！你知道危险，你知道解决方案。

Your choice ! Your know the danger, you know the solution.

USE PARAMETERS！没有理由不这样做。



你正在使用的字符串连接垃圾很容易受到SQL注入攻击，无论是有意还是无意。



我可以通过在任何一个字段中添加'字符来打破你正在构建的SQL语句。

USE PARAMETERS! There is no excuse not to.

This string concatenation garbage you're using is prone to SQL Injection Attacks, be it intentional or not.

I can break your SQL statements you're building just by putting a ' character in any one of those fields.

这篇关于如何在datagridview中插入图片并将其转换为数据库VB.NET而不使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！