Problem with SqlParameterCollection - Looking for advice

I have a site which works fine with an Access DB, I now want to upsize it to use a SQL DB... I have changed my OleDB Commands etc... And Used SqlCommands and everything seems fine..

Only one problem I have and its really stumped me... I always used SQLDataSource for the app even with the AccessDB, as I knew I would be upsizing at some point. We I have the below SQLDataSource

1 <asp:SqlDataSource ID="RandomBusinessDataSource" runat="server"
2 ConnectionString="<%$ ConnectionStrings:SQLDataBaseConnectionString%>"
3 SelectCommand="SELECT TOP (1) intBusinessID, txtBusinessName, intSubCatID, intCatID, txtTelNo, txtPostCode, txtWebAdd, txtReferred, bitBusinessShow, txtLong, txtLat, memBusinessDesc, intIPAddress, bitBusinessPaid FROM tblBusiness WHERE (intBusinessID = @.Param1)" OnSelecting="RandomBusinessDataSource_Selecting">
4 <SelectParameters>
5 <asp:Parameter Name="Param1" Type="Int32" />
6 </SelectParameters>
7 </asp:SqlDataSource>

Very Simple... I have my function that is creating my random number and returning a VALID intbusinessID ... And now I have this OnSelecting event

1 Protected Sub RandomBusinessDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
2 e.Command.Parameters("Param1").Value = MyNewRandomNum
3 End Sub

But when I try and run the page I get the following error (Don't forget this page IS working using the SQLDataSource and an Access DB??)

An SqlParameter with ParameterName 'Param1' is not contained by this SqlParameterCollection.

I am a bit confused?? why would it say this now... Yet it works fine if I just change the connectionString to the access DB?? Any helps very much appreciated... Thanks

Try changing "Param1" to "@.Param1" in the parameter declaration and in the code where you set the value.|||

Hi TGnat ... thanks for the help...

Unfortunately that gave the same error - Although I did manage get sort it, a chap I know at Cre8asites gave me the answer

( thread herehttp://www.cre8asiteforums.com/forums/index.php?showtopic=44722&hl= )

I had to use the following and it worked fine...

e.Command.Parameters(0).Value = MyNewRandomNum

|||Hey TGnat ... Actually you were pretty much right in the end - I printed out the parameter name and it was @.Param1 ... Although I just had the leave the selectparameter name as Param1 ... Weird hey!!... Thanks again

problem with SQL Server Migration Assistant for Access

I'm trying to upsize a client's Access 2003 database to SQL 2005.
I've been trying to use MS's SQL Server Migration Assistant for Access (which
seems to work better than the Access 2007 upsizing wizard), but I've got a few
errors that I'm stumped on.
Out of maybe 40 tables, all copied over and linked, except for issues with 2
tables.
One table showed erors when copying the data, none of the data made it.
Another table copied 95% of the rows, but failed on 5%.
The error log showed this for the table that completely failed:
--
Preparing table theDb.[Job_Budgets]...
Migrating data for the table Job_Budgets from the database theDb...
Error occurred during data migration.
See the log for the detailed information.
Errors: Error occurred during data migration.
See the log for the detailed information.
Data migration complete: 0 rows of 19394 migrated.
--
I've looked at the only .log files created during this time, and none give me
any useful information as to why the data failed to be copied.
There is a primary key, no duplicate records, etc.
How can I determine where the problem lies?
--
Thanks in advance, Les CaudleHello Les,
To isolate the issue, I suggest that you try to imort the table directly by
using Import wizard in management studio. If you still encounter issues,
please provide me with a sample Access database so that I could reproduce
on my side.
Please remove "online" from my displayed email address. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter - yes, that is giving me more info, and I'm able to resolve issues with
one table.
However, on a 2nd table, I'm getting this error msg:
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
returned invalid data for column '[!BulkInsert].Dep1_%'. ".
(SQL Server Import and Export Wizard)
Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
required = no, decimal places = 2.
So, somewhere in the table this field has some invalid values.
How can I find those and correct for them? Hard to write a query to locate
something invalid (which should never be there to begin with. You'd think
compact and repair would deal with those values).
Thanks, Les Caudle
On Fri, 26 Oct 2007 05:47:41 GMT, petery@.online.microsoft.com ("Peter
Yang[MSFT]") wrote:
>Hello Les,
>To isolate the issue, I suggest that you try to imort the table directly by
>using Import wizard in management studio. If you still encounter issues,
>please provide me with a sample Access database so that I could reproduce
>on my side.
>Please remove "online" from my displayed email address. Thank you.
>Best Regards,
>Peter Yang
>MCSE2000/2003, MCSA, MCDBA
>Microsoft Online Community Support
>==================================================>Get notification to my posts through email? Please refer to
>http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>ications
><http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>where an initial response from the community or a Microsoft Support
>Engineer within 1 business day is acceptable. Please note that each follow
>up response may take approximately 2 business days as the support
>professional working with you may need further investigation to reach the
>most efficient resolution. The offering is not appropriate for situations
>that require urgent, real-time or phone-based interactions or complex
>project analysis and dump analysis issues. Issues of this nature are best
>handled working with a dedicated Microsoft Support Engineer by contacting
>Microsoft Customer Support Services (CSS) at
><http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>==================================================>This posting is provided "AS IS" with no warranties, and confers no rights.|||Less,
The problem is the name of the column. You cannot use % in a column
name.
Robert
On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
wrote:
> Peter - yes, that is giving me more info, and I'm able to resolve issues with
> one table.
> However, on a 2nd table, I'm getting this error msg:
> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
> (SQL Server Import and Export Wizard)
> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
> required = no, decimal places = 2.
> So, somewhere in the table this field has some invalid values.
> How can I find those and correct for them? Hard to write a query to locate
> something invalid (which should never be there to begin with. You'd think
> compact and repair would deal with those values).
> Thanks, Les Caudle|||Robert - yes, very strange column name, but that actually was not the problem.
Apparently Access, even with compact and repair, will allow invalid values
(garbage) to to stored.
I cleared out the ones I could see by sorting the column asc desc by hand, and
resolved the problem.
One more reason to get my client away from storing his data in Access!
BUT - I wish there were an automated way to located and fix the invalid data in
his database. I found invalid data all over the place, but had to remove it all
by hand.
Regards, Les Caudle
On Fri, 26 Oct 2007 14:32:35 -0700, raibeart <raibeart@.gmail.com> wrote:
>Less,
>The problem is the name of the column. You cannot use % in a column
>name.
>Robert
>On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
>wrote:
>> Peter - yes, that is giving me more info, and I'm able to resolve issues with
>> one table.
>> However, on a 2nd table, I'm getting this error msg:
>> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
>> error has occurred. Error code: 0x80004005.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
>> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
>> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
>> (SQL Server Import and Export Wizard)
>> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
>> required = no, decimal places = 2.
>> So, somewhere in the table this field has some invalid values.
>> How can I find those and correct for them? Hard to write a query to locate
>> something invalid (which should never be there to begin with. You'd think
>> compact and repair would deal with those values).
>> Thanks, Les Caudle|||Hello Les,
I think there is no automated method way to clean invalid data currently.
Please rest assured that your feedback on this is routed to the product
team. Thank you.
Best Regards,
Peter Yang
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security