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 15 1941
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
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
I need you to send me databases without passwords (so I can see what I am
doing) and the project files please.
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
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
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
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
"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
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.....
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
"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
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
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
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
Glad to hear your issues are resolved.
If you have any more questions about this project do let me know. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Neil Ginsberg |
last post by:
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBC
linked tables. I recently created a new set of tables for the app, and users
are complaining that unsaved data is being...
|
by: Brian |
last post by:
I am so happy to have mastered the technique for "Press Tab to
insert".
I type " += " and up steps Visual Studio to write my event handler for
me.
This is more fun than Intellisense.
But I...
|
by: Michael Adams |
last post by:
I don't know if you have noticed, but it seems like Microsoft is losing
interest in C#, and putting their energy into Visual Basic.NET instead.
For instance, online chats by language since July...
|
by: Jimmy Junatas |
last post by:
When we open a window (using client-side jscript ie.
window.open("/Site/Popup.aspx?...",...)) from Page1.aspx, the called
page Popup.aspx does not have access to the Session variables present
in...
|
by: Matthew Curiale |
last post by:
I have a ddl that is populated in my If Not IsPostBack block in the
Page_load event. A repeater is also populated at this time, showing
different domains for a website. The ddl contains different...
|
by: John |
last post by:
I am loading my controls dynamically into my asp.net page. When I click on a
button on one of those user controls then page reloads and I need to reload
the page based on what has happened in the...
|
by: chromis |
last post by:
Hi there,
I've recently been updating a site to use locking on application level variables, and I am trying to use a commonly used method which copies the application struct into the request...
|
by: Billiska |
last post by:
Hi, I'm creating a "Filter-as-you-type" feature.
The problem is: when a textbox is being keyed in, only the Text property changes but the Value property, which I want to use, doesn't.
The only...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |