Solved

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

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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

734 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