how to update from textbox to database with oledb VFP in VB.NET-CodePudding

Dear All Programmmer,

if I use the manual the database immediately updates but if I use the textbox the database does not update but only an update messagebox appears. Is there something wrong with the code or any other best recommendations?. I'm using visual studio 2015

Thanks

           Dim pathDBF As String = Application.StartupPath()
        Using cn = New System.Data.OleDb.OleDbConnection(String.Concat("Provider=VFPOLEDB;Data Source=" & pathDBF))
                 Dim tableDBF = "RSD, GSD, GPD, FFG, RPD"
            cn.Open()
            For Each tableName In tableDBF.Split(","c)
                   Using cmd = New OleDbCommand(String.Format("update {0} set Item=? where Item==?", tableDBF.Trim()), cn)
                        cmd.Parameters.Add("Item", OleDbType.VarChar).Value = TextBoxnewitem.Text
                    cmd.Parameters.Add("Item", OleDbType.VarChar).Value = TextBoxolditem.Text
                         cmd.ExecuteNonQuery()
                End Using
                MessageBox.Show("updated", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Next
                cn.Close()
        End Using

CodePudding user response：

When you use parameters (?), you wouldn't use any quotes around them and also be sure you specify them in the order they appear in your SQL string (you did correct):

Dim pathDBF As String = Application.StartupPath()
            Using cn = New System.Data.OleDb.OleDbConnection($"Provider=VFPOLEDB;Data Source={pathDBF}")
                Dim tableDBF = "RSD, GSD, GPD, FFG, RPD"
                cn.Open()
                For Each table In tableDBF.Split(","c)
                    'Using cmd = New OleDbCommand(String.Format("update {0} set Itemproduct=""Drink 1015 Full"" where Itemproduct==""Drink 1015""", tableDBF.Trim()), cn)
                    Using cmd = New OleDbCommand(String.Format("update {0} set Itemproduct=? where Itemproduct==?", table.Trim()), cn)
                        cmd.Parameters.Add("Itemproduct1", OleDbType.VarChar).Value = TextBoxnewitem.Text 'Drink 1015 Full
                        cmd.Parameters.Add("Itemproduct2", OleDbType.VarChar).Value = TextBoxolditem.Text 'Drink 1015
                        cmd.ExecuteNonQuery()
                    End Using
                    MessageBox.Show("updated", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Next
                cn.Close()
            End Using

EDIT: You can also make it more readable writing like this:

Dim sql As String = <sql>update {0} 
    set Itemproduct=? 
    where Itemproduct==?
    </sql>
Using cmd = New OleDbCommand(String.Format(sql, tableDBF.Trim()), cn)
'...

EDIT: Use this code as is:

Dim pathDBF As String = Application.StartupPath()
Dim sql As String = <sql>
update {0} 
set Itemproduct=? 
where Itemproduct==?
</sql>
Dim tableDBF = "RSD, GSD, GPD, FFG, RPD"

Using cn = New System.Data.OleDb.OleDbConnection(string.Format("Provider=VFPOLEDB;Data Source={0}",pathDBF))
    cn.Open()
    For Each table In tableDBF.Split(","c)
        Using cmd = New OleDbCommand(String.Format(sql, table.Trim()), cn)
            cmd.Parameters.Add("Itemproduct1", OleDbType.VarChar).Value = TextBoxnewitem.Text
            cmd.Parameters.Add("Itemproduct2", OleDbType.VarChar).Value = TextBoxolditem.Text
            cmd.ExecuteNonQuery()
        End Using
    Next
    cn.Close()
End Using
MessageBox.Show("updated", "", MessageBoxButtons.OK, MessageBoxIcon.Information)