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

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

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")


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
---------------- end of my update code -----------------

 

[nicholas]

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

Sub insert_new_content () 'sender As Object, e As EventArgs)

'define where the connectionstring is here:
Dim MyConnectionString as String =
ConfigurationSettings.AppSettings("ConnectionString")
Dim dbConn As OleDbConnection

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

If Page.IsValid Then

Dim dbComm As New OleDbCommand()
dbComm.CommandType = CommandType.Text
dbComm.CommandText = "INSERT INTO tbl_contents(contenttypeID,
contentEN, contentFR, contentNL, contentDU, contentdescriptionEN,
contentdescriptionFR, contentdescriptionNL, contentdescriptionDU,
contentavailable, contentorder) VALUES (?,?,?,?,?,?,?,?,?,?,?)"
dbComm.Connection = dbConn


dbComm.Parameters.Add("@contenttypeID",SqlDbType.int)
dbComm.Parameters.Add("@contentEN",SqlDbType.NVarChar,50)
dbComm.Parameters.Add("@contentFR",SqlDbType.NVarChar,50)
dbComm.Parameters.Add("@contentNL",SqlDbType.NVarChar,50)
dbComm.Parameters.Add("@contentDU",SqlDbType.NVarChar,50)
dbComm.Parameters.Add("@contentdescriptionEN",SqlDbType.Ntext)
dbComm.Parameters.Add("@contentdescriptionFR",SqlDbType.Ntext)
dbComm.Parameters.Add("@contentdescriptionNL",SqlDbType.Ntext)
dbComm.Parameters.Add("@contentdescriptionDU",SqlDbType.Ntext)
dbComm.Parameters.Add("@contentavailable",SqlDbType.bit)
dbComm.Parameters.Add("@contentorder",SqlDbType.NVarChar,50)


dbComm.Parameters("@contenttypeID").Value =
ctype(contenttypeID.selecteditem.value,integer)
dbComm.Parameters("@contentEN").Value = contentEN.text
dbComm.Parameters("@contentFR").Value = contentFR.text
dbComm.Parameters("@contentNL").Value = contentNL.text
dbComm.Parameters("@contentDU").Value = contentDU.text
dbComm.Parameters("@contentdescriptionEN").Value =
contentdescriptionEN.text
dbComm.Parameters("@contentdescriptionFR").Value =
contentdescriptionFR.text
dbComm.Parameters("@contentdescriptionNL").Value =
contentdescriptionNL.text
dbComm.Parameters("@contentdescriptionDU").Value =
contentdescriptionDU.text
dbComm.Parameters("@contentavailable").Value =
contentavailable.checked
dbComm.Parameters("@contentorder").Value = contentorder.text


Dim dbCommID As New OleDbCommand()
dbCommID.CommandType = CommandType.Text
dbCommID.CommandText = "SELECT @@IDENTITY AS 'Identity'"
dbCommID.Connection = dbConn

Dim intNewID as Integer

Try
dbConn.Open()
dbComm.ExecuteScalar()
intNewID = dbCommID.ExecuteScalar()

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

Catch ex As Exception
tblform.visible = false
processing.visible = 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