Losing patience with .NET... one thing after another... .Update Method..

[graham]

Hi all,

<bitching and moaning section>
I am asking for any help I can get here... I am at the end of my tether... I
don;t consider myself a genius in any way whatsoever, but I do believe I
have a logical way of thinking that has served me well during my years of
application development with various tools...

But having been working with VB.NET now for close to a month... I am
beginning to doubt my abilities...

I have a small project to complete.. and since the momkent I opened up the
IDE for the first time, I have peppered various n/g's.. developer pages and
google searches for information...

There never appears to be a definitive answer/resolution to a question...

I am now sitting at my notebook fighting off the urges to throw the damn
machine into the garbage...

<the problem>
I believe I have followed the advice I have received and implemented my
application correctly.

I have defined a dataadaptor - generated datasets, created relations, and
used the bindingmanagerbase as recommended EVERYTHING appears to work.. then
I click my save button and try to perform my .Update after first checking
for .HasChanges. and BANG.

I get "Syntax error in INSERT INTO statement."

I have a correct .xsd file, my InsertCommand text is fine, if I interrogate
the values in my datarows before I perform an update, they are there.
however, if I investigate the values of the parameters, there is nothing
there - but then again, I have no idea (nor would I know if I was supposed
to have an idea) when the values of my dataset are supposed to be assigned
to the parameters. Also, I dont see a way of finding the actual command
being issued on the database..

Anyway, I will as ever... show you my code below... and if anyone has any
ideas.. I'd appreciate some help... before I lose my mind completely.

Here's the complete code:-

Private dataSummit As New SummitDataAccess.SummitDataAccess

' path variables for databases;
Dim strNMANpath As String
Dim strLinkMANpath As String

Dim ClientID As String

Dim b_selected_db As Boolean = False
Dim int_current_year As Int16

Dim bmbLinkMAN_farm_trans As BindingManagerBase

Dim dvLinkMAN_farm As DataView
Dim dvLinkMAN_farm_trans As DataView

Dim daSummit_farm As New OleDbDataAdapter
Dim dsSummit_farm As New DataSet
Dim dvSummit_farm As DataView

(windows generated code removed here to save space)

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

bmbLinkMAN_farm_trans = Me.BindingContext(dsLinkMAN_farm_trans_1,
"farm_trans")

' get current year and make the default
int_current_year = Convert.ToInt16(Format(Now(), "yyyy") + 1)

Dim var_year As Int16
For var_year = (int_current_year - 10) To (int_current_year + 10)
ddYear.Items.Add(var_year.ToString)
Next
ddYear.Text = int_current_year.ToString

strLinkMANpath = "..\data\LinkMAN.mdb"

Dim str As String = "c:\sst\ReferenceData.mdb"

dataSummit.ReferenceDatabasePath = "..\data\ReferenceData.mdb"

str = "c:\sst\UserData.mdb"
dataSummit.UserDatabasePath = "..\data\UserData.mdb"

End Sub
Private Sub CompareTables()

GetLinkMAN_FarmTrans()
GetNMAN_Farms()
GetSummitFarms()
GetChanges()

End Sub
Private Sub GetLinkMAN_FarmTrans()

' setup connection to LinkMAN database
With daLinkMAN_farm_trans

.SelectCommand.Parameters("summit_client_id").Value = ClientID
.SelectCommand.Parameters("year").Value = int_current_year
.Fill(dsLinkMAN_farm_trans_1, "farm_trans")

End With


End Sub
Private Sub GetNMAN_Farms()

With daNMAN_farm

.SelectCommand.Parameters("year").Value = int_current_year
.Fill(dsNMAN_farm_1)

dsLinkMAN_farm_trans_1.Tables("farm").Clear()
.Fill(dsLinkMAN_farm_trans_1, "farm")

End With

End Sub
Private Sub GetSummitFarms()

Dim id As String
Dim name As String

Try
Dim rsfarms As ADODB.Recordset = dataSummit.GetFarms(ClientID)

If rsfarms.State <> ADODB.ObjectStateEnum.adStateClosed Then

daSummit_farm.Fill(dsSummit_farm, rsfarms, "Farms")

For Each dr As DataRow In dsSummit_farm.Tables("Farms").Rows

id = dr.Item(0).ToString
name = dr.Item(1).ToString

Next

End If

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub
Private Sub GetChanges()

' Find changes between databases
Dim drLinkMAN As DataRow

For Each drNMAN_farm As DataRow In dsNMAN_farm_1.Tables("farm").Rows

With daLinkMAN_trans_counter

.SelectCommand.Parameters("year").Value = int_current_year
.SelectCommand.Parameters("farm_id").Value =
drNMAN_farm("farm_id")
.Fill(dsLinkMAN_farm_trans_1, "trans_counter")

End With

If
dsLinkMAN_farm_trans_1.Tables("trans_counter").Rows(0).Item("number_records"
) = 0 Then

' add to our list of transactions
drLinkMAN =
dsLinkMAN_farm_trans_1.Tables("farm_trans").NewRow()
drLinkMAN("path") = strNMANpath
drLinkMAN("summit_client_id") = ClientID
drLinkMAN("summit_farm_id") = ""
drLinkMAN("year") = drNMAN_farm("year")
drLinkMAN("farm_id") = drNMAN_farm("farm_id")
drLinkMAN("created") = Now()

dsLinkMAN_farm_trans_1.Tables("farm_trans").Rows.Add(drLinkMAN)

End If

Next
If bmbLinkMAN_farm_trans.Count = 0 Then
Exit Sub
End If

dvLinkMAN_farm = New DataView(dsLinkMAN_farm_trans_1.Tables("farm"))
dvSummit_farm = New DataView(dsSummit_farm.Tables("Farms"))

txtNMANfarmname.DataBindings.Clear()
txtNMANfarmname.DataBindings.Add("Text", dvLinkMAN_farm,
"farm_name")

txtsummitfarmname.DataBindings.Clear()
txtsummitfarmname.DataBindings.Add("Text", dvSummit_farm,
"FarmName")

dvLinkMAN_farm_trans = New
DataView(dsLinkMAN_farm_trans_1.Tables("farm_trans"))

bmbLinkMAN_farm_trans.Position = 0

DisplayPosition()

End Sub
Private Sub DisplayPosition()

bmbLinkMAN_farm_trans.EndCurrentEdit()

btnPrevious.Enabled = Not (bmbLinkMAN_farm_trans.Position = 0)
btnNext.Enabled = Not (bmbLinkMAN_farm_trans.Position =
bmbLinkMAN_farm_trans.Count - 1)

stsbar.Text = bmbLinkMAN_farm_trans.Position + 1 & " of " &
bmbLinkMAN_farm_trans.Count & " farms found."

Dim drLinkMAN_farm_trans As DataRowView =
DirectCast(bmbLinkMAN_farm_trans.Current, DataRowView)
dvLinkMAN_farm.RowFilter = "farm_id='" &
drLinkMAN_farm_trans("farm_id") & "' and year=" &
drLinkMAN_farm_trans("year")

dvSummit_farm.RowFilter = "farmid='" &
drLinkMAN_farm_trans("summit_farm_id") & "'"

End Sub
Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles btnPrevious.Click, btnNext.Click


Select Case sender.Name

Case "btnPrevious"
bmbLinkMAN_farm_trans.Position -= 1


Case "btnNext"
bmbLinkMAN_farm_trans.Position += 1

End Select
DisplayPosition()

End Sub
Private Sub SaveRows()

' Save the data
bmbLinkMAN_farm_trans.EndCurrentEdit()

Dim drLinkMAN_farm_trans As DataRow

If dsLinkMAN_farm_trans_1.HasChanges Then

Dim intx As Int16

For Each drLinkMAN_farm_trans In
dsLinkMAN_farm_trans_1.Tables("farm_trans").Rows
MessageBox.Show(drLinkMAN_farm_trans("path"))
MessageBox.Show(drLinkMAN_farm_trans("summit_client_id"))
MessageBox.Show(drLinkMAN_farm_trans("summit_farm_id"))
MessageBox.Show(drLinkMAN_farm_trans("farm_id"))
MessageBox.Show(drLinkMAN_farm_trans("year"))
MessageBox.Show(drLinkMAN_farm_trans("created"))

Next

daLinkMAN_farm_trans.Update(dsLinkMAN_farm_trans_1,
"farm_trans")
If dsLinkMAN_farm_trans_1.HasErrors Then
Dim e As String = ""

For Each drLinkMAN_farm_trans In
dsLinkMAN_farm_trans_1.Tables("farm_trans").Rows
If drLinkMAN_farm_trans.HasErrors Then
e &= drLinkMAN_farm_trans.Item("farm_id") & ": " &
drLinkMAN_farm_trans.RowError & ControlChars.CrLf
End If
Next
MessageBox.Show(e, "Update Errors", MessageBoxButtons.OK,
MessageBoxIcon.Warning)

End If
End If

End Sub
Private Sub btnAnalyze_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAnalyze.Click

CompareTables()

End Sub

Private Sub ddYear_TextChanged(ByVal sender As Object, ByVal e As
System.EventArgs) Handles ddYear.TextChanged

' year changed, update year variable
If IsNumeric(ddYear.Text) Then
int_current_year = ddYear.Text
Else
ddYear.Text = int_current_year.ToString
End If

End Sub

Private Sub frmFarmUpdate_Closed(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Closed

frmFarmUpdateRef = Nothing

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click

' check haschanges and do update
SaveRows()
Me.Close()

End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCancel.Click

' user cancels everything
' do check here to advise that no changes will be saved

If MessageBox.Show("You have not saved the new transactions. Are you
sure you want to exit?", "Unsaved Transactions", MessageBoxButtons.YesNo,
MessageBoxIcon.Question) = DialogResult.Yes Then
Me.Close()
End If

End Sub

Private Sub lklSummitFarm_LinkClicked(ByVal sender As System.Object,
ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles
lklSummitFarm.LinkClicked

' Display the search farms form and return selected record

Dim drLinkMAN_farm_trans As DataRowView =
DirectCast(bmbLinkMAN_farm_trans.Current, DataRowView)

Dim frm As New
frmSummitFarms(drLinkMAN_farm_trans("summit_client_id"))

With frm

.StartPosition = FormStartPosition.CenterParent
.ShowDialog()

If frm.retval_farmid <> "" Then
drLinkMAN_farm_trans("summit_farm_id") = frm.retval_farmid
DisplayPosition()

End If

End With

End Sub
End Class



Basically, my SaveRows() is the sub being fired that causes the error.

Here are the Select and Insert Commands....

SELECT id, path, summit_client_id, summit_farm_id, year, farm_id, created
FROM farm_trans WHERE (summit_client_id = ?) AND (year = ?)

INSERT INTO farm_trans (path, summit_client_id, summit_farm_id, year,
farm_id, created) VALUES (?, ?, ?, ?, ?, ?)


Thanks,
Graham

 

[scorpion53061]

Graham,

First off relax. You came to the right place for help. Actually you might
want to go to the ado.net ng but in this case I am sure you don't want to be
redirected.

Lets see your INSERT statement.

Does your table have a primary key?

Check your data types.

and lastly send your project to

(e-mail address removed)

remove the no spam part.

and I will help you....I have inlaws here all weekend and would love nothing
more than to divert my attention with this.

 

[graham]

Thanks for helping out...

The insert statement is

INSERT INTO farm_trans (path, summit_client_id, summit_farm_id, year,
farm_id, created) VALUES (?, ?, ?, ?, ?, ?)

The farm_trans table has a primary key (autonumber)..

Have checked datatypes... I found an article about Access datetime fields
and someone having a similar issue with an Update comment, it turned out the
Data adaptor wizard sets the datetime field to 'DBDate' and not 'Date' in
the mappings - I tried making this change - still no joy.

I also incorporated some handlers to see what was happening to the rows
prior to update. The parameters definitely get loaded, but don;t appear to
be included in the INSERT command during the RowUpdating event...

Anyway, if you have any ideas....

I'll be happy to send the project to you at your e-mail so you can see...

Thanks again for your time...
Graham

 

[scorpion53061]

yes send it to me. I will do the basic insert, update, and select commands
as an example. Don't forget to send the database you are using.

 

[scorpion53061]

I need you to send me databases without passwords (so I can see what I am
doing) and the project files please.

 

[Cor Ligthert]

Hi Graham,

A lot of code and in my opinion a lot of times changed.

Basicly you need only in my opinion to do the update when your dataadapter
is placed global

dim cmb as new commandbuilder(daLinkMAN_farm_trans)

(You have normaly to do nothing with it, it creates the insert, update and
delete). You can use the cmb however for special purposes.

And set that on a place where the dataadapter has all his parameters and
before the update.

(Do not use this when your selectstatement is complex because there is told
that there are bugs in it in this newsgroups, so you have to test it well).

I hope this helps?

Cor

 

[Cor Ligthert]

Correction on sentence

A lot of code and in my opinion a lot of times changed.

Basicly you need only in my opinion to do the update when your dataadapter
is placed global

Basicly you need only in my opinion to do the update *the commandbuilder*.
For what is written below that dataadapter has to be placed global

Cor

 

[graham]

Hi Cor, - Thanks - but that is essentially what I have. The dataadaptor,
datasets, and the commands are all generated at design-time. The table I am
trying to update is the 'fram_trans' table of the daLinkMAN_farm_trans
dataadaptor.

All of the other code is basically setting up the display, and/or getting
the values to insert into my records.

What would be good is if I could interrogate the insertcommand just prior to
execution, to see what it is in fact sending.

I tried adding a AddHandler for RowUpdating, and from there I can see my
parameter values, but if I look at the Command.commandtext at this point it
just shows the insert statement with (?,?,?,?.... not the actual values it
has as parameters. I'm just wondering if it is placing quotes or something
in the wrong places.....

Thanks,
Graham

 

[Armin Zingler]

Here are the Select and Insert Commands....

SELECT id, path, summit_client_id, summit_farm_id, year, farm_id,
created FROM farm_trans WHERE (summit_client_id = ?) AND (year =
?)

INSERT INTO farm_trans (path, summit_client_id, summit_farm_id,
year, farm_id, created) VALUES (?, ?, ?, ?, ?, ?)

I haven't examined the whole code, but don't you have to enclose the 'year'
in square brackets (year occurs twice)?

SELECT id, path, summit_client_id, summit_farm_id, [year], farm_id,
created FROM farm_trans WHERE (summit_client_id = ?) AND ([year] =
?)


--
Armin

How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

 

[Cor Ligthert]

Hi Graham,

Where is that commandbuilder in your code, it was a lot however I could not
find it.

Cor

 

[graham]

YES!!!! Thank you Armin - Thank you very much!

Is year a reserved word in Access?

Graham

Here are the Select and Insert Commands....

SELECT id, path, summit_client_id, summit_farm_id, year, farm_id,
created FROM farm_trans WHERE (summit_client_id = ?) AND (year =
?)

INSERT INTO farm_trans (path, summit_client_id, summit_farm_id,
year, farm_id, created) VALUES (?, ?, ?, ?, ?, ?)

I haven't examined the whole code, but don't you have to enclose the 'year'
in square brackets (year occurs twice)?

SELECT id, path, summit_client_id, summit_farm_id, [year], farm_id,
created FROM farm_trans WHERE (summit_client_id = ?) AND ([year] =
?)


--
Armin

How to quote and why:
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

 

[Armin Zingler]

YES!!!! Thank you Armin - Thank you very much!

Is year a reserved word in Access?

Yes.

select year(datetimefield) from tablename

 

[Guest]

Hi i am vasu from india, Though i am learning VB.net i can
suggest you dont execute ur program after writing some 500
or so lines of code. u just write 2 buttons code and
execute and then 2 like this u go on . So that u could
easily find out where the error is ok. This is the method
i follow if u dont like i am sorry ok.

Any douts contact to this mail.

Name: R. Vasu
E-mail: (e-mail address removed)
Mobile: 98486 13743


Bye

 

[Cor Ligthert]

Hi Graham,

I do not understand anything from your message, you said everything works
except the update, how is that possible when there is an error in the select
clause from the fill.

That should mean that nothing is working.

Strange

Cor

 

[graham]

Hi Cor,

That's the strange thing - and what caused me to not even consider that the
SQL was the problem.

The select statement does and did work fine, - but when it came to
inserting - it returned the errors.
Anyway, I added the parenthesis around the 'year' column name and now all
works fine....

the only thing .NET did here that I would say was incorrect, was determining
that I was using a Jet provider and not determining that the year should
have been enclosed in [] when generating the Select, Insert, Update clauses.

Thanks for your help,
Graham

 

[scorpion53061]

Glad to hear your issues are resolved.

If you have any more questions about this project do let me know.