My Brain Hurts - Help

  • Thread starter Thread starter Siv
  • Start date Start date
S

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.
 
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

Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Bernie,
I'll give that a go and report back, if that's what it is I'll be well pleased!

--
Siv
Martley, Worcester, UK.
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

Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Bernie,

Tried it and this didn't make any difference. BAAHHH!
What I don't get is that the CommandBuilder object should create the Insert
command for me automatically based on the select query.
Why would Dot Net be getting it wrong with such a simple SQL statement??
I've looked at other routines where I use the same technique and it works
and I can't see what is different between them?
--
Siv
Martley, Worcester, UK.

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

Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command
builder using the datadapter
dt = New Data.DataTable
da.Fill(dt)
'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the
currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text
'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is:
String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is:
String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is:
String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" &
txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is:
String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is:
String.
rw("DateCreated") = Now 'Type
is: Date.
rw("User") = CurrentUser 'Type
is String
dt.Rows.Add(rw) 'Add
the new row
da.Update(dt) 'Send the update to the actual database using the
adapter

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

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

Siv
Martley, Worcester, UK.
 
Darious,
I could do that, but I am more curious as to why this should error when most autogenerated insert statements of the same type work fine without needing to manually create an insert statement?

--
Siv
Martley, Worcester, UK.
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Hi All,

One thing that occurs to me but I am not sure whether I have always done it in my versions of this technique that do work, is that I am not filling all the fields in the table. My assumption being that ADO.NET will do as per ADO and leave Jet to fill in any default values for fields that aren't specifically altered. Is this assumption correct in VB.NET??

If you must fill in all the fields that would be returned by the select statement, this would account for me getting an error, but why it appears as "Syntax error in INSERT INTO statement." baffles me.

On pausing the code and interrogating the number of rows at the line just prior to the error line, the number of rows has increased by one in the DataTable, so it has definitely got that far without tripping anything up?

I'm stumped?


--
Siv
Martley, Worcester, UK.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Hi Siv,

I will continue to look at your code to come up with an answer. Re the
commandbuilder - it works fine for me on literally hundreds of occasions, so
I don't think that is your problem.

For now, wrap the update call in a try ... catch block and messagebox.show
ex.message, like this:
Try

da.Update(dt)

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Also, start to comment out one column at a time; once it works, we'll know
that that is the column causing the problem.

Bernie
 
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
 
Does the table you are inserting into have a primary key defined?
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.
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Bernie,

I had a look at that after your comment about the date field, as I do tend
to get tripped up by variable types (VB6 allowed us to get away with a lot
of implied conversions that just aren't allowed in VB.NET). I went through
all the fields in the table in Access to check that the text fields would
allow zero length strings and that I was populating any fields that are
required.

In fact I tried adding the record manually through access only entering the
fields that the program is and it works fine.

One thing I have been getting tripped up on is the difference between "Long"
in Access and "Long" in VB.NET. I did have a couple of fields where I was
using Longs in VB.NET and they were going into fields which are Access
"Longs" but should have been VB.NET Integers. I changed the VB.NET code so
that the fields in question were being assigned Integers and it made no
difference?

It really is confusing!
 
Darious,

Yes it does it is the ClientID field.

--
Siv
Martley, Worcester, UK.
Does the table you are inserting into have a primary key defined?
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.
Check into setting da..UpdateCommand.CommandText() = to a valid insert command.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Hi Siv,

Another idea: print out the commandbuilder's insert statement, thus:
MessageBox.Show(cb.GetInsertCommand.CommandText)

Let's see what that looks like.

Bernie
 
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?
 
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.
 
Hi Siv,

No; the question marks are perfectly normal; they represent replaceable
variables.

Bernie
 
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
 
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

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.
Hi,
If I run the following:

strSQL = "Select * FROM Clients;"

da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) 'Create command builder using the datadapter
dt = New Data.DataTable
da.Fill(dt) 'pour in the data using the adapter

rw = dt.NewRow
rw("ClientID") = GetNextIDNumber("Clients")

CurrentClientID = CLng(rw("ClientID")) 'Set the currentClientID to this new client

rw("RelatedClientLinkID") = CurrentRelatedClientID 'Type is: Long.
rw("IsPrimaryClient") = True
rw("Title") = txtTitleSelf.Text 'Type is: String.
rw("Forename") = txtForenameSelf.Text 'Type is: String.
rw("OtherNames") = txtOtherNameSelf.Text 'Type is: String.
rw("Surname") = txtSurnameSelf.Text 'Type is: String.
rw("RelationshipToPartner") = txtRelToPartnerSelf.Text 'Type is: String.
rw("DOB") = txtDOBYYYYSelf.Text & "-" & txtDOBMMSelf.Text & "-" & txtDOBDDSelf.Text 'Type is: Date.
rw("StateOfHealth") = txtStateOfHealthSelf.Text 'Type is: String.
rw("SmokerYN") = chkSmokerSelf.Checked 'Type is: Boolean.
rw("Notes") = txtNotesSelf.Text 'Type is: String.
rw("DateCreated") = Now 'Type is: Date.
rw("User") = CurrentUser 'Type is String
dt.Rows.Add(rw) 'Add the new row
da.Update(dt) 'Send the update to the actual database using the adapter

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

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

Siv
Martley, Worcester, UK.
 
Hi Siv,

I see now you have nothing to do with the commandbuilder and dataadapter - just change the column 'NI Number' and we will know if that is the answer.

Bernie

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.
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help - Problem Getting DataAdapter to update 11
Pls Help: IErrorInfo.GetDescription failed with E_FAIL 1
Database Help 2
GetOleDbSchemaTable question 1
Problem with binding data retrieve from oracle DB to textbox 1
problem adding new record to access database table 2
Help with strSQL 5
newrow to Oracle dataset 1

Back
Top