[RESOLVED] insert memo field msaccess vb.net

[moushmif]

We are trying to read from an MS access database table and write into multiple tables. Both source and destination have memo fields that store tag related data with special characters. We are trying to write a vb.net program using datareader and have not been able to insert the read data into the destination column successfully.
Is there any way to get around this issue. We are at our wits end at this point.

Thanks!!

[jmcilhinney]

To give us an idea of what the issue might be, how about you show us what you're doing at the moment? There's not much point suggesting something that you may already be doing.

[moushmif]

Iam trying to copy the contents of fields from one table in a access database to other fields in another table of a second access database.
Iam pasting the code that iam working on.iam not able to copy the contents of the memo fields into the destination database.in this col6 and col7 are the memo fields .

Dim cn As OleDbConnection
Dim cn_dest As OleDbConnection
Dim cmd As OleDbCommand
Dim cmdold As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String

Dim col3 As Integer
Dim col4 As String
Dim col5 As String
Dim col6 As String
Dim col7 As String

Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Amoushmi\CMS\Old_db\cMS.mdb;")
'provider to be used when working with access database
cn.Open()
cmdold = New OleDbCommand("select * from WebContent", cn)
dr = cmdold.ExecuteReader
cn_dest = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Amoushmi\CMS\New_cms\DB\aCMS.mdb;")
cn_dest.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM [tblContent_languages]", cn_dest)
AccessCommand.ExecuteNonQuery()

While dr.Read()


col3 = CInt(dr(1))
col4 = CStr(dr(2))
col5 = CStr(dr(4))
col6 = CStr(dr(5))
' col7 = CStr(dr(21))

str = "insert into tblContent_languages(templateid,contentName,contentTitle,contentBody) values (" & col3 & ",'" & col4 & "','" & col5 & "','" & col6 & "')"
'string stores the command and CInt is used to convert number to string
cmd = New OleDbCommand(str, cn_dest)

icount = cmd.ExecuteNonQuery
End While
Catch
End Try
dr.Close()
cn.Close()
cn_dest.Close()

[jmcilhinney]

You should not be using string concatenation to build SQL statements at the best of times, but if you have large memo fields then you're going to break the SQL statement character limit pretty quickly.

str = "INSERT INTO tblContent_languages (templateid, contentName, contentTitle, contentBody) VALUES (@TemplateID, @ContentName, @ContentTitle, @ContentBody)"
cmd = New OleDbCommand(str, cn_dest)
cmd.Parameters.AddWithValue("@TemplateID", col3)
cmd.Parameters.AddWithValue("@ContentName", col4)
cmd.Parameters.AddWithValue("@ContentTitle", col5)
cmd.Parameters.AddWithValue("@ContentBody", col6)

[moushmif]

That worked! Thanks a bunch!

[jmcilhinney]

Cool. Don't forget to resolve your thread from the Thread Tools menu.

[RobDog888]

Fyi, Aprox. 64,000 characters for an Access sql string limit.