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