Why do I get OleDb Error here?

[benlinkknilneb]

Hi all, here's the code:

Dim DBase As New OleDb.OleDbConnection(DBString)
Dim Adpt As New OleDb.OleDbDataAdapter()
Dim Cmd As New OleDb.OleDbCommand(SQLStatement, DBase)
Adpt.InsertCommand = Cmd
DBase.Open()
Cmd.ExecuteNonQuery()
DBase.Close()

Further Information:
-DBString is the Connection String. This is passed from another part of the program, and it has been used in at least one other location in the code by the time it gets to this point.
-I have verified the contents of SQLStatement... it is an Insert Query and it works if entered directly into Access.

It always hangs on "ExecuteNonQuery()" and gives me the same error type: System.Data.OleDb.OleDbException. No other information shows in the error screen. What am I doing wrong?

Ben

 

[JohnEfford]

What is the SQL command you are using, does it not have any parameters that need setting?

Also it is not necessary to use a data adapter to hold the insert command, you only need to use a data adapter in conjuction with a data table (although this should not be causing the error)

Regards

 

[benlinkknilneb]

The SQL Command I'm using is as follows:

INSERT INTO JournalEntries (EntryDate, User, Scope, PartNumber, EntryType, CastDate, Comments) SELECT #7/18/2003# AS EDTE, "Ben" AS USER, "Public" AS SCOPE, "005055P01Q00" AS PARTNUM, "General Note" AS ENTYPE, #7/18/2003# AS CAST, "asdfdsdsaffdsa" AS COMMENTS;

I have tested this directly in Access, and it works fine. I am building the string in VB, based on the user's entry to the form. Then I append the different pieces to the string to create my SQL Statement; thus no parameters.

Ben

 

[santosh1]

Try this:

Dim strSQL As String

Try
' Build Insert statement to insert new Invoice into the Invoices table
strSQL = "INSERT into JournalEntries(EntryDate, " & _
"User, Scope, PartNumber, EntryType, CastDate, " & _
"Comments) VALUES (" & _
PrepareStr(#7/18/2003#) AS EDTE & "," & _
PrepareStr(Ben) AS SCOPE & "," & _
PrepareStr(Public) AS PARTNUM & "," & _
Preparestr(005055P01Q00) AS ENTYPE & "," & _
Preparestr(#7/18/2003#) AS CAST & "," & _
PrepareStr(asdfdsdsaffdsa) AS COMMENTS & &quot "

Dim myConnection As OleDbConnection = New OleDbConnection(ConnectionString)
myConnection.Open()
Dim myCommand As OleDbCommand = New OleDbCommand(strSQL, myConnection)
InvoiceKey = CInt(myCommand.ExecuteScalar)
' Close and Clean up objects
myConnection.Close()
myCommand.Dispose()
myConnection.Dispose()
AddDonorCenters(InvoiceKey)

Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error&quot

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error&quot
End Try

Private Function PrepareStr(ByVal strValue As String) As String
' This function accepts a string and creates a string that can
' be used in a SQL statement by adding single quotes around
' it and handling empty values.
If strValue.Trim() = "" Then
Return "NULL"
ElseIf InStr(strValue.Trim(), "'&quot Then
Dim ReplacedValue As String = Replace(strValue.Trim(), "'", "&quot
Return "'" & ReplacedValue.Trim() & "'"
Else : Return "'" & strValue.Trim() & "'"
End If
End function

 

[benlinkknilneb]

Hi all,

I got this working by rebuilding the form from scratch. Thanks for your suggestions... I never did find my error though.

Ben