Parameter @name has no default value

[wilsonchama]

Hi guys

I am developing a simple job card creator application with VB.net 2012, using Access 2010 as database.

The code I have generated to write data from a datagridview to the database table is successfully saving the data, but with an error "Parameter @name has no default value". The code is shown below:

myConnToAccess = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "/JobcardGenerator.mdb")
            myConnToAccess.Open()

            Dim jobno As Integer
            Dim jobtype As String
            Dim jobdescription As String
            Dim jobstatus As String
            Dim datecreated As Date
            Dim plandate As Date
            Dim closedate As Date
            Dim workarea As String
            Dim subworkarea As String
            Dim machineno As String
            Dim machineserial As String
            Dim machinetype As String
            Dim machinestatus As String
            Dim proactus As String
            Dim enginehours As Long
            Dim transhours As Long
            Dim partno As String
            Dim partname As String
            Dim partdescription As String
            Dim stockcode As String
            Dim quantityreq As Integer
            Dim stockinhand As Integer
            Dim shortfall As Integer
            Dim sql As String

            For jobcard As Integer = 0 To dtgjobcard.Rows.Count - 1

                jobno = dtgjobcard.Rows(jobcard).Cells(0).Value
                jobtype = dtgjobcard.Rows(jobcard).Cells(1).Value
                jobdescription = dtgjobcard.Rows(jobcard).Cells(2).Value
                jobstatus = dtgjobcard.Rows(jobcard).Cells(3).Value
                datecreated = dtgjobcard.Rows(jobcard).Cells(4).Value
                plandate = dtgjobcard.Rows(jobcard).Cells(5).Value
                closedate = dtgjobcard.Rows(jobcard).Cells(6).Value
                workarea = dtgjobcard.Rows(jobcard).Cells(7).Value
                subworkarea = dtgjobcard.Rows(jobcard).Cells(8).Value
                machineno = dtgjobcard.Rows(jobcard).Cells(9).Value
                machineserial = dtgjobcard.Rows(jobcard).Cells(10).Value
                machinetype = dtgjobcard.Rows(jobcard).Cells(11).Value
                machinestatus = dtgjobcard.Rows(jobcard).Cells(12).Value
                proactus = dtgjobcard.Rows(jobcard).Cells(13).Value
                enginehours = dtgjobcard.Rows(jobcard).Cells(14).Value
                transhours = dtgjobcard.Rows(jobcard).Cells(15).Value
                partno = dtgjobcard.Rows(jobcard).Cells(16).Value
                partname = dtgjobcard.Rows(jobcard).Cells(17).Value
                partdescription = dtgjobcard.Rows(jobcard).Cells(18).Value
                stockcode = dtgjobcard.Rows(jobcard).Cells(19).Value
                quantityreq = dtgjobcard.Rows(jobcard).Cells(20).Value
                stockinhand = dtgjobcard.Rows(jobcard).Cells(21).Value
                shortfall = dtgjobcard.Rows(jobcard).Cells(22).Value

                sql = "INSERT INTO tblJobs([JobNo], [Jobtype], Jobdescription, Jobstatus, [Datecreated], [Plandate], [Closingdate], Workingarea, Subworkingarea, MachineNo, Machineserial, Machinetype, Machinestatus, Proactusrefno, Enginehours, Transhours, PartNo, Partname, Partdescription, Stockcode, Quantityreq, Quantityavailable, Shortfall) Values (@jno, @jtype, @jdescription, @jstatus, @dcreated, @pdate, @cdate, @warea, @sbwarea, @mno, @mserial, @mtype, @mstatus,  @proactus, @ehours, @thours, @pno, @pname, @pdescription, @stcode, @qtyreq, @stkinhand, @sfall)"

                Dim Comm As New OleDbCommand(sql, myConnToAccess)

                Comm.Parameters.AddWithValue("@jno", jobno)
                Comm.Parameters.AddWithValue("@jtype", jobtype)
                Comm.Parameters.AddWithValue("@jdescription", jobdescription)
                Comm.Parameters.AddWithValue("@jstatus", jobstatus)
                Comm.Parameters.AddWithValue("@dcreated", datecreated)
                Comm.Parameters.AddWithValue("@pdate", plandate)
                Comm.Parameters.AddWithValue("@cdate", closedate)
                Comm.Parameters.AddWithValue("@warea", workarea)
                Comm.Parameters.AddWithValue("@sbwarea", subworkarea)
                Comm.Parameters.AddWithValue("@mno", machineno)
                Comm.Parameters.AddWithValue("@mserial", machineserial)
                Comm.Parameters.AddWithValue("@mtype", machinetype)
                Comm.Parameters.AddWithValue("@mstatus", machinestatus)
                Comm.Parameters.AddWithValue("@proactus", proactus)
                Comm.Parameters.AddWithValue("@ehours", enginehours)
                Comm.Parameters.AddWithValue("@thours", transhours)
                Comm.Parameters.AddWithValue("@pno", partno)
                Comm.Parameters.AddWithValue("@pname", partname)
                Comm.Parameters.AddWithValue("@pdescription", partdescription)
                Comm.Parameters.AddWithValue("@stcode", stockcode)
                Comm.Parameters.AddWithValue("@qtyreq", quantityreq)
                Comm.Parameters.AddWithValue("@stkinhand", stockinhand)
                Comm.Parameters.AddWithValue("@sfall", shortfall)

                Comm.ExecuteNonQuery()
                Comm.Dispose()

This code like I said is quite ok posting the data to the database, but with the error i mentioned earlier. I seem not to understand why the error is produced because I have tried to assign default values to the table fields but it keeps doing the same.

Kindly assist me to identify the cause of the problem.

Thanks in advance

[Reverend Jim]

That seems like an odd error considering that you don't have a parameter named @name. Are you sure there isn't a typo in there? Please add the following code just above Comm.ExecuteNonQuery and post the output here

For Each item In Comm.Parameters
    Debug.WriteLine(item.parametername & " = """ & item.value & """")
Next

[rproffitt]

I have another thought on this. I don't see your database schema so What if? What if there's an @name column and it's value has no default? Then adding records missing that value may fail.

[Reverend Jim]

I considered that but the error seems to be with the parameter, not the table. An actual copy/paste of the error rather than a paraphrase might have been helpful. In any case, a table schema always helps.

[wilsonchama]

Thanks Reverend for your concern. Sorry I did not Explain fully, I only used the @name as an example. I actually do not have that parameter name.
In the first instance the parameter that had an error was @jobno, this was when the counting of the cells was starting with 1. But after changing the cells count start from 0 the error moved to @jtype.

[Reverend Jim]

Please add the loop code and post the results.

[rproffitt]

I'd use Reverend Jim's example to see what you are trying to insert in this Access table. We don't know your table design and if the item is blank then Access could cough up that error. Your choice to change the Access table to have default values.

[Mr.M]

I think you can solve that error by changing your loop to a For Each because with my guess based on your code you want to take each item and it subitems and assign it to corresponding variables so that you will be able to store them correctly on your database. Now the error is with the count because you refered it to jobcard which will change it value so I think using for Each Item in your tool do this will help because the index will be correcly adjusted with position as well in correspondence.