• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

VB.net comman.text - Database not updating

Hello, I’m putting together a .net application that uses excel as its database. I’m trying to get my application update records in the excel database. I’m able to read data from the database using my application so it’s setup correctly. Can someone please assist me with this? When I run my application I’m getting “Unable to Submit” (My error).  It might be my syntax and I’m not sure how to code the Comm.Command.text = Update…. Command. Please see my code below.

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles SubmitTime.Click

        If Not TotalHrs.Text = "" Then

            Dim Conn As New OleDbConnection
            Dim Comm As New OleDbCommand
            Conn.ConnectionString = CreateConnString("c:\Data\TimeData.xls")
            Comm.Connection = Conn
            Comm.CommandText = "update [TimeData$] set '" & ComboBox1.Text & "'='" & TotalHrs.Text & "' where Ename = '" & EmpName.Text & "'"
                MsgBox("Time Submitted Sucessfully.")
            Catch ex As Exception
                MsgBox("Unable to Submit.")
            End Try

            MsgBox("Please calculate your time before submitting")
        End If

    End Sub

Open in new window

3 Solutions
Paul JacksonSoftware EngineerCommented:
If you run the program in debug can you see what the value of ex is when it hits the line where you display 'Unable To Submit'

or maybe change the line :

MsgBox("Unable to Submit.")

MsgBox("Unable to Submit - " & ex.Message)

so you can see what the actual error is
Paul MacDonaldDirector, Information SystemsCommented:
Additionally, how certain are you "ComboBox1.Text" will always be a valid field name?
there is a open source  code you my be able to use a little easier. It reads to a excel
sheet by using a dataset.
AnthonySmithMCPAuthor Commented:
Thanks everyone for the help but the problem was I need a space after the = sign..

In my code:
 Comm.CommandText = "update [TimeData$] set '" & ComboBox1.Text & "'='" & TotalHrs.Text & "' where Ename = '" & EmpName.Text & "'"

Open in new window

But it needed to be:

            Comm.CommandText = "Update [TimeData$] SET " & ComboBox1.Text & "= '" & TotalHrs.Text & "' where Ename = '" & EmpName.Text & "'"

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now