Execute the insert command with boolean

**Ken Cox [Microsoft MVP]** · Nov 18 '05, 08:28 PM

Re: Execute the insert command with boolean

It looks like the Boolean field is being inserted as a string. You may need
to drop the apostrophe on either side and use something like

contentavailabl e.Checked.ToStr ing

"nicholas" <murmurait1@hot mail.com> wrote in message
news:uJEPEALsEH A.3336@tk2msftn gp13.phx.gbl...[color=blue]
> my insert works, but without the boolean value contentavailabl e.
> When I try to insert this boolean I get a data mismatch error.
> this "contentavailab le" is an asp.net checkbox
>
> I use a ms access dbase.
>
> any ideas? THX
>
> here's the code:
>
> commInsert.Exec ute("INSERT INTO tbl_contents (contenttypeID, contentEN,
> contentFR, contentNL, contentDU, contentdescript ionEN,
> contentdescript ionFR,
> contentdescript ionNL, contentdescript ionDU, contentavailabl e,
> contentorder)
> VALUES('" & ctype(contentty peID.selectedit em.value,intege r) & "','" &
> contentEN.text & "','" & contentFR.text & "','" & contentNL.text & "','" &
> contentDU.text & "','" & contentdescript ionEN.text & "','" &
> contentdescript ionFR.text & "','" & contentdescript ionNL.text & "','" &
> contentdescript ionDU.text & "','" & contentavailabl e.checked & "','" &
> contentorder.te xt & "');")
>
>[/color]

**Davide Vernole [MVP]** · Nov 18 '05, 08:28 PM

Re: Execute the insert command with boolean

Ken Cox [Microsoft MVP] <BANSPAMken_cox @sympatico.ca> typed:[color=blue]
> It looks like the Boolean field is being inserted as a string. You
> may need to drop the apostrophe on either side and use something like
>
> contentavailabl e.Checked.ToStr ing
>[/color]

Just to remember you that it's better to don't use this kind of SQL
Statement in your code. Your application risk SQL Injection attacks (see
this useful article:
http://msdn.microsoft.com/msdnmag/is.../SQLInjection/)

Don't forget to review your code to avoid SQL Injection ;-)

--
Davide Vernole
MVP ASP/ASP.NET
Microsoft Certified Solution Developer

**nicholas** · Nov 18 '05, 08:29 PM

Re: Execute the insert command with boolean

indeed
THX

"Ken Cox [Microsoft MVP]" <BANSPAMken_cox @sympatico.ca> wrote in message
news:u8BFOVLsEH A.2612@TK2MSFTN GP10.phx.gbl...[color=blue]
> It looks like the Boolean field is being inserted as a string. You may[/color]
need[color=blue]
> to drop the apostrophe on either side and use something like
>
> contentavailabl e.Checked.ToStr ing
>
> "nicholas" <murmurait1@hot mail.com> wrote in message
> news:uJEPEALsEH A.3336@tk2msftn gp13.phx.gbl...[color=green]
> > my insert works, but without the boolean value contentavailabl e.
> > When I try to insert this boolean I get a data mismatch error.
> > this "contentavailab le" is an asp.net checkbox
> >
> > I use a ms access dbase.
> >
> > any ideas? THX
> >
> > here's the code:
> >
> > commInsert.Exec ute("INSERT INTO tbl_contents (contenttypeID, contentEN,
> > contentFR, contentNL, contentDU, contentdescript ionEN,
> > contentdescript ionFR,
> > contentdescript ionNL, contentdescript ionDU, contentavailabl e,
> > contentorder)
> > VALUES('" & ctype(contentty peID.selectedit em.value,intege r) & "','" &
> > contentEN.text & "','" & contentFR.text & "','" & contentNL.text & "','"[/color][/color]
&[color=blue][color=green]
> > contentDU.text & "','" & contentdescript ionEN.text & "','" &
> > contentdescript ionFR.text & "','" & contentdescript ionNL.text & "','" &
> > contentdescript ionDU.text & "','" & contentavailabl e.checked & "','" &
> > contentorder.te xt & "');")
> >
> >[/color]
>[/color]

**nicholas** · Nov 18 '05, 08:29 PM

Re: Execute the insert command with boolean

Yes, you're right.
I didn't know about this.

Could you help me change my code in a good way?
I know how to do it when I work with an SQL-server, but as for this
application I use a ms Access (mdb) database and as I want to retrieve the
ID of the newly created record, this is the only code combination that
works. (and I can assure you, I tried a lot of code combinations !!)

So If you could help me implement this code, but with @parameters, would be
great !

THX a lot,
Frederic

Here's my code executed on the on_click event of an asp.net button

######## code start #######

'define where the connectionstrin g is here:
Dim MyConnectionStr ing as String =
ConfigurationSe ttings.AppSetti ngs("Connection String")

dim commInsert = Server.CreateOb ject("ADODB.Con nection")
dim rsnewID = Server.CreateOb ject("ADODB.rec ordset")
commInsert.Open (MyConnectionSt ring) ' Replace with your OLE DB
connection string.
commInsert.Exec ute("INSERT INTO tbl_contents (contenttypeID, contentEN,
contentFR, contentNL, contentDU, contentdescript ionEN, contentdescript ionFR,
contentdescript ionNL, contentdescript ionDU, contentavailabl e, contentorder)
VALUES('" & ctype(contentty peID.selectedit em.value,intege r) & "','" &
contentEN.text & "','" & contentFR.text & "','" & contentNL.text & "','" &
contentDU.text & "','" & contentdescript ionEN.text & "','" &
contentdescript ionFR.text & "','" & contentdescript ionNL.text & "','" &
contentdescript ionDU.text & "'," & contentavailabl e.checked & ",'" &
contentorder.te xt & "');")

rsNewID = commInsert.Exec ute("SELECT @@IDENTITY ")
dim intNewID = rsNewID(0).valu e
rsNewID.Close
rsNewID = Nothing
commInsert.Clos e
commInsert = Nothing

response.write( intnewid)

######### end of code ########
I also had to add this: AspCompat="true " to the page directive to make it
work.

"Davide Vernole [MVP]" <davide@online. knodev.com> wrote in message
news:OXwOUZLsEH A.1520@TK2MSFTN GP11.phx.gbl...[color=blue]
> Ken Cox [Microsoft MVP] <BANSPAMken_cox @sympatico.ca> typed:[color=green]
> > It looks like the Boolean field is being inserted as a string. You
> > may need to drop the apostrophe on either side and use something like
> >
> > contentavailabl e.Checked.ToStr ing
> >[/color]
>
> Just to remember you that it's better to don't use this kind of SQL
> Statement in your code. Your application risk SQL Injection attacks (see
> this useful article:
> http://msdn.microsoft.com/msdnmag/is.../SQLInjection/)
>
> Don't forget to review your code to avoid SQL Injection ;-)
>
> --
> Davide Vernole
> MVP ASP/ASP.NET
> Microsoft Certified Solution Developer
>
>[/color]

**Davide Vernole [MVP]** · Nov 18 '05, 08:36 PM

Re: Execute the insert command with boolean

nicholas <murmurait1@hot mail.com> typed:[color=blue]
> Yes, you're right.
> I didn't know about this.
>
> Could you help me change my code in a good way?
> I know how to do it when I work with an SQL-server, but as for this
> application I use a ms Access (mdb) database and as I want to
> retrieve the ID of the newly created record, this is the only code
> combination that works. (and I can assure you, I tried a lot of code
> combinations !!)
>
> So If you could help me implement this code, but with @parameters,
> would be great !
>
> THX a lot,
> Frederic
>
> Here's my code executed on the on_click event of an asp.net button
>
> ######## code start #######
>
> 'define where the connectionstrin g is here:
> Dim MyConnectionStr ing as String =
> ConfigurationSe ttings.AppSetti ngs("Connection String")
>
> dim commInsert = Server.CreateOb ject("ADODB.Con nection")
> dim rsnewID = Server.CreateOb ject("ADODB.rec ordset")
> commInsert.Open (MyConnectionSt ring) ' Replace with your OLE DB
> connection string.
> commInsert.Exec ute("INSERT INTO tbl_contents (contenttypeID,
> contentEN, contentFR, contentNL, contentDU, contentdescript ionEN,
> contentdescript ionFR, contentdescript ionNL, contentdescript ionDU,
> contentavailabl e, contentorder) VALUES('" &
> ctype(contentty peID.selectedit em.value,intege r) & "','" &
> contentEN.text & "','" & contentFR.text & "','" & contentNL.text &
> "','" & contentDU.text & "','" & contentdescript ionEN.text & "','" &
> contentdescript ionFR.text & "','" & contentdescript ionNL.text & "','"
> & contentdescript ionDU.text & "'," & contentavailabl e.checked & ",'"
> & contentorder.te xt & "');")
>
> rsNewID = commInsert.Exec ute("SELECT @@IDENTITY ")
> dim intNewID = rsNewID(0).valu e
> rsNewID.Close
> rsNewID = Nothing
> commInsert.Clos e
> commInsert = Nothing
>
> response.write( intnewid)
>
>
> ######### end of code ########
> I also had to add this: AspCompat="true " to the page directive to
> make it work.[/color]

Your are OT. Here we talk about .NET. However, try to see this link
http://support.microsoft.com/kb/232144/EN-US/. Maybe you could find
something interesting for you.

See this for more info about CommandParamete r:

Error - Office.com

http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp

404 - File or directory not found.

http://www.devguru.com/Technologies/ado/quickref/command_parameterscollection.html

404 - Page not found | W3Schools.com

http://www.w3schools.com/ado/met_comm_createparameter.asp

Sorry! We can't seem to find the resource you're looking for

......see google for more ;-)

HTH

--
Davide Vernole
MVP ASP/ASP.NET
Microsoft Certified Solution Developer

**Davide Vernole [MVP]** · Nov 18 '05, 08:36 PM

Re: Execute the insert command with boolean

nicholas <murmurait1@hot mail.com> typed:[color=blue]
> Yes, you're right.
> I didn't know about this.
>
> Could you help me change my code in a good way?
> I know how to do it when I work with an SQL-server, but as for this
> application I use a ms Access (mdb) database and as I want to
> retrieve the ID of the newly created record, this is the only code
> combination that works. (and I can assure you, I tried a lot of code
> combinations !!)
>
> So If you could help me implement this code, but with @parameters,
> would be great !
>
> THX a lot,
> Frederic
>
> Here's my code executed on the on_click event of an asp.net button
>
> ######## code start #######
>
> 'define where the connectionstrin g is here:
> Dim MyConnectionStr ing as String =
> ConfigurationSe ttings.AppSetti ngs("Connection String")
>
> dim commInsert = Server.CreateOb ject("ADODB.Con nection")
> dim rsnewID = Server.CreateOb ject("ADODB.rec ordset")
> commInsert.Open (MyConnectionSt ring) ' Replace with your OLE DB
> connection string.
> commInsert.Exec ute("INSERT INTO tbl_contents (contenttypeID,
> contentEN, contentFR, contentNL, contentDU, contentdescript ionEN,
> contentdescript ionFR, contentdescript ionNL, contentdescript ionDU,
> contentavailabl e, contentorder) VALUES('" &
> ctype(contentty peID.selectedit em.value,intege r) & "','" &
> contentEN.text & "','" & contentFR.text & "','" & contentNL.text &
> "','" & contentDU.text & "','" & contentdescript ionEN.text & "','" &
> contentdescript ionFR.text & "','" & contentdescript ionNL.text & "','"
> & contentdescript ionDU.text & "'," & contentavailabl e.checked & ",'"
> & contentorder.te xt & "');")
>
> rsNewID = commInsert.Exec ute("SELECT @@IDENTITY ")
> dim intNewID = rsNewID(0).valu e
> rsNewID.Close
> rsNewID = Nothing
> commInsert.Clos e
> commInsert = Nothing
>
> response.write( intnewid)
>
>
> ######### end of code ########
> I also had to add this: AspCompat="true " to the page directive to
> make it work.
>[/color]

Your are OT. Here we talk about .NET. However, try to see this link
http://support.microsoft.com/kb/232144/EN-US/. Maybe you could find
something interesting for you.

See this for more info about CommandParamete r: