Office 2003 Web Service Reference Tool fails on ReportingService

I am trying to add a web reference to an Access 2003 database form using the
latest version of the Web Services Toolkit. When I try to add the reference
it runs for a while and then blows up with the following message.
Cannot Create Class.
The Web Service References Tool could not generate the requested code. Any
changes that were made to your project have been rolled back.
Any help would be appreciated.this newsgroup is for SQL Server Reporting Services. You will get better
responses if you post this in one of the Access newsgroups.
"TMesich" <TMesich@.discussions.microsoft.com> wrote in message
news:6EB2929F-C179-4348-B931-E15451985189@.microsoft.com...
>I am trying to add a web reference to an Access 2003 database form using
>the
> latest version of the Web Services Toolkit. When I try to add the
> reference
> it runs for a while and then blows up with the following message.
> Cannot Create Class.
> The Web Service References Tool could not generate the requested code.
> Any
> changes that were made to your project have been rolled back.
> Any help would be appreciated.

Of Multiple Parameters, SqlDataSource and Text boxes

Hi,
Hope if someone can help me here. Keep in mind I an fairly new to .NET and SQL and am learning to break my MS Access habit :)

I have a web form that is using a SqlDataSource and a FormView control. In addition to this I have 2 text boxes. What I am trying to do is display results in the FormView based on what a user types into one of the Text Boxes (one or the other…Not both)

The SELECT statement in the SqlDataSource looks like this in concept.

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (Field2 IS NULL)
OR (Field2 = @.Field2) AND (Field1 IS NULL)

I have the two text boxes pointing at the parameters (@.Field1 and @.Field2) so in theory I would expect that when a user populates one of the text boxes and clicks a button to databind the FormView it would display a record matching that criteria…. But it's not all I get is a blank/missing FormView.

I tried different variations on the SQL statement and tried using = '' instead of IS NULL but still the same results.
However, if I populate one text box with a value that I know is not in my table and populate the other with a value of which I know exists in my table is…It works.
What am I missing?

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (Field2 IS NULL)
OR (Field2 = @.Field2) AND (Field1 IS NULL)

Should be:

SELECT Field1, Field2, Field3, Field4
FROM dbo.MYTABLE
WHERE (Field1 = @.Field1) AND (@.Field2 IS NULL)
OR (Field2 = @.Field2) AND (@.Field1 IS NULL)

However, I suspect that there are other issues you are having, as your original statement would have only worked if you actually had a NULL in one of your search fields. Please copy and paste the SqlDataSource control from the .ASPX page, as I'm guessing that it's not really a logical problem, more like an oops, I knew that problem.

|||

Opps yeah...That was a typo on my part. Well the field in the text box is blank and I have "ConvertEmptyString ToNull" in the parameters advanced properties set to true. But, your right it is behaving as though the query not seeing null.

Is there syntax to set the default value of the parameter to null in the Configure Data Source/Query Builder pop up ? (Using VS 2005)

|||

Here is the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WWID, WONumber, WorkWeek, ID FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (@.WONumber = '') OR (@.ID = '') AND (WONumber = @.WONumber)" EnableCaching="True">

<FilterParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID" PropertyName="Text" />

</FilterParameters>

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID" PropertyName="Text" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text" />

</SelectParameters>

</asp:SqlDataSource>

|||

Remove the filter parameters section, it isn't needed, and would cause some queries to fail when they shouldn't.

Also, you either need to change ConvertEmptyStringsToNull (Real property name should be close) to false, OR change the ='' parts of your query to IS NULL, since the empty string parameters are being converted to NULL. That should take care of it for you.

|||

OK have this now and still not working. Could it have something to do with VB passing NULL as Nothing?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (WONumber IS NULL) OR (ID IS NULL) AND (WONumber = @.WONumber)">

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID"

PropertyName="Text" Type="Int32" DefaultValue="" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text"

Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

|||

took out the default values...same result

OK have this now and still not working. Could it have something to do with VB passing NULL as Nothing?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:QualityConnectionString %>"

SelectCommand="SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) AND (WONumber IS NULL) OR (ID IS NULL) AND (WONumber = @.WONumber)">

<SelectParameters>

<asp:ControlParameter ControlID="TextBox1" Name="ID"

PropertyName="Text" Type="Int32" />

<asp:ControlParameter ControlID="TextBox2" Name="WONumber" PropertyName="Text"

Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

|||

I'm pretty sure you have it right. Try making a new page, then copy/paste the SqlDataSource on it. Drag 2 NEW textboxes on the new page, and drag a NEW gridview on the page. Configure the grid to display data from the sqldatasource, and run the page and see if it works.

|||Tried from scratch same result. The empty text boxes must not be passing a Null. I found a work around by setting the default paramater values to a negative integer and leaving out the @.parameter IS NULL ...But this is driving me insane!|||

try this instead:

SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE ((ID = @.ID) OR (@.ID IS NULL)) AND ((WONumber = @.WONumber) OR (@.WONumber IS NULL))

The parameters should have no defaults, and convertemtystringtonull should be true.

|||

That didn't work either. Setting ConvertEmptyString ToNull toFalse and the below SQL statement works though. I thought I tried that before but must have missed a parameter property setting.

SELECT WONumber, ID, WWID, WorkWeek FROM dbo.WorkOrderInfo WHERE (ID = @.ID) OR (WONumber = @.WONumber)

This is odd because I have another page with a reportviewer that uses a table adaptor etc. and several text boxes for passing parameters to a stored procedure to generate data for the report. Any one of them can be null and it works great. Not sure what is going on here though.

|||I also changed to parameter type to "Empty" (default) not int32...|||

Agh!! I was wrong it actualy does not work the way I wanted. Seems the empty string (blank text box) for the WONumber parameter thinks it is a zero too. So for any WONumber that is 0 and the text box being empty, it will retireve that record.

Theonlyway I can get to work is a stored procedure.

CREATE PROCEDURE dbo.SelectWO(@.ID int,
@.WONumber int )
AS
If @.ID = ''
SET @.ID = Null
If @.WONumber = ''
SET @.WONumber = Null
SELECT ID, WWID, WONumber,FROM dbo.WorkOrderInfo
WHERE (ID = @.ID AND @.WONumber IS NULL) OR (WONumber = @.WONumber AND @.ID IS NULL)

OR( @.ID IS NULL AND @.WONumber IS NULL)

ORDER BY ID
GO

Had to leave ConvertEmptyStringtoNull to false and convert in my SP. setting it to True in VS breaks the SP.

Anyone ...try to build a web form with 2 search parameters getting thier values from to text boxes see if you can get it working cause I could not.

|||

/sigh

CREATE PROCEDURE dbo.SelectWO(@.ID int,
@.WONumber int )
AS
SELECT ID, WWID, WONumber,FROM dbo.WorkOrderInfo
WHERE (ID = @.ID AND @.WONumber IS NULL) OR (WONumber = @.WONumber AND @.ID IS NULL)OR( @.ID IS NULL AND @.WONumber IS NULL)
ORDER BY ID
GO

Is the SP you want. Change the ConvertEmptyStringToNull's on both parameters to true, and explicitly set the parameter types to "int"/"integer". Also make sure you set the sqldatasouce property "CancelOnNullParameter" to false.

It'll work.

The problem is that yes, if you leave ConvertEmptyStringToNull false, the paramters will be converted to 0. Your checks IF @.ID='' will never be true, but @.ID is an int, and int's can never be an empty string. They can only be an integer or NULL.

ODBC--call failed

I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:

>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

ODBC--call failed

I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
records in the linked table they come up fine, but after about 30
seconds it dings and i get the pop up ODBC--call failed. If i close the
form and reopen it, the records are there again, but the connection
drops again in another 30 seconds.
I'm stumped here because my connection does work, it just keeps
dropping.
Any help or ideas would be greatly appreciated.
Thanks!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!What happens if after you open the form, you go to the last
record and then go about and do whatever with the form? Does
the same thing still happen?
ODBC call failed can happen for a lot of different reasons.
A good way to track them down is to turn on ODBC tracing to
look for specific errors. You would want to make sure to
turn it back off after you get the error as it will really
slow things down.
To turn on tracing, go to the ODBC Data Source Administrator
applet and go to the tracing tab. Just click on start
tracing now and note the location for the trace file. After
you hit the error, go back and click on the stop tracing now
button. Then you can go to the trace file and see what other
information you can get out of the trace file.
-Sue
On Thu, 13 Jan 2005 13:19:15 -0800, vespo
<anonymous@.devdex.com> wrote:

>
>I have an Access 97 database (using Jet 4.0) connecting to a SQL 2000
>via ODBC - the OS is Win XP Pro SP2. When i open a form to display the
>records in the linked table they come up fine, but after about 30
>seconds it dings and i get the pop up ODBC--call failed. If i close the
>form and reopen it, the records are there again, but the connection
>drops again in another 30 seconds.
>I'm stumped here because my connection does work, it just keeps
>dropping.
>Any help or ideas would be greatly appreciated.
>Thanks!
>Vicki
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!|||Thanks Sue!
That ODBC Trace really helped! It turns out it is an issue with Access
97 where even if you bracket an alias fieldname in a SQL View, it
considers reserved words as reserved words and can't bring them across.
I was trying to enable my client the usage of their old Access 97 forms
and reports by linking to the data i migrated into SQL, so to do that i
had created a view that mimicked their old fields names ("Phone Number",
"Date Updated", etc) and had no problems staying connected when i tried
it on my server (which has Access 2000) but unfortunately my client
isn't ready to upgrade their Access version.
Oddly though -- reports will work in Access 97 using the reserved field
name, it's just the forms and opening the table directly which caused
the ODBC to drop.
Thanks again!
Vicki
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table from
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for the
recordsource, it works fine. What am I doing wrong?
You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate controls.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for the
>recordsource, it works fine. What am I doing wrong?
|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subform
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing the
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table f
rom
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls
.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for th
e
recordsource, it works fine. What am I doing wrong?You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table
from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate control
s.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for t
he
>recordsource, it works fine. What am I doing wrong?|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subfor
m
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
>|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing th
e
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:
>