Select Query from SQL to VB.NET - using SUM

[VBNewbie]

Hi people,

I have a question that nobody as of yet has been able to answer for me.

I have an SQL query which is:

SELECT sum(TimeSpent) AS TimeSpentDWR
FROM DWRWork
WHERE DWRID = @DWRID

When I provide the parameter, it sums the timespent field OK.

Now in VB.Net (2003) I am unable to do this. I have created a DataAdapter
with a select command of above, and generated the DataSet.
Within my form load, I have the line:
SqlSelectCommand11.Parameter("@DWRID").Value = lblDWRID.text

When I try to run the form, I get "System Error" from debug, but if I run
the form without debugging, I then get the error within the program telling
me that the parameter @DWRID has not been provided.

Does anybody have any solution to this? I need to sum two fields up onto
one form, and at the moment I can't even get one to work - I'm new to
programming and this is my first project, so is getting a bit depressing at
the moment as it's been a problem for a few months!

Thanks for your help.

[John Spiegel]

Hi,

Two possibilities come to mind offhand:

1) Have you added the parameter itself (as in using
SqlSelectCommand11.Parameters.Add())?
2) What is the datatype of your parameter? It might (though somewhat
unlikely) be something along the lines that no type was specified or it's
otherwise expecting character data, but SQL Server's parameter type should
be int. It's probably not this specific issue, but maybe that will give a
little idea...

- John

"VBNewbie" <VB******@discussions.microsoft.com> wrote in message
news:68**********************************@microsof t.com...

Hi people,

I have a question that nobody as of yet has been able to answer for me.

I have an SQL query which is:

SELECT sum(TimeSpent) AS TimeSpentDWR
FROM DWRWork
WHERE DWRID = @DWRID

When I provide the parameter, it sums the timespent field OK.

Now in VB.Net (2003) I am unable to do this. I have created a DataAdapter
with a select command of above, and generated the DataSet.
Within my form load, I have the line:
SqlSelectCommand11.Parameter("@DWRID").Value = lblDWRID.text

When I try to run the form, I get "System Error" from debug, but if I run
the form without debugging, I then get the error within the program
telling
me that the parameter @DWRID has not been provided.

Does anybody have any solution to this? I need to sum two fields up onto
one form, and at the moment I can't even get one to work - I'm new to
programming and this is my first project, so is getting a bit depressing
at
the moment as it's been a problem for a few months!

Thanks for your help.

[VBNewB]

Hi John,

Thanks for your reply.

I sat down and figured this out yesterday afternoon (well I think I've
figured it).

The error wasn't coming from the sum - the error was coming from when the
dataAdapters fill.
I generated a new dataSet for the sum tables, and then created a new sub in
the code called "RefreshTime" with the parameters matching the label text
showing the ID fields, and then clearing the dataSet, and filling the two sum
DataAdapters.

Instead of kicking this off at form load time (which was causing the
problem), I run this sub when the child dataGrid row is changed.

I think the problem was because it was running at form load time, the
parameters were looking at the label text for their value - these hadn't been
filled yet.

As said earlier, not sure whether this is the correct way, but it's working!!


"John Spiegel" wrote:

Hi,

Two possibilities come to mind offhand:

1) Have you added the parameter itself (as in using
SqlSelectCommand11.Parameters.Add())?
2) What is the datatype of your parameter? It might (though somewhat
unlikely) be something along the lines that no type was specified or it's
otherwise expecting character data, but SQL Server's parameter type should
be int. It's probably not this specific issue, but maybe that will give a
little idea...

- John