DB insert problems with apostrophe in a Surname and Datetime value

[geodev]

I’m copying data across from an Access database to an MSDE database through
the use of some vb.net code.

Unfortunately the application crashes when it goes to copy across data, in
particular surnames that have an apostrophe such as O’Shay etc.

Also I have a ‘date/time’ value in my Access database that I have to copy
across to my MSDE database in a field that is of datatype ‘datetime’ and
length ‘8’. How do I get this right I tried to get this working and the page
would crash every time.

The code I’m using is as follows.

Dim conAuthors As OleDbConnection
Dim cmdSelectAuthor s As OleDbCommand
Dim dtrAuthors As OleDbDataReader
Dim txt_RC_TITLE, txt_RC_GIVENNAM E, txt_RC_SURNAME, txt_RC_MOBILE,
txt_RC_EMAIL, txt_RC_GROUPS as Object
Dim dte_RC_DATESTAM P as object 'the dte_RC_DATESTAM P is a datestamp that is
in the Access database that I have to copy across but I’m having a lot of
trouble not sure how to do it

'open Access database with customer details
conAuthors = New OleDbConnection ("PROVIDER=Micr osoft.Jet.OLEDB .4.0;DATA
Source=C:\Custo merDatabase.mdb ")
conAuthors.Open ()
cmdSelectAuthor s = New OleDbCommand( "Select * From CUSTOMERTABLE",
conAuthors )
dtrAuthors = cmdSelectAuthor s.ExecuteReader ()

'loop through all customer details in Access database, copy these details to
a new set of two tables in the MSDE database
While dtrAuthors.Read ()
'set up read in values from Access database
txt_RC_TITLE = dtrAuthors( "txtTITLE" )
txt_RC_GIVENNAM E = dtrAuthors( "txtGIVENNA ME" )
txt_RC_SURNAME = dtrAuthors( "txtSURNAME " )
txt_RC_MOBILE = dtrAuthors( "txtMOBILE" )
txt_RC_EMAIL = dtrAuthors( "txtEMAIL" )
'dte_RC_DATESTA MP = dtrAuthors( "dteDATESTA MP" )

'insert details into CUSTOMERDETAILS table in MSDE database and get that
intCUSID(custom er id) that is a unique integer that is created for each
record that is inserted into the table
Dim conDB As SqlConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

conDB = New
SqlConnection(" SERVER=xxx.xxx. xxx.xxx;UID=sa; PWD=xxxx;DATABA SE=customerdb")

strInsert = "Insert into CUSTOMERDETAILS (txtTITLE, txtGIVENNAME,
txtSURNAME, txtMOBILEPHONE) Values ('" & txt_RC_TITLE & "', '" &
txt_RC_GIVENNAM E & "', '" & txt_RC_SURNAME & "', '" & txt_RC_MOBILE & "');
SELECT SCOPE_IDENTITY( )"

cmdInsert = New SqlCommand(strI nsert, conDB)

conDB.Open()
Dim intCUSID As Integer
intCUSID = cint(cmdInsert. ExecuteScalar() )
conDB.Close()

'Add the customers email address and the intCUSID(custom er id) to the
CUSMARKETING table
Dim strConn As String =
"SERVER=xxx.xxx .xxx.xxx;UID=sa ;PWD=xxxx;DATAB ASE=customerdb"
Dim cmd As New SqlCommand("INS ERT INTO CUSMARKETING (intCUSID,
txtEMAILADDRESS ) VALUES('" & intCUSID & "', '" & txt_RC_EMAIL & "')", New
SqlConnection(s trConn))

cmd.Connection. Open()
cmd.ExecuteNonQ uery()
cmd.Connection. Close()

End While
dtrAuthors.Clos e()
conAuthors.Clos e()

[Alan Samet]

I highly recommend using parameterized SQL statements. I believe this
will fix most of your problems.

Example:

Dim cn As New SqlConnection(s trConn)
Try
cn.Open()
Dim cmd As New SqlCommand("INS ERT INTO CUSMARKETING (intCUSID,
txtEMAILADDRESS ) SELECT @intCUSID, @txtEMAILADDRES S", cn)
cmd.Parameters. Add("@intCUSID" , intCUSID)
cmd.Parameters. Add("@txtEMAILA DDRESS", txt_RC_EMAIL)
cmd.ExecuteNonQ uery()
Finally
cn.Close()
End Try