Re: spaces and special characters in field names, brackets don't work

They still work. The issue is, that the column names in the dataset, get
the brackets stripped off.

Set QuotePrefix and QuoteSuffix on the command builder to [ and ].

"Yall" <Yall@discussions.microsoft.com> wrote in message
news:FEDCA9A9-FEFC-4A23-BF8E-A1C082A2C7AD@microsoft.com...
> When I had non standard field names I used to be able to do this in VB6
and
> Access97. Now using Access2002 and VB.net. I inherited this data base and
> have to use the field names with special characters and spaces.
>
> Now in VB.net it gives me and error.
>
> If I don't use the fields that have brackets, things work OK, if I use *
> instead of naming fields it fails in the same way because of the space and
> the # in the name of the two fields.
>
> The brackets [ ] used to work for a field that had a space or special
> character in it. Do you know how to do this in VB.net and Access2002?
>
> strSQL = "SELECT StreamID, SolidWasteCode, Approved, "
> strSQL = strSQL & "Description, SolidWasteDescription,
Category,
> "
> strSQL = strSQL & "[STPStream#], [EPA Code] "
> strSQL = strSQL & "FROM WasteStream;"
>
> Dim da1 As New OleDbDataAdapter
> Dim ds1 As New DataSet
>
> da1.SelectCommand = New OleDbCommand(strSQL, OleDbConnection1)
>
> Dim NewWasteStream As OleDbCommandBuilder = New
> OleDbCommandBuilder(da1)
> da1.Fill(ds1)
>
> Dim dr1 As DataRow = ds1.Tables(0).NewRow
>
> If Not ds1 Is Nothing Then
>
> dr1("StreamID") = Me.txtStreamID.Text & ""
> dr1("STPStream#") = Me.txtSTPStream.Text & ""
> dr1("EPA Code") = Me.txtEPA_Code.Text & ""
> ds1.Tables(0).Rows.Add(dr1)
>
> da1.Update(ds1)
>
> End If
>

Have you ever set properties on an object before? You must have, otherwise
you wouldn't have been able to write this code... so I'm very confused about
your question.

"Yall" <Yall@discussions.microsoft.com> wrote in message
news:09ADCD65-DE20-4748-87BA-83B4BC8C8561@microsoft.com...
> How do I "Set QuotePrefix and QuoteSuffix on the command builder to [
and ]."?
>
> "Marina" wrote:
>
> > They still work. The issue is, that the column names in the dataset,
get
> > the brackets stripped off.
> >
> > Set QuotePrefix and QuoteSuffix on the command builder to [ and ].
> >
> > "Yall" <Yall@discussions.microsoft.com> wrote in message
> > news:FEDCA9A9-FEFC-4A23-BF8E-A1C082A2C7AD@microsoft.com...
> > > When I had non standard field names I used to be able to do this in
VB6
> > and
> > > Access97. Now using Access2002 and VB.net. I inherited this data base
and
> > > have to use the field names with special characters and spaces.
> > >
> > > Now in VB.net it gives me and error.
> > >
> > > If I don't use the fields that have brackets, things work OK, if I use
*
> > > instead of naming fields it fails in the same way because of the space
and
> > > the # in the name of the two fields.
> > >
> > > The brackets [ ] used to work for a field that had a space or special
> > > character in it. Do you know how to do this in VB.net and Access2002?
> > >
> > > strSQL = "SELECT StreamID, SolidWasteCode, Approved, "
> > > strSQL = strSQL & "Description, SolidWasteDescription,
> > Category,
> > > "
> > > strSQL = strSQL & "[STPStream#], [EPA Code] "
> > > strSQL = strSQL & "FROM WasteStream;"
> > >
> > > Dim da1 As New OleDbDataAdapter
> > > Dim ds1 As New DataSet
> > >
> > > da1.SelectCommand = New OleDbCommand(strSQL,
OleDbConnection1)
> > >
> > > Dim NewWasteStream As OleDbCommandBuilder = New
> > > OleDbCommandBuilder(da1)
> > > da1.Fill(ds1)
> > >
> > > Dim dr1 As DataRow = ds1.Tables(0).NewRow
> > >
> > > If Not ds1 Is Nothing Then
> > >
> > > dr1("StreamID") = Me.txtStreamID.Text & ""
> > > dr1("STPStream#") = Me.txtSTPStream.Text & ""
> > > dr1("EPA Code") = Me.txtEPA_Code.Text & ""
> > > ds1.Tables(0).Rows.Add(dr1)
> > >
> > > da1.Update(ds1)
> > >
> > > End If
> > >
> >
> >
> >

Yes, that would be it.

"Yall" <Yall@discussions.microsoft.com> wrote in message
news:2A89A7C9-66F9-444F-B3F3-D4F7F43091CC@microsoft.com...
> I am at a remote site and not near my project's computer right now, sorry.
I
> have really been struggling with this and am tired too LOL
>
> I guess it would be something like this?
>
> NewWasteStream.QuotePrefix = "["
>
> After I built the
>
> Dim NewWasteStream As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
>
> in the below code
>
> "Marina" wrote:
>
> > Have you ever set properties on an object before? You must have,
otherwise
> > you wouldn't have been able to write this code... so I'm very confused
about
> > your question.
> >
> > "Yall" <Yall@discussions.microsoft.com> wrote in message
> > news:09ADCD65-DE20-4748-87BA-83B4BC8C8561@microsoft.com...
> > > How do I "Set QuotePrefix and QuoteSuffix on the command builder to [
> > and ]."?
> > >
> > > "Marina" wrote:
> > >
> > > > They still work. The issue is, that the column names in the
dataset,
> > get
> > > > the brackets stripped off.
> > > >
> > > > Set QuotePrefix and QuoteSuffix on the command builder to [ and ].
> > > >
> > > > "Yall" <Yall@discussions.microsoft.com> wrote in message
> > > > news:FEDCA9A9-FEFC-4A23-BF8E-A1C082A2C7AD@microsoft.com...
> > > > > When I had non standard field names I used to be able to do this
in
> > VB6
> > > > and
> > > > > Access97. Now using Access2002 and VB.net. I inherited this data
base
> > and
> > > > > have to use the field names with special characters and spaces.
> > > > >
> > > > > Now in VB.net it gives me and error.
> > > > >
> > > > > If I don't use the fields that have brackets, things work OK, if I
use
> > *
> > > > > instead of naming fields it fails in the same way because of the
space
> > and
> > > > > the # in the name of the two fields.
> > > > >
> > > > > The brackets [ ] used to work for a field that had a space or
special
> > > > > character in it. Do you know how to do this in VB.net and
Access2002?
> > > > >
> > > > > strSQL = "SELECT StreamID, SolidWasteCode, Approved, "
> > > > > strSQL = strSQL & "Description, SolidWasteDescription,
> > > > Category,
> > > > > "
> > > > > strSQL = strSQL & "[STPStream#], [EPA Code] "
> > > > > strSQL = strSQL & "FROM WasteStream;"
> > > > >
> > > > > Dim da1 As New OleDbDataAdapter
> > > > > Dim ds1 As New DataSet
> > > > >
> > > > > da1.SelectCommand = New OleDbCommand(strSQL,
> > OleDbConnection1)
> > > > >
> > > > > Dim NewWasteStream As OleDbCommandBuilder = New
> > > > > OleDbCommandBuilder(da1)
> > > > > da1.Fill(ds1)
> > > > >
> > > > > Dim dr1 As DataRow = ds1.Tables(0).NewRow
> > > > >
> > > > > If Not ds1 Is Nothing Then
> > > > >
> > > > > dr1("StreamID") = Me.txtStreamID.Text & ""
> > > > > dr1("STPStream#") = Me.txtSTPStream.Text & ""
> > > > > dr1("EPA Code") = Me.txtEPA_Code.Text & ""
> > > > > ds1.Tables(0).Rows.Add(dr1)
> > > > >
> > > > > da1.Update(ds1)
> > > > >
> > > > > End If
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Do yourself (and those that have to inherit this code) a favor. Get rid of
the imbedded spaces in your object names. You might have fixed this problem,
but you'll constantly be having problems like this crop up forever you
don't.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Yall" <Yall@discussions.microsoft.com> wrote in message
news:6A4E5364-C3A5-4919-8585-E1482D3E2263@microsoft.com...
> Thanks you so much Marina, I really struggled for two weeks with this. I
> really appreciate your prompt and helpful response.
>
> It worked like a champ!
>
> ::HUGZ::
>
> "Marina" wrote:
>
>> Yes, that would be it.
>>
>> "Yall" <Yall@discussions.microsoft.com> wrote in message
>> news:2A89A7C9-66F9-444F-B3F3-D4F7F43091CC@microsoft.com...
>> > I am at a remote site and not near my project's computer right now,
>> > sorry.
>> I
>> > have really been struggling with this and am tired too LOL
>> >
>> > I guess it would be something like this?
>> >
>> > NewWasteStream.QuotePrefix = "["
>> >
>> > After I built the
>> >
>> > Dim NewWasteStream As OleDbCommandBuilder = New
>> > OleDbCommandBuilder(da1)
>> >
>> > in the below code
>> >
>> > "Marina" wrote:
>> >
>> > > Have you ever set properties on an object before? You must have,
>> otherwise
>> > > you wouldn't have been able to write this code... so I'm very
>> > > confused
>> about
>> > > your question.
>> > >
>> > > "Yall" <Yall@discussions.microsoft.com> wrote in message
>> > > news:09ADCD65-DE20-4748-87BA-83B4BC8C8561@microsoft.com...
>> > > > How do I "Set QuotePrefix and QuoteSuffix on the command builder to
>> > > > [
>> > > and ]."?
>> > > >
>> > > > "Marina" wrote:
>> > > >
>> > > > > They still work. The issue is, that the column names in the
>> dataset,
>> > > get
>> > > > > the brackets stripped off.
>> > > > >
>> > > > > Set QuotePrefix and QuoteSuffix on the command builder to [
>> > > > > and ].
>> > > > >
>> > > > > "Yall" <Yall@discussions.microsoft.com> wrote in message
>> > > > > news:FEDCA9A9-FEFC-4A23-BF8E-A1C082A2C7AD@microsoft.com...
>> > > > > > When I had non standard field names I used to be able to do
>> > > > > > this
>> in
>> > > VB6
>> > > > > and
>> > > > > > Access97. Now using Access2002 and VB.net. I inherited this
>> > > > > > data
>> base
>> > > and
>> > > > > > have to use the field names with special characters and spaces.
>> > > > > >
>> > > > > > Now in VB.net it gives me and error.
>> > > > > >
>> > > > > > If I don't use the fields that have brackets, things work OK,
>> > > > > > if I
>> use
>> > > *
>> > > > > > instead of naming fields it fails in the same way because of
>> > > > > > the
>> space
>> > > and
>> > > > > > the # in the name of the two fields.
>> > > > > >
>> > > > > > The brackets [ ] used to work for a field that had a space or
>> special
>> > > > > > character in it. Do you know how to do this in VB.net and
>> Access2002?
>> > > > > >
>> > > > > > strSQL = "SELECT StreamID, SolidWasteCode,
>> > > > > > Approved, "
>> > > > > > strSQL = strSQL & "Description,
>> > > > > > SolidWasteDescription,
>> > > > > Category,
>> > > > > > "
>> > > > > > strSQL = strSQL & "[STPStream#], [EPA Code] "
>> > > > > > strSQL = strSQL & "FROM WasteStream;"
>> > > > > >
>> > > > > > Dim da1 As New OleDbDataAdapter
>> > > > > > Dim ds1 As New DataSet
>> > > > > >
>> > > > > > da1.SelectCommand = New OleDbCommand(strSQL,
>> > > OleDbConnection1)
>> > > > > >
>> > > > > > Dim NewWasteStream As OleDbCommandBuilder = New
>> > > > > > OleDbCommandBuilder(da1)
>> > > > > > da1.Fill(ds1)
>> > > > > >
>> > > > > > Dim dr1 As DataRow = ds1.Tables(0).NewRow
>> > > > > >
>> > > > > > If Not ds1 Is Nothing Then
>> > > > > >
>> > > > > > dr1("StreamID") = Me.txtStreamID.Text & ""
>> > > > > > dr1("STPStream#") = Me.txtSTPStream.Text & ""
>> > > > > > dr1("EPA Code") = Me.txtEPA_Code.Text & ""
>> > > > > > ds1.Tables(0).Rows.Add(dr1)
>> > > > > >
>> > > > > > da1.Update(ds1)
>> > > > > >
>> > > > > > End If
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>> > >
>> > >
>>
>>
>>

I can't, I inherited this database myself. There are other programs outside
the organization that use it. Don't know what all it would break.

I choose to conform to my customer's needs, not mine.

I am aware of the problems that you talk about, just can't do anything about
it but serve my customer the best way I can.

"William (Bill) Vaughn" wrote:

> Do yourself (and those that have to inherit this code) a favor. Get rid of
> the imbedded spaces in your object names. You might have fixed this problem,
> but you'll constantly be having problems like this crop up forever you
> don't.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "Yall" <Yall@discussions.microsoft.com> wrote in message
> news:6A4E5364-C3A5-4919-8585-E1482D3E2263@microsoft.com...
> > Thanks you so much Marina, I really struggled for two weeks with this. I
> > really appreciate your prompt and helpful response.
> >
> > It worked like a champ!
> >
> > ::HUGZ::
> >
> > "Marina" wrote:
> >
> >> Yes, that would be it.
> >>
> >> "Yall" <Yall@discussions.microsoft.com> wrote in message
> >> news:2A89A7C9-66F9-444F-B3F3-D4F7F43091CC@microsoft.com...
> >> > I am at a remote site and not near my project's computer right now,
> >> > sorry.
> >> I
> >> > have really been struggling with this and am tired too LOL
> >> >
> >> > I guess it would be something like this?
> >> >
> >> > NewWasteStream.QuotePrefix = "["
> >> >
> >> > After I built the
> >> >
> >> > Dim NewWasteStream As OleDbCommandBuilder = New
> >> > OleDbCommandBuilder(da1)
> >> >
> >> > in the below code
> >> >
> >> > "Marina" wrote:
> >> >
> >> > > Have you ever set properties on an object before? You must have,
> >> otherwise
> >> > > you wouldn't have been able to write this code... so I'm very
> >> > > confused
> >> about
> >> > > your question.
> >> > >
> >> > > "Yall" <Yall@discussions.microsoft.com> wrote in message
> >> > > news:09ADCD65-DE20-4748-87BA-83B4BC8C8561@microsoft.com...
> >> > > > How do I "Set QuotePrefix and QuoteSuffix on the command builder to
> >> > > > [
> >> > > and ]."?
> >> > > >
> >> > > > "Marina" wrote:
> >> > > >
> >> > > > > They still work. The issue is, that the column names in the
> >> dataset,
> >> > > get
> >> > > > > the brackets stripped off.
> >> > > > >
> >> > > > > Set QuotePrefix and QuoteSuffix on the command builder to [
> >> > > > > and ].
> >> > > > >
> >> > > > > "Yall" <Yall@discussions.microsoft.com> wrote in message
> >> > > > > news:FEDCA9A9-FEFC-4A23-BF8E-A1C082A2C7AD@microsoft.com...
> >> > > > > > When I had non standard field names I used to be able to do
> >> > > > > > this
> >> in
> >> > > VB6
> >> > > > > and
> >> > > > > > Access97. Now using Access2002 and VB.net. I inherited this
> >> > > > > > data
> >> base
> >> > > and
> >> > > > > > have to use the field names with special characters and spaces.
> >> > > > > >
> >> > > > > > Now in VB.net it gives me and error.
> >> > > > > >
> >> > > > > > If I don't use the fields that have brackets, things work OK,
> >> > > > > > if I
> >> use
> >> > > *
> >> > > > > > instead of naming fields it fails in the same way because of
> >> > > > > > the
> >> space
> >> > > and
> >> > > > > > the # in the name of the two fields.
> >> > > > > >
> >> > > > > > The brackets [ ] used to work for a field that had a space or
> >> special
> >> > > > > > character in it. Do you know how to do this in VB.net and
> >> Access2002?
> >> > > > > >
> >> > > > > > strSQL = "SELECT StreamID, SolidWasteCode,
> >> > > > > > Approved, "
> >> > > > > > strSQL = strSQL & "Description,
> >> > > > > > SolidWasteDescription,
> >> > > > > Category,
> >> > > > > > "
> >> > > > > > strSQL = strSQL & "[STPStream#], [EPA Code] "
> >> > > > > > strSQL = strSQL & "FROM WasteStream;"
> >> > > > > >
> >> > > > > > Dim da1 As New OleDbDataAdapter
> >> > > > > > Dim ds1 As New DataSet
> >> > > > > >
> >> > > > > > da1.SelectCommand = New OleDbCommand(strSQL,
> >> > > OleDbConnection1)
> >> > > > > >
> >> > > > > > Dim NewWasteStream As OleDbCommandBuilder = New
> >> > > > > > OleDbCommandBuilder(da1)
> >> > > > > > da1.Fill(ds1)
> >> > > > > >
> >> > > > > > Dim dr1 As DataRow = ds1.Tables(0).NewRow
> >> > > > > >
> >> > > > > > If Not ds1 Is Nothing Then
> >> > > > > >
> >> > > > > > dr1("StreamID") = Me.txtStreamID.Text & ""
> >> > > > > > dr1("STPStream#") = Me.txtSTPStream.Text & ""
> >> > > > > > dr1("EPA Code") = Me.txtEPA_Code.Text & ""
> >> > > > > > ds1.Tables(0).Rows.Add(dr1)
> >> > > > > >
> >> > > > > > da1.Update(ds1)
> >> > > > > >
> >> > > > > > End If
> >> > > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>