WHERE Statement Fails. WHY!

Private Function Check() 'CheckX10Alerts(ByVal X10_RecvCMD As String, ByVal sndDeviceAddress As String, ByVal typEvent As String)

'Prepare to look for matching alert database
Dim int As Int32

bolAlertsEnabled = True
If bolAlertsEnabled = True Then
Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Dot_Net_Projects\Visual_Basic\aiHome\Website\App_Data\aiHome.mdb;")
Dim command As New OleDbCommand("SELECT strMedium, sndDeviceAddress, typEvent FROM x10_Triggers WHERE strMedium = AND sndDeviceAddress = AND typEvent = ", connection)

command.Parameters.Add(strName, OleDbType.Char, 150).Value = "Movement - Front Porch"
command.Parameters.Add(strMedium, OleDbType.Char, 10).Value = strX10_Command
command.Parameters.Add(sndDeviceAddress, OleDbType.Char, 3).Value = strX10_DeviceAddress
command.Parameters.Add(typEvent, OleDbType.Char, 12).Value = strX10_RecvCMD

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader

While reader.Read()
int = int + 1
Console.WriteLine(reader(0).ToString())
Console.WriteLine(reader(1).ToString())
Console.WriteLine(reader(2).ToString())
Console.WriteLine(reader(3).ToString())
Console.WriteLine(reader(4).ToString())
Console.WriteLine(reader(5).ToString())
Console.WriteLine(int)
End While
reader.Close()
End Using

End If

End Function

Thanks

P.S. Accessing databases was much easier back in V.B. 6.

.NET Development26

Do you get any errors If so, what

You are passing in four parameters:

command.Parameters.Add(strName, OleDbType.Char, 150).Value = "Movement - Front Porch"
command.Parameters.Add(strMedium, OleDbType.Char, 10).Value = strX10_Command
command.Parameters.Add(sndDeviceAddress, OleDbType.Char, 3).Value = strX10_DeviceAddress
command.Parameters.Add(typEvent, OleDbType.Char, 12).Value = strX10_RecvCMD

but only have 3 parameter placeholders:

WHERE strMedium = AND sndDeviceAddress = AND typEvent =

Could this be why it's not working as expected.

Unfortunately no. Remarking out the first parameter and I still get the:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

My logic: Search database to see if the value I just received is already in the database. If I can get this to work I would then look at the record count which should 0 or 1. I guess I should also ask how do I do that.

Every time I go to look in MSDN it turns into hours trying to implement what I am reading.

Can you believe I am doing this for fun!

Thanks.

Where exatly is the error occuring At the execute I assume

That is correct.

Thanks

Can you post the rest of the trace from the exception There's usually a bit more information than this.

This code has several problems:

1) Too many input parameters as mentioned above.

2) You need quotes arround your parameter names: command.Parameters.Add("strMedium" ... for all your parameters. This is why you are getting an error when executing the command.

3) You are reading 6 columns ( reader(0) - reader(5) ) when your select statement only asks for 3 columns per row: strMedium, sndDeviceAddress, typEvent. Hence, when you do fix the parameter issues you will get an error when reading the rows :) Speed is probably not a factor, so I would just use the column names instead of the ordinal position for ease of understanding, etc.

4) As a side note, you are also naming an integer (Int32) variable int, which I assume is not a keyword in VB.NET That wouldn't compile in C#, but I guess it must compile and work fine in VB.NET. Looks funny is all :)

VB.Net is not my thing, but here is a cut at rewriting the code. Unfortunately, I don't have time to create an Access Database to test:

Private Function Check() 'CheckX10Alerts(ByVal X10_RecvCMD As String, ByVal sndDeviceAddress As String, ByVal typEvent As String)

'Prepare to look for matching alert database
Dim int As Int32

bolAlertsEnabled = True
If bolAlertsEnabled = True Then
Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Dot_Net_Projects\Visual_Basic\aiHome\Website\App_Data\aiHome.mdb;")
Dim command As New OleDbCommand("SELECT strMedium, sndDeviceAddress, typEvent FROM x10_Triggers WHERE strMedium = AND sndDeviceAddress = AND typEvent = ", connection)

command.Parameters.Add("strMedium", OleDbType.Char, 10).Value = strX10_Command
command.Parameters.Add("sndDeviceAddress", OleDbType.Char, 3).Value = strX10_DeviceAddress
command.Parameters.Add("typEvent", OleDbType.Char, 12).Value = strX10_RecvCMD

connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader

While reader.Read()
int = int + 1
Console.WriteLine(reader("strMedium").ToString())
Console.WriteLine(reader("sndDeviceAddress").ToString())
Console.WriteLine(reader("typEvent").ToString())
Console.WriteLine(int)
End While
reader.Close()
End Using

End If

End Function

Good luck. Hope this helps,

Dave

Hi All,

thanks for looking at my problem.

David thanks for the re-write but unfortunately I still get:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

when the following line executes:

Dim reader As OleDbDataReader = command.ExecuteReader

Paul the above error message is the only thing I receive in the debug window.

Thanks!

Do you have full permissions to the network resource where the database is located This is required in order for the Jet Database Engine to create/modify/delete the corresponding Access .LDB file.

Yes, everyone has "Full Control"

Thanks,

Paul

Does it make any difference if you use OleDbType.VarChar instead of OleDbType.Char as the parameter data type

It gave the same error message with the OleDbType.VarChar.

Paul

I dont know if this would help but try changing the followin from WHERE strMedium = AND sndDeviceAddress = AND typEvent =

to

WHERE [strMedium] = AND [sndDeviceAddress] = AND [typEvent] =

Nope, same error message.

Paul

make sure that you have spaces inbetween each statement. see if that helps.