Re: Getting the SUM of two field values - Database Discussion Boards

Which local datasource to use? [modified]

Rashar10-Jul-06 13:44

10-Jul-06 13:44

Hello,

Looking for some advice on choosing a local data source for a call reporting project that will have approximately 25 end users using it. I have currently developed the app in vb.net.

As of now, I am just using SQL Express edition and if I am deploying the project, I would like to be able to quickly access the data on the end users PC. Also, i'm finding that if I make a change in any of my table definitions (such as adding or removing a column), it seems to clear the data in my tables.

On the other hand, if I use MS Access, I get quick access to the tables if need be and I can make changes to the table without worrying about loosing data.

Any suggestions from a maintenance / performance perspective on the 2 sources?

Thanks,

Rashar

-- modified at 19:45 Monday 10th July, 2006

Re: Which local datasource to use?

god_bless_the_world10-Jul-06 13:56

god_bless_the_world

10-Jul-06 13:56

I think SQL server is a better option.

In faith there is enough light for those who want to believe and enough shadows to blind those who don't.
- Blaise Pascal

Error with INSERT statement

leckey10-Jul-06 11:09

leckey

10-Jul-06 11:09

I have an asp.net project in which I pass a part number, and a part description to the stored proc.

What I want to insert (in table Parts)in the end is the part number, and the part type ID. I get that part type ID from table PartType.ID.

So my syntax is correct in that Enterprise manager makes no fuss, but when I actually do the insert I get the error "Cannot insert the value NULL into column 'PartTypeId',
table 'PartsDB.dbo.Parts'; column does not allow nulls.
INSERT fails. The statement has been terminated." I am not sure what is wrong with the code here.

CREATE PROCEDURE [dbo].[UpdatePart_Step_1]<br />
@PartNumber varchar (15),<br />
@PartType varchar (60) <br />
<br />
AS<br />
declare @pt_id integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT  @pt_id = pt.ID FROM PartTypes pt <br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartType<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
END<br />
return 0<br />
GO

Does anyone see my problem?

Re: Error with INSERT statement

Colin Angus Mackay10-Jul-06 12:13

Colin Angus Mackay

10-Jul-06 12:13

I would guess that the SELECT @pt_id... is not returning any rows so @pt_id remains in its initial NULL state.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Getting the SUM of two field values [modified]

Nino_110-Jul-06 9:06

Nino_1

10-Jul-06 9:06

Hi,

I'm trying to get the sum of two fields:

declare @totalsum money

set @totalsum = (sum(fieldname1 + fieldname2))

the error message displays an invalid fieldname for both fields, i have the fieldnames spelled correctly but still get the error.

Any suggestions would be great.

Thanks,

Nino

Italy == World Champions

-- modified at 15:06 Monday 10th July, 2006

Re: Getting the SUM of two field values

Eric Dahlvang10-Jul-06 9:16

Eric Dahlvang

10-Jul-06 9:16

I'm not sure what you are trying to do, but if you want the sum of all (fieldname1 + fieldname2) values for every record in a particular table, then you need to supply the table name and change the word set to select.

declare @totalsum money

select @totalsum = (sum(fieldname1 + fieldname2))<code> from tablename</code>

But most likely, you want the sum of these two fields for a particular row, so you would need a where clause:

declare @totalsum money

select @totalsum = fieldname1 + fieldname2 from tablename<code> where primarykeyfield = 1</code>

or something...

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Getting the SUM of two field values

Nino_110-Jul-06 11:44

Nino_1

10-Jul-06 11:44

Ok, I should have posted the entire code block, I tried the method listed above with the rest of the SELECT statement and get an error message as follows.

Server: Msg 141, Level 15, State 1, Line 4
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Here is the entire code block:

declare @totalsum money

select @totalsum = (sum(d.amount + d.debit)), a.officer, d.amount,d.debit, e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype, a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow inner join e120 d on d.escrow = e.escrow and a.officer is not null order by e.open_date

Thanks

Nino

Re: Getting the SUM of two field values

Eric Dahlvang10-Jul-06 12:00

Eric Dahlvang

10-Jul-06 12:00

Yeah, you can't do that. If you want to sum all amount and debit columns for the entire table...then you wouldn't also pull back all the other fields. If you want to just add amount and debit together for each record, then you don't need a sum aggregate function.

select d.amount + d.debit as recsum, a.officer, d.amount,d.debit, 
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype, 
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow 
inner join e120 d on d.escrow = e.escrow and a.officer is not null 
order by e.open_date

declare @totalsum money
select @totalsum = sum(d.amount + d.debit)
from escrow e inner join a10 a on e.escrow = a.escrow 
inner join e120 d on d.escrow = e.escrow and a.officer is not null

or you could combine them like this - and every record will have a column with the TotalSum in it:

select (select sum(d2.amount + d2.debit)
     from escrow e2 inner join a10 a2 on e2.escrow = a2.escrow 
     inner join e120 d2 on d2.escrow = e2.escrow and a2.officer is not null) as totalsum,
d.amount + d.debit as recsum, a.officer, d.amount,d.debit, 
e.fullesc as ESCROW_AND_CITY_CODE, e.open_date, e.close_date, e.ftype, 
a.street, a.city, a.state, a.zip
from escrow e inner join a10 a on e.escrow = a.escrow 
inner join e120 d on d.escrow = e.escrow and a.officer is not null 
order by e.open_date

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Getting the SUM of two field values

Nino_111-Jul-06 7:04

Nino_1

11-Jul-06 7:04

Great! thanks so much.

Nino

using BETWEEN in sql2005

toink toink9-Jul-06 21:29

toink toink

9-Jul-06 21:29

I have an sql statement comparing dates in the Where clause.
This is my code:

[CODE]
CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2[/CODE]

The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?

Re: using BETWEEN in sql2005

Colin Angus Mackay9-Jul-06 23:39

Colin Angus Mackay

9-Jul-06 23:39

toink toink wrote:
The problem with the statement, is that, it doesn't show any records when one of the parameters is blank or null. How would it be able to show records giving value only to either of the parameters?

That is correct. A NULL in a set based operation always yields NULL.

toink toink wrote:
CONVERT(NVARCHAR, D.Spd_deldateans, 101) between @DATE1 AND @DATE2

Why are you converting Db.Spd_deldateans to an NVARCHAR?

If D.Spd_deldateanes is already a DATETIME or SMALLDATETIME then

WHERE
(D.Spd_deldateans >= @DATE1 OR @DATE1 IS NULL) AND
(D.Spd_deldateans <= @DATE2 OR @DATE2 IS NULL)

What this does is say the equivalent of
IF D.Spd_deldateans is greater than or equal to @DATE1 OR @DATE1 IS NULL
which evaluates to TRUE if @DATE1 is null which means that the >= operation is effectively overriden

how to use now() function in the Access when I write SQL

KSCsoft9-Jul-06 3:33

KSCsoft

9-Jul-06 3:33

how to use now() function in the Access when I write SQL?
look at this:
insert into table(LoginTime)values(now())
it's right?

KSCsoft

Re: how to use now() function in the Access when I write SQL

kumarprabhakar7410-Jul-06 1:14

kumarprabhakar74

10-Jul-06 1:14

insert into table(LoginTime)values(now())

Instead of this u can use following statement

insert into table(LoginTime)values(now)

Kumar

Re: how to use now() function in the Access when I write SQL

kumarprabhakar7410-Jul-06 1:15

kumarprabhakar74

10-Jul-06 1:15

insert into table(LoginTime)values(now())

Instead of this u can use following statement

insert into table(LoginTime)values(now)

Kumar

Data Source Reconciliation (Sybase)

SSLaks9-Jul-06 1:41

SSLaks

9-Jul-06 1:41

I have a table with data from five sources, and a column specifying which one.

I need to compare this data for missing rows (Present in one source but not another one), and for different values. I would like to do this entirely in SQL.

The database is in Sybase, in which the SQL syntax differs slightly from Access (eg, no semicolons)

please help me!!!

kicap8-Jul-06 17:29

kicap

8-Jul-06 17:29

I having problems with getting Front Page 98’s hover buttons to function. So i need your help.i hope anyone can solve my problem qickly.please help me.i really need your help.this is the code.
Applet codes of my web page is as follow:

<applet code="fphover.class" codebase="./" width="120" height="24">
<param name="color" value="#000080" />
<param name="hovercolor" value="#0000FF" />
<param name="textcolor" value="#FFFFFF" />
<param name="text" value="C Programming" />
<param name="effect" value="glow" />
<param name="url" valuetype="ref" value="http://metalab.uniten.edu.my/~azhana/indexCProg.html" />

thank a alot to whom reply it....

Re: please help me!!!

Paul Conrad8-Jul-06 19:11

Paul Conrad

8-Jul-06 19:11

Uhhh, what does this have to do with SQL / ADO / ADO.NET?

Re: please help me!!!

Eric Dahlvang10-Jul-06 3:26

Eric Dahlvang

10-Jul-06 3:26

Try this forum...[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

how to update dataset after session cache?

Ed 548-Jul-06 12:40

Ed 54

8-Jul-06 12:40

I'm teaching myself ADO .NET, and am struggling with how to programmatically update a dataset after I have cached it and recovered it in an ASP .NET web app.

I want to use a DataAdapter to populate a DataSet, cache the DataSet in Session, then recover the DataSet in a different page, work on it, and update the database. I can populate the DataSet, cache it, recover it, and make changes. However, I cannot get the adapter.update(dataset, "table") command to work properly. Have tried multiple solutions, with multiple errors revolving around lack of INSERT commands, uninitialized connection strings, etc.

What programmatic steps must I take in a new page in order to reconnect that dataset with the origin database? Do I have to instantiate a new DataAdapter, build new commands, open a new connection, etc? Can I cache and recover any of the relevant objects (DataAdapter, SqlCommand, commandBuilder) and use them?

Here's the code to populate and cache the DataSet. It works fine (but may be excessive):

using (SqlConnection connection = new SqlConnection(connStr))
{
    try
    {
        SqlDataAdapter pointAdapter = new SqlDataAdapter();
        pointAdapter.TableMappings.Add("Table", "UserPoints");
        SqlCommand pointCommand = new SqlCommand();
        pointCommand.CommandText = "SELECT * FROM Points WHERE userID = @userID";
        pointCommand.Parameters.AddWithValue("@userID", userIDstr);
        pointCommand.Connection = connection;
        pointCommand.CommandType = CommandType.Text;
        pointAdapter.SelectCommand = pointCommand;
        SqlCommandBuilder pointCmdBuilder = new SqlCommandBuilder(pointAdapter);
        pointAdapter.Fill(ds);
        Session["UserDS"] = ds;

And here's what I'm trying to do in a different page:

dataset = (DataSet)Session["UserDS"];
// ... do some stuff to rows
using (SqlConnection connection = new SqlConnection(connStr))
{
    try
    {
        SqlDataAdapter pointAdapter = (SqlDataAdapter)Session["pointAdapter"];
        pointAdapter.Update(dataset, "UserPoints");

With this code I get a "connection string uninitialized" error. But I've gotten several different errors, enough to convince me I'm going about this the hard way. Is there a standard pattern for this sort of thing, or a tutorial someone can point me to?

Re: how to update dataset after session cache?

Igor Sukhov9-Jul-06 0:24

Igor Sukhov

9-Jul-06 0:24

In your second code you're creating the instance of the SqlConnection class (initialised with the connection string) and never use it due to the fact that SqlDataAdapter object is restored from the session state.

The standard aproach in hadling data with the ADO.NET is that simple:

*Create and open connection
*Populate and update data
*Close connection

There's no reason to store SqlConnection, DataAdapter classes because you'll not benefit from it - just recreate them when you're need them. Another hint to you - no DataAdapter neither SqlConnection is serializable therefore they're not designed to be stored persistently in the session state. To prove it right - change the session state model from "InProc" to "StateServer" or "SqlServer" and you'll get exception while putting this objects into state.

To sum up that I wrote above: don't try to cache anything from the data. When you decide to cache the data in the session state - make sure it not going to exhaust web server memory as for the every user session there'll be and cached dataset in the memory.

Best regards,

-----------
Igor Sukhovhttp://sukhov.net

Re: how to update dataset after session cache?

Ed 5410-Jul-06 8:35

Ed 54

10-Jul-06 8:35

Thanks Igor. As you suggested, I ditched the attempt to store SqlConnection and DataAdapter classes, and created a new insance when I needed them. It works fine now.

I am still caching the dataset, because it is relatively small and I think I'd take a bigger hit from loading it every time I need it (which is often). But I'll watch out for this as I scale my app.

About DataType in the access

KSCsoft8-Jul-06 5:04

KSCsoft

8-Jul-06 5:04