Change dynamically(via code) the SqlDataSource for a GridView....

Hi,

say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2...

Does anybody know how can I alter programmatically these two sqldatasources in a gridview?

Thanks!!!

Ok,I resolved it,

I had to delete the "DataSourceID="SqlDataSource1" from the gridview directive and add the following code to a button event:

GridView1.DataSource = SqlDataSource1

GridView1.DataBind()

|||

But now my new question is :

How can I see what is the current/previous DataSource of my GridView Control?

Thanks...

|||

GridView1.DataSource will return the data source (as an object)

You can also useGridView1.DataSourceID to obtain the ID of the datasource as a string

For example:

dim TheCurrentDataSource as object

TheCurrentDataSource =GridView1.DataSource

GridView1.DataSource = The NextDataSource

GridView1.DataBind()

HTH

challenge...

How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?

Like I have a table employee

Employee

(

colA int

colB int

colC varchar

)

If i run the tsql..

it should give me

Employee

(

colA varchar

colB varchar

colC varchar

)

Is the table empty? If not, what is the disposition of the data that is already in the table?

|||

Well you could try using a change script generated by Enterprise Manager, like this one:

Code Snippet

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT

|||

Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.

Cdosys

I am trying to send job status information using smtp via cdosys. I have it emailing but I want to pass the servername and jobname in the email. Can anyone assist?

***oops, here is the SP and the command ran from the job steps;;;

************************************************** *****

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@.From varchar(100) ,
@.To varchar(100) ,
@.Subject varchar(100)=" ",
@.Body varchar(4000) =" "
/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpserver'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
************************************************** ****

declare @.Body varchar(4000)
select @.Body = 'Job Succeeded'
exec sp_send_cdosysmail 'me@.you.com','you@.me.com','servername Jobname',@.Body

ThanksAnyone? Bueller?

Catch Duplicate records

I have a table that I am inserting records to via VB6 using an ADO command.
If I try to insert a duplicate record I get the error message
-2147217873 Access Primary Key violation etc
This error doesn't jump to my error handler so how can I catch it? is there
something I can do from with SQL? or am I missing something in VB
ThanksThe best way to handle the error is to prevent it from happening in the firs
t
place. That can be done by, for example, checking to see if the value exists
before you try to insert it.
You can certainly catch this error in VB and I would propose that is the bes
t
place to do it.
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:%239gwgADTFHA.3544@.TK2MSFTNGP10.phx.gbl...
>I have a table that I am inserting records to via VB6 using an ADO command.
> If I try to insert a duplicate record I get the error message
> -2147217873 Access Primary Key violation etc
> This error doesn't jump to my error handler so how can I catch it? is the
re
> something I can do from with SQL? or am I missing something in VB
> Thanks
>|||If u use stored procedure
u can return value from it
e.g
....
.....
...
INSERT INTO ....
......
RETURN @.@.ERROR
in VB u must check the return value if return value != 0
then an error occuerd. In case everything ok zero will returnd
Message posted via http://www.webservertalk.com|||Thanks, I have tried this
declare @.Stockcode char(30)
declare @.Barcode char(20)
declare @.Qty int
set @.Stockcode = '123456'
set @.Barcode = '1234567890123'
set @.Qty = 12
INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
RETURN @.@.ERROR
GO
but I get the error
A RETURN statement with a return value cannot be used in this context.
What am I doing wrong?
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
> If u use stored procedure
> u can return value from it
> e.g
> ....
> .....
> ...
> INSERT INTO ....
> ......
> RETURN @.@.ERROR
>
> in VB u must check the return value if return value != 0
> then an error occuerd. In case everything ok zero will returnd
> --
> Message posted via http://www.webservertalk.com|||Again, prevent the error from happening by checking for the value. Given tha
t
you did not specifiy the PK I'll use the Force and guess:
Create Table dbo.OuterBarCodes
(
StockCode Char(30)
, BarCode Char(20)
, Quantity Int
, Constraint PK_OuterBarCodes Primary Key (StockCode, BarCode)
)
If Not Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
If you really want to know whether the value exists then do this:
If Exists(
Select *
From dbo.OuterBarCodes As O1
Where O1.StockCode = @.StockCode
And O1.BarCode = @.BarCode
)
Begin
Raiserror('Duplicate StockCode and BarCode', 16, 1)
Return
End
Insert OuterBarCodes(StockCode, BarCode, Quantity)
Values(@.StockCode, @.BarCode, @.Quantity)
Thomas
"Al Newbie" <nospamthanks@.iveenuf.com> wrote in message
news:ODBIBhDTFHA.560@.TK2MSFTNGP10.phx.gbl...
> Thanks, I have tried this
> declare @.Stockcode char(30)
> declare @.Barcode char(20)
> declare @.Qty int
> set @.Stockcode = '123456'
> set @.Barcode = '1234567890123'
> set @.Qty = 12
>
> INSERT INTO OuterBarcodes VALUES(@.Stockcode, @.Barcode, @.Qty)
> RETURN @.@.ERROR
> GO
> but I get the error
> A RETURN statement with a return value cannot be used in this context.
> What am I doing wrong?
> "E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
> news:4f9223565fb54af3b2af960ded5e10de@.SQ
webservertalk.com...
>

Catastrophic failure

Hello there!

We have recently upgraded our SQL server to SQL server 2003.
Not entierly without problems i might ad..

The latest one is if you via Excel (2000 or 2003 is tested) try to make a local cube file, using the built-in query wizard, you get an errormessage that only say "Catastrophic failure" and nothing else.

If you instead of using the wizard, do the same thing directly in MsQuery (uncheck the box at the first "new database query window") you dont get the error.

Why!? Many of our users does not have the knowledge on how to write SQL querys and find the wizard very useful. So it is not an option to have them using MsQuery.

And ideas?

From the reference to cube I assume that you are trying to access Analysis Services. I will be moving the thread to the "Data Mining" forum, which has a higher chance to provide an answer.

|||

Well.. That is not correct..

You can create a "local cube" directly from any datasource in Excel..

Call it a lightversion.. ;)

//P?r

|||

[moving thread to the Analyis Services forum]

Did you install Excel or other Office components after you installed SQL Server 2005? In that case, you'll need to re-register "Program Files\Common Files\System\Ole DB\msolap90.dll" to regain connectivity to AS 2005 servers and local cubes.

FYI: The local cube functionality in Excel is provided by a "local server" version of Analysis Services.

|||

Well.. We first tried with the old installation of office 2000 and got the same error, but then we installed 2003 with no success.

But why does it work if you not use the query wizard?

|||If you've upgraded to 2005, the other thing you may want to do is download the SP1 update (which is available soon) as it fixes some issues with local cubes as well, although this may not be the issue you are seeing here.|||

We have now updated to SP1. But the problem remains.

Works without the wizard.. (using MsQuery)

|||

Not sure if you're seeing the same issue, but there was a recent fix made for the OLEDB provider in an upcoming service pack to accomodate an issue where the connection string was being limited to 256 characters when taking a cube offline from XL. You could try to choose a very short path for the local cube and see if this avoids the error.

Sorry you hit this issue and I hope that helps.