Solved

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

Posted on 2014-04-18
8
1,482 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DSum for Access 6 43
ms/access hyperlink/ftp 7 31
Excess tables to Excel BackUp 3 27
Web based Access Database and licencing 5 27
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

932 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

11 Experts available now in Live!

Get 1:1 Help Now