Data type mismatch criteria in Access db date field 3

[JulesBos]

Hi all, can't work this one out.... I'm updating an Access table from a web form. I've got 2 date fields, one updates, the other creates a data type mismatch criteria error. I've run the SQL directly in Access and it works fine. I must have made a stupid error somewhere but I can't for the life of me see it.. any ideas? Code below:

Dim CommandString As String

CommandString = "UPDATE tblCapList SET PartNumber = ?, UnitType = ?, Description = ?, Status = ?, Scope = ?, Cell = ?, CMM = ?, Model = ?, ATAChapNo = ?, CRating = ?, DateAdded = ?, DateRemoved = ?, RevNo = ? WHERE PartNumber = '" + OrigPN + "'"

Me.UpdatePartCommand.CommandText = CommandString
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("PartNumber", System.Data.OleDb.OleDbType.VarWChar, 50, "PartNumber"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("UnitType", System.Data.OleDb.OleDbType.VarWChar, 50, "UnitType"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Description", System.Data.OleDb.OleDbType.VarWChar, 50, "Description"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Status", System.Data.OleDb.OleDbType.Integer, 0, "Status"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Scope", System.Data.OleDb.OleDbType.Integer, 0, "Scope"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Cell", System.Data.OleDb.OleDbType.Integer, 0, "Cell"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("CMM", System.Data.OleDb.OleDbType.VarWChar, 50, "CMM"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Model", System.Data.OleDb.OleDbType.VarWChar, 50, "Model"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("ATAChapNo", System.Data.OleDb.OleDbType.VarWChar, 50, "ATAChapNo"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("CRating", System.Data.OleDb.OleDbType.Integer, 0, "CRating"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateAdded", System.Data.OleDb.OleDbType.DBDate, 0, "DateAdded"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("RevNo", System.Data.OleDb.OleDbType.VarWChar, 50, "RevNo"))
Me.UpdatePartCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("DateRemoved", System.Data.OleDb.OleDbType.DBDate, 0, "DateRemoved"))

With Me.UpdatePartCommand
.Parameters("PartNumber").Value = PartNo
.Parameters("UnitType").Value = Unit
.Parameters("Description").Value = Desc
.Parameters("Status").Value = Status
.Parameters("Scope").Value = Scope
.Parameters("Cell").Value = Cell
.Parameters("CMM").Value = CMM
.Parameters("Model").Value = Model
.Parameters("ATAChapNo").Value = ATAChap
.Parameters("CRating").Value = CRating
.Parameters("DateAdded").Value = Now
.Parameters("RevNo").Value = "Temp"
.Parameters("DateRemoved").Value = DBNull.Value
End With

UpdateConnection.Open()
Me.UpdatePartCommand.ExecuteNonQuery()
UpdateConnection.Close()

The parameter/field I'm having the problem with is DateRemoved. I've tried changing dbnull.value to Now or putting a specific date in and I'm afraid I still get the same error. All help appreciated! Thanks in advance!

Julia

 

[ShawnMolloy]

I would try using the DateTime.Parse() or DateTime.ParseExact() methods to help retrofit your data for the database.

Also, what is the data type of the column you're trying to insert into?

http://msdn2.microsoft.com/en-us/library/system.datetime.parse.aspx

Shawn Molloy
Seattle, WA

 

[JulesBos]

Thanks Shawn

The column is a date/time one, with medium date format (UK not US).

I'm confused because my other date/time field is exactly the same and this works fine - "DateAdded", I don't get any error messages for this one.

I'm actually trying to update the field to a null value so I don't believe the datetime.parse method is what I want, but I may have misunderstood, so please accept my apologies if I have.

Julia

 

[chrissie1]

if you use the ? then the parameters need to be added in the same order so revno after dateremoved.

Christiaan Baes
Belgium

"My new site" - Me

 

[chrissie1]

and you get a star just because you use parameters.

Christiaan Baes
Belgium

"My new site" - Me

 

[JulesBos]

Christiaan, thanks so much for that, you're a star!! That worked a treat, I swapped the order of adding parameters and everything's working fine. Something so simple, but I'd never have realised that.

Thanks for my star too I'm new to this so it's nice to know someone thinks I'm doing something right.

Julia

 

[ca8msm]

You could also look at moving your update statement into a Stored Procedure. This will then seperate your database code evn further and any updates to the SP can be applied without changing the .NET code (unless of course the parameters are being changed). You may also notice a slight performance increase when using SP's.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

And before somebody says access doesn't have stored procedures

http://www.devcity.net/Articles/18/msaccess_sp.aspx

Christiaan Baes
Belgium

"My new site" - Me