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

Data type mismatch when trying to update MS Access table via asp.net

I'm received a datatype mismatch when trying to update a Microsoft Access table via my windows application.

I've checked my code and don't see where there is any datatype issue.  Need another set of eyes on it.

Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click

        ' Declare and assign variables

        Dim sqlCon As New OleDbConnection(ConfigurationManager.ConnectionStrings("T_REQS.My.MySettings.TREQSConnectionString").ToString)
        Dim qry As String
        Dim reqid As String = ReqIDTextBox.Text
        Dim title As String = TitleTextBox.Text
        Dim description As String = DescriptionTextBox.Text
        Dim priority As Integer = PriorityTextBox.Text
        Dim status As Integer = StatusTextBox.Text
        Dim category As Integer = CategoryTextBox.Text
        Dim lastmodifieddate As Date = Now
        Dim lastmodifiedby As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name


        qry = "UPDATE Requests SET title = ?, description = ?, priority = ?, status = ?," &
            "category = ?, lastmodifieddate = ?, lastmodifiedby = ? WHERE [reqID] = ?"

        Try
            'Assign values to query variables
            Dim cmd As New OleDbCommand(qry, sqlCon)

            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("?", title)
            cmd.Parameters.AddWithValue("?", description)
            cmd.Parameters.AddWithValue("?", priority)
            cmd.Parameters.AddWithValue("?", status)
            cmd.Parameters.AddWithValue("?", category)
            cmd.Parameters.AddWithValue("?", lastmodifieddate)
            cmd.Parameters.AddWithValue("?", lastmodifiedby)
            cmd.Parameters.AddWithValue("?", reqid)


            'Open SQL Connection and run command
            sqlCon.Open()
            cmd.ExecuteNonQuery()
            sqlCon.Close()

            MessageBox.Show("Request has been updated successfully", "Success", MessageBoxButtons.OK)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not sqlCon Is Nothing Then sqlCon.Dispose()
        End Try
    End Sub

Open in new window


Access table schema

ReqID Text
Category number
Title text
Description memo
Status number
Priority number
LastModifiedDate date/time
LastModifiedBy text

Open in new window

0
imstac73
Asked:
imstac73
2 Solutions
 
KorbusCommented:
Is it possible the problem is with your data?  I don't see any data validation going on there.  For example, what if PriorityTextBox.Text  is filled with "a".  Perhaps you do this validation elsewhere?

Can you show us a sample of the SQL call used in cmd.ExecuteNonQuery(), (With data)?
0
 
imstac73Author Commented:
I did some testing and it is the date field.  Access requires that the date/time be within #.
I can't figure out how to pass the date with the # though.
0
 
PatHartmanCommented:
Try printing out the query string just before you send it.  Remember - dates must be delimited by # and strings by single or double quotes.  Numeric values are not delimited.  So the string as it is sent will look something like:

"UPDATE Requests SET title = 'this is the title', description = 'this is the description', priority = 4, status = 'Open', category = 5, lastmodifieddate = #4/18/14#, lastmodifiedby = 'Pat' WHERE [reqID] = 48493"
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
imstac73Author Commented:
I fixed it by replacing
cmd.Parameters.AddWithValue("?", lastmodifieddate)

with this

cmd.Parameters.AddWithValue("?", DbType.DateTime).Value = DateTime.Now.ToString()
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
Be aware that the Now function is overused and is usually the wrong one to use. Too many programmers use Now and then format the output so that only the date is shown.

Under the hood however, this can cause problems. If the time is useless, it should not be recorded. Otherwise, many queries won't work properly.

If you run a query to search for a given date, you might well end up with no result, because the date when you type a date in a search box, there is no time component, the time component defaults to midnight. You fill find only the results that match that day at midnight.

You might be running a query for your results on the last 15 previous days, and the query will not give the same answer in the morning and in the afternoon.

If the time is not important when treating a date or datetime variable (they are the same under the hood), always use Date.Today instead of Date.Now.
0
 
Gustav BrockCIOCommented:
You are converting to string which can't be right. If it works it is pure luck.

This is how to manage this:
        Dim lastmodifieddate As Date = Now  ' Or = Date as noted.

' Snip

   cmd.Parameters.AddWithValue("?", DbType.DateTime).Value = lastmodifieddate

' Snip

Open in new window

/gustav
0
 
Gustav BrockCIOCommented:
Your solution was not exact.

/gustav
0
 
imstac73Author Commented:
Found the solution myself with help from other post.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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