Strange error.

[Jason Lepack]

I'm working with an OLEDB connection to a Microsoft Access database,
coding in VB.net

' Connection String is set here (called from constructor)
Private Function GetConnection() As OleDb.OleDbConnection
Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;" _
& "Data Source=" & mDataPath & "\DAQ.mdb")
End Function

' Sub that's throwing the error:
Public Sub insertStart(ByVal sTime As Date, ByVal jobID As
Integer)
Dim sql As String = "EXECUTE startTheor"

insCmd = New OleDb.OleDbCommand(sql, conn)
insCmd.Parameters.Add(New OleDb.OleDbParameter("[jobid]",
jobID))
insCmd.Parameters.Add(New OleDb.OleDbParameter("[dt]", sTime))

Try
conn.Open()
insCmd.ExecuteNonQuery() -- error on this line
Finally
conn.Close()
End Try
End Sub

' sub above is called from here
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
If txtStart.Text = "" Then
txtStart.Text = Now
End If
If txtJob.Text = "blah" Then
txtJob.Text = "1"
End If
DAQ_Data.DAQ_Data.DataModule.insertStart(CDate(txtStart.Text),
CInt(txtJob.Text))
End Sub

SQL of startTheor:
INSERT INTO theoretical_job_data ( job_theor_dt, job_id, qty )
SELECT [dt] AS Expr1, [jobid] AS Expr2, 0 AS Expr3;

Structure of theoretical_job_data:
job_theor_dt - date/time
job_id - number (long)
qty - number (double)

The error that I receive is:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Any help would be greatly appreciated. I run a bunch of other delete
and insert queries in the same fashion and don't have any other
problems with them.

Cheers,
Jason Lepack

 

[Michel Walsh]

The recommended SQL syntax is not:

INSERT INTO theoretical_job_data ( job_theor_dt, job_id, qty )
SELECT [dt] AS Expr1, [jobid] AS Expr2, 0 AS Expr3;


but:

INSERT INTO theoretical_job_data ( job_theor_dt, job_id, qty )
VALUES([dt] , [jobid] , 0 ) ;



Can you append the said record from, say, Access? It may be that you cannot
append the said record due to a key violation, or some data referential
integrity, or otherwise.

I am not using that approach, thanks to the Framework 2.0, but is it not
AddWithValues rather than just Add, to be used, when you specify the
parameters value, in that circumstance?

Should you not use CreateCommand form the connection object, rather than new
OleDb.OleDbCommand, to initialize your command object ? Sure, it may have
been overloaded to accept the syntax you use, again, I don't use that
approach.

Isn't preferable to have the command string = "startTheor" and the command
type a CommandType.StoredProcedure ? Using "execute startTheor" implies
there is no parameter, at first glance. "execute startTheor param1, param2"
is more usual, when parameters are implied, with execute, and the values of
the parameters are there given, not 'named', so no parameters collection
needed. So, if you intend to use a parameter collection, you supply the
stored proc name, and specify you did it, through the CommandType, then, use
the Parameters collection.



Vanderghast, Access MVP

I'm working with an OLEDB connection to a Microsoft Access database,
coding in VB.net

' Connection String is set here (called from constructor)
Private Function GetConnection() As OleDb.OleDbConnection
Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;" _
& "Data Source=" & mDataPath & "\DAQ.mdb")
End Function

' Sub that's throwing the error:
Public Sub insertStart(ByVal sTime As Date, ByVal jobID As
Integer)
Dim sql As String = "EXECUTE startTheor"

insCmd = New OleDb.OleDbCommand(sql, conn)
insCmd.Parameters.Add(New OleDb.OleDbParameter("[jobid]",
jobID))
insCmd.Parameters.Add(New OleDb.OleDbParameter("[dt]", sTime))

Try
conn.Open()
insCmd.ExecuteNonQuery() -- error on this line
Finally
conn.Close()
End Try
End Sub

' sub above is called from here
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
If txtStart.Text = "" Then
txtStart.Text = Now
End If
If txtJob.Text = "blah" Then
txtJob.Text = "1"
End If
DAQ_Data.DAQ_Data.DataModule.insertStart(CDate(txtStart.Text),
CInt(txtJob.Text))
End Sub

SQL of startTheor:
INSERT INTO theoretical_job_data ( job_theor_dt, job_id, qty )
SELECT [dt] AS Expr1, [jobid] AS Expr2, 0 AS Expr3;

Structure of theoretical_job_data:
job_theor_dt - date/time
job_id - number (long)
qty - number (double)

The error that I receive is:
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Any help would be greatly appreciated. I run a bunch of other delete
and insert queries in the same fashion and don't have any other
problems with them.

Cheers,
Jason Lepack