By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,321 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,321 IT Pros & Developers. It's quick & easy.

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

P: n/a
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").Valu e = 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").Row s(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.A dd(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_clien t_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
Nov 20 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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

ad***@nospamkjmsolutions.com

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" <gr*************@rogers.com> wrote in message
news:pI**************@news04.bloor.is.net.cable.ro gers.com...
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").Valu e = 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").Row s(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.A dd(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_clien t_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

Nov 20 '05 #2

P: n/a
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" <sc************@nospamhereeveryahoo.com> wrote in message
news:On**************@TK2MSFTNGP11.phx.gbl...
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

ad***@nospamkjmsolutions.com

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" <gr*************@rogers.com> wrote in message
news:pI**************@news04.bloor.is.net.cable.ro gers.com...
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").Valu e = 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").Row s(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.A dd(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_clien t_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


Nov 20 '05 #3

P: n/a
I need you to send me databases without passwords (so I can see what I am
doing) and the project files please.
Nov 20 '05 #4

P: n/a
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.

"graham" <gr*************@rogers.com> wrote in message
news:fN****************@news04.bloor.is.net.cable. rogers.com...
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" <sc************@nospamhereeveryahoo.com> wrote in message
news:On**************@TK2MSFTNGP11.phx.gbl...
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

ad***@nospamkjmsolutions.com

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" <gr*************@rogers.com> wrote in message
news:pI**************@news04.bloor.is.net.cable.ro gers.com...
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").Valu e = 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").Row s(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.A dd(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_clien t_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



Nov 20 '05 #5

P: n/a
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

<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").Valu e = 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").Row s(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.A dd(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_clien t_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

Nov 20 '05 #6

P: n/a
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
Nov 20 '05 #7

P: n/a
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


"Cor Ligthert" <no**********@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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

Nov 20 '05 #8

P: n/a
"graham" <gr*************@rogers.com> schrieb
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

Nov 20 '05 #9

P: n/a
Hi Graham,

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

Cor
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.....

Nov 20 '05 #10

P: n/a
YES!!!! Thank you Armin - Thank you very much!

Is year a reserved word in Access?

Graham

"Armin Zingler" <az*******@freenet.de> wrote in message
news:40***********************@news.freenet.de...
"graham" <gr*************@rogers.com> schrieb
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

Nov 20 '05 #11

P: n/a
"graham" <gr*************@rogers.com> schrieb
YES!!!! Thank you Armin - Thank you very much!

Is year a reserved word in Access?


Yes.

select year(datetimefield) from tablename
--
Armin

Nov 20 '05 #12

P: n/a

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: va*********@yahoo.co.in
Mobile: 98486 13743
Bye

Nov 20 '05 #13

P: n/a
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
Nov 20 '05 #14

P: n/a
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


"Cor Ligthert" <no**********@planet.nl> wrote in message
news:u3**************@TK2MSFTNGP09.phx.gbl...
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

Nov 20 '05 #15

P: n/a
Glad to hear your issues are resolved.

If you have any more questions about this project do let me know.
Nov 20 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.