Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
28  1851 
Hi Siv,
I think your problem is with the dob column. You are sending it text but it requires date data. Wrap it in "#" on both ends and see what happens. Also, just to verify my belief, simply change it to now.date to see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Bernie,
I'll give that a go and report back, if that's what it is I'll be well pleased!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text but it requires date data. Wrap it in "#" on both ends and see what happens. Also, just to verify my belief, simply change it to now.date to see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the Insert
command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it works
and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text but it
requires date data. Wrap it in "#" on both ends and see what happens.
Also, just to verify my belief, simply change it to now.date to see if that
is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt)
'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the
currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text
'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is:
String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw) 'Add
the new row
da.Update(dt) 'Send the update to the actual database using the
adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT
INTO statement."
I have other code that uses the same technique and I don't get an error at
all. I have tried changing the strSQL text so that it is more complex and
it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of
my misery!
Siv
Martley, Worcester, UK.
Darious,
I could do that, but I am more curious as to why this should error when most autogenerated insert statements of the same type work fine without needing to manually create an insert statement?
--
Siv
Martley, Worcester, UK.
"Darious Snell" <nu**@sdlkfweu.org> wrote in message news:41***********************@news.twtelecom.net. ..
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Hi All,
One thing that occurs to me but I am not sure whether I have always done it in my versions of this technique that do work, is that I am not filling all the fields in the table. My assumption being that ADO.NET will do as per ADO and leave Jet to fill in any default values for fields that aren't specifically altered. Is this assumption correct in VB.NET??
If you must fill in all the fields that would be returned by the select statement, this would account for me getting an error, but why it appears as "Syntax error in INSERT INTO statement." baffles me.
On pausing the code and interrogating the number of rows at the line just prior to the error line, the number of rows has increased by one in the DataTable, so it has definitely got that far without tripping anything up?
I'm stumped?
--
Siv
Martley, Worcester, UK.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
I will continue to look at your code to come up with an answer. Re the
commandbuilder - it works fine for me on literally hundreds of occasions, so
I don't think that is your problem.
For now, wrap the update call in a try ... catch block and messagebox.show
ex.message, like this:
Try
da.Update(dt)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Also, start to comment out one column at a time; once it works, we'll know
that that is the column causing the problem.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl... Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it works
and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text but
it requires date data. Wrap it in "#" on both ends and see what happens.
Also, just to verify my belief, simply change it to now.date to see if
that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the
currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw)
'Add the new row
da.Update(dt) 'Send the update to the actual database using the
adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at
all. I have tried changing the strSQL text so that it is more complex and
it makes no difference.
If anyone can see the glaring mistake that I am making please put me out
of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying to
fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl... Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it works
and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text but
it requires date data. Wrap it in "#" on both ends and see what happens.
Also, just to verify my belief, simply change it to now.date to see if
that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the
currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw)
'Add the new row
da.Update(dt) 'Send the update to the actual database using the
adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at
all. I have tried changing the strSQL text so that it is more complex and
it makes no difference.
If anyone can see the glaring mistake that I am making please put me out
of my misery!
Siv
Martley, Worcester, UK.
Does the table you are inserting into have a primary key defined?
"Siv" <ms**********@removeme.sivill.com> wrote in message news:%2****************@TK2MSFTNGP12.phx.gbl...
Darious,
I could do that, but I am more curious as to why this should error when most autogenerated insert statements of the same type work fine without needing to manually create an insert statement?
--
Siv
Martley, Worcester, UK.
"Darious Snell" <nu**@sdlkfweu.org> wrote in message news:41***********************@news.twtelecom.net. ..
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Bernie,
I had a look at that after your comment about the date field, as I do tend
to get tripped up by variable types (VB6 allowed us to get away with a lot
of implied conversions that just aren't allowed in VB.NET). I went through
all the fields in the table in Access to check that the text fields would
allow zero length strings and that I was populating any fields that are
required.
In fact I tried adding the record manually through access only entering the
fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between "Long"
in Access and "Long" in VB.NET. I did have a couple of fields where I was
using Longs in VB.NET and they were going into fields which are Access
"Longs" but should have been VB.NET Integers. I changed the VB.NET code so
that the fields in question were being assigned Integers and it made no
difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl... Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying to
fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl... Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it works
and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text but
it requires date data. Wrap it in "#" on both ends and see what happens.
Also, just to verify my belief, simply change it to now.date to see if
that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set
the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error
at all. I have tried changing the strSQL text so that it is more complex
and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out
of my misery!
Siv
Martley, Worcester, UK.
Darious,
Yes it does it is the ClientID field.
--
Siv
Martley, Worcester, UK.
"Darious Snell" <nu**@sdlkfweu.org> wrote in message news:41***********************@news.twtelecom.net. ..
Does the table you are inserting into have a primary key defined?
"Siv" <ms**********@removeme.sivill.com> wrote in message news:%2****************@TK2MSFTNGP12.phx.gbl...
Darious,
I could do that, but I am more curious as to why this should error when most autogenerated insert statements of the same type work fine without needing to manually create an insert statement?
--
Siv
Martley, Worcester, UK.
"Darious Snell" <nu**@sdlkfweu.org> wrote in message news:41***********************@news.twtelecom.net. ..
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
Did you set up the try...catch block?
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do tend
to get tripped up by variable types (VB6 allowed us to get away with a lot
of implied conversions that just aren't allowed in VB.NET). I went
through all the fields in the table in Access to check that the text
fields would allow zero length strings and that I was populating any
fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl... Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl... Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it
works and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text
but it requires date data. Wrap it in "#" on both ends and see what
happens. Also, just to verify my belief, simply change it to now.date to
see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set
the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser
'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error
at all. I have tried changing the strSQL text so that it is more
complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out
of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do tend
to get tripped up by variable types (VB6 allowed us to get away with a lot
of implied conversions that just aren't allowed in VB.NET). I went
through all the fields in the table in Access to check that the text
fields would allow zero length strings and that I was populating any
fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl... Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl... Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it
works and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text
but it requires date data. Wrap it in "#" on both ends and see what
happens. Also, just to verify my belief, simply change it to now.date to
see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set
the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser
'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error
at all. I have tried changing the strSQL text so that it is more
complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out
of my misery!
Siv
Martley, Worcester, UK.
Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window so I
could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI Number ,
TaxCode , Income , SalaryOTBonus , SalaryReviewDate , InvPensIncome ,
SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome , BenefitsInKindYN ,
BenefitDesc1 , BenefitValue1 , BenefitDesc2 , BenefitValue2 , BenefitDesc3 ,
BenefitValue3 , DateCreated , User ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred or
is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away with
a lot of implied conversions that just aren't allowed in VB.NET). I went
through all the fields in the table in Access to check that the text
fields would allow zero length strings and that I was populating any
fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl... Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl...
Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL
statement??
I've looked at other routines where I use the same technique and it
works and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text
but it requires date data. Wrap it in "#" on both ends and see what
happens. Also, just to verify my belief, simply change it to now.date
to see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set
the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now
'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error
at all. I have tried changing the strSQL text so that it is more
complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me
out of my misery!
Siv
Martley, Worcester, UK.
Bernie,
There always was a try catch around the whole shebang. The whole procedure
is as follows:
Dim strSQL As String, dt As Data.DataTable, da As
OleDb.OleDbDataAdapter, cb As OleDb.OleDbCommandBuilder
Dim rw As Data.DataRow, r As Long
Try 'First do Primary Client Details
If NewRecord Then
strSQL = "Select Clients.* FROM Clients ORDER BY ClientID;"
Else
strSQL = "Select * from Clients WHERE ClientID = " &
CurrentClientID
End If
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder
using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
If NewRecord Then 'If doing a new record, begin edit and get
next id number
rw = dt.NewRow
rw("ClientID") = 2 'GetNextIDNumber("Clients")
CurrentClientID = 2 'rw("ClientID") 'Set the currentClientID
to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type
is: Long.
rw("AdviserLinkID") = 0
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text
'Type is: String.
rw("DOB") = Now 'txtDOBYYYYSelf.Text & "-" &
txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("NI Number") = "" 'Type is: String.
rw("TaxCode") = "" 'Type is: String.
rw("Income") = 0 'Type is: Single.
rw("SalaryOTBonus") = 0 'Type is: Single.
rw("SalaryReviewDate") = Now 'Type is: Date.
rw("InvPensIncome") = 0 'Type is: Single.
rw("SelfEmpNetRelEarnings") = 0 'Type is: Single.
rw("TaxRatePercentage") = 0 'Type is: Single.
rw("NetIncome") = 0 'Type is: Single.
rw("BenefitsInKindYN") = False 'Type is: Boolean.
rw("BenefitDesc1") = "" 'Type is: String.
rw("BenefitValue1") = 0 'Type is: Decimal.
rw("BenefitDesc2") = "" 'Type is: String.
rw("BenefitValue2") = 0 'Type is: Decimal.
rw("BenefitDesc3") = "" 'Type is: String.
rw("BenefitValue3") = 0 'Type is: Decimal.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
Else 'We're just modifying it
dt.Rows(r).Item("RelatedClientLinkID") =
CurrentRelatedClientID 'Type is: Long.
dt.Rows(r).Item("Title") = txtTitleSelf.Text 'Type is:
String.
dt.Rows(r).Item("Forename") = txtForenameSelf.Text 'Type
is: String.
dt.Rows(r).Item("OtherNames") = txtOtherNameSelf.Text 'Type
is: String.
dt.Rows(r).Item("Surname") = txtSurnameSelf.Text 'Type is:
String.
dt.Rows(r).Item("RelationshipToPartner") =
txtRelToPartnerSelf.Text 'Type is: String.
dt.Rows(r).Item("DOB") = txtDOBYYYYSelf.Text & "-" &
txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
dt.Rows(r).Item("StateOfHealth") = txtStateOfHealthSelf.Text
'Type is: String.
dt.Rows(r).Item("SmokerYN") = chkSmokerSelf.Checked 'Type
is: Boolean.
dt.Rows(r).Item("Notes") = txtNotesSelf.Text 'Type is:
String.
dt.Rows(r).Item("DateCreated") = Now 'Type is: Date.
dt.Rows(r).Item("User") = CurrentUser 'Type is String
da.Update(dt) 'Send update to database
End If
dt.Dispose()
da.Dispose()
Catch ex As Exception
Call ProgErrorHandler("SaveData - Primary Client Data",
"frmCust", ex.Message, False)
End Try
'================================================= ========
You'll see that I have started modifying the New side of the If statement to
include all fields with values and this made no difference.
"NewRecord" is passed to this sub to tell it to do a new rather than an
amend.
"ProgErrorHandler" is a generic routine that just throws up a messagebox
detailing the error that has occurred.
The "Type is" comments are generated by a VBA macro that dumps the field
names and their types into a text file that I can copy into VB.NET to save
time getting all the field names and their types. Be aware that this is
quoting Access variable types (It is an outstanding job to modify that
routine so that it translates Access variables into VB.NET variable types)
which is where I was getting my Long data types confused.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eK**************@TK2MSFTNGP12.phx.gbl... Hi Siv,
Did you set up the try...catch block?
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away with
a lot of implied conversions that just aren't allowed in VB.NET). I went
through all the fields in the table in Access to check that the text
fields would allow zero length strings and that I was populating any
fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl... Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl...
Bernie,
Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the
Insert command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL
statement??
I've looked at other routines where I use the same technique and it
works and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:u1**************@TK2MSFTNGP10.phx.gbl...
Hi Siv,
I think your problem is with the dob column. You are sending it text
but it requires date data. Wrap it in "#" on both ends and see what
happens. Also, just to verify my belief, simply change it to now.date
to see if that is indeed the problem.
HTH,
Bernie Yaeger
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set
the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
Boolean.
rw("Notes") = txtNotesSelf.Text 'Type
is: String.
rw("DateCreated") = Now
'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using
the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in
INSERT INTO statement."
I have other code that uses the same technique and I don't get an error
at all. I have tried changing the strSQL text so that it is more
complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me
out of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
No; the question marks are perfectly normal; they represent replaceable
variables.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window so
I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI Number
, TaxCode , Income , SalaryOTBonus , SalaryReviewDate , InvPensIncome ,
SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome , BenefitsInKindYN ,
BenefitDesc1 , BenefitValue1 , BenefitDesc2 , BenefitValue2 , BenefitDesc3
, BenefitValue3 , DateCreated , User ) VALUES ( ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred
or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away
with a lot of implied conversions that just aren't allowed in VB.NET).
I went through all the fields in the table in Access to check that the
text fields would allow zero length strings and that I was populating
any fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl...
Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl...
> Bernie,
>
> Tried it and this didn't make any difference. BAAHHH!
> What I don't get is that the CommandBuilder object should create the
> Insert command for me automatically based on the select query.
> Why would Dot Net be getting it wrong with such a simple SQL
> statement??
> I've looked at other routines where I use the same technique and it
> works and I can't see what is different between them?
> --
> Siv
> Martley, Worcester, UK.
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:u1**************@TK2MSFTNGP10.phx.gbl...
> Hi Siv,
>
> I think your problem is with the dob column. You are sending it text
> but it requires date data. Wrap it in "#" on both ends and see what
> happens. Also, just to verify my belief, simply change it to now.date
> to see if that is indeed the problem.
>
> HTH,
>
> Bernie Yaeger
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:ek**************@TK2MSFTNGP09.phx.gbl...
> Hi,
> If I run the following:
>
> strSQL = "Select * FROM Clients;"
>
> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
> adapter
> cb = New OleDb.OleDbCommandBuilder(da) 'Create
> command builder using the datadapter
> dt = New Data.DataTable
> da.Fill(dt) 'pour in the data using the adapter
>
> rw = dt.NewRow
> rw("ClientID") = GetNextIDNumber("Clients")
>
> CurrentClientID = CLng(rw("ClientID")) 'Set
> the currentClientID to this new client
>
> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
> Long.
> rw("IsPrimaryClient") = True
> rw("Title") = txtTitleSelf.Text 'Type is: String.
> rw("Forename") = txtForenameSelf.Text 'Type is:
> String.
> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
> String.
> rw("Surname") = txtSurnameSelf.Text 'Type is:
> String.
> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
> is: String.
> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
> txtDOBDDSelf.Text 'Type is: Date.
> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
> String.
> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
> Boolean.
> rw("Notes") = txtNotesSelf.Text
> 'Type is: String.
> rw("DateCreated") = Now 'Type is: Date.
> rw("User") = CurrentUser 'Type is String
> dt.Rows.Add(rw) 'Add the new row
> da.Update(dt) 'Send the update to the actual database using
> the adapter
>
> At the da.Update(dt) line I det an exception thrown: "Syntax error in
> INSERT INTO statement."
>
> I have other code that uses the same technique and I don't get an
> error at all. I have tried changing the strSQL text so that it is
> more complex and it makes no difference.
> If anyone can see the glaring mistake that I am making please put me
> out of my misery!
>
> Siv
> Martley, Worcester, UK.
>
Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space. It
should appear as [NI Number] but the commandbuilder is not smart enough to
deal with it. Try changing the table structure to 'NINumber' (no space) and
then rebuild the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window so
I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI Number
, TaxCode , Income , SalaryOTBonus , SalaryReviewDate , InvPensIncome ,
SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome , BenefitsInKindYN ,
BenefitDesc1 , BenefitValue1 , BenefitDesc2 , BenefitValue2 , BenefitDesc3
, BenefitValue3 , DateCreated , User ) VALUES ( ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred
or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl... Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away
with a lot of implied conversions that just aren't allowed in VB.NET).
I went through all the fields in the table in Access to check that the
text fields would allow zero length strings and that I was populating
any fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl...
Hi Siv,
Here's another idea: are any of the textboxes empty and are they trying
to fill a column that does not allow nulls? Just a thought.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Od**************@TK2MSFTNGP12.phx.gbl...
> Bernie,
>
> Tried it and this didn't make any difference. BAAHHH!
> What I don't get is that the CommandBuilder object should create the
> Insert command for me automatically based on the select query.
> Why would Dot Net be getting it wrong with such a simple SQL
> statement??
> I've looked at other routines where I use the same technique and it
> works and I can't see what is different between them?
> --
> Siv
> Martley, Worcester, UK.
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:u1**************@TK2MSFTNGP10.phx.gbl...
> Hi Siv,
>
> I think your problem is with the dob column. You are sending it text
> but it requires date data. Wrap it in "#" on both ends and see what
> happens. Also, just to verify my belief, simply change it to now.date
> to see if that is indeed the problem.
>
> HTH,
>
> Bernie Yaeger
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:ek**************@TK2MSFTNGP09.phx.gbl...
> Hi,
> If I run the following:
>
> strSQL = "Select * FROM Clients;"
>
> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
> adapter
> cb = New OleDb.OleDbCommandBuilder(da) 'Create
> command builder using the datadapter
> dt = New Data.DataTable
> da.Fill(dt) 'pour in the data using the adapter
>
> rw = dt.NewRow
> rw("ClientID") = GetNextIDNumber("Clients")
>
> CurrentClientID = CLng(rw("ClientID")) 'Set
> the currentClientID to this new client
>
> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
> Long.
> rw("IsPrimaryClient") = True
> rw("Title") = txtTitleSelf.Text 'Type is: String.
> rw("Forename") = txtForenameSelf.Text 'Type is:
> String.
> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
> String.
> rw("Surname") = txtSurnameSelf.Text 'Type is:
> String.
> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
> is: String.
> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
> txtDOBDDSelf.Text 'Type is: Date.
> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
> String.
> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
> Boolean.
> rw("Notes") = txtNotesSelf.Text
> 'Type is: String.
> rw("DateCreated") = Now 'Type is: Date.
> rw("User") = CurrentUser 'Type is String
> dt.Rows.Add(rw) 'Add the new row
> da.Update(dt) 'Send the update to the actual database using
> the adapter
>
> At the da.Update(dt) line I det an exception thrown: "Syntax error in
> INSERT INTO statement."
>
> I have other code that uses the same technique and I don't get an
> error at all. I have tried changing the strSQL text so that it is
> more complex and it makes no difference.
> If anyone can see the glaring mistake that I am making please put me
> out of my misery!
>
> Siv
> Martley, Worcester, UK.
>
No, Siv, you do not have to fill all the fields; ADO .Net will work, in this regard, as ADO does.
But see my last response to you re 'NI Number'.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message news:eT**************@tk2msftngp13.phx.gbl...
Hi All,
One thing that occurs to me but I am not sure whether I have always done it in my versions of this technique that do work, is that I am not filling all the fields in the table. My assumption being that ADO.NET will do as per ADO and leave Jet to fill in any default values for fields that aren't specifically altered. Is this assumption correct in VB.NET??
If you must fill in all the fields that would be returned by the select statement, this would account for me getting an error, but why it appears as "Syntax error in INSERT INTO statement." baffles me.
On pausing the code and interrogating the number of rows at the line just prior to the error line, the number of rows has increased by one in the DataTable, so it has definitely got that far without tripping anything up?
I'm stumped?
--
Siv
Martley, Worcester, UK.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Hi Siv,
I see now you have nothing to do with the commandbuilder and dataadapter - just change the column 'NI Number' and we will know if that is the answer.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message news:eT**************@tk2msftngp13.phx.gbl...
Hi All,
One thing that occurs to me but I am not sure whether I have always done it in my versions of this technique that do work, is that I am not filling all the fields in the table. My assumption being that ADO.NET will do as per ADO and leave Jet to fill in any default values for fields that aren't specifically altered. Is this assumption correct in VB.NET??
If you must fill in all the fields that would be returned by the select statement, this would account for me getting an error, but why it appears as "Syntax error in INSERT INTO statement." baffles me.
On pausing the code and interrogating the number of rows at the line just prior to the error line, the number of rows has increased by one in the DataTable, so it has definitely got that far without tripping anything up?
I'm stumped?
--
Siv
Martley, Worcester, UK.
"Siv" <ms**********@removeme.sivill.com> wrote in message news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."
I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!
Siv
Martley, Worcester, UK.
Bernie,
One thing I noticed was the field "NI Number" which has a space in it and
wondered if this was causing the problem, so I renamed the field in MS
Access ("NINumber") and also changed my code to match. It still errors on
the da.update(dt) line with the same INSERT error?
Aggghh! I hate it when you can't see what it is, and you know that there is
probably a really stupid simple reason?
Does the INSERT statement look wrong to you? in any way, (to me it looks
fine). Is this one of those weird VB/ADO bugs that reports one error but is
actually a completely different thing??
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OV**************@tk2msftngp13.phx.gbl... Hi Siv,
No; the question marks are perfectly normal; they represent replaceable
variables.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred
or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away
with a lot of implied conversions that just aren't allowed in VB.NET).
I went through all the fields in the table in Access to check that the
text fields would allow zero length strings and that I was populating
any fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl...
> Hi Siv,
>
> Here's another idea: are any of the textboxes empty and are they
> trying to fill a column that does not allow nulls? Just a thought.
>
> Bernie
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:Od**************@TK2MSFTNGP12.phx.gbl...
>> Bernie,
>>
>> Tried it and this didn't make any difference. BAAHHH!
>> What I don't get is that the CommandBuilder object should create the
>> Insert command for me automatically based on the select query.
>> Why would Dot Net be getting it wrong with such a simple SQL
>> statement??
>> I've looked at other routines where I use the same technique and it
>> works and I can't see what is different between them?
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>> Hi Siv,
>>
>> I think your problem is with the dob column. You are sending it text
>> but it requires date data. Wrap it in "#" on both ends and see what
>> happens. Also, just to verify my belief, simply change it to now.date
>> to see if that is indeed the problem.
>>
>> HTH,
>>
>> Bernie Yaeger
>>
>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>> Hi,
>> If I run the following:
>>
>> strSQL = "Select * FROM Clients;"
>>
>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>> adapter
>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>> command builder using the datadapter
>> dt = New Data.DataTable
>> da.Fill(dt) 'pour in the data using the adapter
>>
>> rw = dt.NewRow
>> rw("ClientID") = GetNextIDNumber("Clients")
>>
>> CurrentClientID = CLng(rw("ClientID"))
>> 'Set the currentClientID to this new client
>>
>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>> Long.
>> rw("IsPrimaryClient") = True
>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>> rw("Forename") = txtForenameSelf.Text 'Type is:
>> String.
>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>> String.
>> rw("Surname") = txtSurnameSelf.Text 'Type
>> is: String.
>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>> is: String.
>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
>> txtDOBDDSelf.Text 'Type is: Date.
>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
>> String.
>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>> Boolean.
>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>> rw("DateCreated") = Now 'Type is: Date.
>> rw("User") = CurrentUser 'Type is String
>> dt.Rows.Add(rw) 'Add the new row
>> da.Update(dt) 'Send the update to the actual database
>> using the adapter
>>
>> At the da.Update(dt) line I det an exception thrown: "Syntax error in
>> INSERT INTO statement."
>>
>> I have other code that uses the same technique and I don't get an
>> error at all. I have tried changing the strSQL text so that it is
>> more complex and it makes no difference.
>> If anyone can see the glaring mistake that I am making please put me
>> out of my misery!
>>
>> Siv
>> Martley, Worcester, UK.
>>
>
>
Bernie,
We're obviously on the same wavelength, I had just posted a message to you
saying that I'd spotted that as well but that it didn't work as this message
appeared!?
When you say "rebuild the commandbuilder" do you mean make the change to the
field and the code and then restart the application, or is there some other
technique that I need to do to get it to build a new copy of the
commandbuilder?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl... Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space. It
should appear as [NI Number] but the commandbuilder is not smart enough to
deal with it. Try changing the table structure to 'NINumber' (no space)
and then rebuild the commandbuilder (by rebuilding first the oledb
dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred
or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away
with a lot of implied conversions that just aren't allowed in VB.NET).
I went through all the fields in the table in Access to check that the
text fields would allow zero length strings and that I was populating
any fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl...
> Hi Siv,
>
> Here's another idea: are any of the textboxes empty and are they
> trying to fill a column that does not allow nulls? Just a thought.
>
> Bernie
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:Od**************@TK2MSFTNGP12.phx.gbl...
>> Bernie,
>>
>> Tried it and this didn't make any difference. BAAHHH!
>> What I don't get is that the CommandBuilder object should create the
>> Insert command for me automatically based on the select query.
>> Why would Dot Net be getting it wrong with such a simple SQL
>> statement??
>> I've looked at other routines where I use the same technique and it
>> works and I can't see what is different between them?
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>> Hi Siv,
>>
>> I think your problem is with the dob column. You are sending it text
>> but it requires date data. Wrap it in "#" on both ends and see what
>> happens. Also, just to verify my belief, simply change it to now.date
>> to see if that is indeed the problem.
>>
>> HTH,
>>
>> Bernie Yaeger
>>
>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>> Hi,
>> If I run the following:
>>
>> strSQL = "Select * FROM Clients;"
>>
>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>> adapter
>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>> command builder using the datadapter
>> dt = New Data.DataTable
>> da.Fill(dt) 'pour in the data using the adapter
>>
>> rw = dt.NewRow
>> rw("ClientID") = GetNextIDNumber("Clients")
>>
>> CurrentClientID = CLng(rw("ClientID"))
>> 'Set the currentClientID to this new client
>>
>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>> Long.
>> rw("IsPrimaryClient") = True
>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>> rw("Forename") = txtForenameSelf.Text 'Type is:
>> String.
>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>> String.
>> rw("Surname") = txtSurnameSelf.Text 'Type
>> is: String.
>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>> is: String.
>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
>> txtDOBDDSelf.Text 'Type is: Date.
>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
>> String.
>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>> Boolean.
>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>> rw("DateCreated") = Now 'Type is: Date.
>> rw("User") = CurrentUser 'Type is String
>> dt.Rows.Add(rw) 'Add the new row
>> da.Update(dt) 'Send the update to the actual database
>> using the adapter
>>
>> At the da.Update(dt) line I det an exception thrown: "Syntax error in
>> INSERT INTO statement."
>>
>> I have other code that uses the same technique and I don't get an
>> error at all. I have tried changing the strSQL text so that it is
>> more complex and it makes no difference.
>> If anyone can see the glaring mistake that I am making please put me
>> out of my misery!
>>
>> Siv
>> Martley, Worcester, UK.
>>
>
>
Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I am
trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original post I
am using "now" for dates (just in case). I am just about to press F8 to go
onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it work
or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl... Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space. It
should appear as [NI Number] but the commandbuilder is not smart enough to
deal with it. Try changing the table structure to 'NINumber' (no space)
and then rebuild the commandbuilder (by rebuilding first the oledb
dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has occurred
or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl... Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
Bernie,
I had a look at that after your comment about the date field, as I do
tend to get tripped up by variable types (VB6 allowed us to get away
with a lot of implied conversions that just aren't allowed in VB.NET).
I went through all the fields in the table in Access to check that the
text fields would allow zero length strings and that I was populating
any fields that are required.
In fact I tried adding the record manually through access only entering
the fields that the program is and it works fine.
One thing I have been getting tripped up on is the difference between
"Long" in Access and "Long" in VB.NET. I did have a couple of fields
where I was using Longs in VB.NET and they were going into fields which
are Access "Longs" but should have been VB.NET Integers. I changed the
VB.NET code so that the fields in question were being assigned Integers
and it made no difference?
It really is confusing!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:up****************@TK2MSFTNGP14.phx.gbl...
> Hi Siv,
>
> Here's another idea: are any of the textboxes empty and are they
> trying to fill a column that does not allow nulls? Just a thought.
>
> Bernie
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:Od**************@TK2MSFTNGP12.phx.gbl...
>> Bernie,
>>
>> Tried it and this didn't make any difference. BAAHHH!
>> What I don't get is that the CommandBuilder object should create the
>> Insert command for me automatically based on the select query.
>> Why would Dot Net be getting it wrong with such a simple SQL
>> statement??
>> I've looked at other routines where I use the same technique and it
>> works and I can't see what is different between them?
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>> Hi Siv,
>>
>> I think your problem is with the dob column. You are sending it text
>> but it requires date data. Wrap it in "#" on both ends and see what
>> happens. Also, just to verify my belief, simply change it to now.date
>> to see if that is indeed the problem.
>>
>> HTH,
>>
>> Bernie Yaeger
>>
>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>> Hi,
>> If I run the following:
>>
>> strSQL = "Select * FROM Clients;"
>>
>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>> adapter
>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>> command builder using the datadapter
>> dt = New Data.DataTable
>> da.Fill(dt) 'pour in the data using the adapter
>>
>> rw = dt.NewRow
>> rw("ClientID") = GetNextIDNumber("Clients")
>>
>> CurrentClientID = CLng(rw("ClientID"))
>> 'Set the currentClientID to this new client
>>
>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>> Long.
>> rw("IsPrimaryClient") = True
>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>> rw("Forename") = txtForenameSelf.Text 'Type is:
>> String.
>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>> String.
>> rw("Surname") = txtSurnameSelf.Text 'Type
>> is: String.
>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>> is: String.
>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
>> txtDOBDDSelf.Text 'Type is: Date.
>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
>> String.
>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>> Boolean.
>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>> rw("DateCreated") = Now 'Type is: Date.
>> rw("User") = CurrentUser 'Type is String
>> dt.Rows.Add(rw) 'Add the new row
>> da.Update(dt) 'Send the update to the actual database
>> using the adapter
>>
>> At the da.Update(dt) line I det an exception thrown: "Syntax error in
>> INSERT INTO statement."
>>
>> I have other code that uses the same technique and I don't get an
>> error at all. I have tried changing the strSQL text so that it is
>> more complex and it makes no difference.
>> If anyone can see the glaring mistake that I am making please put me
>> out of my misery!
>>
>> Siv
>> Martley, Worcester, UK.
>>
>
>
Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the user
might be a reserved word. Now rename that field back to user to see if it
was 'ni number' that was causing the problem!
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I am
trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original post
I am using "now" for dates (just in case). I am just about to press F8 to
go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it work
or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl... Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space.
It should appear as [NI Number] but the commandbuilder is not smart
enough to deal with it. Try changing the table structure to 'NINumber'
(no space) and then rebuild the commandbuilder (by rebuilding first the
oledb dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl... Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has
occurred or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl...
Hi Siv,
Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)
Let's see what that looks like.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:O4**************@tk2msftngp13.phx.gbl...
> Bernie,
>
> I had a look at that after your comment about the date field, as I do
> tend to get tripped up by variable types (VB6 allowed us to get away
> with a lot of implied conversions that just aren't allowed in VB.NET).
> I went through all the fields in the table in Access to check that the
> text fields would allow zero length strings and that I was populating
> any fields that are required.
>
> In fact I tried adding the record manually through access only
> entering the fields that the program is and it works fine.
>
> One thing I have been getting tripped up on is the difference between
> "Long" in Access and "Long" in VB.NET. I did have a couple of fields
> where I was using Longs in VB.NET and they were going into fields
> which are Access "Longs" but should have been VB.NET Integers. I
> changed the VB.NET code so that the fields in question were being
> assigned Integers and it made no difference?
>
> It really is confusing!
>
>
> --
> Siv
> Martley, Worcester, UK.
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:up****************@TK2MSFTNGP14.phx.gbl...
>> Hi Siv,
>>
>> Here's another idea: are any of the textboxes empty and are they
>> trying to fill a column that does not allow nulls? Just a thought.
>>
>> Bernie
>>
>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>> news:Od**************@TK2MSFTNGP12.phx.gbl...
>>> Bernie,
>>>
>>> Tried it and this didn't make any difference. BAAHHH!
>>> What I don't get is that the CommandBuilder object should create the
>>> Insert command for me automatically based on the select query.
>>> Why would Dot Net be getting it wrong with such a simple SQL
>>> statement??
>>> I've looked at other routines where I use the same technique and it
>>> works and I can't see what is different between them?
>>> --
>>> Siv
>>> Martley, Worcester, UK.
>>>
>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>>> Hi Siv,
>>>
>>> I think your problem is with the dob column. You are sending it
>>> text but it requires date data. Wrap it in "#" on both ends and see
>>> what happens. Also, just to verify my belief, simply change it to
>>> now.date to see if that is indeed the problem.
>>>
>>> HTH,
>>>
>>> Bernie Yaeger
>>>
>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>>> Hi,
>>> If I run the following:
>>>
>>> strSQL = "Select * FROM Clients;"
>>>
>>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>>> adapter
>>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>>> command builder using the datadapter
>>> dt = New Data.DataTable
>>> da.Fill(dt) 'pour in the data using the adapter
>>>
>>> rw = dt.NewRow
>>> rw("ClientID") = GetNextIDNumber("Clients")
>>>
>>> CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID
>>> to this new client
>>>
>>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>>> Long.
>>> rw("IsPrimaryClient") = True
>>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>>> rw("Forename") = txtForenameSelf.Text 'Type
>>> is: String.
>>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>>> String.
>>> rw("Surname") = txtSurnameSelf.Text 'Type
>>> is: String.
>>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>>> is: String.
>>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
>>> txtDOBDDSelf.Text 'Type is: Date.
>>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
>>> String.
>>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>>> Boolean.
>>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>>> rw("DateCreated") = Now 'Type is: Date.
>>> rw("User") = CurrentUser 'Type is String
>>> dt.Rows.Add(rw) 'Add the new row
>>> da.Update(dt) 'Send the update to the actual database
>>> using the adapter
>>>
>>> At the da.Update(dt) line I det an exception thrown: "Syntax error
>>> in INSERT INTO statement."
>>>
>>> I have other code that uses the same technique and I don't get an
>>> error at all. I have tried changing the strSQL text so that it is
>>> more complex and it makes no difference.
>>> If anyone can see the glaring mistake that I am making please put me
>>> out of my misery!
>>>
>>> Siv
>>> Martley, Worcester, UK.
>>>
>>
>>
>
>
Bernie,
Doing it now!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl... Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the user
might be a reserved word. Now rename that field back to user to see if it
was 'ni number' that was causing the problem!
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I
am trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original
post I am using "now" for dates (just in case). I am just about to press
F8 to go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it
work or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl... Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space.
It should appear as [NI Number] but the commandbuilder is not smart
enough to deal with it. Try changing the table structure to 'NINumber'
(no space) and then rebuild the commandbuilder (by rebuilding first the
oledb dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl...
Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has
occurred or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl...
> Hi Siv,
>
> Another idea: print out the commandbuilder's insert statement, thus:
> MessageBox.Show(cb.GetInsertCommand.CommandText)
>
> Let's see what that looks like.
>
> Bernie
>
>
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:O4**************@tk2msftngp13.phx.gbl...
>> Bernie,
>>
>> I had a look at that after your comment about the date field, as I do
>> tend to get tripped up by variable types (VB6 allowed us to get away
>> with a lot of implied conversions that just aren't allowed in
>> VB.NET). I went through all the fields in the table in Access to
>> check that the text fields would allow zero length strings and that I
>> was populating any fields that are required.
>>
>> In fact I tried adding the record manually through access only
>> entering the fields that the program is and it works fine.
>>
>> One thing I have been getting tripped up on is the difference between
>> "Long" in Access and "Long" in VB.NET. I did have a couple of fields
>> where I was using Longs in VB.NET and they were going into fields
>> which are Access "Longs" but should have been VB.NET Integers. I
>> changed the VB.NET code so that the fields in question were being
>> assigned Integers and it made no difference?
>>
>> It really is confusing!
>>
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:up****************@TK2MSFTNGP14.phx.gbl...
>>> Hi Siv,
>>>
>>> Here's another idea: are any of the textboxes empty and are they
>>> trying to fill a column that does not allow nulls? Just a thought.
>>>
>>> Bernie
>>>
>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>> news:Od**************@TK2MSFTNGP12.phx.gbl...
>>>> Bernie,
>>>>
>>>> Tried it and this didn't make any difference. BAAHHH!
>>>> What I don't get is that the CommandBuilder object should create
>>>> the Insert command for me automatically based on the select query.
>>>> Why would Dot Net be getting it wrong with such a simple SQL
>>>> statement??
>>>> I've looked at other routines where I use the same technique and it
>>>> works and I can't see what is different between them?
>>>> --
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>>>> Hi Siv,
>>>>
>>>> I think your problem is with the dob column. You are sending it
>>>> text but it requires date data. Wrap it in "#" on both ends and
>>>> see what happens. Also, just to verify my belief, simply change it
>>>> to now.date to see if that is indeed the problem.
>>>>
>>>> HTH,
>>>>
>>>> Bernie Yaeger
>>>>
>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>>>> Hi,
>>>> If I run the following:
>>>>
>>>> strSQL = "Select * FROM Clients;"
>>>>
>>>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>>>> adapter
>>>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>>>> command builder using the datadapter
>>>> dt = New Data.DataTable
>>>> da.Fill(dt) 'pour in the data using the adapter
>>>>
>>>> rw = dt.NewRow
>>>> rw("ClientID") = GetNextIDNumber("Clients")
>>>>
>>>> CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID
>>>> to this new client
>>>>
>>>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>>>> Long.
>>>> rw("IsPrimaryClient") = True
>>>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>>>> rw("Forename") = txtForenameSelf.Text 'Type
>>>> is: String.
>>>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>>>> String.
>>>> rw("Surname") = txtSurnameSelf.Text 'Type
>>>> is: String.
>>>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>>>> is: String.
>>>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-"
>>>> & txtDOBDDSelf.Text 'Type is: Date.
>>>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type
>>>> is: String.
>>>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>>>> Boolean.
>>>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>>>> rw("DateCreated") = Now 'Type is: Date.
>>>> rw("User") = CurrentUser 'Type is String
>>>> dt.Rows.Add(rw) 'Add the new row
>>>> da.Update(dt) 'Send the update to the actual database
>>>> using the adapter
>>>>
>>>> At the da.Update(dt) line I det an exception thrown: "Syntax error
>>>> in INSERT INTO statement."
>>>>
>>>> I have other code that uses the same technique and I don't get an
>>>> error at all. I have tried changing the strSQL text so that it is
>>>> more complex and it makes no difference.
>>>> If anyone can see the glaring mistake that I am making please put
>>>> me out of my misery!
>>>>
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>
>>>
>>
>>
>
>
Bernie,
Changed the program back so that the UserName field is now "User" again.
Also exited VB.NET and went back in as last time (just in case that is doing
something), also deleted the record in Access that was successfully created
so that I am at exactly the same point as I was before except I have
"NINumber" and "User" rather than "NINumber" and "UserName". Guess what, it
fails again, so a field called "User" is not allowed!!!
Is this a bug or what??
I certainly haven't spotted anything in the help files about avoiding any
field names??
Where's Herfried!?
BUG REPORT!!
Thanks again for your help, it always helps to have someone to bounce ideas
off. It's a bit difficult when you work at home and it's late at night
(23:57 here in UK at the moment).
Thank God for this newsgroup and helpful VB.Neters on hand to help out.
I don't know if you have already answered my question about "then rebuild
the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment" question but if you haven't spotted it I would be
keen to understand what you meant there as I may be missing a trick when
re-running VB.NET after making changes like this?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl... Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the user
might be a reserved word. Now rename that field back to user to see if it
was 'ni number' that was causing the problem!
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I
am trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original
post I am using "now" for dates (just in case). I am just about to press
F8 to go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it
work or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl... Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space.
It should appear as [NI Number] but the commandbuilder is not smart
enough to deal with it. Try changing the table structure to 'NINumber'
(no space) and then rebuild the commandbuilder (by rebuilding first the
oledb dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl...
Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate window
so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome ,
BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has
occurred or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eU**************@TK2MSFTNGP15.phx.gbl...
> Hi Siv,
>
> Another idea: print out the commandbuilder's insert statement, thus:
> MessageBox.Show(cb.GetInsertCommand.CommandText)
>
> Let's see what that looks like.
>
> Bernie
>
>
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:O4**************@tk2msftngp13.phx.gbl...
>> Bernie,
>>
>> I had a look at that after your comment about the date field, as I do
>> tend to get tripped up by variable types (VB6 allowed us to get away
>> with a lot of implied conversions that just aren't allowed in
>> VB.NET). I went through all the fields in the table in Access to
>> check that the text fields would allow zero length strings and that I
>> was populating any fields that are required.
>>
>> In fact I tried adding the record manually through access only
>> entering the fields that the program is and it works fine.
>>
>> One thing I have been getting tripped up on is the difference between
>> "Long" in Access and "Long" in VB.NET. I did have a couple of fields
>> where I was using Longs in VB.NET and they were going into fields
>> which are Access "Longs" but should have been VB.NET Integers. I
>> changed the VB.NET code so that the fields in question were being
>> assigned Integers and it made no difference?
>>
>> It really is confusing!
>>
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:up****************@TK2MSFTNGP14.phx.gbl...
>>> Hi Siv,
>>>
>>> Here's another idea: are any of the textboxes empty and are they
>>> trying to fill a column that does not allow nulls? Just a thought.
>>>
>>> Bernie
>>>
>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>> news:Od**************@TK2MSFTNGP12.phx.gbl...
>>>> Bernie,
>>>>
>>>> Tried it and this didn't make any difference. BAAHHH!
>>>> What I don't get is that the CommandBuilder object should create
>>>> the Insert command for me automatically based on the select query.
>>>> Why would Dot Net be getting it wrong with such a simple SQL
>>>> statement??
>>>> I've looked at other routines where I use the same technique and it
>>>> works and I can't see what is different between them?
>>>> --
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>>>> Hi Siv,
>>>>
>>>> I think your problem is with the dob column. You are sending it
>>>> text but it requires date data. Wrap it in "#" on both ends and
>>>> see what happens. Also, just to verify my belief, simply change it
>>>> to now.date to see if that is indeed the problem.
>>>>
>>>> HTH,
>>>>
>>>> Bernie Yaeger
>>>>
>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>>>> Hi,
>>>> If I run the following:
>>>>
>>>> strSQL = "Select * FROM Clients;"
>>>>
>>>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>>>> adapter
>>>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>>>> command builder using the datadapter
>>>> dt = New Data.DataTable
>>>> da.Fill(dt) 'pour in the data using the adapter
>>>>
>>>> rw = dt.NewRow
>>>> rw("ClientID") = GetNextIDNumber("Clients")
>>>>
>>>> CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID
>>>> to this new client
>>>>
>>>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is:
>>>> Long.
>>>> rw("IsPrimaryClient") = True
>>>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>>>> rw("Forename") = txtForenameSelf.Text 'Type
>>>> is: String.
>>>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>>>> String.
>>>> rw("Surname") = txtSurnameSelf.Text 'Type
>>>> is: String.
>>>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>>>> is: String.
>>>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-"
>>>> & txtDOBDDSelf.Text 'Type is: Date.
>>>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type
>>>> is: String.
>>>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>>>> Boolean.
>>>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>>>> rw("DateCreated") = Now 'Type is: Date.
>>>> rw("User") = CurrentUser 'Type is String
>>>> dt.Rows.Add(rw) 'Add the new row
>>>> da.Update(dt) 'Send the update to the actual database
>>>> using the adapter
>>>>
>>>> At the da.Update(dt) line I det an exception thrown: "Syntax error
>>>> in INSERT INTO statement."
>>>>
>>>> I have other code that uses the same technique and I don't get an
>>>> error at all. I have tried changing the strSQL text so that it is
>>>> more complex and it makes no difference.
>>>> If anyone can see the glaring mistake that I am making please put
>>>> me out of my misery!
>>>>
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>
>>>
>>
>>
>
>
Hi Siv,
Re 'rebuilding the commandbuilder' I did answer - I was wrong about that, as
you are not building a dataadapter using the wizard, but rather by code - so
there's no 'rebuilding' to do.
OK; we now know that it's 'user' that's the culprit - it is evidently a
reserved word. UPDATE: In fact, I just 'googled' it, and User is indeed an
MS Access reserved word.
Re these ng's: couldn't agree more. People have helped me out more times
than I can count, especially Cor and Herfried and Ken Tucker, and many, many
others.
Glad to be of help to you.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:eB**************@TK2MSFTNGP11.phx.gbl... Bernie,
Changed the program back so that the UserName field is now "User" again.
Also exited VB.NET and went back in as last time (just in case that is
doing something), also deleted the record in Access that was successfully
created so that I am at exactly the same point as I was before except I
have "NINumber" and "User" rather than "NINumber" and "UserName". Guess
what, it fails again, so a field called "User" is not allowed!!!
Is this a bug or what??
I certainly haven't spotted anything in the help files about avoiding any
field names??
Where's Herfried!?
BUG REPORT!!
Thanks again for your help, it always helps to have someone to bounce
ideas off. It's a bit difficult when you work at home and it's late at
night (23:57 here in UK at the moment).
Thank God for this newsgroup and helpful VB.Neters on hand to help out.
I don't know if you have already answered my question about "then rebuild
the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment" question but if you haven't spotted it I would be
keen to understand what you meant there as I may be missing a trick when
re-running VB.NET after making changes like this?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl... Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the user
might be a reserved word. Now rename that field back to user to see if
it was 'ni number' that was causing the problem!
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I
am trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original
post I am using "now" for dates (just in case). I am just about to
press F8 to go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it
work or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl...
Hi Siv,
OK; I'm on to something, I think. The column 'NI Number' has a space.
It should appear as [NI Number] but the commandbuilder is not smart
enough to deal with it. Try changing the table structure to 'NINumber'
(no space) and then rebuild the commandbuilder (by rebuilding first the
oledb dataadapter in tne .net environment).
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:Oe**************@TK2MSFTNGP10.phx.gbl...
> Bernie,
> Printed out the cb.GetInsertCommand.Commandtext at the immediate
> window so I could grab the text which is:
>
> "INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID ,
> IsPrimaryClient , Title , Forename , OtherNames , Surname ,
> RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
> Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
> InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome
> , BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
> BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
> VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
> , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
>
> Do the values appear as ? because I did this after the error has
> occurred or is this the reason the Insert error is coming up?
>
> --
> Siv
> Martley, Worcester, UK.
>
> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
> news:eU**************@TK2MSFTNGP15.phx.gbl...
>> Hi Siv,
>>
>> Another idea: print out the commandbuilder's insert statement, thus:
>> MessageBox.Show(cb.GetInsertCommand.CommandText)
>>
>> Let's see what that looks like.
>>
>> Bernie
>>
>>
>>
>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>> news:O4**************@tk2msftngp13.phx.gbl...
>>> Bernie,
>>>
>>> I had a look at that after your comment about the date field, as I
>>> do tend to get tripped up by variable types (VB6 allowed us to get
>>> away with a lot of implied conversions that just aren't allowed in
>>> VB.NET). I went through all the fields in the table in Access to
>>> check that the text fields would allow zero length strings and that
>>> I was populating any fields that are required.
>>>
>>> In fact I tried adding the record manually through access only
>>> entering the fields that the program is and it works fine.
>>>
>>> One thing I have been getting tripped up on is the difference
>>> between "Long" in Access and "Long" in VB.NET. I did have a couple
>>> of fields where I was using Longs in VB.NET and they were going into
>>> fields which are Access "Longs" but should have been VB.NET
>>> Integers. I changed the VB.NET code so that the fields in question
>>> were being assigned Integers and it made no difference?
>>>
>>> It really is confusing!
>>>
>>>
>>> --
>>> Siv
>>> Martley, Worcester, UK.
>>>
>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>> news:up****************@TK2MSFTNGP14.phx.gbl...
>>>> Hi Siv,
>>>>
>>>> Here's another idea: are any of the textboxes empty and are they
>>>> trying to fill a column that does not allow nulls? Just a thought.
>>>>
>>>> Bernie
>>>>
>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>> news:Od**************@TK2MSFTNGP12.phx.gbl...
>>>>> Bernie,
>>>>>
>>>>> Tried it and this didn't make any difference. BAAHHH!
>>>>> What I don't get is that the CommandBuilder object should create
>>>>> the Insert command for me automatically based on the select query.
>>>>> Why would Dot Net be getting it wrong with such a simple SQL
>>>>> statement??
>>>>> I've looked at other routines where I use the same technique and
>>>>> it works and I can't see what is different between them?
>>>>> --
>>>>> Siv
>>>>> Martley, Worcester, UK.
>>>>>
>>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>>>>> Hi Siv,
>>>>>
>>>>> I think your problem is with the dob column. You are sending it
>>>>> text but it requires date data. Wrap it in "#" on both ends and
>>>>> see what happens. Also, just to verify my belief, simply change it
>>>>> to now.date to see if that is indeed the problem.
>>>>>
>>>>> HTH,
>>>>>
>>>>> Bernie Yaeger
>>>>>
>>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>>>>> Hi,
>>>>> If I run the following:
>>>>>
>>>>> strSQL = "Select * FROM Clients;"
>>>>>
>>>>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>>>>> adapter
>>>>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>>>>> command builder using the datadapter
>>>>> dt = New Data.DataTable
>>>>> da.Fill(dt) 'pour in the data using the adapter
>>>>>
>>>>> rw = dt.NewRow
>>>>> rw("ClientID") = GetNextIDNumber("Clients")
>>>>>
>>>>> CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID
>>>>> to this new client
>>>>>
>>>>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type
>>>>> is: Long.
>>>>> rw("IsPrimaryClient") = True
>>>>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>>>>> rw("Forename") = txtForenameSelf.Text 'Type
>>>>> is: String.
>>>>> rw("OtherNames") = txtOtherNameSelf.Text 'Type is:
>>>>> String.
>>>>> rw("Surname") = txtSurnameSelf.Text 'Type
>>>>> is: String.
>>>>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text
>>>>> 'Type is: String.
>>>>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-"
>>>>> & txtDOBDDSelf.Text 'Type is: Date.
>>>>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type
>>>>> is: String.
>>>>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>>>>> Boolean.
>>>>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>>>>> rw("DateCreated") = Now 'Type is: Date.
>>>>> rw("User") = CurrentUser 'Type is String
>>>>> dt.Rows.Add(rw) 'Add the new row
>>>>> da.Update(dt) 'Send the update to the actual database
>>>>> using the adapter
>>>>>
>>>>> At the da.Update(dt) line I det an exception thrown: "Syntax error
>>>>> in INSERT INTO statement."
>>>>>
>>>>> I have other code that uses the same technique and I don't get an
>>>>> error at all. I have tried changing the strSQL text so that it is
>>>>> more complex and it makes no difference.
>>>>> If anyone can see the glaring mistake that I am making please put
>>>>> me out of my misery!
>>>>>
>>>>> Siv
>>>>> Martley, Worcester, UK.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Bernie,
Thanks, I hadn't twigged you had answered my question, but now I understand.
Thanks again. I can sleep now thanks to you!
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:em**************@TK2MSFTNGP11.phx.gbl... Hi Siv,
Re 'rebuilding the commandbuilder' I did answer - I was wrong about that,
as you are not building a dataadapter using the wizard, but rather by
code - so there's no 'rebuilding' to do.
OK; we now know that it's 'user' that's the culprit - it is evidently a
reserved word. UPDATE: In fact, I just 'googled' it, and User is indeed
an MS Access reserved word.
Re these ng's: couldn't agree more. People have helped me out more times
than I can count, especially Cor and Herfried and Ken Tucker, and many,
many others.
Glad to be of help to you.
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:eB**************@TK2MSFTNGP11.phx.gbl... Bernie,
Changed the program back so that the UserName field is now "User" again.
Also exited VB.NET and went back in as last time (just in case that is
doing something), also deleted the record in Access that was successfully
created so that I am at exactly the same point as I was before except I
have "NINumber" and "User" rather than "NINumber" and "UserName". Guess
what, it fails again, so a field called "User" is not allowed!!!
Is this a bug or what??
I certainly haven't spotted anything in the help files about avoiding any
field names??
Where's Herfried!?
BUG REPORT!!
Thanks again for your help, it always helps to have someone to bounce
ideas off. It's a bit difficult when you work at home and it's late at
night (23:57 here in UK at the moment).
Thank God for this newsgroup and helpful VB.Neters on hand to help out.
I don't know if you have already answered my question about "then rebuild
the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment" question but if you haven't spotted it I would
be keen to understand what you meant there as I may be missing a trick
when re-running VB.NET after making changes like this?
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eK**************@tk2msftngp13.phx.gbl... Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the
user might be a reserved word. Now rename that field back to user to
see if it was 'ni number' that was causing the problem!
Bernie
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...
Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I
am trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original
post I am using "now" for dates (just in case). I am just about to
press F8 to go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it
work or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:OO**************@tk2msftngp13.phx.gbl...
> Hi Siv,
>
> OK; I'm on to something, I think. The column 'NI Number' has a space.
> It should appear as [NI Number] but the commandbuilder is not smart
> enough to deal with it. Try changing the table structure to
> 'NINumber' (no space) and then rebuild the commandbuilder (by
> rebuilding first the oledb dataadapter in tne .net environment).
>
> Bernie
>
> "Siv" <ms**********@removeme.sivill.com> wrote in message
> news:Oe**************@TK2MSFTNGP10.phx.gbl...
>> Bernie,
>> Printed out the cb.GetInsertCommand.Commandtext at the immediate
>> window so I could grab the text which is:
>>
>> "INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID
>> , IsPrimaryClient , Title , Forename , OtherNames , Surname ,
>> RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
>> Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
>> InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome
>> , BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
>> BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
>> VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
>> ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
>>
>> Do the values appear as ? because I did this after the error has
>> occurred or is this the reason the Insert error is coming up?
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>> news:eU**************@TK2MSFTNGP15.phx.gbl...
>>> Hi Siv,
>>>
>>> Another idea: print out the commandbuilder's insert statement, thus:
>>> MessageBox.Show(cb.GetInsertCommand.CommandText)
>>>
>>> Let's see what that looks like.
>>>
>>> Bernie
>>>
>>>
>>>
>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>> news:O4**************@tk2msftngp13.phx.gbl...
>>>> Bernie,
>>>>
>>>> I had a look at that after your comment about the date field, as I
>>>> do tend to get tripped up by variable types (VB6 allowed us to get
>>>> away with a lot of implied conversions that just aren't allowed in
>>>> VB.NET). I went through all the fields in the table in Access to
>>>> check that the text fields would allow zero length strings and that
>>>> I was populating any fields that are required.
>>>>
>>>> In fact I tried adding the record manually through access only
>>>> entering the fields that the program is and it works fine.
>>>>
>>>> One thing I have been getting tripped up on is the difference
>>>> between "Long" in Access and "Long" in VB.NET. I did have a couple
>>>> of fields where I was using Longs in VB.NET and they were going
>>>> into fields which are Access "Longs" but should have been VB.NET
>>>> Integers. I changed the VB.NET code so that the fields in question
>>>> were being assigned Integers and it made no difference?
>>>>
>>>> It really is confusing!
>>>>
>>>>
>>>> --
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>> news:up****************@TK2MSFTNGP14.phx.gbl...
>>>>> Hi Siv,
>>>>>
>>>>> Here's another idea: are any of the textboxes empty and are they
>>>>> trying to fill a column that does not allow nulls? Just a
>>>>> thought.
>>>>>
>>>>> Bernie
>>>>>
>>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>>> news:Od**************@TK2MSFTNGP12.phx.gbl...
>>>>>> Bernie,
>>>>>>
>>>>>> Tried it and this didn't make any difference. BAAHHH!
>>>>>> What I don't get is that the CommandBuilder object should create
>>>>>> the Insert command for me automatically based on the select
>>>>>> query.
>>>>>> Why would Dot Net be getting it wrong with such a simple SQL
>>>>>> statement??
>>>>>> I've looked at other routines where I use the same technique and
>>>>>> it works and I can't see what is different between them?
>>>>>> --
>>>>>> Siv
>>>>>> Martley, Worcester, UK.
>>>>>>
>>>>>> "Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
>>>>>> news:u1**************@TK2MSFTNGP10.phx.gbl...
>>>>>> Hi Siv,
>>>>>>
>>>>>> I think your problem is with the dob column. You are sending it
>>>>>> text but it requires date data. Wrap it in "#" on both ends and
>>>>>> see what happens. Also, just to verify my belief, simply change
>>>>>> it to now.date to see if that is indeed the problem.
>>>>>>
>>>>>> HTH,
>>>>>>
>>>>>> Bernie Yaeger
>>>>>>
>>>>>> "Siv" <ms**********@removeme.sivill.com> wrote in message
>>>>>> news:ek**************@TK2MSFTNGP09.phx.gbl...
>>>>>> Hi,
>>>>>> If I run the following:
>>>>>>
>>>>>> strSQL = "Select * FROM Clients;"
>>>>>>
>>>>>> da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data
>>>>>> adapter
>>>>>> cb = New OleDb.OleDbCommandBuilder(da) 'Create
>>>>>> command builder using the datadapter
>>>>>> dt = New Data.DataTable
>>>>>> da.Fill(dt) 'pour in the data using the adapter
>>>>>>
>>>>>> rw = dt.NewRow
>>>>>> rw("ClientID") = GetNextIDNumber("Clients")
>>>>>>
>>>>>> CurrentClientID = CLng(rw("ClientID")) 'Set the
>>>>>> currentClientID to this new client
>>>>>>
>>>>>> rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type
>>>>>> is: Long.
>>>>>> rw("IsPrimaryClient") = True
>>>>>> rw("Title") = txtTitleSelf.Text 'Type is: String.
>>>>>> rw("Forename") = txtForenameSelf.Text 'Type
>>>>>> is: String.
>>>>>> rw("OtherNames") = txtOtherNameSelf.Text 'Type
>>>>>> is: String.
>>>>>> rw("Surname") = txtSurnameSelf.Text
>>>>>> 'Type is: String.
>>>>>> rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type
>>>>>> is: String.
>>>>>> rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text &
>>>>>> "-" & txtDOBDDSelf.Text 'Type is: Date.
>>>>>> rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type
>>>>>> is: String.
>>>>>> rw("SmokerYN") = chkSmokerSelf.Checked 'Type is:
>>>>>> Boolean.
>>>>>> rw("Notes") = txtNotesSelf.Text 'Type is: String.
>>>>>> rw("DateCreated") = Now 'Type is: Date.
>>>>>> rw("User") = CurrentUser 'Type is String
>>>>>> dt.Rows.Add(rw) 'Add the new row
>>>>>> da.Update(dt) 'Send the update to the actual database
>>>>>> using the adapter
>>>>>>
>>>>>> At the da.Update(dt) line I det an exception thrown: "Syntax
>>>>>> error in INSERT INTO statement."
>>>>>>
>>>>>> I have other code that uses the same technique and I don't get an
>>>>>> error at all. I have tried changing the strSQL text so that it
>>>>>> is more complex and it makes no difference.
>>>>>> If anyone can see the glaring mistake that I am making please put
>>>>>> me out of my misery!
>>>>>>
>>>>>> Siv
>>>>>> Martley, Worcester, UK.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Siv,
If you are going to use a CommandBuilder with any spaces or possibly
reserved words in column names you need to set the QuotePrefix and
QuoteSuffix properties of the CommandBuilder. Usually to '[' and ']'
respectively.
Ron Allen
"Siv" <ms**********@removeme.sivill.com> wrote in message
news:ek**************@TK2MSFTNGP09.phx.gbl...
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt)
'pour in the data using the adapter
rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")
CurrentClientID = CLng(rw("ClientID")) 'Set the
currentClientID to this new client
rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text
'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is:
String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw) 'Add
the new row
da.Update(dt) 'Send the update to the actual database using the
adapter
At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT
INTO statement."
I have other code that uses the same technique and I don't get an error at
all. I have tried changing the strSQL text so that it is more complex and
it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of
my misery!
Siv
Martley, Worcester, UK.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: brendan |
last post by:
here's a brain teaser for someone with more skills or at least more
lateral thinking capability than me - done my nut over this one...
have written a list manager in PHP which
a) posts out new...
|
by: Brett conklin |
last post by:
I have an xml document that I am trying to transform into HTML. This
sounds straigt forward but the xml uses an format that I don't know
how to xslt. Here is the format.
<RESULTSET>
<HIT>...
|
by: D. Bemis |
last post by:
MS Access SQL question: Using MS Access, I'm trying to
take data from two different tables and dump it to a new
table. Below are a couple of examples of what I have tried
and was unsuccessful.
...
|
by: RC |
last post by:
Dear Dudes,
I post this in multiple groups for opening brain storm.
Sometime I need to query the data from database server then display them
into user's browser in HTML <table>. But if the...
|
by: Mark A |
last post by:
If server 01 running HADR in the primary role crashes, and the DBA does a
HADR takeover by force on the 02 server to switch roles, then the 02 server
is now the primary.
What happens when the...
| | |
by: sd00 |
last post by:
Hi all, can someone give me some coding help with a problem that
*should* be really simple, yet I'm struggling with.
I need the difference between 2 times (Target / Actual)
However, these times...
|
by: Joachim Klassen |
last post by:
Hi all,
if I accidentally use a TAKEOVER command with BY FORCE clause while
primary and standby are in peer state I'll end up with two primary's
(at least with FP10 and Windows). Is this works ...
|
by: bigmentor |
last post by:
Good Morning Folks
I have bought and own a copy of ASPImage.
Installed on my Internet server.
Works like a Dream writing and saving image files containing text.
My brain seems to have...
|
by: nicko |
last post by:
hi,
i am developing a db in access 2000 and i am stuck on what i consider to be a basic requirement.
in the table i have 12 similar text fields called "client contact week n" where n is a number...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
