Hi Siv,
Wow - we are really on the same wavelength - I was also thinking the
user might be a reserved word. Now rename that field back to user to
see if it was 'ni number' that was causing the problem!
Bernie
Bernie,
I modified the fields as per your previous post and I also changed the
"User" field just in case that is a reserved word or something and just
before processing the line printed out the ItemArray of the data that I
am trying to update which is as follows:
? dt.Rows(2).ItemArray
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"
As you can see all fields have relevant values - as per your original
post I am using "now" for dates (just in case). I am just about to
press F8 to go onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it
work or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on
it.
--
Siv
Martley, Worcester, UK.
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
Bernie,
Printed out the cb.GetInsertCommand.Commandtext at the immediate
window so I could grab the text which is:
"INSERT INTO Clients( ClientID , RelatedClientLinkID , AdviserLinkID
, IsPrimaryClient , Title , Forename , OtherNames , Surname ,
RelationshipToPartner , DOB , StateOfHealth , SmokerYN , Notes , NI
Number , TaxCode , Income , SalaryOTBonus , SalaryReviewDate ,
InvPensIncome , SelfEmpNetRelEarnings , TaxRatePercentage , NetIncome
, BenefitsInKindYN , BenefitDesc1 , BenefitValue1 , BenefitDesc2 ,
BenefitValue2 , BenefitDesc3 , BenefitValue3 , DateCreated , User )
VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
Do the values appear as ? because I did this after the error has
occurred or is this the reason the Insert error is coming up?
--
Siv
Martley, Worcester, UK.
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,
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.
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
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.
