Inserting date to access problem

[johnuk4]

Hi, sorry if this is a bit trivial but im a bit of a beginner. Im trying to write a datetime into an access database using the following code:

Private Sub btnLog_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLog.Click
Dim HardwareId As String
Dim Staff As String
Dim Description As String
Dim Location As String
Dim d As DateTime = DateTime.Now
Dim Message As String
HardwareId = ListId.Text
Staff = txtStaff.Text
Description = txtDescription.Text
Location = txtLocation.Text
Try
DBCon.Open()
Dim sa As Integer
Dim DSet4 As New DataSet, SQLStr4 As String
Dim dbAdaptr4 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
With dbAdaptr4
.TableMappings.Add("Table", "Hardware_Problems")
SQLStr4 = "INSERT INTO Hardware_Problems (Hardware_Id, Date_Time, Staff_Reported, Problem_Description, Location) VALUES (@Hardware_Id, @Date_Time, @Staff_Reported, @Problem_Description, @Location)"
Dim sqlCommand As New System.Data.OleDb.OleDbCommand
sqlCommand.Connection = DBCon
sqlCommand.CommandText = SQLStr4
sqlCommand.Parameters.Add("@Hardware_Id", SqlDbType.VarChar).Value = HardwareId
sqlCommand.Parameters.Add("@Date_Time", SqlDbType.SmallDateTime).Value = d
sqlCommand.Parameters.Add("@Staff_Reported", SqlDbType.VarChar).Value = Staff
sqlCommand.Parameters.Add("@Problem_Description", SqlDbType.VarChar).Value = Description
sqlCommand.Parameters.Add("@Location", SqlDbType.VarChar).Value = Location
sa = sqlCommand.ExecuteNonQuery()
Message = ("Problem In " & Location & " Logged")
Label1.Text = Message
End With
DBCon.Close()
Catch ex As Exception
Label1.Text = "Error reading database " & ex.Message
End Try
End Sub

Insert worked before trying the datatime part however since then it keeps throwing the error:

Error reading database datatype mismatch in criteria expression.

Any help and ideas would be much appreciated thanks

johnuk4

 

[Schenz]

try changing the paramter section of the code to:

[SIZE=2] sqlCommand.Parameters.Add("@Hardware_Id", [/SIZE]OleDbType[SIZE=2].VarChar).Value = HardwareId
sqlCommand.Parameters.Add("@Date_Time", [/SIZE]OleDb[SIZE=2]Type.SmallDateTime).Value = d
sqlCommand.Parameters.Add("@Staff_Reported", [/SIZE]Ole[SIZE=2]DbType.VarChar).Value = Staff
sqlCommand.Parameters.Add("@Problem_Description", [/SIZE]Ole[SIZE=2]DbType.VarChar).Value = Description
sqlCommand.Parameters.Add("@Location", [/SIZE]Ole[SIZE=2]DbType.VarChar).Value = Location

we were using the SQL Types before and SQL Server handles dates differently than Access. Let me know if this works, or at least changes the error.

[/SIZE]

 

[johnuk4]

Thanks, but where would i declared the OleDbType as it says its not been declared when i change it

john

 

[Schenz]

If you have not imported System.Data.OleDb then you will need to reference the complete path to the oleDbType (System.Data.OleDb.oleDbType).

Also once you have done that the date one may need to be adjusted as well...

 

[johnuk4]

Thanks for that, i was just trying something similar. It got me past the data mismatch however now an error:

Error reading database ExecuteNonQuery: Connection property has not been properly initialized.

This shows when the insert is executed.

thanks for your help

john

 

[Schenz]

Maybe it is a good time to see your updated code

(hint: use [code][/code] around your code as it can make it alot easier to read.)

 

[johnuk4]

Hi, sorry it was me been stupid id left out the

[SIZE=2]sqlCommand.Connection = DBCon
sqlCommand.CommandText = SQLStr4
[/SIZE]

must have been getting late last night!

Thanks a lot for the advice though its really appreciated

johnuk4

 

[Schenz]

So, It is working now?