My Brain Hurts - Help

**Bernie Yaeger** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <berniey@cherwe llinc.com> wrote in message news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Darious Snell** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <berniey@cherwe llinc.com> wrote in message
news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the
currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt
'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is:
String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is:
String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is:
String.
rw("DateCreated ") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw) 'Add
the new row
da.Update(dt) 'Send the update to the actual database using the
adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT
INTO statement."

I have other code that uses the same technique and I don't get an error at
all. I have tried changing the strSQL text so that it is more complex and
it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of
my misery!

Siv
Martley, Worcester, UK.

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <null@sdlkfweu. org> wrote in message news:41d9b54c$0 $69059$39cecf19 @news.twtelecom .net...
Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Bernie Yaeger** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:Od586qd8EH A.3376@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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" <berniey@cherwe llinc.com> wrote in message
> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
> Hi,
> If I run the following:
>
> strSQL = "Select * FROM Clients;"
>
> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the
> currentClientID to this new client
>
> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
> rw("IsPrimaryCl ient") = True
> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
> rw("Forename") = txtForenameSelf .Text 'Type is:
> String.
> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
> rw("Surname") = txtSurnameSelf. Text 'Type is:
> String.
> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
> String.
> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
> txtDOBDDSelf.Te xt 'Type is: Date.
> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
> String.
> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
> rw("Notes") = txtNotesSelf.Te xt '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.
>[/color]

**Bernie Yaeger** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:Od586qd8EH A.3376@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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" <berniey@cherwe llinc.com> wrote in message
> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
> Hi,
> If I run the following:
>
> strSQL = "Select * FROM Clients;"
>
> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the
> currentClientID to this new client
>
> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
> rw("IsPrimaryCl ient") = True
> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
> rw("Forename") = txtForenameSelf .Text 'Type is:
> String.
> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
> rw("Surname") = txtSurnameSelf. Text 'Type is:
> String.
> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
> String.
> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
> txtDOBDDSelf.Te xt 'Type is: Date.
> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
> String.
> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
> rw("Notes") = txtNotesSelf.Te xt '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.
>[/color]

**Darious Snell** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Does the table you are inserting into have a primary key defined?
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:%23Uiwisd8 EHA.1260@TK2MSF TNGP12.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" <null@sdlkfweu. org> wrote in message news:41d9b54c$0 $69059$39cecf19 @news.twtelecom .net...
Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

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" <berniey@cherwe llinc.com> wrote in message
news:uppzF%23d8 EHA.3708@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> 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" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:Od586qd8EH A.3376@TK2MSFTN GP12.phx.gbl...[color=green]
>> 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" <berniey@cherwe llinc.com> wrote in message
>> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
>> Hi,
>> If I run the following:
>>
>> strSQL = "Select * FROM Clients;"
>>
>> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
>> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set
>> the currentClientID to this new client
>>
>> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
>> rw("IsPrimaryCl ient") = True
>> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
>> rw("Forename") = txtForenameSelf .Text 'Type is:
>> String.
>> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is:
>> String.
>> rw("Surname") = txtSurnameSelf. Text 'Type is:
>> String.
>> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
>> String.
>> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
>> txtDOBDDSelf.Te xt 'Type is: Date.
>> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
>> String.
>> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
>> rw("Notes") = txtNotesSelf.Te xt '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.
>>[/color]
>
>[/color]

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Darious,

Yes it does it is the ClientID field.

--
Siv
Martley, Worcester, UK.
"Darious Snell" <null@sdlkfweu. org> wrote in message news:41d9c2bb$0 $69055$39cecf19 @news.twtelecom .net...
Does the table you are inserting into have a primary key defined?
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:%23Uiwisd8 EHA.1260@TK2MSF TNGP12.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" <null@sdlkfweu. org> wrote in message news:41d9b54c$0 $69059$39cecf19 @news.twtelecom .net...
Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set the currentClientID to this new client

rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is: Long.
rw("IsPrimaryCl ient") = True
rw("Title") = txtTitleSelf.Te xt 'Type is: String.
rw("Forename") = txtForenameSelf .Text 'Type is: String.
rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is: String.
rw("Surname") = txtSurnameSelf. Text 'Type is: String.
rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Te xt 'Type is: String.
rw("DateCreated ") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

At the da.Update(dt) line I det an exception thrown: "Syntax error in INSERT INTO statement."

I have other code that uses the same technique and I don't get an error at all. I have tried changing the strSQL text so that it is more complex and it makes no difference.
If anyone can see the glaring mistake that I am making please put me out of my misery!

Siv
Martley, Worcester, UK.

**Bernie Yaeger** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Hi Siv,

Did you set up the try...catch block?

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:O4KejGe8EH A.1228@tk2msftn gp13.phx.gbl...[color=blue]
> 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" <berniey@cherwe llinc.com> wrote in message
> news:uppzF%23d8 EHA.3708@TK2MSF TNGP14.phx.gbl. ..[color=green]
>> 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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>> news:Od586qd8EH A.3376@TK2MSFTN GP12.phx.gbl...[color=darkred]
>>> 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" <berniey@cherwe llinc.com> wrote in message
>>> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
>>> Hi,
>>> If I run the following:
>>>
>>> strSQL = "Select * FROM Clients;"
>>>
>>> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data
>>> adapter
>>> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set
>>> the currentClientID to this new client
>>>
>>> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is:
>>> Long.
>>> rw("IsPrimaryCl ient") = True
>>> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
>>> rw("Forename") = txtForenameSelf .Text 'Type is:
>>> String.
>>> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is:
>>> String.
>>> rw("Surname") = txtSurnameSelf. Text 'Type is:
>>> String.
>>> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
>>> String.
>>> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
>>> txtDOBDDSelf.Te xt 'Type is: Date.
>>> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
>>> String.
>>> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is:
>>> Boolean.
>>> rw("Notes") = txtNotesSelf.Te xt '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.
>>>[/color]
>>
>>[/color]
>
>[/color]

**Bernie Yaeger** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Hi Siv,

Another idea: print out the commandbuilder' s insert statement, thus:
MessageBox.Show (cb.GetInsertCo mmand.CommandTe xt)

Let's see what that looks like.

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:O4KejGe8EH A.1228@tk2msftn gp13.phx.gbl...[color=blue]
> 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" <berniey@cherwe llinc.com> wrote in message
> news:uppzF%23d8 EHA.3708@TK2MSF TNGP14.phx.gbl. ..[color=green]
>> 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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>> news:Od586qd8EH A.3376@TK2MSFTN GP12.phx.gbl...[color=darkred]
>>> 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" <berniey@cherwe llinc.com> wrote in message
>>> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
>>> Hi,
>>> If I run the following:
>>>
>>> strSQL = "Select * FROM Clients;"
>>>
>>> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data
>>> adapter
>>> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set
>>> the currentClientID to this new client
>>>
>>> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is:
>>> Long.
>>> rw("IsPrimaryCl ient") = True
>>> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
>>> rw("Forename") = txtForenameSelf .Text 'Type is:
>>> String.
>>> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is:
>>> String.
>>> rw("Surname") = txtSurnameSelf. Text 'Type is:
>>> String.
>>> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
>>> String.
>>> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
>>> txtDOBDDSelf.Te xt 'Type is: Date.
>>> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
>>> String.
>>> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is:
>>> Boolean.
>>> rw("Notes") = txtNotesSelf.Te xt '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.
>>>[/color]
>>
>>[/color]
>
>[/color]

**Siv** · Nov 21 '05, 09:48 AM

Re: My Brain Hurts - Help

Bernie,
Printed out the cb.GetInsertCom mand.Commandtex t at the immediate window so I
could grab the text which is:

"INSERT INTO Clients( ClientID , RelatedClientLi nkID , AdviserLinkID ,
IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToP artner , DOB , StateOfHealth , SmokerYN , Notes , NI Number ,
TaxCode , Income , SalaryOTBonus , SalaryReviewDat e , InvPensIncome ,
SelfEmpNetRelEa rnings , TaxRatePercenta ge , NetIncome , BenefitsInKindY N ,
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" <berniey@cherwe llinc.com> wrote in message
news:eUOKZMe8EH A.2012@TK2MSFTN GP15.phx.gbl...[color=blue]
> Hi Siv,
>
> Another idea: print out the commandbuilder' s insert statement, thus:
> MessageBox.Show (cb.GetInsertCo mmand.CommandTe xt)
>
> Let's see what that looks like.
>
> Bernie
>
>
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:O4KejGe8EH A.1228@tk2msftn gp13.phx.gbl...[color=green]
>> 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" <berniey@cherwe llinc.com> wrote in message
>> news:uppzF%23d8 EHA.3708@TK2MSF TNGP14.phx.gbl. ..[color=darkred]
>>> 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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:Od586qd8EH A.3376@TK2MSFTN GP12.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" <berniey@cherwe llinc.com> wrote in message
>>>> news:u1VdPbd8EH A.2608@TK2MSFTN GP10.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" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>>> news:ekSZ7Nd8EH A.1524@TK2MSFTN GP09.phx.gbl...
>>>> Hi,
>>>> If I run the following:
>>>>
>>>> strSQL = "Select * FROM Clients;"
>>>>
>>>> da = New OleDb.OleDbData Adapter(strSQL, Conn) 'Create data
>>>> adapter
>>>> cb = New OleDb.OleDbComm andBuilder(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("Client ID")) 'Set
>>>> the currentClientID to this new client
>>>>
>>>> rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type is:
>>>> Long.
>>>> rw("IsPrimaryCl ient") = True
>>>> rw("Title") = txtTitleSelf.Te xt 'Type is: String.
>>>> rw("Forename") = txtForenameSelf .Text 'Type is:
>>>> String.
>>>> rw("OtherNames" ) = txtOtherNameSel f.Text 'Type is:
>>>> String.
>>>> rw("Surname") = txtSurnameSelf. Text 'Type is:
>>>> String.
>>>> rw("Relationshi pToPartner") = txtRelToPartner Self.Text 'Type is:
>>>> String.
>>>> rw("DOB") = txtDOBYYYYSelf. Text & "-" & txtDOBMMSelf.Te xt & "-" &
>>>> txtDOBDDSelf.Te xt 'Type is: Date.
>>>> rw("StateOfHeal th") = txtStateOfHealt hSelf.Text 'Type is:
>>>> String.
>>>> rw("SmokerYN") = chkSmokerSelf.C hecked 'Type is:
>>>> Boolean.
>>>> rw("Notes") = txtNotesSelf.Te xt '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.
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

My Brain Hurts - Help

My Brain Hurts - Help

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment