String size???

by mapache at 2012-11-20 05:33:11

Hi All,

I’m trying to save a query to an access datafile but when i execute it gives me the following error:
[list]Exception calling "ExecuteNonQuery" with "0" argument(s): "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."
At C:\BD\Interface.ps1:95 char:44
+ $commandSvQ.ExecuteNonQuery <<<< ()
+ CategoryInfo : NotSpecified: (:slight_smile: , MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException[/list]

but when i do this query directly in access it does work.
my code:

$connectionSvQ = New-Object System.Data.OleDb.OleDbConnection
$connectionSvQ.ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source=$Db"
$commandSvQ = New-Object System.Data.OleDb.OleDbCommand
$commandSvQ.Connection = $connectionSvQ
$commandSvQ.CommandText = "INSERT INTO query (name_query, query) Values (‘$($objTextBoxQuery.Text)’ , &quot;$($saveQQ)")"

the query or the "$saveQQ"
[list]select u.shrt_nm, u.nm, c.id_comp, u.BIZ_UN_ABBR, u.DEPT from computers as c, [ODBC;DSN=dns;;uid=user_usr;pwd=password].table_name_oracle as u where = c.id_usr and u.BIZ_UN_ABBR like ‘BBBBB’ and u.DEPT like ‘DDDD’ [/list]

what should i do? is this string to big? cause i read somewhere that the size is almost 2 gb???
Any ideas?

Thanks in advance,
by DonJ at 2012-11-20 07:58:31
No, the System.String object type isn’t your problem. That’d be a completely different exception. It may well be the OleDb provider, in which case there’s nothing you can do. However, I will tell you that dropping a text box contents into a query like that can get problematic. If the text box gets any special characters - like a single quote or double-dash - it’ll bork your query. In SQL, we’d use a stored procedure to pass this kind of stuff more safely, and that’d probably solve your problem. Not sure if Access has anything like that?

Like, your $saveQQ definitely has single quotes. That’ll mess up the query unless you escape them.
by mapache at 2012-11-20 22:54:14
No, Access doesn’t do that :frowning: but i’m curious how can you do it with a stored procedure, pass by args ?.. Can you give me a light here? i’ll try to see if there’s a way in access
Thanks for the advice for the textbox,
many thanks for the answer;)

by ArtB0514 at 2012-11-21 07:09:39
I don’t work with Access, but when running into problems working with .NET objects like this, one of the first things I’ve learned to do is to double check the command string to make sure it was set to what I expected. Try printing out $commandSvQ.CommandText before you execute it to see if it really has what you expect.
by mapache at 2012-11-22 01:32:44
I tried and it seems okay so will not use this way. I’ll try to figured it out!!!
So i’m doing another thing, i’m adding into a txt file and then load from there :frowning:

Thanks for suggestion :wink: