无法更新已连接的表 [英] Cannot update a connected table

问题描述

Private Sub UpdateTable（ReportName As String）不会抛出错误，但它也不会更新表格。



我知道这是次要的，但是无法理解。



感谢您的帮助。



我尝试过：

The Private Sub UpdateTable(ReportName As String) does not throw an error, but it also does not update the table.

I know it's something minor, but cannot figure it out.

Thanks for any help.

What I have tried:

Option Compare Text
Imports System.Globalization
Imports System.IO
Imports System.Text
Imports Renci
Imports Renci.SshNet
Imports Renci.SshNet.Sftp
Imports System.Data.OleDb
Public Class Form1
    '
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim cnnOLEDB As New OleDbConnection
    Dim dbProvider As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Public HomeDir As String = Directory.GetCurrentDirectory & "\"
    Dim TheDatabase As String = "AddressBook.mdb"
    Dim dbSource As String = "Data Source = " & HomeDir & TheDatabase & ";Jet OLEDB:System Database=" & HomeDir & "AddressBook.mdw;User ID = End_User;"

    Private host As String = ""
    Private port As Integer = 0
    Private username As String = ""
    Private password As String = ""
    Private remoteDirectory As String = ""
    Private encryptcode As String = ""
    Private files As String = ""
    Dim directoryinfo
    Dim f
    Dim sfilename As String
    Dim strExt As String
    Dim strQuery As String = "Select * from usysConnect"

    ' Connect to remote site And download filenames.  If extension matches, write to a table. 
    Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            If Not IO.File.Exists(HomeDir & TheDatabase) Then
                MessageBox.Show("Database not found")
            Else
                cnnOLEDB.ConnectionString = dbProvider & dbSource
                cnnOLEDB.Open()

                Dim cmd As OleDbCommand = Nothing
                Dim reader As OleDbDataReader = Nothing
                cmd = New OleDbCommand("Select * From UsysConnect", cnnOLEDB)
                reader = cmd.ExecuteReader()
                While reader.Read()
                    host = IIf(reader.IsDBNull(0), "", reader.GetValue(0))
                    port = IIf(reader.IsDBNull(1), 0, reader.GetValue(1))
                    username = IIf(reader.IsDBNull(2), "", reader.GetValue(2))
                    password = IIf(reader.IsDBNull(3), "", reader.GetValue(3))
                    encryptcode = IIf(reader.IsDBNull(4), "", reader.GetValue(4))
                End While

                Using sftp As SftpClient = New SftpClient(host, port, username, password)
                    sftp.Connect()
                    directoryinfo = sftp.ListDirectory(remoteDirectory)
                    For Each f In directoryinfo
                        sfilename = (f.Name)
                        strExt = Path.GetExtension(sfilename)
                        If strExt = ".zip" Then
                            UpdateTable(sfilename)
                        End If
                    Next f
                    sftp.Disconnect()
                End Using
            End If
        Catch ex As Exception
            MsgBox(ex.Message & " -  " & ex.Source)
        End Try
        If Not IsNothing(cnnOLEDB) Then
            cnnOLEDB.Close()
        End If

        Me.Close()
    End Sub

    Private Sub UpdateTable(ReportName As String)
        Dim Str As String = ""
        Dim cmd As OleDbCommand = Nothing
        If Not IO.File.Exists(HomeDir & TheDatabase) Then
            MessageBox.Show("Database not found")
        Else
            Try
                cmd = New OleDbCommand("Select * From Usysreportname", cnnOLEDB)
                Str = "UPdate() UsysReportName SET ReportName =" & ReportName
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show("Could Not Insert Record!!!")
                MsgBox(ex.Message & " -  " & ex.Source)
                cnnOLEDB.Close()
            End Try
        End If
    End Sub
End Class

推荐答案

仔细查看TRY块：

第1行：将OleDbCommand（cmd）设置为SELECT语句

第2行：你将Str设置为UPDATE查询。

第3行：执行cmd语句，而不是存储在Str中的UPDATE

Look carefully at the TRY block:
Line 1: You set your OleDbCommand (cmd) to be a SELECT statement
Line 2: You set Str to be the UPDATE query.
Line 3: You execute the cmd statement, not the UPDATE stored in Str

Try
  cmd = New OleDbCommand("Select * From Usysreportname", cnnOLEDB)
  Str = "UPdate() UsysReportName SET ReportName =" & ReportName
  cmd.ExecuteNonQuery()
Catch ex As Exception

除了MadMyche的回答，即使您执行了 UPDATE 查询，仍然会有一些问题。

• 单词 UPdate 后面的括号是语法错误;
• 新的报告名称值是没有被引号括起来;
• 文件名可以包含可能触发 SQL注入 [ ^ ]漏洞;

In addition to MadMyche's answer, even if you had executed the UPDATE query, there would still be some problems.

• The brackets after the word UPdate are a syntax error;
• The new report name value is not surrounded by quotes;
• Filenames can contain characters which could trigger a SQL Injection[^] vulnerability;

Private Sub UpdateTable(ByVal ReportName As String)
    If Not IO.File.Exists(HomeDir & TheDatabase) Then
        MessageBox.Show("Database not found")
        Return
    End If
    
    Try
        Using cmd As New OleDbCommand("UPDATE UsysReportName SET ReportName = ?", cnnOLEDB)
            cmd.Parameters.AddWithValue("ReportName", ReportName)
            cmd.ExecuteNonQuery()
        End Using
    Catch ex As Exception
        MessageBox.Show("Could Not Update Record!!!")
        MessageBox.Show(ex.Message & " -  " & ex.Source)
    End Try
End Sub

这篇关于无法更新已连接的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！