Solved

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

Posted on 2014-04-18
8
1,479 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
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 34

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 49

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 49

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now