By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,837 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,837 IT Pros & Developers. It's quick & easy.

insert new record in Access dbase AND retrieve autonumber

P: n/a
To insert a record in a Ms Access database and be able to retrieve the
newly
created ID (autonumber) I used the code below (code 1).
Now, the problem is that this is not very secure and that, if for
example an
insertion contains a ' or a " this code fails. It is much better to
work
with @parameters.

So could someone change my code to make it work with @parameters and
that I
still can retrieve that autonumber. (note that it is for a MS Access
dbase,
where stored procedures do not work, unfortunately !)
I think that by changing just a few things in my code, we should be
able to
make it work, but I'm not 'professional' enough yet for this...

So, hope someone can help me.
Thank you !

Here's my code (code 1):

---------------- my insert code -----------------
Sub insert_new_content () 'sender As
Object, e As EventArgs)

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
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, contentfile1, contentfile2,
contentfile3,
contentfile4, contentfile5) 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 & "','" & extfile1 &
"','" & extfile2 & "','" & extfile3
& "','" & extfile4 & "','" &
extfile5 &
"');") ' Execute the insert command
rsNewID = commInsert.Execute("SELECT @@IDENTITY
") ' Create a
recordset and SELECT the new Identity
dim intNewID = rsNewID(0).value ' Store the value of
the new
identity in variable intNewID
rsNewID.Close
rsNewID = Nothing
commInsert.Close
commInsert = Nothing
End Sub---------------- end of my insert code
-----------------

---------------- my update code -----------------
[code:1:b2820991b5] Sub update_content () 'sender As
Object, e As EventArgs)

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
dim commInsert =
Server.CreateObject("ADODB.Connection")
commInsert.Open(MyConnectionString) ' Replace with
your OLE DB
connection string.
commInsert.Execute("UPDATE tbl_contents SET
contenttypeID ='" &
ctype(contenttypeID.selecteditem.value,integer) & "',
contentEN='" &
contentEN.text & "', contentFR='" & contentFR.text
& "', contentNL='" &
contentNL.text & "', contentDU='" & contentDU.text
& "',
contentdescriptionEN='" & contentdescriptionEN.text &
"',
contentdescriptionFR='" & contentdescriptionFR.text &
"',
contentdescriptionNL='" & contentdescriptionNL.text &
"',
contentdescriptionDU='" & contentdescriptionDU.text &
"', contentavailable="
& contentavailable.checked & ", contentorder='"
& contentorder.text & "',
contentfile1='" & extfile1 & "',
contentfile2='" & extfile2 & "',
contentfile3='" & extfile3 & "',
contentfile4='" & extfile4 & "',
contentfile5='" & extfile5 & "' WHERE contentID =
" &
request.querystring("contentID") &
";") ' Execute the update command
commInsert.Close
commInsert = Nothing

End Sub[/code:1:b2820991b5]---------------- end of my update code
-----------------

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*
Nov 18 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You could just replace the ' with '' (two singlequotes) and " with "", e.g.

replace(replace(contentEN.text,"'","''"),"""","""" "")

--
Ben Strackany
www.developmentnow.com
"loreille" <ma**@squaresweb-dot-com.no-spam.invalid> wrote in message
news:41********@Usenet.com...
To insert a record in a Ms Access database and be able to retrieve the
newly
created ID (autonumber) I used the code below (code 1).
Now, the problem is that this is not very secure and that, if for
example an
insertion contains a ' or a " this code fails. It is much better to
work
with @parameters.

So could someone change my code to make it work with @parameters and
that I
still can retrieve that autonumber. (note that it is for a MS Access
dbase,
where stored procedures do not work, unfortunately !)
I think that by changing just a few things in my code, we should be
able to
make it work, but I'm not 'professional' enough yet for this...

So, hope someone can help me.
Thank you !

Here's my code (code 1):

---------------- my insert code -----------------
Sub insert_new_content () 'sender As
Object, e As EventArgs)

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
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, contentfile1, contentfile2,
contentfile3,
contentfile4, contentfile5) 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 & "','" & extfile1 &
"','" & extfile2 & "','" & extfile3
& "','" & extfile4 & "','" &
extfile5 &
"');") ' Execute the insert command
rsNewID = commInsert.Execute("SELECT @@IDENTITY
") ' Create a
recordset and SELECT the new Identity
dim intNewID = rsNewID(0).value ' Store the value of
the new
identity in variable intNewID
rsNewID.Close
rsNewID = Nothing
commInsert.Close
commInsert = Nothing
End Sub---------------- end of my insert code
-----------------

---------------- my update code -----------------
[code:1:b2820991b5] Sub update_content () 'sender As
Object, e As EventArgs)

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionStrin g")
dim commInsert =
Server.CreateObject("ADODB.Connection")
commInsert.Open(MyConnectionString) ' Replace with
your OLE DB
connection string.
commInsert.Execute("UPDATE tbl_contents SET
contenttypeID ='" &
ctype(contenttypeID.selecteditem.value,integer) & "',
contentEN='" &
contentEN.text & "', contentFR='" & contentFR.text
& "', contentNL='" &
contentNL.text & "', contentDU='" & contentDU.text
& "',
contentdescriptionEN='" & contentdescriptionEN.text &
"',
contentdescriptionFR='" & contentdescriptionFR.text &
"',
contentdescriptionNL='" & contentdescriptionNL.text &
"',
contentdescriptionDU='" & contentdescriptionDU.text &
"', contentavailable="
& contentavailable.checked & ", contentorder='"
& contentorder.text & "',
contentfile1='" & extfile1 & "',
contentfile2='" & extfile2 & "',
contentfile3='" & extfile3 & "',
contentfile4='" & extfile4 & "',
contentfile5='" & extfile5 & "' WHERE contentID =
" &
request.querystring("contentID") &
";") ' Execute the update command
commInsert.Close
commInsert = Nothing

End Sub[/code:1:b2820991b5]---------------- end of my update code
-----------------

*-----------------------*
Posted at:
www.GroupSrv.com
*-----------------------*

Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.