Question Parameter ?_1 has no default value

[dineshkumaar]

I am frustrated getting this message again and again.

I have developed a database with built in dataentry form. This database is distributed to many different users for dataentry. After data entry, I want to Merge all the user files to one Master Database. For that I wanted to create a merge utility in vb.net. The form will ask the Master Databse Location and 1 User database location. there are two datagridview for browising current table for Master and Databse and a start button to merge the table. When the start button is clicked, It will fill data from master and user database and start the merging. On clicking merege, I get the error - "Parameter ?_1 has no default value"

The entiore form code is given below

Imports System.IO.Stream
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.Common

Public Class Form1
    Dim MasterFileName As String
    Dim UserFileName As String

    Dim strProviderM As String
    Dim strSqlM As String
    Dim ConM As OleDbConnection
    Dim cmdM As OleDbCommand
    Dim daM As OleDbDataAdapter
    Dim faM As DataTable

    Dim strProviderU As String
    Dim ConU As OleDbConnection
    Dim cmdU As OleDbCommand
    Dim daU As OleDbDataAdapter
    Dim faU As DataTable

    'Dim tblNo As Int16
    Dim TableNo As Int16
    Dim TablesList() As String = {"FarmersAddress", "FarmersTelephone", "FarmersLands", "FarmersCrops", "FarmersBorewells", _
                                  "FarmersIrrigation", "FarmersBorewells", "FarmersSugarCaneDisposal", _
                                  "FarmersBiomass", "FarmersBankAcs"}


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnMaster_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMaster.Click

        If Not ofdBrowse.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        MasterFileName = ofdBrowse.SafeFileName
        txtMaster.Text = MasterFileName


    End Sub

    Private Sub btnChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChild.Click

        If Not ofdBrowse.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        UserFileName = ofdBrowse.SafeFileName
        txtUser.Text = UserFileName

        'Dim strProviderU As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + UserFileName + ";"
        'Dim strSqlU As String = "SELECT * FROM FarmersAddress"
        'Dim conU As New OleDbConnection(strProviderU)
        'Dim cmdU As New OleDbCommand(strSqlU, conU)
        'conU.Open()
        'cmdU.CommandType = CommandType.Text
        'Dim daU As New OleDbDataAdapter(cmdU)
        'Dim faU As New DataTable("FarmersAddress")
        'daU.Fill(faU)
        'dgvUser.DataSource = faU

    End Sub

    Private Sub MergeTables()
        Dim UserRow As DataRow
        Dim MasterRow As DataRow
        Dim n As Int16 = 0
        Dim Errors As Int16 = 0
        Dim ProgStep As Int16 = faU.Rows.Count / 100
        Dim SelectCmdText As String
        Dim InsertCmdText As String
        Dim UpdateCmdText As String
        'Dim CmdParText As String
        Dim ColName As String
        Dim dbColumn As DataColumn

        For Each UserRow In faU.Rows
            SelectCmdText = "Select "
            InsertCmdText = "Insert into `" + TablesList(TableNo) + "` ("
            UpdateCmdText = "Update `" & TablesList(TableNo) & "` Set "
            MasterRow = faM.NewRow()

            daM.InsertCommand = New OleDbCommand
            daM.InsertCommand.Connection = ConM

            For n = 0 To faU.Columns.Count - 1
                ColName = faU.Columns(n).ColumnName
                SelectCmdText += "[" + ColName + "]" + ", "

                ColName = "[" + ColName + "]"
                dbColumn = faU.Columns(n)
                InsertCmdText += ColName + ", "

                daM.InsertCommand.Parameters.Add(ColName, GetOleDbType(dbColumn.DataType))
                'daM.InsertCommand.Parameters(ColName).Value = DBNull.Value
                'daM.InsertCommand.CommandText = "insert into FarmersAddress(FarmersName) values (?)"
                'daU.UpdateCommand.Parameters.Add(ColName, GetOleDbType(dbColumn.DataType))

                MasterRow(n) = UserRow(n)
                'If Len(MasterRow(n).ToString.Trim) = 0 Then
                '    MasterRow(n) = #1/1/2009#
                'End If
                txtMsg.Text += vbCrLf + UserRow(n).ToString
            Next
            InsertCmdText = Mid(InsertCmdText, 1, Len(InsertCmdText) - 2) + ")" + " Values("
            SelectCmdText = Mid(SelectCmdText, 1, Len(SelectCmdText) - 2) + " From " + TablesList(TableNo)

            For n = 0 To faU.Columns.Count - 1
                InsertCmdText += "?, "
            Next
            InsertCmdText = Mid(InsertCmdText, 1, Len(InsertCmdText) - 2) + ")"
            txtMsg.Text += InsertCmdText + vbCrLf

            '"INSERT INTO `FarmersAddress` (`UnitCode`, `CaneGrowerNo`, `FarmerName`, `FathersName`, `Village`, `Panchayat`, `StateCode`, `DistrictCode`, `Pincode`, `Block Code`, `DOB`, `BPLstatus`, `DifferentMailAddress`, `MVillage`, `MPanchayat`, `MStateCode`, `MDistrictCode`, `MPincode`, `Age`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            '"INSERT INTO FarmersAddress (UnitCode, CaneGrowerNo, FarmerName, FathersName, Village, Panchayat, StateCode, DistrictCode, Pincode, Block Code, DOB, BPLstatus, DifferentMailAddress, MVillage, MPanchayat, MStateCode, MDistrictCode, MPincode, Age) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

            'InsertCmdText = "INSERT INTO `FarmersAddress` (`UnitCode`, `CaneGrowerNo`, `FarmerName`, `FathersName`, `Village`, `Panchayat`, `StateCode`, `DistrictCode`, `Pincode`, `Block Code`, `DOB`, `BPLstatus`, `DifferentMailAddress`, `MVillage`, `MPanchayat`, `MStateCode`, `MDistrictCode`, `MPincode`, `Age`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            daM.SelectCommand.CommandText = SelectCmdText
            daM.InsertCommand.CommandText = InsertCmdText

            Try
                daM.Fill(faM)
            Catch
            End Try

            faM.Rows.Add(MasterRow)

            Try
                'faM.AcceptChanges()
                'faM.BeginInit()
                'faM.EndInit()
                daM.Update(faM)
                MsgBox("success")
            Catch ex As Exception
                txtMsg.Text += vbCrLf + ex.Message + vbCrLf + "Stack Trace : -->" + ex.StackTrace + vbCrLf + "Source: -->" + vbCrLf + ex.Source + vbCrLf

                Errors += 1
            End Try
            ProgressBar.Value += ProgStep
        Next
        txtMsg.Text += vbCrLf + "Merge Completed with " + Errors.ToString + " Errors"
        txtMsg.Text += vbCrLf + "-------------------------------------------------------------------------------"
        txtMsg.Text += vbCrLf + vbCrLf

    End Sub

    Private Sub ShowTables()



        For tblNo = 0 To TablesList.Length - 1

            ConM = New OleDbConnection
            cmdM = New OleDbCommand
            daM = New OleDbDataAdapter

            ConU = New OleDbConnection
            cmdU = New OleDbCommand
            daU = New OleDbDataAdapter

            strProviderM = New String("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + MasterFileName + ";")
            ConM.ConnectionString = strProviderM

            strProviderU = New String("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + UserFileName + ";")
            ConU.ConnectionString = strProviderU

            strSqlM = "SELECT * FROM " & TablesList(tblNo)

            '******************** Open Master Database
            cmdM.CommandText = (strSqlM)
            cmdM.Connection = ConM
            ConM.Open()
            cmdM.CommandType = CommandType.Text

            daM.SelectCommand = cmdM

            faM = New DataTable(TablesList(tblNo))

            daM.Fill(faM)

            dgvMaster.DataSource = faM


            '******************** Open User Database
            cmdU.CommandText = (strSqlM)
            cmdU.Connection = ConU
            ConU.Open()
            cmdU.CommandType = CommandType.Text

            daU.SelectCommand = cmdU
            faU = New DataTable(TablesList(tblNo))

            daU.Fill(faU)
            dgvUser.DataSource = faU
            txtMsg.Text += "Starting Merge Process for table: " + TablesList(tblNo) + Chr(13)
            MergeTables()
        Next
    End Sub

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
        ShowTables()
    End Sub

    ' Return the OleDbType that represents the specified system type
    ' This is particularly useful when you have a DataTable and want to create a 
    ' OleDbParameter for onw of its columns, but don't know its type. 
    ' DataColumnName.DataType returns a Type, and you can convert it to OleDbType 
    ' by passing it to this function.
    ' Note: requires System.Data.OleDb
    ' Example:
    '   Dim param As New OleDbParameter("TestCol", GetOleDbType(String))
    Private Function GetOleDbType(ByVal sysType As Type) As OleDbType
        If sysType Is GetType(String) Then
            Return OleDbType.VarChar
        ElseIf sysType Is GetType(Integer) Then
            Return OleDbType.Integer
        ElseIf sysType Is GetType(Boolean) Then
            Return OleDbType.Boolean
        ElseIf sysType Is GetType(Date) Then
            Return OleDbType.Date
        ElseIf sysType Is GetType(Char) Then
            Return OleDbType.Char
        ElseIf sysType Is GetType(Decimal) Then
            Return OleDbType.Decimal
        ElseIf sysType Is GetType(Double) Then
            Return OleDbType.Double
        ElseIf sysType Is GetType(Single) Then
            Return OleDbType.Single
        ElseIf sysType Is GetType(Byte()) Then
            Return OleDbType.Binary
        ElseIf sysType Is GetType(Guid) Then
            Return OleDbType.Guid
        End If
    End Function

End Class

Please help me out!

Thanks
Dinesh Kumaar

 

[jmcilhinney]

1. We'd rather just the relevant code to all the code. Having to trawl through your code to determine what's relevant makes it harder for us and less likely that you'll get the help you want. Also, posting code that's been commented out just makes it more confusing still for us.

2. You might want to consider calling ToString on your exceptions rather than that long and messy method of displaying the error information. If you don't want to call ToString then you should consider using String.Format to build composite strings because lots of &'s like that is hard to read and, therefore, error prone.

3. It's really too hard to see what your code is doing without watching it run. Again, I suggest that use String.Format to build composite strings and you should also make use of String.Join. As an example, try running this code:

Dim tableName = "Table1"
Dim columnList = New String() {"Column1", "Column2", "Column3"}
Dim valueList = New String() {"?", "?", "?"}
Dim sql = String.Format("INSERT INTO [{0}] ([{1}]) VALUES ({2})", _
                        tableName, _
                        String.Join("], [", columnList), _
                        String.Join(", ", valueList))

MessageBox.Show(sql)

There's no doubt that you could make your code easier to read doing that sort of thing.

 

[InertiaM]

Firstly, read jmcilhinney's post carefully - it will definitly help you write better code.

To answer your post, I would investigate why you are supplying parameters but no values for your Insert and Update command.

 

[dineshkumaar]

Thanks 4 your suggessions.

I will tell you what I exactly want

I want to write a MergeUtility. This uitility will ask the user the Master Databse Location where the Records should be appended and the User Database from which Records should be taken to append to the master database. The database contains 28 Tables, but I just want to Append Records from the tables listed below from UserDatabase to the Corresponding Tables in the Master Database. Can you please write the code for me.


Dim TablesList() As String = {"FarmersAddress", "FarmersTelephone", "FarmersLands", "FarmersCrops", "FarmersBorewells", _
"FarmersIrrigation", "FarmersBorewells", "FarmersSugarCaneDisposal", _
"FarmersBiomass", "FarmersBankAcs"}


Thanks

 

[jmcilhinney]

Can you please write the code for me.

That's not really why I post here. How about you follow the advice provided so far to clean up your code somewhat and then, if you can't see what's wrong, at least we'll be better able to see what's happening and then we can help you improve the code further. Doing is the best way to learn, not having others do for you.

 

[dineshkumaar]

Ok ! Thanks again 4 ur suggessions. I improve my code and post it back on this forum for help

Thanks

 

[dineshkumaar]

Resolved - Parameter ?_1 Has No Default Value

Hello Sir..

Thankyou for all your suggession in improving my code and making it easy to read for every one.

I have resolved my Problem. The problem was that I was trying to using the DataAdapter.Update(Dataset, Tablename) to update the Newly added rows from the user table to the master table. It requires a valid InsertCommand and UpdateCommand with the parameters in the dataadapter. I was manually providing the Insert and Update command for the dataadapter along with the parameters. But it didn't working for whatever reason. After Googling more I found a thead from [ame=http://www.vbforums.com/showthread.php?t=548082][2008] Update unable to find TableMapping['Table'] or DataTable 'Table' - VBForums[/ame] from where I clicked the Idea of using OledbCommandBuilder which generated the Insert Update and Delete commands automatically based on the results of Select Command. It also manages the parameters. That's all!

I have given my entire code below...

Thanks Once Again Sir..

Imports System.IO.Stream
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.Common
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO

Public Class Form1
    Dim MasterFileName As String
    Dim UserFileName As String
    Dim TableNo As Int16

    'List of Tables to Merge From User Database to Master Database
    Dim TablesList() As String = {"FarmersAddress", "FarmersTelephone", "FarmersLands", "FarmersCrops", "FarmersBorewells", _
                                  "FarmersIrrigation", "FarmersBorewells", "FarmersSugarCaneDisposal", _
                                  "FarmersBiomass", "FarmersBankAcs"}


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub

    Private Sub btnMaster_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMaster.Click

        If Not ofdBrowse.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        MasterFileName = ofdBrowse.SafeFileName
        txtMaster.Text = MasterFileName

    End Sub

    Private Sub btnChild_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChild.Click
        If Not ofdBrowse.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Exit Sub
        End If

        UserFileName = ofdBrowse.SafeFileName
        txtUser.Text = UserFileName

    End Sub

    Private Sub MergeTables()
        For x = 0 To TablesList.Length - 1
            OpenTable(MasterFileName, UserFileName, TablesList(x))
        Next
    End Sub

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
        MergeTables()
    End Sub

    Private Sub OpenTable(ByVal MasterDataBaseName As String, ByVal UserDataBaseName As String, ByVal TableName As String)
        Try

            '****** Master Database
            ' Define a connection object 
            Dim dbConn As New OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", MasterDataBaseName))

            ' Create a data adapter to retrieve records from db
            Dim daMaster As New OleDbDataAdapter(String.Format("SELECT * From {0}", TableName), dbConn)
            Dim dsMaster As New DataSet()
            Dim cmbuilder As New OleDbCommandBuilder(daMaster)

            '******* User Database
            Dim dbConnUser As New OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", UserDataBaseName))
            Dim daUser As New OleDbDataAdapter(String.Format("SELECT * From {0}", TableName), dbConnUser)
            Dim dtUser As New DataTable(TableName)

            ' Fill the Master dataset
            daMaster.Fill(dsMaster, TableName)

            ' Fill User Table
            daUser.Fill(dtUser)

            ' Go through the records and Assign UserTable Column Values to MasterTable Columns
            Dim r As DataRow

            For Each r In dtUser.Rows
                Dim NewMasterRow = dsMaster.Tables(TableName).NewRow
                For n = 0 To dtUser.Columns.Count - 1
                    NewMasterRow(n) = r(n)
                Next
                dsMaster.Tables(TableName).Rows.Add(NewMasterRow)
                daMaster.Update(dsMaster, TableName)
            Next

            dgvMaster.DataSource = dsMaster.Tables(TableName)
            dgvUser.DataSource = dtUser
        Catch ex As Exception
            'An error occurred. Show the error message
            MsgBox(ex.Message)
        End Try

    End Sub

End Class

 

[cjard]

If you read the DW2 link in my signature, you'll get pointers on how to create data access classes in your project in seconds.. Start with reading the Creating a Simple Data App link.

All the code you have here, I think would take me about 5 minutes to write, using the advice found in that link (and it would be more secure, faster code too, because Visual Studio writes it in a very good way)