Cannot update a connected table

Question

0.00/5 (No votes)

See more:

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

Posted 1-Apr-19 12:42pm

T Gorman

Updated 4-Apr-19 7:12am

Add a Solution

Comments

T Gorman 1-Apr-19 20:00pm

Can you be more specific? I've tried various code to make it work e.g.

cmd = New OleDbCommand("UPDATE * From Usysreportname", cnnOLEDB)
Str = "UPDATE() UsysReportName SET ReportName =" & ReportName
cmd.ExecuteNonQuery()

You infer I'm mixing up SELECT and UPDATE statements. Could you specifically quote the correct code please?

MadMyche 2-Apr-19 7:03am

UPDATE * From Usysreportname is not a valid SQL statement.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-04-01T13:10:00

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

VB

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

Richard Deeming · Answer 2 · 2019-04-04T07:13:00

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;

Fixed code:

VB.NET

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

Cannot update a connected table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0