can't save multi-line textbox

[VBVines]

For some reason when I try to save/update a form with a multi-line text box on it I get oledb errors. Then I simply change the text box back to NOT multi line and it saves/updates just fine. The backend database is sql 7 the field is varchar set for 500 characters.

aspvbwannab

 

[gmmastros]

I assure you that this can be made to work. Please post your error message and a code snippet where you save the contents of the multi-line text box to the database.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VBVines]

I have done it before and don't remember running into this issue. Here is the error and code and thanks for checking it out, maybe a second set of eyes...
=========
run-time error '2147217887(80040e21)

Multiple-step operation generated errors. Check each ole db status value, if available. No work was done.

Private Sub CmdSave_Click()
Dim ssql As String
Dim valdetail As String
Dim rs As Recordset
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
With conn
.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Administration;Data Source=SOLOFIND"
.Open

Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
'rs.Open "Resolution", conn, , , adCmdTable

ssql = "Select * from resolution Where res_id = '" & Cmbres.Text & "'"
rs.Open ssql, conn, adOpenKeyset, adLockOptimistic, adCmdText


rs!fname = "" & Txtfname
rs!lname = "" & Txtlname
rs!retailer = "" & txtret
'On Error Resume Next
rs!detail = "" & txtresolution




rs.Update

MsgBox "Datebase Updated"

valdetail = "" & rs!detail

End With

End Sub


aspvbwannab

 

[jebenson]

Which line is causing the error?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[AEtherson]

It is not a problem with your code. It is with the Database

 

[wangdong]

1. if the property of the 'res_id' field is integer, you do not need a single quote
2. Why don't use a update query

Update resolution Set fname = "" & Txtfname, lname = "" & Txtlname, retailer = "" & txtret, detail = "" & txtresolution Where res_id = '" & Cmbres.Text & "'"

 

[gmmastros]

I've had this problem before. For me, the root of the problem occurred when I tried to set too much data in to a field that could not accept it. In your original post, you mention that the field is set for 500 characters (varchar). Could it *possibly* be that you are trying to set too much data in to the field?

I think you get the same error message if you try to set data in to a field with the wrong data type. For example, if you have an integer field that you set to a string, you will get the error also.

Bottom line... check your data types, and check the length of the fields vs the length of data you are trying to store.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VBVines]

gmmastros and AEtherson, that is exactly right. Earlier this morning I changed the data type of the field from varchar at 500 to Text which defaults to 16 ( it won't let you put anything else in there )not sure what the 16 means but... it worked. I hope it doesn't come back later to take a chunk out of you know where.

aspvbwannab

 

[gmmastros]

The 16 in the Text field refers to the amount of storage 'in the row'. Text allows for approximately 2 GB of data per field. It takes 16 bits of data to point the the file offset location used to store the actual data.

There are some limitations with using the text data types. They may not affect you, but you should be very careful to test your entire application.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VBVines]

I certainly do not need 2 gig of data and I know there is an overhead associated with this but I am not sure which datatype I should use if not this type. Thanks,

aspvbwannab

 

[bjd4jc]

The size column is 16 bytes. This is the amount of storage that is used in the table for a text field. There is an 8K limit on the size of a row, the 16 is taken from the 8K. The actual text stored in a text field, is, I believe stored somewhere else. Someone else can correct me if I'm wrong there.

 

[bjd4jc]

Sorry, I was a little late in posting....
glad to know someone else concurred with what I thought, though.

 

[gmmastros]

Personally, I would simply increase the size of the varchar field. Change it from 500 to 5000 (for example). You should also take care in preventing the user from putting too much data in to it. For example, if last name is a varchar 20, then set the MaxLength property of the text box to 20. If the field is a varchar 500, set the maxlength to 500. It's better to prevent a user from entering too much data than it is to have your application crash in front of them.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VBVines]

Alright Gmmastros took your advice. Thanks.

aspvbwannab

 

[tsdragon]

Here's a warning when accessing fields longer than 500 (approx) characters. I know it's a problem with SQL Server (DSN-less connection) using rdoConnection and rdoResultset, it may be for others.

When you select a field containing more than (approx) 500 characters, the FIRST time you access the field, it will give you the data you asked for. After that, the field will be EMPTY! I don't know why this happens, but you can verify it in debug mode. Just stop the code at the read and hover your mouse over the resultset("fieldname") or resultset!fieldname. The first time you do so you will see the contents of the field. Move the mouse off and back again and the field will be empty! I've gotten burned by this more than a few times. The only way around it is to save the field to a string variable IMMEDIATELY after the read (or moveNext, or whatever).


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.