Database Discussion Boards

Re: how to update,delete from asp.net? [modified]

Oracle Friend11-Sep-07 23:49

Oracle Friend

11-Sep-07 23:49

Thanks rohitsrivastava this is very Advance code ,but at the moment am using access database .I want only the Syntax or the command to delete,update.

I remmber in the past I was writing like this but am not soure about the Syntax

updatecode="update EMP set f_name Val&"(first_name.txt)"&''&"where id=Val&"(txtid.txt)"&"

-- modified at 8:07 Wednesday 12th September, 2007

yasser

Re: how to update,delete from asp.net?

Pete O'Hanlon12-Sep-07 3:40

Pete O'Hanlon

12-Sep-07 3:40

How do you think this will work? First of all, you are connecting to SQL Server here, secondly the strupdate command is wrong. Finally, always use parameterized queries as they are much better.

Deja View - the feeling that you've seen this post before.

Week of the month

kibromg11-Sep-07 1:40

kibromg

11-Sep-07 1:40

Hi All,
How do i get week of a month?
somthing like ....

Datename(week,getdate())

Can you please help?

Thanks
kibrom

Re: Week of the month

Blue_Boy11-Sep-07 3:22

Blue_Boy

11-Sep-07 3:22

select datediff(week,'2007.09.01',getdate())
it return value 2 which means it is second week from given date

I Love SQL

Re: Week of the month

kibromg11-Sep-07 5:12

kibromg

11-Sep-07 5:12

Thanks!!!

Re: Week of the month

PIEBALDconsult11-Sep-07 9:57

PIEBALDconsult

11-Sep-07 9:57

Months don't have weeks, years have weeks. At least that seems to be what ISO says. Big Grin | :-D

Re: Week of the month

DerekFL12-Sep-07 5:24

DerekFL

12-Sep-07 5:24

SELECT CAST(DATEPART(wk,getdate())-DATEPART(wk,DATEADD(dd, -(DATEPART(dd, GETDATE()) - 1), GETDATE()))+1 as varchar)

back up sql server databases from server to local

Saeed.39411-Sep-07 1:15

Saeed.394

11-Sep-07 1:15

Hi,i want to know that how can i get schedule back up from server to local ??
for example :
i have a database ("testDB")in the server ( www.server.com) then i need get backup every day and transfer all new data to my PC in (localhost ).

thanks.

Re: back up sql server databases from server to local

rohitsrivastava11-Sep-07 23:27

rohitsrivastava

11-Sep-07 23:27

objsqlconn=new sqlconnection("user id=sa;password=;data source=servername")
objsqlconn.open

objsqlcmd=new sqlcommand
objsqlcmd.connection=objsqlconn
objsqlcmd.commandtext="master..xp_cmdshell 'del d:\" + databasename + "'"
objsqlcmd.commandtype=commandtype.text
objsqlcmd.executenonquery

objsqlcmd.commandtext=" backup database " + databasename + " to disk='" + databasename + "'"
objsqlcmd.commandtype=commandtype.text
objsqlcmd.executenonquery
this is coding to backup of database from the server

how to convert hex value????

sonal dadhania110-Sep-07 21:09

sonal dadhania1

10-Sep-07 21:09

I have a string of hex characters "0x89504e". I convert this to a string of binary characters @sOutput = "100010010101000001001110". I then run SELECT CAST(@sOutput AS BINARY). This returns me "0x313030303130303130313031303030303031303031313130000000000000".

How would I go about converting the string to binary such that the value would return as the original "0x89504e"? It's looking like the CAST is treating the string as numeric and converting a really large int to binary.

Re: how to convert hex value????

Blue_Boy10-Sep-07 21:37

Blue_Boy

10-Sep-07 21:37

SELECT CONVERT(varbinary(20), 'stringValue')

I Love SQL

Re: how to convert hex value????

Colin Angus Mackay10-Sep-07 23:45

Colin Angus Mackay

10-Sep-07 23:45

sonal dadhania1 wrote:
It's looking like the CAST is treating the string as numeric and converting a really large int to binary

It is treating the string as a string and converting it in to its ASCII codes.

I don't use binary data types much, but the following works:

DECLARE @hex AS VARBINARY(10)
SET @hex = 0x89504e
SELECT @hex

NOTE: There are no quotes/apostrophes around the hex value.

If you are coming from an application, use varbinary parameters and pass the value to the paramter as a byte array (byte[])

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

how to execute a function in linked server

Sebastian T Xavier10-Sep-07 20:33

Sebastian T Xavier

10-Sep-07 20:33

Hi all,

I have a function called fnGetOrderItemPriceDummy, which takes two parameters, such as @OrderID & @chord. I was trying to execute this function in our linked server, and it fired error. My code follows.....

(Select * from Openquery([linkedserver],'select myserver.dbo.fnGetOrderItemPriceDummy ( '+ Convert(varchar(50) ,@OrderID)+' , '''''+ @chord+'''''))

.

Can anyone say where is my mistake....

Thanks for your time.....

Sebastian

Re: how to execute a function in linked server

Marek Grzenkowicz12-Sep-07 1:16

Marek Grzenkowicz

12-Sep-07 1:16

I don't know syntax of OPENQUERY command, but I am sure that you are missing a database name here:

select myserver.DATABASE.dbo.fnGetOrderItemPriceDummy

How do I do this in SQL

Yitzchok Dev10-Sep-07 14:57

Yitzchok Dev

10-Sep-07 14:57

I have these tables

Customer Table:
CustomerID int; PK
FirstName nvarchar;
LastNaem nvarchar;
etc.;

Products Table:
ProductID int; PK
Name varcahr;
etc.;

OrderItems Table:
OrderItemID int; PK
ProductID int; FK
CustomerID int; FK
etc.;

Now I would like to get all users that have ordered: Product1 AND Product2, etc.
Only those people that orders all the selected products

-- modified at 21:15 Monday 10th September, 2007

Re: How do I do this in SQL [modified]

Chetan Patel10-Sep-07 19:43

Chetan Patel

10-Sep-07 19:43

Pass @Products --> with comma separated values like 'product1,product2'
and no of items @ProductCount --> 2

Select * from Customers Where CustomerID in
(Select CustomerID From
(Select CustomerID from OrderItems Inner Join Products on Products .ProductID = OrderItems.productid
Where [Name] IN (@products)
Group by CustomerID,[Name]) as Temp
Group by CustomerID having Count(CustomerID) = @productCount)

Best Regards,
Chetan Patel

Huh?

Arjan Einbu10-Sep-07 22:15

Arjan Einbu

10-Sep-07 22:15

This can't possibly work, can it?

I mean, the WHERE [Name] IN (@Products) part specifically.

Re: Huh?

John-ph10-Sep-07 23:15

John-ph

10-Sep-07 23:15

If this has to work then the query should get execute using "EXEC sp_executesql" command

Regards
J O H N

"Even eagles need a push." David McNally

Re: How do I do this in SQL

Arjan Einbu10-Sep-07 22:58

Arjan Einbu

10-Sep-07 22:58

You could create the inner query like this:

SELECT oi.CustomerID
FROM OrderItems oi
	JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = 2

You'll have to dynamically create the WHERE p.ProductID IN (...) part, and the value for the HAVING COUNT(*) = 2 should use a parameter.

Joined with the customer table, the results could look something like this:

DECLARE @NumberOfProducts int
SET @NumberOfProducts = 2

SELECT
	c.*
FROM Customer c
	JOIN
	(
		SELECT oi.CustomerID
		FROM OrderItems oi
			JOIN Products p
		WHERE p.ProductID IN ('Product1', 'Product2')
		GROUP BY oi.CustomerID
		HAVING COUNT(*) = @NumberOfProducts
	) AS t
		ON t.CustomerID = c.CustomerID

Re: How do I do this in SQL

Yitzchok Dev17-Sep-07 6:44

Yitzchok Dev

17-Sep-07 6:44

Thank you

analysis service sql2005

ABBASI_RA9-Sep-07 20:14

ABBASI_RA

9-Sep-07 20:14

Notify when a special partition in analysis service must update?

how i can triger a program to update a special partition of a cube at a required time?

or

how to found a way to know if a special partition need to be update?

thanks

connecting the sql db in the server

prasadbuddhika9-Sep-07 19:01

prasadbuddhika

9-Sep-07 19:01

when you want to connect to the db in the server do i have to use sqloledb provider , can't i use sqlclient provider. Smile | :)

Re: connecting the sql db in the server

Blue_Boy9-Sep-07 21:02

Blue_Boy

9-Sep-07 21:02

http://wwww.connectionstrings.com

I Love SQL

Re: connecting the sql db in the server

Rami Said Abd Alhalim9-Sep-07 23:34

Rami Said Abd Alhalim

9-Sep-07 23:34

you want to add port on firewall port number = 1433 to you can use sqlclient

Rami Abdalhalim

Re: connecting the sql db in the server

Frank Kerrigan10-Sep-07 9:24

Frank Kerrigan

10-Sep-07 9:24

sqlclient uses a an ole connection and you can use to connect it to SQL Server, this is the prefered method and is used in most examples.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.