SQL Update Statement, OleDbDataAdapter, and ms access 2007 - Database Discussion Boards

Then you are making an assumption which is not true.

The following are possible assumptions. There could be others.
- You are connecting to different databases.
- You are connecting to different servers.
- The data in VS is not commited.

Sorry what assumption am I making?

There is only one database
There is only one server which is the machine the program, VS and data are on.
The data in VS is not committed because it is already in the database. I am reading it not writing it.

Thanks for your time on this one Smile | :)

but I have decided to change over to MySql. I can't be doing with the vagaries of Microsoft.

TheComputerMan wrote:
Sorry what assumption am I making?

It would of course be your assumption, so there is no way for me to know what it is.

You have an impossible situation.
Since computers are deterministic that means that you made an assumption which is wrong.
You can't find assumptions by looking at code (although code might have been written with an assumption in mind.)

Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help?

/* This template is an aging report for private pay open balances*/

USE AR

SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR,
--CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE,
--CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE,
--DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE,

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [0-30 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [31-60 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [61-90 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [91-180 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181
AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [181-360 DAYS],

CASE
WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361
THEN SUM(ARO.TOTALAMOUNT)
ELSE 0
END AS [OVER 360 DAYS]

FROM dbo.tblAROPEN AS ARO

INNER JOIN dbo.tblCLMHDR AS CLM
ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH
AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH

WHERE ARO.INS_CO_AR IN ('0','000')
AND ARO.TOTALAMOUNT >0

GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH

--ORDER BY ARO.TOTALAMOUNT DESC

Assuming the fields you ultimately want are:
SUM_OF_TOTALAMOUNT
INS_CO_AR
[0-30 DAYS]
[31-60 DAYS]
[61-90 DAYS]
[91-180 DAYS]
[181-360 DAYS]
[OVER 360 DAYS]...

Then, use your query as a sub-select and do a select from there.
SELECT sum(SUM_OF_TOTALAMOUNT) as TotalAmount,
sum([0-30 DAYS]),
sum([31-60 DAYS]),
sum([61-90 DAYS]),
sum([91-180 DAYS]),
sum([181-360 DAYS]),
sum([OVER 360 DAYS]),
INS_CO_AR
from ( your query ) as DT
group by INS_CO_AR

Or something to that effect...

Do you mean to put all the code I have written into a sub query ????

I'd probably do it like this. The main difference is making each [xx-yy] field a subquery, and using BETWEEN rather than doubling up the datediff's.

select SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,
[0-30 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) <= 30),
[31-60 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 31 and 60),
[61-90 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 61 and 90),
[91-180 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 91 and 180),
[181-360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 181 and 360),
[over 360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) >= 361)
from <your table joins>
group by <your grouping>
order by <your ordering> desc

modified on Tuesday, June 21, 2011 3:23 PM

Hi guys,

I'm trying to understand an Update query generated from vs2010. I'm new to sql and all of the msdn answers are hard-coded, and I need some help to do this with variable data.

I've figured out my insert statement

DataSet dataChanges = new UserData();
                dataChanges.DataSetName = "dataChanges";

                dataChanges = this._userDataSet.GetChanges(DataRowState.Modified | DataRowState.Added | DataRowState.Deleted);

                this._dbConnection.ConnectionString = this._connectionString;
                this._dbConnection.Open();

                //insert new rows
                OleDbCommand command = new OleDbCommand("INSERT INTO tblUsers(ID_NAME, ID_PASSWORD, ID_RIGHTS, ID_JOBS)VALUES(?, ?, ?, ?)", this._dbConnection);
                command.Parameters.Add(this._userDataSet.tblUsers.ID_NAMEColumn.ColumnName,
                                        OleDbType.VarChar,
                                        this._userDataSet.tblUsers.ID_NAMEColumn.MaxLength, 
                                        "ID_NAME");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_PASSWORDColumn.ColumnName,
                                        OleDbType.VarChar,
                                        this._userDataSet.tblUsers.ID_PASSWORDColumn.MaxLength,
                                        "ID_PASSWORD");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_RIGHTSColumn.ColumnName,
                                        OleDbType.Integer,
                                        this._userDataSet.tblUsers.ID_RIGHTSColumn.MaxLength, 
                                        "ID_RIGHTS");

                command.Parameters.Add(this._userDataSet.tblUsers.ID_JOBSColumn.ColumnName,
                                        OleDbType.Integer,
                                        this._userDataSet.tblUsers.ID_JOBSColumn.MaxLength,
                                        "ID_JOBS");
                this._dbAdapter.InsertCommand = command;

_userDataSet is a typed dataset.
The vs generated UPDATE query looks like this:

//update rows;
                command = new OleDbCommand("UPDATE tblUsers SET ID_NAME = ?, ID_PASSWORD = ?," +
                                            "ID_RIGHTS = ?, ID_JOBS = ? WHERE ((ID_NAME = ?) AND" + 
                                            "((? = 1 AND ID_PASSWORD IS NULL) OR (ID_PASSWORD = ?))" +
                                            "AND ((? = 1 AND ID_RIGHTS IS NULL) OR (ID_RIGHTS = ?))" +
                                            "AND ((? = 1 AND ID_JOBS IS NULL) OR (ID_JOBS = ?)))", this._dbConnection);

All the msdn examples are hard coded, ...SET ID_NAME = "some static text string"...

1) I know the "?" are placeholders for the parameters, but which values go where?
2) After I build my Add, Update, and Delete commands, I call

this._dbAdapter.Update(dataChanges, "tblUsers");

which should execute all three commands based on the changes to the dataset, so I'd like to know how to do this programatically.

Kind of a big question, but I've been banging my head on the wall for the better part of a day.

[Insert Witty Sig Here]

In order to fill in the parameters you would use the same type of code from the INSERT statement generation:

C#

<br />
command.Parameters.Add(this._userDataSet.tblUsers.ID_NAMEColumn.ColumnName,<br />
                                        OleDbType.VarChar,<br />
                                        this._userDataSet.tblUsers.ID_NAMEColumn.MaxLength, <br />
                                        "ID_NAME");<br />
 <br />
                command.Parameters.Add(this._userDataSet.tblUsers.ID_PASSWORDColumn.ColumnName,<br />
                                        OleDbType.VarChar,<br />
                                        this._userDataSet.tblUsers.ID_PASSWORDColumn.MaxLength,<br />
                                        "ID_PASSWORD");<br />
<br />
// Etc., etc.<br />

You would change the OleDbType to the appropriate type for the parameter. And you change the last parameter to the name of the field. Does that make sense?

Hopefully this helps. I answered based on what I could gather from your questions.

Hello,

I need to insert the newly inserted/Updated/Deleted records into History table in following format.

ID - Field Name - Old Value - New Value - Date

So what would be the best way to achive this. Should i go for Trigger? Write insert statement in each insert/Update/Delete stored procedure?

Demo code would help more.

As described above,

There would be a table names "History" having columns,
ID - TableName - Field Name - Old Value - New Value - Date - AdminID [Id of user who is modifing field]

Now Table name and field name would be the values indicating the inserted/modified/delete field name. Oldvalue coloumn would be showing old value if any and new value would show current value.

So all the database table would have single History table.

Hope this made more clear. Thanks.

Thanks in advance.

modified on Thursday, June 16, 2011 12:46 AM

It all depends on what database you are using. You can use a trigger, or you can simply update your code to call another stored procedure to put an entry into the History table.

Clearly if you are deleting a record, you will need to make a copy of it before you actually delete it!!

The power is in your hands!!

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

What are the business requirements?

Will end-users see/use the data, a silent audit log,.. ?

What will it be used for?

Brad

Depends on other information.

Also you might want to verify if the user that originated the change must be tracked.

Are you sure you want to keep a record by column versus just keeping the entire row? Your solution adds complexity and your actual use cases and retention might not make it worthwhile.

Make sure you build in a process for archiving this History table on a periodic basis. It will grow by leaps and bounds in no time. Also, consider a method for turning this auditing off ... there may be a case where you have a large update job that you don't really want to audit.

Just a thought from someone who has been there ... Cool | :cool:

Good luck. Thumbs Up | :thumbsup:

If your using SQL Server 2005 or greater have a look at the TSQL Output clause[^]

this will give you access to the deleted and inserted tables that you normally get with a trigger.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Is it possible to get information about whether a child table has reference to a parent table through a certain record?
For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together.
If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.

modified on Tuesday, June 14, 2011 3:16 AM

It's a very simple query to return how many records in a child table match a parent's ID, however, you haven't really made it clear what you want to achieve.

How many child tables do you have? Why don't you want to query them individually?

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Well as I said, if possible, I want to get a tabular output showing which tables are related to a certain primary table and the related data in them all-together. Please refer to the second paragraph in my OP. Feel free to ask if I'm still not clear.

Have a read through this article on sp_help[^]

There is a section in the article that shows the tables a given table references.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

While there are system views which will identify the FKs between tables this is not a system issues but a data problem. You will need to craft a query that get the data you want the way you want it. There is no 'system' query/function etc that will service this requirement.

It is a fairly simple excercise using either sub queries or left joins depending on the required output.

Never underestimate the power of human stupidity
RAH

How can I select records from a single sql table that have common data in two of their columns.

Thanks,

i.e.

table customer {Name, Address, Phone Number)

How can I select all records from customer where address and phone number are the same even though the records are unique?

Ugly way that works:

select distinct a.* from Customer a, Customer b where a.Address = b.Address
and
a.[Phone Number] = b.[Phone Number]
and a.Name <> b.Name
order by Address, [Phone Number], Name

There must be a prettier way to do it...but at least it works in MS SQL. Poke tongue | ;-P

Build a man a fire, and he'll be warm for a day.
Set a man on fire, and he'll be warm for the rest of his life.

My Mu[sic]
My Films
My Windows Programs, etc.

You could try:

SELECT *
FROM customer
WHERE Phone = Address

My advice is free, and you may get what you paid for.

No, that will return customers who have the same information stored in the phone and address fields of a single record, rather than two different customers who have the same phone number and the same address...

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Okay, I see how I may have misinterpreted the requirement.

I thought he wanted to find mistakes where say a phone number was filled into the address field.

My advice is free, and you may get what you paid for.