Database Command Not working

[UF_Supra]

Hey guys,

I'm trying to run an Update statement through VB.Net to my database, code is as follows:

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jcarney\My Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Relearn.mdb"
    Dim cnnDatabase As New OleDb.OleDbConnection(strConnectionString)
 
Private Sub btnScrambler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnScrambler.Click
        tableName = "CustomerID"
        Dim myCommand As New OleDb.OleDbCommand
        Try
            cnnDatabase.Open()
            myCommand.Connection = cnnDatabase
            myCommand.CommandText = "UPDATE CustomerID " & _
                                    "SET [Name]= REPLACE(Name,'M','u') " & _
                                    "WHERE CustomerID;"
 
            myCommand.ExecuteNonQuery()
 
            Dim strSQL = "SELECT * FROM " & tableName
            Dim adpDatabase As New OleDb.OleDbDataAdapter(strSQL, cnnDatabase)
            Dim dstRelearn As New DataSet
            adpDatabase.Fill(dstRelearn, "Parts")
 
            Dim rowParts As DataRow
            Dim litParts As ListViewItem
            For Each rowParts In dstRelearn.Tables("parts").Rows
                litParts = lvwTable.Items.Add(rowParts("CustomerID"))
                litParts.SubItems.Add(rowParts("Name"))
                litParts.SubItems.Add(rowParts("Address"))
                litParts.SubItems.Add(rowParts("City"))
            litParts.SubItems.Add(rowParts("State"))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnnDatabase.Close()
        End Try
    End Sub

I keep getting the following error:

"Undefined function 'REPLACE' in expression."

any help would greatly be appreciated.

[TokersBall_CDXX]

wouldn't you want.

myCommand.CommandText = "UPDATE CustomerID " & _
                                    "SET [Name[B]]=" & REPLACE(Name,'M','u')  [/B] & _
                                    " WHERE CustomerID;"

?

[UF_Supra]

thanks, got rid of that error, but now:

myCommand.CommandText = "UPDATE CustomerID " & _
                                        "SET [Name]=" & REPLACE(Name, "M", "u")   & _
                                        " WHERE CustomerID;"

gives me the error:

"Syntax error (missing operator) in query expression 'Form1WHERE CustomerID'"

Any help here?

[si_the_geek]

Strings in SQL need to be surrounded by ' characters, eg:

"SET [Name]= '" & REPLACE(Name, "M", "u") & "'"  & _

[UF_Supra]

Thanks so much for helping me out guys,

Now when my form loads and I run the SQL statement it changes all values in my "Name" column to "Form1" instead of just changing the "M"'s to "u"'s???

[si_the_geek]

Ah, I see what you want now.. to do that you need to use functions that are specific to the DBMS you are using. As you are using Access, the Replace function as you originally had it (the version that TokersBall_CDXX and I posted uses the name of the form, instead of the field called name) should be fine.. I can't think of any alternatives right now.

[UF_Supra]

myCommand.CommandText = "UPDATE CustomerID " & _
                                    "SET [Name]= REPLACE(Name,'M','u') " & _
                                    "WHERE CustomerID;"

so you're saying this should work? (It throws a "Undefined function 'Replace' in expression" error when I try to run it after my form loads)

[sevenhalo]

My guess is, if you think Name is a reserved word (I don't remember for sure in Access), be consistent on making it literal:

myCommand.CommandText = "UPDATE CustomerID " & _
                                    "SET [Name]= REPLACE([Name],'M','u') " & _
                                    "WHERE CustomerID;"

EDIT:
I just looked it up:
http://support.microsoft.com/kb/q209187/
It is indeed reserved.

[UF_Supra]

I've done this already, thanks for pointing it out though, and I still get the same error...

[si_the_geek]

You need to use an alternative to REPLACE, but I'm having problems thinking of any.. I'll have a play around and post back later.

[UF_Supra]

Thank you, I really appreciate all the help guys!!

[UF_Supra]

Sidenote,

Is there a way to take a value from the table and pass it into an array. For example:

Dim strNames As String
Dim aryNames()
 
Try
            cnnDatabase.Open()
            cmdDatabase.Connection = cnnDatabase
            cmdDatabase.CommandText = "SELECT * FROM CustomerID WHERE CustomerID = 1001;"
 
            Dim rdrMyReader As OleDb.OleDbDataReader = cmdDatabase.ExecuteReader
 
            rdrMyReader.Read()
            strNames = rdrMyReader("Name")
 
'Then take the strNames string and pass it into the array aryNames() such that each character occupies one space.  So if the name was "Sue" it would be
aryNames(0) = S
aryNames(1) = u
aryNames(2) = e

???

[sevenhalo]

You can turn any string into a character array by using the String.ToCharArray method.

[Lasering]

Hi!! Just a ideia why dont u reduce ur code instead of

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jcarney\My Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Relearn.mdb"
    Dim cnnDatabase As New OleDb.OleDbConnection(strConnectionString)
 
Private Sub btnScrambler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnScrambler.Click
        tableName = "CustomerID"
        Dim myCommand As New OleDb.OleDbCommand
        Try
            cnnDatabase.Open()
            myCommand.Connection = cnnDatabase
            myCommand.CommandText = "UPDATE CustomerID " & _
            "SET [Name]= REPLACE(Name,'M','u') " & _
            "WHERE CustomerID;"
            myCommand.ExecuteNonQuery()
 
            Dim strSQL = "SELECT * FROM " & tableName
            Dim adpDatabase As New OleDb.OleDbDataAdapter(strSQL, cnnDatabase)
            Dim dstRelearn As New DataSet
            adpDatabase.Fill(dstRelearn, "Parts")
 
            Dim rowParts As DataRow
            Dim litParts As ListViewItem
            For Each rowParts In dstRelearn.Tables("parts").Rows
                litParts = lvwTable.Items.Add(rowParts("CustomerID"))
                litParts.SubItems.Add(rowParts("Name"))
                litParts.SubItems.Add(rowParts("Address"))
                litParts.SubItems.Add(rowParts("City"))
            litParts.SubItems.Add(rowParts("State"))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnnDatabase.Close()
        End Try
    End Sub

U could put:

Dim cnnDatabase As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jcarney\My Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Relearn.mdb")
Private Sub btnScrambler_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnScrambler.Click
        Dim myCommand As New OleDb.OleDbCommand("UPDATE CustomerID " & "SET [Name]= REPLACE(Name,'M','u') " & "WHERE CustomerID;", cnnDataBase
        Try
            cnnDatabase.Open()
            myCommand.ExecuteNonQuery()
 
            Dim adpDatabase As New OleDb.OleDbDataAdapter("SELECT * FROM CustomerID", cnnDatabase)
            Dim dstRelearn As New DataSet
            adpDatabase.Fill(dstRelearn, "Parts")
 
            Dim rowParts As DataRow
            Dim litParts As ListViewItem
            For Each rowParts In dstRelearn.Tables("parts").Rows
                litParts = lvwTable.Items.Add(rowParts("CustomerID"))
                litParts.SubItems.Add(rowParts("Name"))
                litParts.SubItems.Add(rowParts("Address"))
                litParts.SubItems.Add(rowParts("City"))
            litParts.SubItems.Add(rowParts("State"))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnnDatabase.Close()
        End Try
    End Sub

It's just a ideia of mine...

[si_the_geek]

Both of the following work fine for me in Access (I cant test your code at the moment):

"SET [Name]= REPLACE([Name],'M','u') " & _

"Left([Name], Instr(1,[Name],'M')-1) & 'u' & " & _
            "Mid(FName, Instr(1,FName,'M')+1)

..the second one only changes one character tho, so you'll need to run it repeatedly until no records are changed (to check this you will also need to use a Where clause that limits to values that contain the letter).

[UF_Supra]

Ok so I've changed my plan of attack on this...

My question now is... is there a way to step through an entire column of information without knowing what the last field's value is going to be.

For example: repeat until Field value = null... or something like that?

Here's my new code (using two List boxes so I can quickly double check that operations/functions work correctly)...

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jcarney\My Documents\Visual Studio Projects\WindowsApplication2\WindowsApplication2\Relearn.mdb"
    Dim cnnDatabase As New OleDb.OleDbConnection(strConnectionString)
    Dim cmdDatabase As New OleDb.OleDbCommand
    Dim tableName As String
    Sub frmThinker_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            cnnDatabase.Open()
            tableName = "CustomerID"
 
            Dim strSQL = "SELECT * FROM " & tableName
            Dim adpDatabase As New OleDb.OleDbDataAdapter(strSQL, cnnDatabase)
 
            Dim dstRelearn As New DataSet
            adpDatabase.Fill(dstRelearn, "Parts")
 
            Dim rowParts As DataRow
            Dim litParts As ListViewItem
            For Each rowParts In dstRelearn.Tables("parts").Rows
                litParts = lvwOne.Items.Add(rowParts("CustomerID"))
                litParts.SubItems.Add(rowParts("Name"))
                litParts.SubItems.Add(rowParts("Address"))
                litParts.SubItems.Add(rowParts("City"))
                litParts.SubItems.Add(rowParts("State"))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnnDatabase.Close()
        End Try
    End Sub
 
    Sub btnButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnButton.Click
        Dim strNames As String
        Dim aryNames() As Char
        tableName = "CustomerID"
 
        Try
            '***Convert Values
            cnnDatabase.Open()
            cmdDatabase.Connection = cnnDatabase
            cmdDatabase.CommandText = "SELECT * FROM CustomerID WHERE CustomerID = 1001;"
 
            Dim rdrMyReader As OleDb.OleDbDataReader = cmdDatabase.ExecuteReader
            rdrMyReader.Read()
            strNames = rdrMyReader("Name")
            aryNames = strNames.ToCharArray()
 
            Dim intArrayCounter As Integer = 0
            Dim intCurrent As Short = 0
            Dim intNew As Short = 0
 
            Do Until intArrayCounter = 10
                If aryNames(intArrayCounter) = "M" Then
                    aryNames(intArrayCounter) = "u"
               End If
                intArrayCounter += 1
            Loop
            
            cnnDatabase.Close()
 
 
            '***Update Database
            cnnDatabase.Open()
            Dim myCommand As New OleDb.OleDbCommand
            myCommand.Connection = cnnDatabase
            myCommand.CommandText = "UPDATE CustomerID SET [Name]= '" & aryNames & "' WHERE CustomerID = 1001;"
            myCommand.ExecuteNonQuery()
            cnnDatabase.Close()
 
 
 
            '***Fill Second Grid
            cnnDatabase.Open()
            Dim strSQL = "SELECT * FROM " & tableName
            Dim adpDatabase As New OleDb.OleDbDataAdapter(strSQL, cnnDatabase)
 
 
            Dim dstRelearn As New DataSet
            adpDatabase.Fill(dstRelearn, "Parts")
 
            Dim rowParts As DataRow
            Dim litParts As ListViewItem
            For Each rowParts In dstRelearn.Tables("parts").Rows
                litParts = lvwTwo.Items.Add(rowParts("CustomerID"))
                litParts.SubItems.Add(rowParts("Name"))
                litParts.SubItems.Add(rowParts("Address"))
                litParts.SubItems.Add(rowParts("City"))
                litParts.SubItems.Add(rowParts("State"))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cnnDatabase.Close()
        End Try
 
    End Sub