Field size is wrong...

[Andrew Herrmann]

A few days ago, I posted a problem that I was having with a strange error. I've done a little more investigating and maybe what I've found will be enough for somebody to help me out...

I'm using MySQL and the following VB Code:

------- CODE HERE -------

selStmt = "SELECT * FROM customer;"
recset1.Open selStmt, cnn1, adOpenKeyset, adLockPessimistic
With recset1
.AddNew
.Fields("cust_name") = CustomerName
.Fields("contact_name") = ContactName
.Update
.Requery
.MoveLast
.Close
End With

------ CODE ENDS ---------

I was getting an error when the length of the data that I was adding to a field exceeded a certain size. I decided to look at the field type and field defined size. What I discovered was that the field defined size was set to the size of the largest existing field.

The field is defined as a varchar of length 40 -- This shows up properly on MySQL and in Access. I'm guessing that when I do my query, VB makes the fields only as large as they need to be.

Unfortunately, if I want to add a string that is larger than one of the existing strings, the program will bomb. Has anyone run into this problem before and/or know how to solve it?

Thanks..

[lychew]

Are you using a fixed size declaration for your Customername and contactname variable such as
Customername as string* 40

??

[Andrew Herrmann]

To answer both of the above (or is that below?) questions:

1) The error that I get is:

------
Run-time error '-2147217887 (80040e21)':

Errors occurred
------


2) The fields in the table are defined as varchar(40). The two strings that I'm trying to place into the table are defined as regular strings (dim str as string .. NOT dim str as string *40) ... However, I'll give this a shot.

[Andrew Herrmann]

For anyone that is interested in how I got around this problem:

I figured that when VB creates a recordset, it's going to try to conserve space by making the fields as small as possible (the length of the longest data item in a column). This isn't a problem if you're reading the data. It becomes a problem if you're trying to write data.

So, I went about trying to find a way to access the table without having VB create a recordset. The solution I came up with: Use the 'EXECUTE' method on the connection object with the desired SQL command. Something along the lines of:

------ CODE START -----

selStmt = "INSERT INTO customer SET cust_name = '" & _
CustomerName & "', contact_name = '" & _
ContactName & "';"

cnn1.Execute selStmt

------ CODE ENDS ------

I hope this helps anyone who might run into a similar problem.