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()

 

[AMDRIT]

I do not know what the datestamp data type in Access represents and a
cursory on the web did not lead me to any good guesses as to what the value
stored represents.

if the value is just a date field, then declare dte_RC_DATESTAMP as date so
that future developers are also clear, simply then ecapsulate the value in
single quotes when making your insert statement.

If the value is an EPOCH date, that is just the time in seconds from
01/01/1970 and therefore it is an integer value and you should not have to
encapsulate it at all.

If the value is specific date format that is not easily converted to a
datetime value (i.e, 20020707), parse the value and format it as a date,
dte_RC_DATESTAMP = ctype(dtrAuthors( "dteDATESTAMP" ).substring(7,2) & "/" &
"dteDATESTAMP" ).substring(5,2) & "/" &
"dteDATESTAMP" ).substring(0,4),date). Again you would declare the value as
a date and encapsulate it with single quotes in your insert statement.

As for your surname issue, simple escape each occurance of the single quote
with an additional quote.

dim Field1 as string
dim Field2 as date
dim sSQL as string

Field1 = "o'Maley"
Field2 = ctype("01/01/1970",date)

sSQL = "Insert MyTable (StringField, DateField) Values ('" &
field1.replace("'","''") & "', '" & field2.tolongdatestring & "')"

or - if you prefer -

sSQL = string.format("Insert MyTable (StringField, DateField) Values ('{0}',
'{1}')",Field1.replace("'","''"),Field2.ToLongDateString)


Hope that helps

 

[Otis Mukinfus]

You don't have to double up on the single quotes if you use OleDbCommands with
parameters.

Otis

 

[AMDRIT]

I realize this, however geodev was using inline SQL. I was just following
his format to convey the points of interest.

 

[W.G. Ryan - MVP]

Geodev - If i understand you correctly, I think all you need to do is use
Paramaeters instead of hard coding the values in there.There are many
reasons this is desirable, and the apostrophe issue is but one of them.

However, another approach you may want to consider is to create two data
adapters, one for the access table and one for the sql server table. Set
AcceptChangesDuringFill to false on the source adapter. Fill the datatable.
If you want the identity values, then select everything. If you want Sql
Server to assign them then don't include the autoincrement column in the
Access Select query. Instread, create a DataColumn with the same name in the
datatable, set the autoincrement value to -1. After filling the table, just
immediately call the Sql Server adapter passing in the datatable you jsut
filled.

Also, ADO.NET is a great technology for data access, but it's disconnected
architecture isn't really the best way to transfer data (although with
SqlBulkInsert, the 2.0 framework addresses this shortcoming). As such, you
may want to use DTS - if you have to do it in ADO.NET, cool, I just figured
I'd open the door to DTS. If you are interested btw in the new features like
SqlBulkInsert, Sahil Malik's Professional ADO.NET 2.0 is probably as good of
a book as can be written on the subject. Again, I just mention it in
passing.

Cheers,

Bill

 

[Otis Mukinfus]

AMDRIT,

Sorry, I guess I should have to geodev's post. The answer was meant for him not
you.

I realize this, however geodev was using inline SQL. I was just following
his format to convey the points of interest.

Otis