473,881 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

My Brain Hurts - Help

Siv
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.
Nov 21 '05 #1
28 1910
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**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #2
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*****@cherwe llinc.com> wrote in message news:u1******** ******@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" <ms**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #3
Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <ms**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #4
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*****@cherwe llinc.com> wrote in message
news:u1******** ******@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" <ms**********@r emoveme.sivill. com> wrote in message
news:ek******** ******@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.
Nov 21 '05 #5
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..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <ms**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #6
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**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #7
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**********@r emoveme.sivill. com> wrote in message
news:Od******** ******@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" <be*****@cherwe llinc.com> wrote in message
news:u1******** ******@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" <ms**********@r emoveme.sivill. com> wrote in message
news:ek******** ******@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.

Nov 21 '05 #8
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**********@r emoveme.sivill. com> wrote in message
news:Od******** ******@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" <be*****@cherwe llinc.com> wrote in message
news:u1******** ******@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" <ms**********@r emoveme.sivill. com> wrote in message
news:ek******** ******@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.

Nov 21 '05 #9
Does the table you are inserting into have a primary key defined?
"Siv" <ms**********@r emoveme.sivill. com> wrote in message news:%2******** ********@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" <nu**@sdlkfweu. org> wrote in message news:41******** *************** @news.twtelecom .net...
Check into setting da..UpdateComma nd.CommandText( ) = to a valid insert command.
"Siv" <ms**********@r emoveme.sivill. com> wrote in message news:ek******** ******@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.
Nov 21 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1760
by: brendan | last post by:
here's a brain teaser for someone with more skills or at least more lateral thinking capability than me - done my nut over this one... have written a list manager in PHP which a) posts out new web bulletin board messages to registered users. b) recieves emails and posts them to the bulletin board if they have the right variables in the subject line The subject line is created by the script and contains the group ID, the bulletin board...
4
2113
by: Brett conklin | last post by:
I have an xml document that I am trying to transform into HTML. This sounds straigt forward but the xml uses an format that I don't know how to xslt. Here is the format. <RESULTSET> <HIT> <FIELD NAME="contenttype">text/html</FIELD> <FIELD NAME="language">en</FIELD> <FIELD NAME="charset">iso-8859-1</FIELD> <FIELD NAME="urls">/wed/dd/Cwebwire-bac_gen.6898.html</FIELD>
4
1350
by: D. Bemis | last post by:
MS Access SQL question: Using MS Access, I'm trying to take data from two different tables and dump it to a new table. Below are a couple of examples of what I have tried and was unsuccessful. Thanks, pea brain Option 1: MS Access blows up when I try this
3
1702
by: RC | last post by:
Dear Dudes, I post this in multiple groups for opening brain storm. Sometime I need to query the data from database server then display them into user's browser in HTML <table>. But if the <table> is very LARGE, let's say 20 columns, hundreds rows. Usually you have header <th> tags on 1st (top) row and 1st (most left) column. For a such LARGE <table>. you really want:
7
10674
by: Mark A | last post by:
If server 01 running HADR in the primary role crashes, and the DBA does a HADR takeover by force on the 02 server to switch roles, then the 02 server is now the primary. What happens when the Server 01 is brought back up? It still thinks it is the primary because that was its role when it crashed and it does not know about the takeover by force command that was issued. Does the 01 server check the 02 server to see what role they are in...
13
2159
by: sd00 | last post by:
Hi all, can someone give me some coding help with a problem that *should* be really simple, yet I'm struggling with. I need the difference between 2 times (Target / Actual) However, these times will fall somewhere between a Start & End time Further more, there will be Break & Lunch times between Start & End. Example... Start 08:00 Break start 10:30
3
5713
by: Joachim Klassen | last post by:
Hi all, if I accidentally use a TAKEOVER command with BY FORCE clause while primary and standby are in peer state I'll end up with two primary's (at least with FP10 and Windows). Is this works as designed or a bug ? Manuals say that the standby will inform the primary about the takeover but will not wait for acknowledgement, so the primary knows about whats going on. In my eyes primary should either switch to standby or shutdown...
2
1607
by: bigmentor | last post by:
Good Morning Folks I have bought and own a copy of ASPImage. Installed on my Internet server. Works like a Dream writing and saving image files containing text. My brain seems to have stopped working. I have tried to write a script that I expect cannot be more than ten lines long. The towel has been thrown in !!
2
1260
by: nicko | last post by:
hi, i am developing a db in access 2000 and i am stuck on what i consider to be a basic requirement. in the table i have 12 similar text fields called "client contact week n" where n is a number from 1 to 12. the data stored in these fields is limited within the input form to 7 types. for example: letter, telephone, face to face etc. i need to produce a report that displays the total records for each type of contact for each week - 12 weeks...
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.