473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

DB insert problems with apostrophe in a Surname and Datetime value

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=x xxx;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=c ustomerdb"
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()

Nov 19 '05 #1
1 2723
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

Nov 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM,...
2
by: Jassim Rahma | last post by:
Hi, I am using C# to insert a date value from the DatePicker into a DateTime filed in mySQL database.. I am using the following syntax: '" + datePicker.Value + "' I also triend it withour...
0
by: Tim::.. | last post by:
Can someone please help! I'm trying to write an insert statement for a complex datagrid! The database consists of the following data structure! ..tblContent PageID PK ModDate Description...
1
by: Matthias De Ridder | last post by:
Hello, I really hope that someone will be able to help me, because I'm desperate now! I'm a student, graduating this year, and I'm working on a thesis where C# Web Services are involved. I...
3
by: brianbasquille | last post by:
Hello all, Strange little problem here... am just trying to insert some basic information into an Access Database using OleDB. I'm getting a "Syntax error in Insert Into statement" when it...
4
by: Arpan | last post by:
A SQL Server 2005 DB table has 4 columns namely "ID" (IDENTITY int column), "FirstName" (varchar(50)), "LastName" (varchar(50)) & "DOB" (datetime). Now when I am trying to add a new row using the...
3
by: dave k | last post by:
I often use the Control and Apostrophe keys to enter the value from the same field of the previous record in a form. I would prefer to use a mouse and the double click function. Can anyone help...
2
by: DavidOwens | last post by:
<form action="do.php" method="post"> <?php /* create table users (id int, staffid int, region varchar(20), firstname varchar(20), surname varchar(20)); insert into users...
0
BenRatcliffe
by: BenRatcliffe | last post by:
Hi there, I was wondering if anyone could help me. I have a comlpex database with a number of forms that have data entered on them and then saved into the correct table etc. In this instance I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.