.net and Access Trouble

[scooby77]

I am trying to write a program that has the user fill in a lot of information and then it uploads the data to 5 tables in an access database. I am sure that if someone could help me upload one set of records into a table then I can get the rest. Here is the code that I am trying but I keep getting an en error that says:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

Please help if you can. Thanks in advanced.


Dim conIncident As OleDbConnection
Dim strIncident As String
Dim strComplaintant As String
Dim strSuspect As String
Dim strNarrative As String
Dim cmdIncident As OleDbCommand
Dim cmdComplaintant As OleDbCommand
Dim cmdSuspect As OleDbCommand
Dim cmdNarrative As OleDbCommand

conIncident = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Incident.mdb&quot

strIncident = "Insert Into tb_IncidentTicket (pk_ReportNumber, DateOccured, TimeOccured, DateReported, TimeReported, Wats, XCoordinate, YCoordinate, CodeNumber, County, District, ReportingMethod, TypeIncident, location, GPSZone, Datum) Values ( @pk_ReportNumber, @DateOccured, @TimeOccured, @DateReported, @TimeReported, @Wats, @XCoordinate, @YCoordinate, @CodeNumber, @County, @District, @ReportingMethod, @TypeIncident, @Location, @GPSZone, @Datum)"


'

cmdIncident = New OleDbCommand(strIncident, conIncident)
cmdIncident.Parameters.Add("@pk_ReportNumber", txtReportNumber.Text)
cmdIncident.Parameters.Add("@DateOccured", txtDateOccured.Text)
cmdIncident.Parameters.Add("@TimeOccured", txtTimeOccured.Text)
cmdIncident.Parameters.Add("@DateReported", txtDateReported.Text)
cmdIncident.Parameters.Add("@TimeReported", txtTimeReported.Text)
cmdIncident.Parameters.Add("@Wats", txtWats.Text)
cmdIncident.Parameters.Add("@XCoordinate", txtXCoord.Text)
cmdIncident.Parameters.Add("@YCoordinate", txtYCoord.Text)
cmdIncident.Parameters.Add("@CodeNumber", txtCodeNumber.Text)
cmdIncident.Parameters.Add("@County", cmbCounty.Text)
cmdIncident.Parameters.Add("@District", cmbDistrict.Text)
cmdIncident.Parameters.Add("@ReportingMethod", cmbReported.Text)
cmdIncident.Parameters.Add("@TypeIncident", cmdTypeIncident.Text)
cmdIncident.Parameters.Add("@Location", txtLocation.Text)
cmdIncident.Parameters.Add("@GPSZone", txtZone.Text)
cmdIncident.Parameters.Add("@Datum", cmdDatum.Text)



conIncident.Open()

cmdIncident.ExecuteNonQuery()

conIncident.Close()

 

[jfrost10]

If you're just grabbing the Textbox.Text property, its going to try and assign a string. It looks to me that you have some fields that could be integers or dates.

Try casting the values to the proper datatype

i.e.

cmdIncident.Parameters.Add("@XCoordinate", txtXCoord.Text)
should be
...("@XCoordinate", CInt(txtXCoord.Text))

hth

D'Arcy

 

[Schultjeff]

I have never had any luck using parameters with Access in Ado.Net. It works nicely with SQL Server, but not Access, so when I write the query I just recreate the SQL string with the value inserted, not parameters.

e.g.

"INSERT INTO Table (Field1, Field2) VALUES (" & intField1Value & ", " & intField2Value & &quot"

Not ideal, but I hope this helps.

Jeff

 

[scooby77]

I tried doing these steps but nothing seems to work. I continue to get the same error. Am I not Importing something right?

 

[Schultjeff]

Can you tell us the Error Message?

 

[scooby77]

I captured the insert statment using a debug call and the statment works ok. Could the connection call be wrong? It is pointed to the right place.

 

[Craig0201]

Parameters in Access are not the same as the ones for SQL Server. The placeholders are all the character ?, not @NameOfParameter.

Craig