Results 1 to 4 of 4

Thread: Parameter @*** has no default value

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    7

    Unhappy Parameter @*** has no default value

    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:
    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

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,018

    Re: Parameter @*** has no default value

    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.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    7

    Re: Parameter @*** has no default value

    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

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,018

    Re: Parameter @*** has no default value

    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?
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width