Database

Re: how to find previos months

1-May-06 1:35

Use DATEADD[^]

For example:

SELECT DATEADD(MONTH, -1, TheDate), DATEADD(MONTH, -2, TheDate)
FROM SomeTable

Does this help?

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

D.N.1-May-06 2:36

D.N.

1-May-06 2:36

thanx

How to embed MSDE installation into my application installer

Hatem Darweesh30-Apr-06 13:24

Hatem Darweesh

30-Apr-06 13:24

Hi,
I create my installation project using VS.NET 2005 , then I add the merge modules to the project ! but it doesn't work !
- I also tried to customize the MSDE Internal properties but it didn't work !

any suggestions !!!

hatem++;

Re: How to embed MSDE installation into my application installer

Mike Dimmick2-May-06 2:24

Mike Dimmick

2-May-06 2:24

From what I recall, using the merge modules is now not recommended. Microsoft continue to ship the merge modules for applications already using them. See this article[^], specifically the comment at the end:

"PSS Recommendations Regarding MSDE Merge Modules

"Due to rapidly changing threats to the security of our IT systems, we strongly suggest that customers use the MSIs provided by Microsoft and not use merge modules. Using the MSIs provided by Microsoft allow the customer greater flexibility in responding to these threats and making sure that their MSDE embedded applications are secure."

You should consider launching the MSDE setup from your own setup launcher program. You may be able to do this with a custom action. See also Microsoft's MSDE 2000 Deployment Toolkit[^].

IIRC the recommended practice for using SQL Server 2005 Express Edition is to allow setup to create a single SQLEXPRESS instance if one is not already present. All applications should then share that instance, specifying the database file to use in the connection string. Microsoft have belatedly realised that this isn't terribly convenient for developers and are now discussing porting SQL Server 2005 Mobile Edition to Windows XP and Windows Server 2003, from Windows CE - it will be called 'SQL Server Everywhere Edition'. This will be implemented as a set of DLLs and will run in your application's process, rather than as a separate service. I suspect this is the long-term replacement for the Jet database engine used by Access.

Stability. What an interesting concept. -- Chris Maunder

Renaming a table through script

clint198230-Apr-06 1:37

clint1982

30-Apr-06 1:37

I would like to know how to rename a certain table through sql script.
I tried to do a stored procedure with the following commands where NewName represents the new table name and PreviousName represents the previous name :
UPDATE [TDM-DataSets].INFORMATION_SCHEMA.TABLES
SET TABLE_NAME = @NewName
WHERE TABLE_NAME = @PreviousName;

The error message i got is :
Ad hoc updates to system catalogs are not allowed.

Does anyone have an idea ?

Thanks,
Clint

Re: Renaming a table through script

Rob Graham30-Apr-06 4:44

Rob Graham

30-Apr-06 4:44

"Rename Table @PreviousName TO @NewName"

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Bulls make money, bears make money, pigs get slaughtered.
Jim Cramer

Re: Renaming a table through script

clint198230-Apr-06 4:58

clint1982

30-Apr-06 4:58

I put the line in the stored procedure and it doesn't work.
I am working with SQL Server.
it doesn't recognize the command rename.

Thanks,
Clint
-- modified at 11:00 Sunday 30th April, 2006

Re: Renaming a table through script

clint198230-Apr-06 5:05

clint1982

30-Apr-06 5:05

Thanks,
I found out :
EXEC dbo.sp_rename 'OldTableName', 'NewTableName'

string of GUID as Select Parameter in objectdatasource

DotNetDominator29-Apr-06 13:28

DotNetDominator

29-Apr-06 13:28

Hi,

I have one ObjectDataSource which has a Select query that requires Parameter @CompanyId. CompanyId is Guid and I want to compare it with a bunch of GUID. Can i pass those Guid programatically???

my Query is like..

select a.companyId, b.startdate, b.....
from a,b
where a.companyId IN (@CompanyID)

Re: string of GUID as Select Parameter in objectdatasource

goyal manish3-May-06 1:43

goyal manish

3-May-06 1:43

hi there
yes you can use this query but remember to pass @CompanyID as a comma separated string of ids.

Re: string of GUID as Select Parameter in objectdatasource

DotNetDominator3-May-06 4:43

DotNetDominator

3-May-06 4:43

Hi,
Thanks for the answer. I found one alternate way to do it as someone suggested me from Asp.net forum.
But, Your reply made me think about the simple mistake i was making. I had provided Comma between values but I did not provide quotes between value. Hence, each guid was compared to entire string i suppose. I'll try to put 'guid','guid' format as well.

Thanks

Collation

militiaware29-Apr-06 4:36

militiaware

29-Apr-06 4:36

in SqlExpress!!!

i created a db in VS.NET 2005, and i want to change the collation of the data base to another language, but it seems it is disabled and set to " <database defalut=""> ".

so what should i do?

Militiaware
Faris Madi

Re: Collation

Paul Conrad29-Apr-06 5:00

Paul Conrad

29-Apr-06 5:00

Have you taken a look at Collation Settings (SQL Server Express) [^] from SQL Server 2005 Express Edition Books Online at MSDN?

Paul

Connection

militiaware29-Apr-06 3:27

militiaware

29-Apr-06 3:27

i wanna create database on VS.Net 2006 SQLExpress.

an error ocuree telling me that the default setting of sqlserver can't connect remotly.
and i don't wanna connect remotly.

all services running...
and when i create new driver in Data Sources (ODBC) he is keep telling me that the SQL Server not exist.

so what i can do!?
it's urgent

Militiaware
Faris Madi

Jerry Hammond29-Apr-06 3:39

Jerry Hammond

29-Apr-06 3:39

Try using SQLExpress and VS.NET2005. There may be compatibility issues with version 2006...

Pictures of the menu available at the drive-thru

Colin Angus Mackay29-Apr-06 3:43

29-Apr-06 3:43

Why are you using ODBC when there is a built in .NET Data Provider for SQL Server?

militiaware29-Apr-06 3:48

militiaware

29-Apr-06 3:48

the SQL SERVER driver don't exist from the begining!!1

Militiaware
Faris Madi

Colin Angus Mackay29-Apr-06 4:06

29-Apr-06 4:06

militiaware wrote:
the SQL SERVER driver don't exist from the begining!!1

What?! That doesn't make sense! What SQL Server driver are you talking about? If you are referring to ODBC use the SQL data provider built into .NET (you can find it in the System.Data.SqlClient namespace)

Jerry Hammond29-Apr-06 5:15

Jerry Hammond

29-Apr-06 5:15

Maybe it doesn't come with the VS2006 version he says he is using. Wink | ;)

Pictures of the menu available at the drive-thru

Colin Angus Mackay29-Apr-06 5:23

How should my database like

29-Apr-06 5:23

Toasty0 wrote:
Maybe it doesn't come with the VS2006 version he says he is using.

Of course! I completely forgot about that! Roll eyes | :rolleyes:

LovelyHelp28-Apr-06 20:06

LovelyHelp

28-Apr-06 20:06

I am using sql 2000. I have a scenario is 1 user can buy many or 0 'package'.
and 1 'package' can have 0 or many users. If the user didnt buy the 'package' then he/she will not allow to use the package.

How should I create my database. How is my database look like.

Colin Angus Mackay28-Apr-06 21:08

28-Apr-06 21:08

In other words a many-to-many join

Here are the key columns in use and their tables

User: UserID
Package: PackageID
UserPackage: UserID, PackageID

The UserPackage table is the intermediate table in the many-to-many join. It has a compound key consisting of both sets of primary keys. When you need to join a user to a package you put a row in the UserPackage table.

Does this help?

LovelyHelp29-Apr-06 3:45

LovelyHelp

29-Apr-06 3:45

I am actually creating my project in .net.
ok now i have my sql command as like this

SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX

for XXXXXXXX what should I put? my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.

Colin Angus Mackay29-Apr-06 4:03

29-Apr-06 4:03

LovelyHelp wrote:
I am actually creating my project in .net

I never doubted it.

LovelyHelp wrote:
SELECT * FROM t_userPackage INNER JOIN t_package,t_user ON t_userPackage.packageID = t_package.packageID AND userID = '" & User.Identity.Name & AND t_package.packageID=XXXXXXXXX

Using SELECT * is a bad idea - if your data model changes the application that is built on top may break becuase of columns being returned that it did not expect, or columns that no longer exist. You should always list the columns you want - This also has the potential benefit of reducing network bandwidth necessary to transmit columns that will get discarded on reaching the application.

Also, when you join two or more tables together you will often get situations where there are columns with the same name. If you list columns explicitly you get the opportunity to rename columns.

The other point about this code is that it looks like it is susceptable to a SQL Injection Attack and you don't want your database to be compromised. See here on how to prevent SQL Injection Attacks[^]

LovelyHelp wrote:
for XXXXXXXX what should I put?my program is when you click on the list, it will then run the sql command to check whethere the specific user have register/pay for the specific package.

I don't know what to put for the XXXXX because it is your application and you haven't told me how it handles products. I'll assume that your application knows the productID (because that's what the code looks like it should know)

Also, from your code it looks like the user name is the key on the user table. (I would recommend keying on a number as it is more efficient than keying on a string)

Anyway your query is very simple:

SELECT COUNT(*) FROM t_userPackage WHERE userID = @userName AND packageID = @packageID

I'm assuming that if the user pays for a package that a join beween the user and package tables will exist. That being the case, the code above will return 0 if the user has not paid for the package. It will return 1 if the user has paid for the package.

I've also used parameters in the query as a part defence against SQL Injection Attacks. In your .NET code you can add parameters to your SqlCommand object like this:

cmd.Parameters.Add("@userName", User.Identity.Name);
cmd.Parameters.Add("@productID", productID);

If any of the assumptions I have made are wrong then you will have to explain your business logic and data model further because I'm using very limited information to work out how to answer your question.

Does this help?