Execute the insert command with boolean

  • Thread starter Thread starter nicholas
  • Start date Start date
N

nicholas

my insert works, but without the boolean value contentavailable.
When I try to insert this boolean I get a data mismatch error.
this "contentavailable" is an asp.net checkbox

I use a ms access dbase.

any ideas? THX

here's the code:

commInsert.Execute("INSERT INTO tbl_contents (contenttypeID, contentEN,
contentFR, contentNL, contentDU, contentdescriptionEN, contentdescriptionFR,
contentdescriptionNL, contentdescriptionDU, contentavailable, contentorder)
VALUES('" & ctype(contenttypeID.selecteditem.value,integer) & "','" &
contentEN.text & "','" & contentFR.text & "','" & contentNL.text & "','" &
contentDU.text & "','" & contentdescriptionEN.text & "','" &
contentdescriptionFR.text & "','" & contentdescriptionNL.text & "','" &
contentdescriptionDU.text & "','" & contentavailable.checked & "','" &
contentorder.text & "');")
 
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

contentavailable.Checked.ToString
 
Ken Cox said:
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

contentavailable.Checked.ToString
Click to expand...

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/issues/04/09/SQLInjection/)

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

Ken Cox said:
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

contentavailable.Checked.ToString
Click to expand...
 
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 connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")

dim commInsert = Server.CreateObject("ADODB.Connection")
dim rsnewID = Server.CreateObject("ADODB.recordset")
commInsert.Open(MyConnectionString) ' Replace with your OLE DB
connection string.
commInsert.Execute("INSERT INTO tbl_contents (contenttypeID, contentEN,
contentFR, contentNL, contentDU, contentdescriptionEN, contentdescriptionFR,
contentdescriptionNL, contentdescriptionDU, contentavailable, contentorder)
VALUES('" & ctype(contenttypeID.selecteditem.value,integer) & "','" &
contentEN.text & "','" & contentFR.text & "','" & contentNL.text & "','" &
contentDU.text & "','" & contentdescriptionEN.text & "','" &
contentdescriptionFR.text & "','" & contentdescriptionNL.text & "','" &
contentdescriptionDU.text & "'," & contentavailable.checked & ",'" &
contentorder.text & "');")

rsNewID = commInsert.Execute("SELECT @@IDENTITY ")
dim intNewID = rsNewID(0).value
rsNewID.Close
rsNewID = Nothing
commInsert.Close
commInsert = Nothing

response.write(intnewid)


######### end of code ########
I also had to add this: AspCompat="true" to the page directive to make it
work.
 
nicholas said:
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 connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")

dim commInsert = Server.CreateObject("ADODB.Connection")
dim rsnewID = Server.CreateObject("ADODB.recordset")
commInsert.Open(MyConnectionString) ' Replace with your OLE DB
connection string.
commInsert.Execute("INSERT INTO tbl_contents (contenttypeID,
contentEN, contentFR, contentNL, contentDU, contentdescriptionEN,
contentdescriptionFR, contentdescriptionNL, contentdescriptionDU,
contentavailable, contentorder) VALUES('" &
ctype(contenttypeID.selecteditem.value,integer) & "','" &
contentEN.text & "','" & contentFR.text & "','" & contentNL.text &
"','" & contentDU.text & "','" & contentdescriptionEN.text & "','" &
contentdescriptionFR.text & "','" & contentdescriptionNL.text & "','"
& contentdescriptionDU.text & "'," & contentavailable.checked & ",'"
& contentorder.text & "');")

rsNewID = commInsert.Execute("SELECT @@IDENTITY ")
dim intNewID = rsNewID(0).value
rsNewID.Close
rsNewID = Nothing
commInsert.Close
commInsert = Nothing

response.write(intnewid)


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

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 CommandParameter:
http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp
http://www.devguru.com/Technologies/ado/quickref/command_parameterscollection.html
http://www.w3schools.com/ado/met_comm_createparameter.asp

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

HTH
 
nicholas said:
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 connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")

dim commInsert = Server.CreateObject("ADODB.Connection")
dim rsnewID = Server.CreateObject("ADODB.recordset")
commInsert.Open(MyConnectionString) ' Replace with your OLE DB
connection string.
commInsert.Execute("INSERT INTO tbl_contents (contenttypeID,
contentEN, contentFR, contentNL, contentDU, contentdescriptionEN,
contentdescriptionFR, contentdescriptionNL, contentdescriptionDU,
contentavailable, contentorder) VALUES('" &
ctype(contenttypeID.selecteditem.value,integer) & "','" &
contentEN.text & "','" & contentFR.text & "','" & contentNL.text &
"','" & contentDU.text & "','" & contentdescriptionEN.text & "','" &
contentdescriptionFR.text & "','" & contentdescriptionNL.text & "','"
& contentdescriptionDU.text & "'," & contentavailable.checked & ",'"
& contentorder.text & "');")

rsNewID = commInsert.Execute("SELECT @@IDENTITY ")
dim intNewID = rsNewID(0).value
rsNewID.Close
rsNewID = Nothing
commInsert.Close
commInsert = Nothing

response.write(intnewid)


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

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 CommandParameter:
http://support.microsoft.com/default.aspx?scid=/Support/ActiveServer/faq/data/adofaq.asp
http://www.devguru.com/Technologies/ado/quickref/command_parameterscollection.html
http://www.w3schools.com/ado/met_comm_createparameter.asp

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

HTH
 
Back
Top