data adapter update method won't work

[SethR]

Someone please help me before I smash my computer.

Every time I try to update a database using my data adapters update method I get the following error.....Object variable or With block variable not set.

I can't for the life of me figure out why this is happening. Here's the code if anyone cares to look.

Public Class Form1

Inherits System.Windows.Forms.Form
Dim conService As String
Dim currpath As String = System.Environment.CurrentDirectory
Dim sqlStr As String
Dim sqlStr2 As String
Dim serviceDT As New DataTable
Dim serviceDS As New DataSet
Public daService
Dim daStatus
Dim rowIndx As Integer
Dim currMan As CurrencyManager

Dim cbServiceCalls As New System.Data.OleDb.OleDbCommandBuilder

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
conService = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & currpath & "\ServiceCalls.mdb"
sqlStr = "Select * from ServiceCalls "
sqlStr2 = "Select * from ServiceStatus"
Dim daService As New OleDb.OleDbDataAdapter(sqlStr, conService)
Dim daStatus As New OleDb.OleDbDataAdapter(sqlStr2, conService)
daService.Fill(serviceDT)
daService.Fill(serviceDS, "ServiceCalls")
daStatus.Fill(serviceDS, "ServiceStatus")
DataGrid1.DataSource = serviceDS.Tables("ServiceCalls")
DataGrid2.DataSource = serviceDS.Tables("ServiceStatus")

currMan = Me.BindingContext(serviceDS, "ServiceCalls")

daService.Dispose()
FillTextBoxes()

Sub FillTextBoxes()
txtSerOrdNum.Text = CInt(serviceDT.Rows(rowIndx)("ServiceOrdNum"))
txtCustId.Text = CInt(serviceDT.Rows(rowIndx)("CustomerID#"))
txtReceived.Text = CStr(serviceDT.Rows(rowIndx)("CallReceivedOn"))
txtMachine.Text = CStr(serviceDT.Rows(rowIndx)("MachineModel"))
txtSerial.Text = CInt(serviceDT.Rows(rowIndx)("SerialNum"))
txtProblem.Text = CStr(serviceDT.Rows(rowIndx)("ProblemDescription"))
txtTech.Text = CStr(serviceDT.Rows(rowIndx)("AssignedTech"))

End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If rowIndx < serviceDT.Rows.Count - 1 Then
rowIndx = rowIndx + 1
FillTextBoxes()
Else
rowIndx = 0
FillTextBoxes()
End If

End Sub

Private Sub btnADDds_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADDds.Click
Dim newRec As DataRow
newRec = serviceDS.Tables("ServiceCalls").NewRow()
newRec("ServiceOrdNum") = InputBox("Enter service order number", "newRec")
newRec("CustomerID#") = InputBox("Enter the customer id number", "newRec")
newRec("CallReceivedOn") = InputBox("Enter call entry date", "newRec")
newRec("MachineModel") = InputBox("Enter the machine model", "newRec")
newRec("SerialNum") = InputBox("Enter the serial number", "newRec")
newRec("ProblemDescription") = InputBox("Enter the problem description", "newRec")
newRec("AssignedTech") = InputBox("Enter the assigned tech", "newRec")
serviceDS.Tables("ServiceCalls").Rows.Add(newRec)

End Sub

Private Sub btnADDdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADDdb.Click

daService.Update(serviceDS)<<<:mad:


End Sub
End Class

[Exelio]

hi seth,

Looks like u r really frustated.U need to make slight changes in the code. Below is the code with the changes.

Dim conService As New SqlConnection("ur path")
Dim currpath As String = System.Environment.CurrentDirectory
Dim sqlStr As String
Dim sqlStr2 As String
Dim serviceDT As New DataTable
Dim serviceDS As New DataSet
Dim daService, daStatus As New SqlDataAdapter
Dim rowIndx As Integer
Dim currMan As CurrencyManager

PrivateSub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
sqlStr = "Select * from "
sqlStr2 = "Select * from "
daService.SelectCommand = New SqlCommand(sqlStr, conService)
Dim cbServiceCalls As New SqlCommandBuilder(daService)
daStatus.SelectCommand = New SqlCommand(sqlStr2, conService)
conService.Open()
daService.Fill(serviceDT)
daService.Fill(serviceDS, "")
daStatus.Fill(serviceDS, "")
DataGrid1.DataSource = serviceDS.Tables("")
DataGrid2.DataSource = serviceDS.Tables("("")
currMan = Me.BindingContext(serviceDS, "")
FillTextBoxes()
End Sub

Sub FillTextBoxes()
txtSerOrdNum.Text = CInt(serviceDT.Rows(rowIndx)("ServiceOrdNum"))
txtCustId.Text = CInt(serviceDT.Rows(rowIndx)("CustomerID#"))
txtReceived.Text = CStr(serviceDT.Rows(rowIndx)("CallReceivedOn"))
txtMachine.Text = CStr(serviceDT.Rows(rowIndx)("MachineModel"))
txtSerial.Text = CInt(serviceDT.Rows(rowIndx)("SerialNum"))
txtProblem.Text = CStr(serviceDT.Rows(rowIndx)("ProblemDescription"))
txtTech.Text = CStr(serviceDT.Rows(rowIndx)("AssignedTech"))


End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If rowIndx < serviceDT.Rows.Count - 1 Then
rowIndx = rowIndx + 1
FillTextBoxes()
Else
rowIndx = 0
FillTextBoxes()
End If

End Sub


Private Sub btnADDds_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADDds.Click
Dim newRec As DataRow
newRec = serviceDS.Tables("ServiceCalls").NewRow()
newRec("ServiceOrdNum") = InputBox("Enter service order number", "newRec")
newRec("CustomerID#") = InputBox("Enter the customer id number", "newRec")
newRec("CallReceivedOn") = InputBox("Enter call entry date", "newRec")
newRec("MachineModel") = InputBox("Enter the machine model", "newRec")
newRec("SerialNum") = InputBox("Enter the serial number", "newRec")
newRec("ProblemDescription") = InputBox("Enter the problem description", "newRec")
newRec("AssignedTech") = InputBox("Enter the assigned tech", "newRec")
serviceDS.Tables("ServiceCalls").Rows.Add(newRec)
End Sub

Private Sub btnADDdb_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnADDdb.Click
daService.Update(serviceDS, "maintable")
daService.Dispose()
MsgBox("Updated", MsgBoxStyle.Information)
End Sub

Hope this helps.

Thanks and regards

Exelio:)

[SethR]

Thank you....I will try you're changes as soon as I get back from work....

[SethR]

It's not working.....I'm using an Access database so I can use a sqlcommand builder.

[Exelio]

hi seth,

I have tried the same coding with access too and i dont seem to have a problem.

Check ur provider path again.
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=0;Data Source=;Jet OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1


When u r using access,use OledbCommandBuilder and not sqlcommandBuilder.

Can u tell me exactly what kind of error u get?

Thanks

Regards

Exelio

[chan_lemo]

Try the follwoing link , it explin DataAdapter.Update method.

http://vb.net-informations.com/dataadapter/dataadapter-updatecommand-sqlserver.htm

lemo.

[reenez]

hullo, i have tried to use the update property in access but it has failed to work out i get this error syntax UPDATE .please help me