tags:

views:

250

answers:

0

Hi, AM a Beginner AM faceing a problem with updating my data adapter.my database is MS Access and my table to be updated is course all its field except courseid(number) are text. Am facing the problem i.e failed to conver parameter from string to int32 when i click save button When the user clicks add the controls clear and user enters data and clicks save . My query here am passing all string values and am not converting any string to INT32.. still am getting error.... i have attached my form_load , save button and add button code for your reference Help me please rectify this problem... Thanks in Advance

Private Sub newcourse_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.Cursor = Cursors.WaitCursor
        Dim connectionstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MY Projects - Dont delete\New Folder\LinkingOverseas_IMS\LinkingOverseas_IMS1\Linkingoverseas.mdb"
        Dim con As New OleDbConnection(connectionstring)
        MsgBox("A Connection to the Database is now open")
        con.Open()
        Dim dataset As New DataSet
' just populating the combobox using our_universities table
        sql = "SELECT * FROM ours_universities"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(dataset, "ours_universities")
        Dim datatable As New DataTable
        datatable = dataset.Tables("ours_universities")
        Dim i As Integer
        For i = 0 To datatable.Rows.Count - 1
            ComboBox1.Items.Add(datatable.Rows(i).Item(1))
        Next
' second table
        Dim commandstring As String = "SELECT * FROM COURSE"
        dataAdapter = New OleDbDataAdapter(commandstring, con)
        dataAdapter.Fill(ds, "COURSE")
        Dt = ds.Tables("COURSE")
        currRec = 0
        totalRec = Dt.Rows.Count       
        Me.TXTUNIVNAME.Text = Dt.Rows(currRec)("UNIVERSITYNAME").ToString()
        Me.TXTCOURSE.Text = Dt.Rows(currRec)("COURSENAME").ToString()
        Me.TXTDURATION.Text = Dt.Rows(currRec)("DURATION").ToString()
        Me.TXTDEGREE.Text = Dt.Rows(currRec)("DEGREE").ToString()
        Me.TXTENTRYREQ.Text = Dt.Rows(currRec)("ENTRY_REQ").ToString()
        Me.TXTENG_REQ.Text = Dt.Rows(currRec)("ENG_REQ").ToString()
        Me.TXTCATEGORY.Text = Dt.Rows(currRec)("CATEGORY").ToString()
        Me.TXTFEE.Text = Dt.Rows(currRec)("TUTION_FEES")
        Me.TXTUNIV_OTHERS.Text = Dt.Rows(currRec)("COMMENTS").ToString()
        Me.TXTINTAKE.Text = Dt.Rows(currRec)("AVAILABLE").ToString()
               Me.Cursor = Cursors.Default
        dataAdapter.InsertCommand = con.CreateCommand()
        dataAdapter.InsertCommand.CommandText = _
            "INSERT INTO COURSE " + _
            "(COURSENAME, UNIVERSITYNAME, DEGREE, DURATION, ENTRY_REQ,ENG_REQ,CATEGORY,TUTION_FEES,COMMENTS,AVAILABLE) " + _
            "VALUES(@COURSENAME, @UNIVERSITYNAME, @DEGREE, @DURATION, @ENTRY_REQ,@ENG_REQ,@CATEGORY,@TUTION_FEES,@COMMENTS,@AVAILABLE)"
        AddParams(dataAdapter.InsertCommand, "COURSENAME", "UNIVERSITYNAME", "DEGREE", _
        "DURATION", "ENTRY_REQ", "ENG_REQ", "CATEGORY", "TUTION_FEES", "COMMENTS", "AVAILABLE")
        ' Preparing Update SQL Command
        dataAdapter.UpdateCommand = con.CreateCommand()
        dataAdapter.UpdateCommand.CommandText = _
            "UPDATE COURSE SET " + _
            "COURSENAME = @COURSENAME, UNIVERSITYNAME = @UNIVERSITYNAME, DEGREE = @DEGREE, " + _
            "DURATION = @DURATION,ENTRY_REQ=@ENTRY_REQ,ENG_REQ=@ENG_REQ,CATEGORY=@CATEGORY," + _
            "TUTION_FEES=@TUTION_FEES,COMMENTS=@COMMENTS,AVAILABLE=@AVAILABLE"
        AddParams(dataAdapter.UpdateCommand, "COURSENAME", "UNIVERSITYNAME", "DEGREE", _
        "DURATION", "ENTRY_REQ", "ENG_REQ", "CATEGORY", "TUTION_FEES", "COMMENTS", "AVAILABLE")
        ' Preparing Delete SQL Command
        dataAdapter.DeleteCommand = con.CreateCommand()
        dataAdapter.DeleteCommand.CommandText = "DELETE FROM COURSE WHERE COURSEID = @COURSEID"
        AddParams(dataAdapter.DeleteCommand, "COURSEID")
End Sub

Private Sub BUTTONSAVE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BUTTONSAVE.Click
        MsgBox(" SAVING CHANGES....")
        Me.Cursor = Cursors.WaitCursor
        Dim row As DataRow = Dt.Rows(currRec)
        row.BeginEdit() 
        row("COURSENAME") = TXTCOURSE.Text
        row("UNIVERSITYNAME") = ComboBox1.SelectedItem
        row("DEGREE") = Me.CBODEGREE.SelectedItem
        row("DURATION") = Me.TXTDURATION.Text
        row("ENTRY_REQ") = Me.TXTENTRYREQ.Text
        row("ENG_REQ") = Me.TXTENG_REQ.Text
        row("CATEGORY") = Me.ComboCATEGORY.SelectedItem
        row("TUTION_FEES") = Me.TXTFEE.Text
        row("COMMENTS") = Me.TXTUNIV_OTHERS.Text
        row("AVAILABLE") = Me.CBOINTAKE.SelectedItem
        row.EndEdit()
        dataAdapter.Update(ds, "COURSE")
        ds.AcceptChanges() '  FAILED to convert parameter value from a string to Int32
        insertSelected = False
        Me.Cursor = Cursors.Default
        MsgBox("SAVED CHANGES......!!!!")

End Sub

Private Sub Butadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Butadd.Click
        insertSelected = True
        Dim row As DataRow = Dt.NewRow()
        Dt.Rows.Add(row)       
        totalRec = Dt.Rows.Count
        currRec = totalRec - 1       
        CLEAR()        
        buttoncontrols()
End Sub