Solved

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

Posted on 2014-04-18
8
1,500 Views
Last Modified: 2014-04-26
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
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 10

Expert Comment

by:Korbus
ID: 40009302
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
 

Author Comment

by:imstac73
ID: 40009374
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
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40009383
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Assisted Solution

by:imstac73
imstac73 earned 0 total points
ID: 40009399
I fixed it by replacing
cmd.Parameters.AddWithValue("?", lastmodifieddate)

with this

cmd.Parameters.AddWithValue("?", DbType.DateTime).Value = DateTime.Now.ToString()
0
 
LVL 40
ID: 40009723
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40010114
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40014096
Your solution was not exact.

/gustav
0
 

Author Closing Comment

by:imstac73
ID: 40024358
Found the solution myself with help from other post.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question