SqlDataAdapter CommandText Size limitations ?

G

gerry

I have an SqlDataAdapter setup with the following insert command :

insert into SomeTable
([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE]
,[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.

am i wrong in this ? is there a work around ?

any input greatly appreciated.

gerry
 
D

David Browne

gerry said:
I have an SqlDataAdapter setup with the following insert command :

insert into SomeTable
([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE],[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.
Click to expand...

This has nothing to do whith the length of your SQL.

The error means that you failed to bind a parameter called "@LINE_TYPE".

David
 
J

Jon Skeet [C# MVP]

gerry said:
I have an SqlDataAdapter setup with the following insert command :

insert into SomeTable
([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE]
,[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.

am i wrong in this ? is there a work around ?
Click to expand...

I suspect that isn't the problem - I suspect it's that you're missing
the @LINE_TYPE parameter (as the exception suggests).
 
G

gerry

well , these answers were close - the parameter value was there ( all params
are mapped to source table columns ) , the source column name was set
incorrectly for a few renamed columns ( 'special' characters in source names
changed to underscores in the destination ). chalk this one up to another
crappy error message - the exception that should have been raised was
"SourceColumn 'xxx' not found in source table" or something to that effect.




Jon Skeet said:
gerry said:
I have an SqlDataAdapter setup with the following insert command :

insert into SomeTable
([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE]
,[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.

am i wrong in this ? is there a work around ?
Click to expand...

I suspect that isn't the problem - I suspect it's that you're missing
the @LINE_TYPE parameter (as the exception suggests).
Click to expand...
 
W

William \(Bill\) Vaughn

I would have approached this problem using a Command object so the
formatting would not have been an issue. I would also consider using an SP
to do this (with the same result).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

gerry said:
well , these answers were close - the parameter value was there ( all params
are mapped to source table columns ) , the source column name was set
incorrectly for a few renamed columns ( 'special' characters in source names
changed to underscores in the destination ). chalk this one up to another
crappy error message - the exception that should have been raised was
"SourceColumn 'xxx' not found in source table" or something to that effect.




([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE],[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
Click to expand...
Click to expand...
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.

am i wrong in this ? is there a work around ?
Click to expand...

I suspect that isn't the problem - I suspect it's that you're missing
the @LINE_TYPE parameter (as the exception suggests).
Click to expand...
Click to expand...
 
G

gerry

i don't understand how a command object would have made any difference, this
is part of a generalized/automated process which includies DataAdapter
generation so the bad source column name would have been there either way as
the problem was with the generator. maybe i am not getting the gist of this
comment ?

SP was the planned route but due to limitations encountered with SqlServer's
ability to invoke remote ( nonSqlServer) stored procedures an intermediary &
external process was required.

Assuming that you are referring to our end to end process and its intended
result with no knowledge of anything other than this da exception and its
cause , I'd be interested to hear you definition of 'this problem' and 'the
same result'.

thanks for the response
gerry


William (Bill) Vaughn said:
I would have approached this problem using a Command object so the
formatting would not have been an issue. I would also consider using an SP
to do this (with the same result).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

gerry said:
well , these answers were close - the parameter value was there ( all params
are mapped to source table columns ) , the source column name was set
incorrectly for a few renamed columns ( 'special' characters in source names
changed to underscores in the destination ). chalk this one up to another
crappy error message - the exception that should have been raised was
"SourceColumn 'xxx' not found in source table" or something to that effect.
Click to expand...
([_ID],[ACCOUNT],[DESCRIPTION],[INACTIVE],[LINE_TYPE],[BAL_TYPE],[ACCT_TYPE],[SORT_GROUP],[POST_TYPE],[COST_CNTR],[FREIGHT],[PROD_LINE_CD],[TEST_ACCOUNT
]) values
Click to expand...
Click to expand...
(@_ID,@ACCOUNT,@DESCRIPTION,@INACTIVE,@LINE_TYPE,@BAL_TYPE,@ACCT_TYPE,@SORT_
GROUP,@POST_TYPE,@COST_CNTR,@FREIGHT,@PROD_LINE_CD,@TEST_ACCOUNT)

which generates the following error :

System.Data.SqlClient.SqlException: Prepared statement '(@_ID
nvarchar(11),@ACCOUNT nvarchar(11),@DESCRIPTION nvarchar(3' expects
parameter @LINE_TYPE, which was not supplied.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

since I have no problem with this in other tables I am assuming that there
is a maximum length for the prepared statements that is being exceeded
I have not found any documentation either supporting or refuting this
assumption.
At just over 300 bytes this seems like an extremely low limit.

am i wrong in this ? is there a work around ?

I suspect that isn't the problem - I suspect it's that you're missing
the @LINE_TYPE parameter (as the exception suggests).
Click to expand...
Click to expand...
Click to expand...
 
K

Kevin Yu [MSFT]

Hi Gerry,

Based on the discussion above, I'd like to know if this issue has been
resolved yet. Is there anything that I can help. I'm still monitoring on
it.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
G

gerry

hi kevin ,
yes - a couple of posts back i explained that it was a problem with
incorrect source column names - ie. the source column name was set to
'some_column' when in it should have been 'some.column'
the exception raised was very misleading stating that there were not enough
parameters supplied when it really should have said that the specified
source column was not found.
the other thing that sent me off down the wrong path was the truncated
prepared statement text that is included in the exception description which
suggested that some statement size limit might have been exceeded.

thanks for responding - everything is now peachy.
gerry
 
Top