Newbie with DataAdapter Question

[atruhoo]

Looked through Forum and appears to me that I have everything mentioned in previous posts so I thought I would post in case someone see's something I missed.

Basically I am trying to add a record to Access DB to no resolve. I was getting errors earlier that I seemed to have corrected now can't figure out why it isn't adding record. Have posted the code so hope someone can help. Currently a VB6 programmer who's trying to teach self the .Net way and occasionally hit stumbling block.
Thanks in advance.

Imports System.Data.OleDb
Public Class PhoneBook

Inherits System.Windows.Forms.Form


Dim objConnection As New OleDb.OleDbConnection( _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\Administrator\Desktop\Phonebook.mdb&quot
Dim objPhBookDA As New OleDb.OleDbDataAdapter( _
"Select * from PhoneBook", objConnection)
Dim objPhBookCB As New OleDb.OleDbCommandBuilder(objPhBookDA)
Dim objDataSet As New DataSet()

Private Sub AddContact()

objPhBookDA.Fill(objDataSet, "PhoneBook&quot

Dim objRow As System.Data.DataRow

'Create a new DataSet object for this table
objRow = objDataSet.Tables("PhoneBook&quot.NewRow

objRow.Item("Name&quot = txtName.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("City&quot = txtCity.Text
objRow.Item("State&quot = txtState.Text
objRow.Item("ZipCode&quot = txtZip.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("HomePhone&quot = txtHomePhone.Text
objRow.Item("CellPhone&quot = txtCell.Text
objRow.Item("WorkPhone&quot = txtWorkPhone.Text
objRow.Item("E-Mail&quot = txtEMail.Text
objRow.Item("ContactType&quot = cboContact.Text
objRow.Item("Notes&quot = txtNotes.Text

objDataSet.Tables("PhoneBook&quot.Rows.Add(objRow)
objDataSet.AcceptChanges()
objPhBookDA.Update(objDataSet, "PhoneBook&quot

End Sub

Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
If (txtName.Text = "" And txtHomePhone.Text = "&quot Or (txtName.Text = "" And txtWorkPhone.Text = "&quot Then
MessageBox.Show("You must enter a Name and PhoneNumber&quot
Else
AddContact()
End If
End Sub
End Class

 

[Ragnarox]

Hi atruhoo,

The only thing that I can see that is missing is that you are not opening a connection to the database before you are trying to update.

Try this around your update statement.

try
objConnection.open
objPhBookDA.Update(objDataSet, "PhoneBook&quot
objConnection.close
Catch ex as exception
MsgBox(ex.ToString)
End Try

Hope this helps.

Brian

 

[atruhoo]

Thanks for the help Brian. Unfortunately that didn't help eas I am still not updating the database. I am able to do this if us code below, however is used masked edit field I did get error. I still hope someone can see something in previous code as I want to learn to utilize the data adapters and not always use the method used below.

Richard




Private Sub AddContact()
Dim strSQL As String
Dim objCommand As New OleDb.OleDbCommand()
strSQL = "Insert into PhoneBook (Name, HomePhone) " & _
"Values('" & txtName.Text & "', '" & txtHomePhone.Text & "')"

objCommand.CommandText = strSQL
objCommand.Connection = objConnection
objConnection.Open()
objCommand.ExecuteNonQuery()
objConnection.Close()
End Sub

 

[Ragnarox]

Hello Richard,

The only other thing that I can think of that you may want to try involves the BindingManagerBase. In my program in order to get the changes accepted into the dataset I had to use the EndCurrentEdit method of my variable.

ex:

variable.EndCurrentEdit

Hope this will work,

Brian

 

[Kris11]

Hi,

Try removing objDataSet.AcceptChanges(), because doing an accept change before Update would probably make the new row state to "Not Modified". So comment that line and try again.

-Kris

 

[atruhoo]

Sorry took so long to reply to possible options given, holidays.

Kris
tried commenting objDataSet.AcceptChanges() and received multiple step OLE DB generated errors.

Brian
Unsure as to what variable you were referring to. Tried with objRow as thought you must have meant BeginEdit and EndEdit. No luck though possible doing wrong.

Thank both of you for your help. I guess I will keep playing around and see if can figure out these DataAdapters. At least I know I can make things work using SQL and OleDbCommand().

Richard

 

[DotNetDoc]

What is the error you are getting "received multiple step OLE DB generated errors"

Make sure you put a try catch block around your code and catch the DB Error explicitly so you get a readable error message.

Try

'Put you code here

Catch ex as OLEDB.OLEDBException

Messagebox.show(ex.ToString)

End Try

If you want to paste your code in here so I can recreate it I could step through it for you. Let me know



DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb
-----------------------------------
If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[atruhoo]

DotNetDoc,

Thanks in advance for any input. Trying to teach myself all the nuances of .Net while trying to convince job to pay for training, but you probably know how that goes. If leave .AcceptChanges uncommented it runs with no errors, but doesn't update. Heres the code.

objPhBookDA.Fill(objDataSet, "PhoneBook&quot

Dim objRow As System.Data.DataRow

'Create a new DataSet object for this table
objRow = objDataSet.Tables("PhoneBook&quot.NewRow

objRow.Item("Name&quot = txtName.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("City&quot = txtCity.Text
objRow.Item("State&quot = txtState.Text
objRow.Item("ZipCode&quot = txtZip.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("HomePhone&quot = txtHomePhone.Text
objRow.Item("CellPhone&quot = txtCell.Text
objRow.Item("WorkPhone&quot = txtWorkPhone.Text
objRow.Item("Email&quot = txtEMail.Text
objRow.Item("ContactType&quot = cboContact.Text
objRow.Item("Notes&quot = txtNotes.Text

Try
objDataSet.Tables("PhoneBook&quot.Rows.Add(objRow)
objConnection.Open()
'objDataSet.AcceptChanges()
objPhBookDA.Update(objDataSet, "PhoneBook&quot
objConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
objConnection.Close()
End Try

As I stated previously I do have this working using SQL and OleDbCommand as seen below, but I want to understand how to do both ways.

Dim strSQL As String
Dim objCommand As New OleDb.OleDbCommand()
Dim myResult As DialogResult

strSQL = "Insert into PhoneBook (Name, Address, City, State, ZipCode, HomePhone, " & _
"WorkPhone, CellPhone, ContactType, Notes, Email)" & _
"Values('" & txtName.Text & "', '" & txtAddr.Text & "', '" & txtCity.Text & "', " & _
"'" & txtState.Text & "', '" & txtZip.Text & "', '" & txtHomePhone.Text & "', " & _
"'" & txtWorkPhone.Text & "', '" & txtCell.Text & "', '" & cboContact.Text & "', " & _
"'" & txtNotes.Text & "', '" & txtEMail.Text & "')"

Try
objCommand.CommandText = strSQL
objCommand.Connection = objConnection
myResult = MessageBox.Show("You are about to add a new record, continue?", "Info", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If myResult = DialogResult.Yes Then
objConnection.Open()
objCommand.ExecuteNonQuery()
objConnection.Close()
MessageBox.Show("New Record Added", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("No record added", "Info", MessageBoxIcon.Information)
End If
Catch ex As Exception
MsgBox(ex.ToString)
objConnection.Close()
End Try

 

[DotNetDoc]

You cant just catch a generic exception
Catch ex As Exception


You must catch the db exception.

Catch ex as OLEDB.OLEDBException

Messagebox.show(ex.ToString)

Make sure you comment out the Acceptchanges. The reason it works when you have this in is because it changes the rowstate of your dataset so that it thinks there are no updates to do.

If you comment it out for now and make sure you put in the new catch above you should get a error message you can handle. add the code and post the error here.


DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb
-----------------------------------
If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[atruhoo]

DotNetDoc,
Followed your above and this was the error message I got.

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Richard

 

[DotNetDoc]

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll


Means that the error was not caught.

Here is the try catch block again. I have a catch for both SQLClient and OLEDB. Make sure you use the right one and make sure it is before the generic exception.

Try



' Put you code here



Catch ex1 As System.Data.OleDb.OleDbException



Messagebox.show(ex1.ToString)



Catch ex2 As System.Data.SqlClient.SqlException



Messagebox.show(ex2.ToString)



Catch ex As Exception

Messagebox.show(ex.ToString)



End Try




Post your code with the Try Catch included.


DotNetDoc
M.C.S.D.

http://www.DotNetDoc.com

---------------------------------------

Tell me and I forget. Show me and I remember. Involve me and I understand.
- Anonymous Chinese Proverb
-----------------------------------
If you can't explain it simply, you don't understand it well enough.
- A. Einstein

 

[atruhoo]

DotNetDoc,

Added the change you suggested and the error was not caught again. Break came at objPhBookDA.Update(objDataSet, "PhoneBook&quot. Here is the code as requested.

objPhBookDA.Fill(objDataSet, "PhoneBook&quot

Dim objRow As System.Data.DataRow

'Create a new DataSet object for this table
objRow = objDataSet.Tables("PhoneBook&quot.NewRow

objRow.Item("Name&quot = txtName.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("City&quot = txtCity.Text
objRow.Item("State&quot = txtState.Text
objRow.Item("ZipCode&quot = txtZip.Text
objRow.Item("Address&quot = txtAddr.Text
objRow.Item("HomePhone&quot = txtHomePhone.Text
objRow.Item("CellPhone&quot = txtCell.Text
objRow.Item("WorkPhone&quot = txtWorkPhone.Text
objRow.Item("Email&quot = txtEMail.Text
objRow.Item("ContactType&quot = cboContact.Text
objRow.Item("Notes&quot = txtNotes.Text

Try
objDataSet.Tables("PhoneBook&quot.Rows.Add(objRow)
objConnection.Open()
'objDataSet.AcceptChanges()
objPhBookDA.Update(objDataSet, "PhoneBook&quot
objConnection.Close()
Catch ex As System.Data.OleDb.OleDbException
'MsgBox("HellO&quot added to ensure not getting to catch
MsgBox(ex.ToString)
objConnection.Close()
End Try

 

[AGIMA]

It sounds as if it could be your Insert object, can you post the Insert object creation code?

It can be found in the #Region " Web Form Designer Generated Code " and should look something like this:

'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO dbo.tblNews(Headline, DateAdded) VALUES (@Headline, @DateAdded); SELE" & _
"CT ID, Headline, DateAdded FROM dbo.tblNews WHERE (ID = @@IDENTITY) ORDER BY Dat" & _
"eAdded DESC"
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Headline", System.Data.SqlDbType.VarChar, 200, "Headline&quot)
Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DateAdded", System.Data.SqlDbType.DateTime, 8, "DateAdded&quot)
'


As you can see in the insert SQL it is a little different than normal, as it has another select statement. It also has paramenters assigned to the object.



AGIMA - professional web hosting is our business.

AGIMA Computing

http://www.agima.com.au

 

[atruhoo]

AGIMA,
This is a Windows Form and not a Web Form. Looked through code anyway though and didn't see anything like above. Is it possible b/c I hardcoded (see first post) and didn't use GUI that I am not seeing this.

Richard

 

[AGIMA]

Richard

Oh yeah, it is a windows form.... Not to worry the code is exactly same (Well the adapter anyways).

I would not recommend manually creating this adapters, when you use the GUI it creates all this other code that is needed for these adapters and the insert is just one of them.

It also create the update and delete SQL statements that also have parameters. So if have not created and insert command and attached it to your adapter (using paramters) then this is your problem.

It is however good to understand the workings of the adapter, so if I were you I would create the adapter through the GUI and then have a look at the code that it produced. This will give you some understanding of what the hell is happening... It all didnt make much sence to me until someone explained it, and now... still not much sence.. only joking.. Worth the look.

Note: .NET 2004 (more specifically ADO.NET v2)will be coming out with a better way to communicate with your data, so you might not even need to learn this method.

Let me know how you go.



AGIMA - professional web hosting is our business.

AGIMA Computing

http://www.agima.com.au

 

[atruhoo]

AGIMA,
According to this book I am reading this statement at bottom creates the Inserts, Deletes, etc for you. I am assuming it is correct since I am doing the Update using the same DataAdapter and that works fine. I think tis is why this is confusing me so much.

'objPhBookDA = OleDbDataAdapter
Dim objPhBookCB As New OleDb.OleDbCommandBuilder(objPhBookDA)

Richard

 

[AGIMA]

I have not personally used the OleDbCommandBuilder, but I would expect that it has the same limitations that the GUI has, and one of those is it sometimes will not create the other SQL statements or only create some of them.

The main reason for this is the Select statement not having any Keys or the table not having any Keys.

Can you return the SQL statements on each of the command objects for the adapter and post them.

AGIMA - professional web hosting is our business.

AGIMA Computing

http://www.agima.com.au

 

[CindiJ]

Atruhoo,

>however is used masked edit field I did get error.

I am a newbie to programming but I found that with a mask edit control I wasn't getting the text from the mask edit box. If I used this;

with mskEdBox
.SelLength = how ever long your string is
stringName = .SelText
end with

I was able to get the text from the box and insert it into the data base.

Good Luck

 

[atruhoo]

AGIMA,
Sorry took so long to respond out sick today and brain taking day off so not sure understand what you are looking for. There appears to be no SQL code created in Region area only SQL for the adapter is what I hardcoded and is shown here.

Dim objConnection As New OleDb.OleDbConnection( _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\Administrator\Desktop\Phonebook.mdb&quot
Dim objPhBookDA As New OleDb.OleDbDataAdapter( _
"Select * from PhoneBook", objConnection)
Dim objPhBookCB As New OleDb.OleDbCommandBuilder(objPhBookDA)
Dim objDataSet As New DataSet()

I too would agree with your previous response that statements not created if I was not able to do an update to the record using the same adapter that trying to use for adding record. So confused and probably shouldn't worry about it since have working using OleDbCommand and SQL but I can't stand it when I am unable to get something to work when I know it should. If this is not what you are looking for let me know and I will repost what you ask for.

CindiJ
I will try you suggestion. Do you have any problems reading data into MaskEd Control. Mine doesn't seem to want to display value eventhough it indicates the masked.text is set to value from DB.

Thanks,
Richard

 

[CindiJ]

Richard,

No, don't have any problem using it as long as I use that snippet of code where ever I get info from the axmaskedit box. Without it I get nothing. I have used it with Access but have yet to try it with SQL.