ADO.net - How to insert long texts?

[Sebastian W.]

Hello.

I am a beginner concerning ADO.net. I want to access a Microsoft
Access file (mdb). Opening the database, selecting rows and reading
then from the table-object into my program is no problem. I use a SQL
expression to load them (SELECT * FROM table WHERE ….).

But I do not know how to insert new data into the tables or how to
update a row.
I first tried to generate a SQL command (INSERT INTO table1(...) SET
column='Value') but the data I need to handle is a multiline text (CR,
LF) and may contain the inverted comma ( ' ) as well... so the SQL-cmd
does not work...

So I tried to use the Dataset and a CommandBuilder, as long as I do
not know how the UpdateCommand and the InsertCommand have to look like
(How knows ADO in which section of the SQL Command it should insert
the data). But it did not work
(Error: "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in
microsoft.visualbasic.dll" at Dataset.Update)


To cut a long story short: How do I update/insert multiline-texts in
my MDB?

Thanks
Sebastian Walters
(e-mail address removed)

 

[William Ryan]

Use parameters might help. cmd.CommandText = "Insert INTO
myTables(someColumn) Values ?" (if this is oledb). Then
in your command object, add a parameter like
so...cmd.Parameters.Add(tbMultiLineTextBox.Text) I'm
haven't used named parameters in Oledb, but I do know
the '?' will work for you.

the '?' indicates a parameter in OLEDb queries. using
parameters gets you out of the whole dynamic sql, did I
remember to put the single quote in the right place,
business.

Let me know if you still have troubles and I'll see what I
can do.

Good Luck,

Bill

 

[Sebastian W.]

Use parameters might help. cmd.CommandText = "Insert INTO

myTables(someColumn) Values ?" (if this is oledb). Then
in your command object, add a parameter like
so...cmd.Parameters.Add(tbMultiLineTextBox.Text) I'm
haven't used named parameters in Oledb, but I do know
the '?' will work for you.

Hi,
thanks for the help, but it did not work... :-(

I created a command using the code below and tried to fill the
parameters. But when I want to add a parameter I just get the
following InvalidCastException:
"The OleDbParameterCollection only accepts non-null OleDbParameter
type objects, not String objects."
So I tried to add OleDBParameters ( cmd.parameter.add(new
oldbparameter...) ), but this is not possible due there are only
contructors which require named parameters...

Here is the code I am using:

Dim Cmd As OleDbCommand
SQL = "INSERT INTO " & Acc & "(MID, From, Subject, To, Date, Priority,
Header, Body, ComState, ReadState, Attachment, Recipient,
ContentType)"
SQL &= " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Cmd = New OleDbCommand(SQL, Conn)
Cmd.Parameters.Add(M.MessageID)
Cmd.Parameters.Add(M.From)
Cmd.Parameters.Add(M.Subject)
Cmd.Parameters.Add(M.To)
Cmd.Parameters.Add(M.Date.DateTime)
Cmd.Parameters.Add(M.AdditionalHeaders.Priority)
Cmd.Parameters.Add(M.Header)
Cmd.Parameters.Add(M.PublicText)
Cmd.Parameters.Add(M.dbComState)
Cmd.Parameters.Add(M.dbReadState)
Cmd.Parameters.Add(Strings.Join(M.Attachments.ToArray, "|"))
Cmd.Parameters.Add(M.AdditionalHeaders.Recipients_String)
Cmd.ExecuteNonQuery()

The properties of M which are added as parameters are mostly Strings
(and Integer)

Thank you for any help,
Sebastian Walters
(e-mail address removed)