DB insert problems with apostrophe in a Surname and Datetime value

[Guest]

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 cmdSelectAuthors As OleDbCommand
Dim dtrAuthors As OleDbDataReader
Dim txt_RC_TITLE, txt_RC_GIVENNAME, txt_RC_SURNAME, txt_RC_MOBILE,
txt_RC_EMAIL, txt_RC_GROUPS as Object
Dim dte_RC_DATESTAMP as object 'the dte_RC_DATESTAMP 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=Microsoft.Jet.OLEDB.4.0;DATA
Source=C:\CustomerDatabase.mdb")
conAuthors.Open()
cmdSelectAuthors = New OleDbCommand( "Select * From CUSTOMERTABLE",
conAuthors )
dtrAuthors = cmdSelectAuthors.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_GIVENNAME = dtrAuthors( "txtGIVENNAME" )
txt_RC_SURNAME = dtrAuthors( "txtSURNAME" )
txt_RC_MOBILE = dtrAuthors( "txtMOBILE" )
txt_RC_EMAIL = dtrAuthors( "txtEMAIL" )
'dte_RC_DATESTAMP = dtrAuthors( "dteDATESTAMP" )

'insert details into CUSTOMERDETAILS table in MSDE database and get that
intCUSID(customer 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;DATABASE=customerdb")

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

cmdInsert = New SqlCommand(strInsert, conDB)

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

'Add the customers email address and the intCUSID(customer id) to the
CUSMARKETING table
Dim strConn As String =
"SERVER=xxx.xxx.xxx.xxx;UID=sa;PWD=xxxx;DATABASE=customerdb"
Dim cmd As New SqlCommand("INSERT INTO CUSMARKETING (intCUSID,
txtEMAILADDRESS ) VALUES('" & intCUSID & "', '" & txt_RC_EMAIL & "')", New
SqlConnection(strConn))

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()

End While
dtrAuthors.Close()
conAuthors.Close()

 

[Alan Samet]

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

Example:

Dim cn As New SqlConnection(strConn)
Try
cn.Open()
Dim cmd As New SqlCommand("INSERT INTO CUSMARKETING (intCUSID,
txtEMAILADDRESS) SELECT @intCUSID, @txtEMAILADDRESS", cn)
cmd.Parameters.Add("@intCUSID", intCUSID)
cmd.Parameters.Add("@txtEMAILADDRESS", txt_RC_EMAIL)
cmd.ExecuteNonQuery()
Finally
cn.Close()
End Try