insert record in Access database

[nicholas]

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_cont ent () 'sender As Object, e As EventArgs)

'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, contentfile1, contentfile2, contentfile3,
contentfile4, contentfile5) 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 & "','" & extfile1 &
"','" & extfile2 & "','" & extfile3 & "','" & extfile4 & "','" & extfile5 &
"');") ' Execute the insert command
rsNewID = commInsert.Exec ute("SELECT @@IDENTITY ") ' Create a
recordset and SELECT the new Identity
dim intNewID = rsNewID(0).valu e ' Store the value of the new
identity in variable intNewID
rsNewID.Close
rsNewID = Nothing
commInsert.Clos e
commInsert = Nothing
End Sub
---------------- end of my insert code -----------------

---------------- my update code -----------------
Sub update_content () 'sender As Object, e As EventArgs)

'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")
commInsert.Open (MyConnectionSt ring) ' Replace with your OLE DB
connection string.
commInsert.Exec ute("UPDATE tbl_contents SET contenttypeID ='" &
ctype(contentty peID.selectedit em.value,intege r) & "', contentEN='" &
contentEN.text & "', contentFR='" & contentFR.text & "', contentNL='" &
contentNL.text & "', contentDU='" & contentDU.text & "',
contentdescript ionEN='" & contentdescript ionEN.text & "',
contentdescript ionFR='" & contentdescript ionFR.text & "',
contentdescript ionNL='" & contentdescript ionNL.text & "',
contentdescript ionDU='" & contentdescript ionDU.text & "', contentavailabl e="
& contentavailabl e.checked & ", contentorder='" & contentorder.te xt & "',
contentfile1='" & extfile1 & "', contentfile2='" & extfile2 & "',
contentfile3='" & extfile3 & "', contentfile4='" & extfile4 & "',
contentfile5='" & extfile5 & "' WHERE contentID = " &
request.queryst ring("contentID ") & ";") ' Execute the update command
commInsert.Clos e
commInsert = Nothing

End Sub
---------------- end of my update code -----------------

[nicholas]

here is my solution to 'my' problem...and it works:

Sub insert_new_cont ent () 'sender As Object, e As EventArgs)

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

'Create a new connection object pointing to the database
dbConn = New OleDbConnection (MyConnectionSt ring)

If Page.IsValid Then

Dim dbComm As New OleDbCommand()
dbComm.CommandT ype = CommandType.Tex t
dbComm.CommandT ext = "INSERT INTO tbl_contents(co ntenttypeID,
contentEN, contentFR, contentNL, contentDU, contentdescript ionEN,
contentdescript ionFR, contentdescript ionNL, contentdescript ionDU,
contentavailabl e, contentorder) VALUES (?,?,?,?,?,?,?, ?,?,?,?)"
dbComm.Connecti on = dbConn
dbComm.Paramete rs.Add("@conten ttypeID",SqlDbT ype.int)
dbComm.Paramete rs.Add("@conten tEN",SqlDbType. NVarChar,50)
dbComm.Paramete rs.Add("@conten tFR",SqlDbType. NVarChar,50)
dbComm.Paramete rs.Add("@conten tNL",SqlDbType. NVarChar,50)
dbComm.Paramete rs.Add("@conten tDU",SqlDbType. NVarChar,50)
dbComm.Paramete rs.Add("@conten tdescriptionEN" ,SqlDbType.Ntex t)
dbComm.Paramete rs.Add("@conten tdescriptionFR" ,SqlDbType.Ntex t)
dbComm.Paramete rs.Add("@conten tdescriptionNL" ,SqlDbType.Ntex t)
dbComm.Paramete rs.Add("@conten tdescriptionDU" ,SqlDbType.Ntex t)
dbComm.Paramete rs.Add("@conten tavailable",Sql DbType.bit)
dbComm.Paramete rs.Add("@conten torder",SqlDbTy pe.NVarChar,50)
dbComm.Paramete rs("@contenttyp eID").Value =
ctype(contentty peID.selectedit em.value,intege r)
dbComm.Paramete rs("@contentEN" ).Value = contentEN.text
dbComm.Paramete rs("@contentFR" ).Value = contentFR.text
dbComm.Paramete rs("@contentNL" ).Value = contentNL.text
dbComm.Paramete rs("@contentDU" ).Value = contentDU.text
dbComm.Paramete rs("@contentdes criptionEN").Va lue =
contentdescript ionEN.text
dbComm.Paramete rs("@contentdes criptionFR").Va lue =
contentdescript ionFR.text
dbComm.Paramete rs("@contentdes criptionNL").Va lue =
contentdescript ionNL.text
dbComm.Paramete rs("@contentdes criptionDU").Va lue =
contentdescript ionDU.text
dbComm.Paramete rs("@contentava ilable").Value =
contentavailabl e.checked
dbComm.Paramete rs("@contentord er").Value = contentorder.te xt
Dim dbCommID As New OleDbCommand()
dbCommID.Comman dType = CommandType.Tex t
dbCommID.Comman dText = "SELECT @@IDENTITY AS 'Identity'"
dbCommID.Connec tion = dbConn

Dim intNewID as Integer

Try
dbConn.Open()
dbComm.ExecuteS calar()
intNewID = dbCommID.Execut eScalar()

tblform.visible = false
processing.visi ble = true
lblrecord.text = "OK"
lbldone.text = "Done !"

Catch ex As Exception
tblform.visible = false
processing.visi ble = true
lblrecord.text = "AN ERROR OCCURRED !!!!"
lbldone.text = ex.Message

Finally
If dbConn.State = ConnectionState .Open Then
dbConn.Close()
End If
End Try

End If
End Sub

"nicholas" <mu********@hot mail.com> wrote in message
news:Og******** ******@TK2MSFTN GP09.phx.gbl...

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_cont ent () 'sender As Object, e As EventArgs)

'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, contentfile1, contentfile2, contentfile3,
contentfile4, contentfile5) 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 & "','" & extfile1 & "','" & extfile2 & "','" & extfile3 & "','" & extfile4 & "','" & extfile5 & "');") ' Execute the insert command
rsNewID = commInsert.Exec ute("SELECT @@IDENTITY ") ' Create a
recordset and SELECT the new Identity
dim intNewID = rsNewID(0).valu e ' Store the value of the new
identity in variable intNewID
rsNewID.Close
rsNewID = Nothing
commInsert.Clos e
commInsert = Nothing
End Sub
---------------- end of my insert code -----------------

---------------- my update code -----------------
Sub update_content () 'sender As Object, e As EventArgs)

'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")
commInsert.Open (MyConnectionSt ring) ' Replace with your OLE DB
connection string.
commInsert.Exec ute("UPDATE tbl_contents SET contenttypeID ='" &
ctype(contentty peID.selectedit em.value,intege r) & "', contentEN='" &
contentEN.text & "', contentFR='" & contentFR.text & "', contentNL='" &
contentNL.text & "', contentDU='" & contentDU.text & "',
contentdescript ionEN='" & contentdescript ionEN.text & "',
contentdescript ionFR='" & contentdescript ionFR.text & "',
contentdescript ionNL='" & contentdescript ionNL.text & "',
contentdescript ionDU='" & contentdescript ionDU.text & "', contentavailabl e=" & contentavailabl e.checked & ", contentorder='" & contentorder.te xt & "',
contentfile1='" & extfile1 & "', contentfile2='" & extfile2 & "',
contentfile3='" & extfile3 & "', contentfile4='" & extfile4 & "',
contentfile5='" & extfile5 & "' WHERE contentID = " &
request.queryst ring("contentID ") & ";") ' Execute the update command
commInsert.Clos e
commInsert = Nothing

End Sub
---------------- end of my update code -----------------