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

My Brain Hurts - Help

P: n/a
Siv
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.
Nov 21 '05 #1
Share this Question
Share on Google+
28 Replies


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

P: n/a
Siv
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.
Nov 21 '05 #3

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

P: n/a
Siv
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.
Nov 21 '05 #5

P: n/a
Siv
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.
Nov 21 '05 #6

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

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

Nov 21 '05 #8

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

Nov 21 '05 #9

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

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


Nov 21 '05 #11

P: n/a
Siv
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.
Nov 21 '05 #12

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



Nov 21 '05 #13

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



Nov 21 '05 #14

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



Nov 21 '05 #15

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



Nov 21 '05 #16

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



Nov 21 '05 #17

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



Nov 21 '05 #18

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

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

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



Nov 21 '05 #21

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



Nov 21 '05 #22

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



Nov 21 '05 #23

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



Nov 21 '05 #24

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



Nov 21 '05 #25

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



Nov 21 '05 #26

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



Nov 21 '05 #27

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



Nov 21 '05 #28

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

This discussion thread is closed

Replies have been disabled for this discussion.