Parameter @*** has no default value

[Avatar_GW]

3rd time lucky perhaps?

I'm having some difficulty with some code to update an Access DB. I keep getting "Parameter @ParamName has no default value"
Renaming the parameter in both the SQL statement and the parameter.add, moves the error to a different parameter but it never goes away.

Code:

        Private Function f_Results_to_DB(ByVal dt_Results As DataTable) As Boolean
            Dim s_CnnStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
                                     "Data Source=\\serverpath\" & _
                                     "Results.mdb;" & _
                                     "Jet OLEDB:Database Password=password;"
            Dim s_SQLCon As String = "SELECT * FROM tbl_DataAudit"
            Dim s_SQL As String = "INSERT INTO tbl_DataAudit (OldPath, FileGUID, UserID, DateTimeDone, LoadedResults, SavedResults) " & _
                                  "VALUES " & _
                                  "(@OldPath, @FileGUID, @UserID, @DateTimeDone, @LoadedResults, @SavedResults)"

            Try
                Using cnn As New OleDbConnection(s_CnnStr)
                    Using OleAdaptor As New OleDbDataAdapter(s_SQLCon, cnn)
                        Dim insert As New OleDbCommand(s_SQL, cnn)
                        insert.Parameters.Add("@OldPath", OleDbType.VarChar, 255, "OldPath")
                        insert.Parameters.Add("@FileGUID", OleDbType.VarChar, 255, "FileGUID")
                        insert.Parameters.Add("@UserID", OleDbType.VarChar, 255, "UserID")
                        insert.Parameters.Add("@DateTimeDone", OleDbType.VarChar, 255, "DateTimeDone")
                        insert.Parameters.Add("@LoadedResults", OleDbType.VarChar, 255, "LoadedResults")
                        insert.Parameters.Add("@SavedResults", OleDbType.VarChar, 255, "SavedResults")

                        OleAdaptor.InsertCommand = insert
                        OleAdaptor.MissingSchemaAction = MissingSchemaAction.AddWithKey

                        'Dim ds As New DataSet()
                        Dim table As New DataTable

                        table = dt_Results

                        'Save the changes.   
                        OleAdaptor.Update(table)
                    End Using
                End Using
                Return True
            Catch ex As Exception
                Return False
            End Try

        End Function

The Datatable being passed in has between 1 and 10 rows and no blank or null fields.

I'm stuck. I havn't been able to find a solution that allows me to continue to use a datatable without iterating through it row by row.

Any ideas?

Regards

[Shaggy Hiker]

Access doesn't use named parameters. Whether that is the issue I can't say, but the end of the SQL string for Access is generally "VALUES (?,?,?,?,?,?)" (or however many ? you need). You then supply the parameters as you are doing, but in the order which they are needed, because that's the order they will be stuck into the survey.

Another point would be to use Parameters.AddWithValue, which I think is replacing Add.

The last point is that you aren't actually supplying any value for the parameters unless you have some VERY strange data. After all, the first argument you are supplying is the parameter name, which you need, even though Access pretty much ignores it. The second argument you are supplying is the type. The third argument looks like size (neither of those arguments are necessary). However, the fourth argument, which should be the value for the parameter, is just the name of the parameter again without the @. Unless you want to insert the parameter names into the DB, which makes little sense, then that part is wrong.

[Avatar_GW]

Thanks for the reply

I plagiarised most of it from the Code Bank here from a post by jmcilhinney called "Retrieving and Saving Data in Databases".

I assumed i had a vague idea of what was going on (or at least what parts to change) and I have a very similar function for a different DB that works using code almost identical to this, but truth be told i guess i don't understand whats going on. I'm new to Visual Studio so most of this is unknown territory for me.

The underlying goal is to insert the data from the datatable into an Access 2003 DB. If you can point me in the right direction, i would be grateful.

Regards

[Shaggy Hiker]

I see that I missed a key point. Specifically, I missed the part where you were running Update on an adapter, and that this whole thing was supplying a custom INSERT command. I rarely do it that way, but I have done it that way in one place I can locate and....I'm afraid I don't see much different from what you did and what I did.

So, that takes me back to the error message itself. After all, one reading of that error message is that you are not supplying values for some non-nullable fields that don't have default values. In other words: If you look at the fields in that table, are any of them set up in the database such that you can't leave them blank (I forget the term that Access uses for this, but I don't think it is Nullable)? If so, then can you be certain that in ALL of the records of the table, there are no records for which that field doesn't have a value?