My Brain Hurts - Help

[Siv]

Re: My Brain Hurts - Help

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.OleDbData Adapter, cb As OleDb.OleDbComm andBuilder
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.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

If NewRecord Then 'If doing a new record, begin edit and get
next id number

rw = dt.NewRow
rw("ClientID") = 2 'GetNextIDNumbe r("Clients")
CurrentClientID = 2 'rw("ClientID") 'Set the currentClientID
to this new client
rw("RelatedClie ntLinkID") = CurrentRelatedC lientID 'Type
is: Long.
rw("AdviserLink ID") = 0
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") = Now '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("NI Number") = "" 'Type is: String.
rw("TaxCode") = "" 'Type is: String.
rw("Income") = 0 'Type is: Single.
rw("SalaryOTBon us") = 0 'Type is: Single.
rw("SalaryRevie wDate") = Now 'Type is: Date.
rw("InvPensInco me") = 0 'Type is: Single.
rw("SelfEmpNetR elEarnings") = 0 'Type is: Single.
rw("TaxRatePerc entage") = 0 'Type is: Single.
rw("NetIncome" ) = 0 'Type is: Single.
rw("BenefitsInK indYN") = False 'Type is: Boolean.
rw("BenefitDesc 1") = "" 'Type is: String.
rw("BenefitValu e1") = 0 'Type is: Decimal.
rw("BenefitDesc 2") = "" 'Type is: String.
rw("BenefitValu e2") = 0 'Type is: Decimal.
rw("BenefitDesc 3") = "" 'Type is: String.
rw("BenefitValu e3") = 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 ("RelatedClient LinkID") =
CurrentRelatedC lientID 'Type is: Long.
dt.Rows(r).Item ("Title") = txtTitleSelf.Te xt 'Type is:
String.
dt.Rows(r).Item ("Forename") = txtForenameSelf .Text 'Type
is: String.
dt.Rows(r).Item ("OtherNames ") = txtOtherNameSel f.Text 'Type
is: String.
dt.Rows(r).Item ("Surname") = txtSurnameSelf. Text 'Type is:
String.
dt.Rows(r).Item ("RelationshipT oPartner") =
txtRelToPartner Self.Text 'Type is: String.
dt.Rows(r).Item ("DOB") = txtDOBYYYYSelf. Text & "-" &
txtDOBMMSelf.Te xt & "-" & txtDOBDDSelf.Te xt 'Type is: Date.
dt.Rows(r).Item ("StateOfHealth ") = txtStateOfHealt hSelf.Text
'Type is: String.
dt.Rows(r).Item ("SmokerYN") = chkSmokerSelf.C hecked 'Type
is: Boolean.
dt.Rows(r).Item ("Notes") = txtNotesSelf.Te xt 'Type is:
String.
dt.Rows(r).Item ("DateCreate d") = 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 ProgErrorHandle r("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.
"ProgErrorHandl er" is a generic routine that just throws up a messagebox
detailing the error that has occurred.
The "Type is" comments are generated by a VBA macro that dumps the field
names and their types into a text file that I can copy into VB.NET to save
time getting all the field names and their types. Be aware that this is
quoting Access variable types (It is an outstanding job to modify that
routine so that it translates Access variables into VB.NET variable types)
which is where I was getting my Long data types confused.


--
Siv
Martley, Worcester, UK.


"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:eKh82Ke8EH A.3504@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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=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]

[Bernie Yaeger]

Re: My Brain Hurts - Help

Hi Siv,

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

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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=green]
>> 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=darkred]
>>> 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. ..
>>>> 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]

[Bernie Yaeger]

Re: My Brain Hurts - Help

Hi Siv,

OK; I'm on to something, I think. The column 'NI Number' has a space. It
should appear as [NI Number] but the commandbuilder is not smart enough to
deal with it. Try changing the table structure to 'NINumber' (no space) and
then rebuild the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment).

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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=green]
>> 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=darkred]
>>> 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. ..
>>>> 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]

[Bernie Yaeger]

Re: My Brain Hurts - Help

No, Siv, you do not have to fill all the fields; ADO .Net will work, in this regard, as ADO does.

But see my last response to you re 'NI Number'.

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:eTzMN8d8EH A.1188@tk2msftn gp13.phx.gbl...
Hi All,

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

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

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

I'm stumped?


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

Re: My Brain Hurts - Help

Hi Siv,

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

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message news:eTzMN8d8EH A.1188@tk2msftn gp13.phx.gbl...
Hi All,

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

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

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

I'm stumped?


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

Re: My Brain Hurts - Help

Bernie,

One thing I noticed was the field "NI Number" which has a space in it and
wondered if this was causing the problem, so I renamed the field in MS
Access ("NINumber") and also changed my code to match. It still errors on
the da.update(dt) line with the same INSERT error?

Aggghh! I hate it when you can't see what it is, and you know that there is
probably a really stupid simple reason?

Does the INSERT statement look wrong to you? in any way, (to me it looks
fine). Is this one of those weird VB/ADO bugs that reports one error but is
actually a completely different thing??

--
Siv
Martley, Worcester, UK.


"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:OVhdzke8EH A.1228@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Siv,
>
> No; the question marks are perfectly normal; they represent replaceable
> variables.
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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=darkred]
>>> 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...
>>>> 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. ..
>>>>> 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]

[Siv]

Re: My Brain Hurts - Help

Bernie,

We're obviously on the same wavelength, I had just posted a message to you
saying that I'd spotted that as well but that it didn't work as this message
appeared!?

When you say "rebuild the commandbuilder" do you mean make the change to the
field and the code and then restart the application, or is there some other
technique that I need to do to get it to build a new copy of the
commandbuilder?


--
Siv
Martley, Worcester, UK.

"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Siv,
>
> OK; I'm on to something, I think. The column 'NI Number' has a space. It
> should appear as [NI Number] but the commandbuilder is not smart enough to
> deal with it. Try changing the table structure to 'NINumber' (no space)
> and then rebuild the commandbuilder (by rebuilding first the oledb
> dataadapter in tne .net environment).
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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=darkred]
>>> 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...
>>>> 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. ..
>>>>> 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]

[Siv]

Re: My Brain Hurts - Help

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).Item Array
{Length=31}
(0): 2 {Integer}
(1): 0 {Integer}
(2): 0 {Integer}
(3): True {Boolean}
(4): "Mr"
(5): "Ray"
(6): ""
(7): "Bellis"
(8): "Husband"
(9): #1/3/2005 11:26:50 PM#
(10): "Excellent"
(11): False {Boolean}
(12): ""
(13): ""
(14): ""
(15): 0.0 {Single}
(16): 0.0 {Single}
(17): #1/3/2005 11:26:51 PM#
(18): 0.0 {Single}
(19): 0.0 {Single}
(20): 0.0 {Single}
(21): 0.0 {Single}
(22): False {Boolean}
(23): ""
(24): 0D
(25): ""
(26): 0D
(27): ""
(28): 0D
(29): #1/3/2005 11:26:53 PM#
(30): "Siv"

As you can see all fields have relevant values - as per your original post I
am using "now" for dates (just in case). I am just about to press F8 to go
onto the da.update(dt) line and see if this works ....
And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
interim as I am awaiting your reply to the "rebuild the Commandbuilder"
question.
So I am not sure if it was closing VB and re-opening that has made it work
or changing the "User" field to "UserName".
I'd be interested in your comments on this as you have stuck with me on it.


--
Siv
Martley, Worcester, UK.

"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Siv,
>
> OK; I'm on to something, I think. The column 'NI Number' has a space. It
> should appear as [NI Number] but the commandbuilder is not smart enough to
> deal with it. Try changing the table structure to 'NINumber' (no space)
> and then rebuild the commandbuilder (by rebuilding first the oledb
> dataadapter in tne .net environment).
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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=darkred]
>>> 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...
>>>> 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. ..
>>>>> 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]

[Bernie Yaeger]

Re: My Brain Hurts - Help

Hi Siv,

Wow - we are really on the same wavelength - I was also thinking the user
might be a reserved word. Now rename that field back to user to see if it
was 'ni number' that was causing the problem!

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:%23FkFyze8 EHA.3260@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> 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).Item Array
> {Length=31}
> (0): 2 {Integer}
> (1): 0 {Integer}
> (2): 0 {Integer}
> (3): True {Boolean}
> (4): "Mr"
> (5): "Ray"
> (6): ""
> (7): "Bellis"
> (8): "Husband"
> (9): #1/3/2005 11:26:50 PM#
> (10): "Excellent"
> (11): False {Boolean}
> (12): ""
> (13): ""
> (14): ""
> (15): 0.0 {Single}
> (16): 0.0 {Single}
> (17): #1/3/2005 11:26:51 PM#
> (18): 0.0 {Single}
> (19): 0.0 {Single}
> (20): 0.0 {Single}
> (21): 0.0 {Single}
> (22): False {Boolean}
> (23): ""
> (24): 0D
> (25): ""
> (26): 0D
> (27): ""
> (28): 0D
> (29): #1/3/2005 11:26:53 PM#
> (30): "Siv"
>
> As you can see all fields have relevant values - as per your original post
> I am using "now" for dates (just in case). I am just about to press F8 to
> go onto the da.update(dt) line and see if this works ....
> And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
> interim as I am awaiting your reply to the "rebuild the Commandbuilder"
> question.
> So I am not sure if it was closing VB and re-opening that has made it work
> or changing the "User" field to "UserName".
> I'd be interested in your comments on this as you have stuck with me on
> it.
>
>
> --
> Siv
> Martley, Worcester, UK.
>
> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
> news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...[color=green]
>> Hi Siv,
>>
>> OK; I'm on to something, I think. The column 'NI Number' has a space.
>> It should appear as [NI Number] but the commandbuilder is not smart
>> enough to deal with it. Try changing the table structure to 'NINumber'
>> (no space) and then rebuild the commandbuilder (by rebuilding first the
>> oledb dataadapter in tne .net environment).
>>
>> Bernie
>>
>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...[color=darkred]
>>> 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...
>>>> 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...
>>>>> 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. ..
>>>>>> 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]

[Siv]

Re: My Brain Hurts - Help

Bernie,
Doing it now!

--
Siv
Martley, Worcester, UK.

"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:eKXgZ2e8EH A.3700@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Siv,
>
> Wow - we are really on the same wavelength - I was also thinking the user
> might be a reserved word. Now rename that field back to user to see if it
> was 'ni number' that was causing the problem!
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:%23FkFyze8 EHA.3260@TK2MSF TNGP14.phx.gbl. ..[color=green]
>> 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).Item Array
>> {Length=31}
>> (0): 2 {Integer}
>> (1): 0 {Integer}
>> (2): 0 {Integer}
>> (3): True {Boolean}
>> (4): "Mr"
>> (5): "Ray"
>> (6): ""
>> (7): "Bellis"
>> (8): "Husband"
>> (9): #1/3/2005 11:26:50 PM#
>> (10): "Excellent"
>> (11): False {Boolean}
>> (12): ""
>> (13): ""
>> (14): ""
>> (15): 0.0 {Single}
>> (16): 0.0 {Single}
>> (17): #1/3/2005 11:26:51 PM#
>> (18): 0.0 {Single}
>> (19): 0.0 {Single}
>> (20): 0.0 {Single}
>> (21): 0.0 {Single}
>> (22): False {Boolean}
>> (23): ""
>> (24): 0D
>> (25): ""
>> (26): 0D
>> (27): ""
>> (28): 0D
>> (29): #1/3/2005 11:26:53 PM#
>> (30): "Siv"
>>
>> As you can see all fields have relevant values - as per your original
>> post I am using "now" for dates (just in case). I am just about to press
>> F8 to go onto the da.update(dt) line and see if this works ....
>> And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
>> interim as I am awaiting your reply to the "rebuild the Commandbuilder"
>> question.
>> So I am not sure if it was closing VB and re-opening that has made it
>> work or changing the "User" field to "UserName".
>> I'd be interested in your comments on this as you have stuck with me on
>> it.
>>
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
>> news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...[color=darkred]
>>> Hi Siv,
>>>
>>> OK; I'm on to something, I think. The column 'NI Number' has a space.
>>> It should appear as [NI Number] but the commandbuilder is not smart
>>> enough to deal with it. Try changing the table structure to 'NINumber'
>>> (no space) and then rebuild the commandbuilder (by rebuilding first the
>>> oledb dataadapter in tne .net environment).
>>>
>>> Bernie
>>>
>>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...
>>>> 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...
>>>>> 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...
>>>>>> 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. ..
>>>>>>> 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]

[Siv]

Re: My Brain Hurts - Help

Bernie,

Changed the program back so that the UserName field is now "User" again.
Also exited VB.NET and went back in as last time (just in case that is doing
something), also deleted the record in Access that was successfully created
so that I am at exactly the same point as I was before except I have
"NINumber" and "User" rather than "NINumber" and "UserName". Guess what, it
fails again, so a field called "User" is not allowed!!!
Is this a bug or what??
I certainly haven't spotted anything in the help files about avoiding any
field names??

Where's Herfried!?
BUG REPORT!!

Thanks again for your help, it always helps to have someone to bounce ideas
off. It's a bit difficult when you work at home and it's late at night
(23:57 here in UK at the moment).
Thank God for this newsgroup and helpful VB.Neters on hand to help out.

I don't know if you have already answered my question about "then rebuild
the commandbuilder (by rebuilding first the oledb dataadapter
in tne .net environment" question but if you haven't spotted it I would be
keen to understand what you meant there as I may be missing a trick when
re-running VB.NET after making changes like this?


--
Siv
Martley, Worcester, UK.



"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:eKXgZ2e8EH A.3700@tk2msftn gp13.phx.gbl...[color=blue]
> Hi Siv,
>
> Wow - we are really on the same wavelength - I was also thinking the user
> might be a reserved word. Now rename that field back to user to see if it
> was 'ni number' that was causing the problem!
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:%23FkFyze8 EHA.3260@TK2MSF TNGP14.phx.gbl. ..[color=green]
>> 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).Item Array
>> {Length=31}
>> (0): 2 {Integer}
>> (1): 0 {Integer}
>> (2): 0 {Integer}
>> (3): True {Boolean}
>> (4): "Mr"
>> (5): "Ray"
>> (6): ""
>> (7): "Bellis"
>> (8): "Husband"
>> (9): #1/3/2005 11:26:50 PM#
>> (10): "Excellent"
>> (11): False {Boolean}
>> (12): ""
>> (13): ""
>> (14): ""
>> (15): 0.0 {Single}
>> (16): 0.0 {Single}
>> (17): #1/3/2005 11:26:51 PM#
>> (18): 0.0 {Single}
>> (19): 0.0 {Single}
>> (20): 0.0 {Single}
>> (21): 0.0 {Single}
>> (22): False {Boolean}
>> (23): ""
>> (24): 0D
>> (25): ""
>> (26): 0D
>> (27): ""
>> (28): 0D
>> (29): #1/3/2005 11:26:53 PM#
>> (30): "Siv"
>>
>> As you can see all fields have relevant values - as per your original
>> post I am using "now" for dates (just in case). I am just about to press
>> F8 to go onto the da.update(dt) line and see if this works ....
>> And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
>> interim as I am awaiting your reply to the "rebuild the Commandbuilder"
>> question.
>> So I am not sure if it was closing VB and re-opening that has made it
>> work or changing the "User" field to "UserName".
>> I'd be interested in your comments on this as you have stuck with me on
>> it.
>>
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
>> news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...[color=darkred]
>>> Hi Siv,
>>>
>>> OK; I'm on to something, I think. The column 'NI Number' has a space.
>>> It should appear as [NI Number] but the commandbuilder is not smart
>>> enough to deal with it. Try changing the table structure to 'NINumber'
>>> (no space) and then rebuild the commandbuilder (by rebuilding first the
>>> oledb dataadapter in tne .net environment).
>>>
>>> Bernie
>>>
>>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...
>>>> 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...
>>>>> 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...
>>>>>> 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. ..
>>>>>>> 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]

[Bernie Yaeger]

Re: My Brain Hurts - Help

Hi Siv,

Re 'rebuilding the commandbuilder' I did answer - I was wrong about that, as
you are not building a dataadapter using the wizard, but rather by code - so
there's no 'rebuilding' to do.

OK; we now know that it's 'user' that's the culprit - it is evidently a
reserved word. UPDATE: In fact, I just 'googled' it, and User is indeed an
MS Access reserved word.

Re these ng's: couldn't agree more. People have helped me out more times
than I can count, especially Cor and Herfried and Ken Tucker, and many, many
others.

Glad to be of help to you.

Bernie

"Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
news:eB4g7Bf8EH A.3336@TK2MSFTN GP11.phx.gbl...[color=blue]
> Bernie,
>
> Changed the program back so that the UserName field is now "User" again.
> Also exited VB.NET and went back in as last time (just in case that is
> doing something), also deleted the record in Access that was successfully
> created so that I am at exactly the same point as I was before except I
> have "NINumber" and "User" rather than "NINumber" and "UserName". Guess
> what, it fails again, so a field called "User" is not allowed!!!
> Is this a bug or what??
> I certainly haven't spotted anything in the help files about avoiding any
> field names??
>
> Where's Herfried!?
> BUG REPORT!!
>
> Thanks again for your help, it always helps to have someone to bounce
> ideas off. It's a bit difficult when you work at home and it's late at
> night (23:57 here in UK at the moment).
> Thank God for this newsgroup and helpful VB.Neters on hand to help out.
>
> I don't know if you have already answered my question about "then rebuild
> the commandbuilder (by rebuilding first the oledb dataadapter
> in tne .net environment" question but if you haven't spotted it I would be
> keen to understand what you meant there as I may be missing a trick when
> re-running VB.NET after making changes like this?
>
>
> --
> Siv
> Martley, Worcester, UK.
>
>
>
> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
> news:eKXgZ2e8EH A.3700@tk2msftn gp13.phx.gbl...[color=green]
>> Hi Siv,
>>
>> Wow - we are really on the same wavelength - I was also thinking the user
>> might be a reserved word. Now rename that field back to user to see if
>> it was 'ni number' that was causing the problem!
>>
>> Bernie
>>
>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>> news:%23FkFyze8 EHA.3260@TK2MSF TNGP14.phx.gbl. ..[color=darkred]
>>> 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).Item Array
>>> {Length=31}
>>> (0): 2 {Integer}
>>> (1): 0 {Integer}
>>> (2): 0 {Integer}
>>> (3): True {Boolean}
>>> (4): "Mr"
>>> (5): "Ray"
>>> (6): ""
>>> (7): "Bellis"
>>> (8): "Husband"
>>> (9): #1/3/2005 11:26:50 PM#
>>> (10): "Excellent"
>>> (11): False {Boolean}
>>> (12): ""
>>> (13): ""
>>> (14): ""
>>> (15): 0.0 {Single}
>>> (16): 0.0 {Single}
>>> (17): #1/3/2005 11:26:51 PM#
>>> (18): 0.0 {Single}
>>> (19): 0.0 {Single}
>>> (20): 0.0 {Single}
>>> (21): 0.0 {Single}
>>> (22): False {Boolean}
>>> (23): ""
>>> (24): 0D
>>> (25): ""
>>> (26): 0D
>>> (27): ""
>>> (28): 0D
>>> (29): #1/3/2005 11:26:53 PM#
>>> (30): "Siv"
>>>
>>> As you can see all fields have relevant values - as per your original
>>> post I am using "now" for dates (just in case). I am just about to
>>> press F8 to go onto the da.update(dt) line and see if this works ....
>>> And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
>>> interim as I am awaiting your reply to the "rebuild the Commandbuilder"
>>> question.
>>> So I am not sure if it was closing VB and re-opening that has made it
>>> work or changing the "User" field to "UserName".
>>> I'd be interested in your comments on this as you have stuck with me on
>>> it.
>>>
>>>
>>> --
>>> Siv
>>> Martley, Worcester, UK.
>>>
>>> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
>>> news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...
>>>> Hi Siv,
>>>>
>>>> OK; I'm on to something, I think. The column 'NI Number' has a space.
>>>> It should appear as [NI Number] but the commandbuilder is not smart
>>>> enough to deal with it. Try changing the table structure to 'NINumber'
>>>> (no space) and then rebuild the commandbuilder (by rebuilding first the
>>>> oledb dataadapter in tne .net environment).
>>>>
>>>> Bernie
>>>>
>>>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>>> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...
>>>>> 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...
>>>>>> 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...
>>>>>>> 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. ..
>>>>>>>> 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]

[Siv]

Re: My Brain Hurts - Help

Bernie,

Thanks, I hadn't twigged you had answered my question, but now I understand.
Thanks again. I can sleep now thanks to you!

--
Siv
Martley, Worcester, UK.

"Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
news:emhDlIf8EH A.3820@TK2MSFTN GP11.phx.gbl...[color=blue]
> Hi Siv,
>
> Re 'rebuilding the commandbuilder' I did answer - I was wrong about that,
> as you are not building a dataadapter using the wizard, but rather by
> code - so there's no 'rebuilding' to do.
>
> OK; we now know that it's 'user' that's the culprit - it is evidently a
> reserved word. UPDATE: In fact, I just 'googled' it, and User is indeed
> an MS Access reserved word.
>
> Re these ng's: couldn't agree more. People have helped me out more times
> than I can count, especially Cor and Herfried and Ken Tucker, and many,
> many others.
>
> Glad to be of help to you.
>
> Bernie
>
> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
> news:eB4g7Bf8EH A.3336@TK2MSFTN GP11.phx.gbl...[color=green]
>> Bernie,
>>
>> Changed the program back so that the UserName field is now "User" again.
>> Also exited VB.NET and went back in as last time (just in case that is
>> doing something), also deleted the record in Access that was successfully
>> created so that I am at exactly the same point as I was before except I
>> have "NINumber" and "User" rather than "NINumber" and "UserName". Guess
>> what, it fails again, so a field called "User" is not allowed!!!
>> Is this a bug or what??
>> I certainly haven't spotted anything in the help files about avoiding any
>> field names??
>>
>> Where's Herfried!?
>> BUG REPORT!!
>>
>> Thanks again for your help, it always helps to have someone to bounce
>> ideas off. It's a bit difficult when you work at home and it's late at
>> night (23:57 here in UK at the moment).
>> Thank God for this newsgroup and helpful VB.Neters on hand to help out.
>>
>> I don't know if you have already answered my question about "then rebuild
>> the commandbuilder (by rebuilding first the oledb dataadapter
>> in tne .net environment" question but if you haven't spotted it I would
>> be keen to understand what you meant there as I may be missing a trick
>> when re-running VB.NET after making changes like this?
>>
>>
>> --
>> Siv
>> Martley, Worcester, UK.
>>
>>
>>
>> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
>> news:eKXgZ2e8EH A.3700@tk2msftn gp13.phx.gbl...[color=darkred]
>>> Hi Siv,
>>>
>>> Wow - we are really on the same wavelength - I was also thinking the
>>> user might be a reserved word. Now rename that field back to user to
>>> see if it was 'ni number' that was causing the problem!
>>>
>>> Bernie
>>>
>>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>> news:%23FkFyze8 EHA.3260@TK2MSF TNGP14.phx.gbl. ..
>>>> Bernie,
>>>> I modified the fields as per your previous post and I also changed the
>>>> "User" field just in case that is a reserved word or something and just
>>>> before processing the line printed out the ItemArray of the data that I
>>>> am trying to update which is as follows:
>>>>
>>>> ? dt.Rows(2).Item Array
>>>> {Length=31}
>>>> (0): 2 {Integer}
>>>> (1): 0 {Integer}
>>>> (2): 0 {Integer}
>>>> (3): True {Boolean}
>>>> (4): "Mr"
>>>> (5): "Ray"
>>>> (6): ""
>>>> (7): "Bellis"
>>>> (8): "Husband"
>>>> (9): #1/3/2005 11:26:50 PM#
>>>> (10): "Excellent"
>>>> (11): False {Boolean}
>>>> (12): ""
>>>> (13): ""
>>>> (14): ""
>>>> (15): 0.0 {Single}
>>>> (16): 0.0 {Single}
>>>> (17): #1/3/2005 11:26:51 PM#
>>>> (18): 0.0 {Single}
>>>> (19): 0.0 {Single}
>>>> (20): 0.0 {Single}
>>>> (21): 0.0 {Single}
>>>> (22): False {Boolean}
>>>> (23): ""
>>>> (24): 0D
>>>> (25): ""
>>>> (26): 0D
>>>> (27): ""
>>>> (28): 0D
>>>> (29): #1/3/2005 11:26:53 PM#
>>>> (30): "Siv"
>>>>
>>>> As you can see all fields have relevant values - as per your original
>>>> post I am using "now" for dates (just in case). I am just about to
>>>> press F8 to go onto the da.update(dt) line and see if this works ....
>>>> And guess what it did!!!! Wahey! I closed and reopened VB.NET in the
>>>> interim as I am awaiting your reply to the "rebuild the Commandbuilder"
>>>> question.
>>>> So I am not sure if it was closing VB and re-opening that has made it
>>>> work or changing the "User" field to "UserName".
>>>> I'd be interested in your comments on this as you have stuck with me on
>>>> it.
>>>>
>>>>
>>>> --
>>>> Siv
>>>> Martley, Worcester, UK.
>>>>
>>>> "Bernie Yaeger" <berniey@cherwe llinc.com> wrote in message
>>>> news:OOdwone8EH A.1228@tk2msftn gp13.phx.gbl...
>>>>> Hi Siv,
>>>>>
>>>>> OK; I'm on to something, I think. The column 'NI Number' has a space.
>>>>> It should appear as [NI Number] but the commandbuilder is not smart
>>>>> enough to deal with it. Try changing the table structure to
>>>>> 'NINumber' (no space) and then rebuild the commandbuilder (by
>>>>> rebuilding first the oledb dataadapter in tne .net environment).
>>>>>
>>>>> Bernie
>>>>>
>>>>> "Siv" <msnewsgroups@r emoveme.sivill. com> wrote in message
>>>>> news:OexgeTe8EH A.2156@TK2MSFTN GP10.phx.gbl...
>>>>>> 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...
>>>>>>> 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...
>>>>>>>> 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. ..
>>>>>>>>> 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]

[Ron Allen]

Re: My Brain Hurts - Help

Siv,
If you are going to use a CommandBuilder with any spaces or possibly
reserved words in column names you need to set the QuotePrefix and
QuoteSuffix properties of the CommandBuilder. Usually to '[' and ']'
respectively.

Ron Allen
"Siv" <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.