Error - No Value Given For One OR More required Parameters

[Guest]

I have the following lines of VB.Net code. Its works if the HDR property is
set to "No" but changing it to "Yes" generates the error "No Value Given For
One OR More required Parameters". What am I missing?

Dim sInsert, sSelect, sConn, sFile As String
sFile = txtFileName.Text.Trim
sInsert = "INSERT INTO [PassResults] (Model, Result, Operator) "
sSelect = "SELECT F1, F2, F3 from [Excel
8.0;DATABASE=C:\PassResults.xls;HDR=Yes;IMEX=1].[Results$];"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TestResults.mdb"
Dim AccessConn As New System.Data.OleDb.OleDbConnection(sConn)
Try
AccessConn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand(sInsert
& sSelect, AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConn.Close()
End Try

 

[Guest]

Hi Shariq,

I have just a couple of observations maybe one or both of them will help.

First if you set HDR=YES that will tell the Jet Driver that there is a
header row. So it will use this to derive column names. If you state HDR=NO
it will state that there is no "header" row so you can use (like you have in
your code) F1, F2, etc. So if you change HDR=YES and you still reference the
columns as F1, F2, etc you may encounter a problem.

Second you also have IMEX=1 in the Jet Connection String. This tells the
Jet Driver to use import mode which should force mixed types to be
interpreted as text (defined in a registry setting). So it could be that you
are receiving a NULL in return on the select statment. Which in turn could
be causing the problem with the insert into the Access database. I have
posted a link to the msdn below regarding this that details steps on how to
reproduce the behavior. You may try leaving this out and coding the
datatypes directly (if known).

I hope this helps.

 

[Guest]

Brian,

I've already tried both ways and none helped; any other suggestions? Thanks

 

[Guest]

Well, actually your suggestion #1 has worked when I changed F1, F2 etc to the
field names. I was not enclosing a field (Serial No) within a bracket.
Thanks for your help.