Inserting Into Access Table - Parameter has no default value

nick447923

Member
Joined
Nov 10, 2009
Messages
14
Programming Experience
Beginner
I am trying to do an insert into a table called Policy. I keep getting an error that says parameter @Split has no default value. To try and resolve the problem, I set the default value of the split field (of type text) in the Policy table to "0" in Access 2007. I am passing in the value of a string "0" into the parameter before the insert statement executes.

<code>
Dim connection As OleDbConnection = PaulMeadeInsuranceDB.GetConnection
Dim insertcommand2 As New OleDbCommand(insertStatement2, connection)
connection.Open()

Dim insertStatement2 As String = "INSERT INTO Policy (CustNumber, AcctNumber, Slot, " _
& "Split, CompanyNumber, PolicyNumber, EffDate, ExpDate, Term, ClassofBus, Commercial, Premium, " _
& "SubCoNumber, SubClassBusiness, CancelDate, CanCode, StatusWord, PolicyStatus, RenewalStatus, CancelStatus, " _
& "BounceStatus, CommRate) VALUES (@CustNumber, @AcctNumber, @Slot, " _
& "@Split, @CompanyNumber, @PolicyNumber, @EffDate, @ExpDate, @Term, @ClassofBus, @Commercial, @Premium, " _
& "@SubCoNumber, @SubClassBusiness, @CancelDate, @CanCode, @StatusWord, @PolicyStatus, @RenewalStatus, @CancelStatus, " _
& "@BounceStatus, @CommRate)"

insertcommand2.Parameters.AddWithValue("@CustNumbe r", Customer.Custnumber)
insertcommand2.Parameters.AddWithValue("@AcctNumbe r", Policy.AcctNum)
insertcommand2.Parameters.AddWithValue("@Slot", Policy.Slot)
insertcommand2.Parameters.AddWithValue("@CompanyNu mber", Policy.CoNumber)
insertcommand2.Parameters.AddWithValue("@PolicyNum ber", Policy.Policynumber)
insertcommand2.Parameters.AddWithValue("@EffDate", Policy.Effdate)
insertcommand2.Parameters.AddWithValue("@ExpDate", Policy.Expdate)
insertcommand2.Parameters.AddWithValue("@Term", Policy.Term)

insertcommand2.Parameters.AddWithValue("@ClassofBu s", Policy.ClassOfBus)
insertcommand2.Parameters.AddWithValue("@Commercia l", Policy.Commercial)
insertcommand2.Parameters.AddWithValue("@Premium", Policy.Premium)
insertcommand2.Parameters.AddWithValue("@SubCoNumb er", Policy.SubCoNumber)
insertcommand2.Parameters.AddWithValue("@SubClassB usiness", Policy.SubClassOfBus)
insertcommand2.Parameters.AddWithValue("@CancelDat e", Policy.Canceldate)
insertcommand2.Parameters.AddWithValue("@CanCode", Policy.CancelCode)
insertcommand2.Parameters.AddWithValue("@StatusWor d", Policy.StatusWord)
insertcommand2.Parameters.AddWithValue("@PolicySta tus", Policy.PolicyStat)
insertcommand2.Parameters.AddWithValue("@RenewalSt atus", Policy.RenStat)
insertcommand2.Parameters.AddWithValue("@CancelSta tus", Policy.CanStat)
insertcommand2.Parameters.AddWithValue("@BounceSta tus", Policy.BounceStat)

insertcommand2.Parameters.AddWithValue("@CommRate" , Policy.CommRate)

insertcommand2.Parameters.AddWithValue("@Split", Policy.Split)

insertcommand2.ExecuteNonQuery()
</code>
 
Last edited:

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
I dont believe Access supports named parameters - use ? instead and add the values in order.
 

nick447923

Member
Joined
Nov 10, 2009
Messages
14
Programming Experience
Beginner
I have run successful inserts into Access 2007 databases using named parameters in VB.net. Perhaps I should of posted this in windows forms.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,333
Location
Norway
Programming Experience
10+
VB.Net parameters supports names, Access queries does not, while you can use names in the query only the place and value matters when it is processed by Access. Therefore the exact order of parameters is significant for Access. So when your query defines "VALUES (@CustNumber, @AcctNumber, @Slot, @Split," etc that is also the order you have to add parameters to the Parameters collection for it to work. You can use names in VB.Net here to make it easier to look up that parameter later if you want to make several queries with same command object, but otherwise these names are not used by Access. This is also what InertiaM was "suggesting".
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
example, if you have:

"INSERT INTO table VALUES(@a, @b, @c, @a)"

you cannot:
AddWithValue("@c", c)
AddWithValue("@b", b)
AddWithValue("@a", a)

you must:
AddWithValue("@a", a)
AddWithValue("@b", b)
AddWithValue("@c", c)
AddWithValue("@aa", a)

Actually, because the names are meaningless to the query you can write:
AddWithValue("@fadfasa", a)
AddWithValue("@bsadfsdf", b)
AddWithValue("@cgdfgd", c)
AddWithValue("@aajkyjkl", a)

If it helps you understand, consider that access takes your query with @names in and replaces them all with ? to become:
"INSERT INTO table VALUES(?,?,?,?)"
without your knowing

Hence only the order and the count matter, and you can call your vb params whatever you like
 

nick447923

Member
Joined
Nov 10, 2009
Messages
14
Programming Experience
Beginner
Thanks you guys for you help. I put the parameters back in the order they are listed in the insert statement and it works perfectly! Now I can move on to the next problem.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Read the DW3 link in my sig, section Creating a Simple Data App

Do your data access this way and you will never hae this problem, nor will you have to write tedious inserts such as this, again (and your code will be better structured)
 
Top Bottom