Return datetime type variable from SP

How can I return a datetime type variable from a stored procedure in SQL Server to C# code?

Hello,

I don't know if I got it right, but to return anything from a Stored procedure just make something like:

Select client_datecreated from clients

if you need a date put it as a field then in C# execute the command and use the sqldatareader class:

check msdnhere

The command can be a Stored as well as a query.

|||Do i need to set an output parameter in the SP? The syntax in the SP is confusing me.|||

select convert(char(10),fieldname in table,101) as test_Date from tablename

the 101 is a code which gives the date in mm/dd/yyyy format.

You should check with 'books online" in your SQL server help section.that gives you a list of different format you may want your date to be in.

convert basicly is truncating your date to have 10 characters otherwise you will have the hours:minute:seconds too in your result.

|||

If you would like to get data as return parameter (not return Value which is always int) you have to define it as OUTPUT in stored procedure definition, and also you have to setup this parameter as output in your SQLcommand object parameters definition.

If you would like to return it as cell in result table you do not have to define parameter and you can just do select yourdatafied from yourtable at the end of your stored procedure.

But SQL Command with output parameter is more elegant solution and will work a little faster ( .net do not have to create table structure for returned data)
and you can use executeNonQuery instead of execute scalar or execute reader.

See VB or C# help for syntax how to do this if you will have problems post again, but help is very good in VS so you should be good.

Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End Function
Does this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegr oups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>

Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End FunctionDoes this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegroups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>

Return Array from Function

I have the following function that works fine, unless I add arguements.
When I add the arguments I get a Run-Time error '13': Type mismatch.
Any help would be greatly appreciated.
Option Base 1
Function FileSearch(Directory As String, Criteria As String)
Dim aFiles()
With Application.FileSearch
.NewSearch
.LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
2) 'Directory
.SearchSubFolders = True
.Filename = Criteria '
ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
.Execute
ReDim aFiles(.FoundFiles.Count)
For i = LBound(aFiles) To UBound(aFiles)
aFiles(i) = .FoundFiles(i)
Debug.Print i, aFiles(i)
Next i
End With
'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
FileSearch = aFiles
End FunctionDoes this have something do to with SQL Server? If not, you should consider
posting this in a VB forum.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"j...greig...davis@.gmail.com" <jgreigdavis@.gmail.com> wrote in message
news:1136905451.257413.287960@.g49g2000cwa.googlegroups.com...
>I have the following function that works fine, unless I add arguements.
> When I add the arguments I get a Run-Time error '13': Type mismatch.
> Any help would be greatly appreciated.
> Option Base 1
> Function FileSearch(Directory As String, Criteria As String)
> Dim aFiles()
>
> With Application.FileSearch
> .NewSearch
> .LookIn = Directory 'ActiveWorkbook.Worksheets("Control").Cells(3,
> 2) 'Directory
> .SearchSubFolders = True
> .Filename = Criteria '
> ActiveWorkbook.Worksheets("Control").Cells(4, 2) 'Criteria
> .Execute
> ReDim aFiles(.FoundFiles.Count)
> For i = LBound(aFiles) To UBound(aFiles)
> aFiles(i) = .FoundFiles(i)
> Debug.Print i, aFiles(i)
> Next i
> End With
> 'FileSearch = Application.WorksheetFunction.Transpose(aFiles)
> FileSearch = aFiles
> End Function
>sql

Return a type

Hello All!
I would like to thank everyone for all the help, but.. (there is always a
but) i have another question.
I would like in my select a column displaying if the current line is a
Company or a Person, something like that:
SELECT *, "(Company or Person) As Type" FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
I anyone could help me, i raelly would appreciate it!
thanks,
Bruno N
CREATE TABLE [Person] (
[Id] [int] NOT NULL ,
[RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Person_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
CREATE TABLE [Customer] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [Company] (
[Id] [int] NOT NULL ,
[CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Company_Customer] FOREIGN KEY
(
[Id]
) REFERENCES [Customer] (
[Id]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]SELECT *,
CASE
WHEN Client.ID = Person.ID THEN 'Person'
WHEN Company.ID = Person.ID THEN 'Company'
ELSE ''
END
FROM
...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bruno N" <nylren@.hotmail.com> wrote in message
news:u31HkjMJFHA.3136@.TK2MSFTNGP15.phx.gbl...
> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>|||SELECT *,
Case When Person.ID Is Not Null then 'Person'
When Company.ID Is Not Null then 'Company'
When Customer.ID Is Not Null then 'Customer' ENd As Type
FROM Client
LEFT JOIN Person ON Client.ID = Person.ID
LEFT JOIN Company ON Client.ID = Company.ID
"Bruno N" wrote:

> Hello All!
> I would like to thank everyone for all the help, but.. (there is always a
> but) i have another question.
> I would like in my select a column displaying if the current line is a
> Company or a Person, something like that:
>
> SELECT *, "(Company or Person) As Type" FROM Client
> LEFT JOIN Person ON Client.ID = Person.ID
> LEFT JOIN Company ON Client.ID = Company.ID
>
> I anyone could help me, i raelly would appreciate it!
> thanks,
> Bruno N
>
> CREATE TABLE [Person] (
> [Id] [int] NOT NULL ,
> [RG] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Person_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
> CREATE TABLE [Customer] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
> CREATE TABLE [Company] (
> [Id] [int] NOT NULL ,
> [CNPJ] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Company_Customer] FOREIGN KEY
> (
> [Id]
> ) REFERENCES [Customer] (
> [Id]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
>
>|||SELECT ...,
CASE
WHEN Person.ID IS NOT NULL THEN 'Person'
WHEN Company.ID IS NOT NULL THEN 'Company'
END, ...
You seem to be missing the alternate key on Customer Name. IDENTITY
should never be the only key of a table. Also, your constraints don't
prevent the same entity being entered as both Cutsomer and Company.
David Portas
SQL Server MVP
--

retrieving the BigInt value from the Identity Column after inserting

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.

How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.

Waht is the way to achive this please?

Marc

What I do is issue the two commands (the sql insert, and the sql select scope_identity) in the same execute separated by semi colon.

the trick is to set the parameter direction to output for the identity.

David H. has a good article.

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

|||

Thank you very much. that did it!

Marc

Retrieving Random Sections From A Data file

On my company's website, we have a quote of the day. I would like to be able to type a hundred or so quotes into a mdf file. Then, I would like to have code that randomly selects one of the quotes every day and posts it.

...What I want is very similar to the "Image Of the Day" section on many websites.

Any ideas?

Okay, I'll bite. Bear in mind that there are CMS out there that offer this - but if you want to roll your own...several approaches come to mind, so you'll want to pick the one with the best performance.Here's one - in steps. (a) Create a table with at least two fields - your quote text and an autoincrement ID (i.e. first record is 1, second is 2 etc). (b) Get the quotes into the table. (c) In your aspx, programmatically get a random integer bounded between 1 and the number of quotes you have - and select that ID from the table for display.Alternatively, have a second table that will only have one quote in it. Have your webpage simply display that one record. Create a stored procedure in the database that removes the existing record and copies in a quote from the full quote table (based again on generating a random ID - the rand() function does the trick in SQL). Then set up a daily scheduled job that calls the stored procedure which changes the quote.|||

Thanks. This looks good. Do you have any tips on what sort of code I should write to obtain the random integer?

|||

Hi,

Try this, select top 1 * from TableName order by NEWID();

Thanks.

Retrieving NEWSEQUENTIALID

Hi,
We are thinking of changing the primary key of our table from GUID type to
squential id using the new NEWSEQUENTIALID function. But the issue we are
having is how to efficiently retrieve the generated value because we need to
pass this information back to the client app. With GUID we have a stored
procedure that calls NEWID and then we assign the generated value to the
primary key column. But with NEWSEQUENTIALID it can only be used as DEFAULT
constraint. We came up with a solution to retreive the generated id but the
performance is very slow.
Has anyone come up with a better strategy in doing this?
Thanks.Roy
Read this article in the BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e06d2cab-f1ff-42f1-8550-
6aaec57be36f.htm
I think , as the BOL suggests to use a scalar UDF to rertieve the value
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> I think , as the BOL suggests to use a scalar UDF to rertieve the value
Uri,
I'm not sure if we have a different rev of BOL (I'm looking at the
December version), but mine seems to indicate that you CANNOT use a UDF on
NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
CREATE TABLE x1
(
col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
)
GO
INSERT x1
OUTPUT inserted.col
DEFAULT VALUES
GO
DROP TABLE x1
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

>
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>|||DOH!!!! Thanks Adam. I did not read it properly , ( I need to freshen up
myself in the morning)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OUOE$tzmGHA.3440@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||Another approach is to assign the uniqueidentifier value in application code
rather than in SQL Server. That way, you don't need to retrieve the
assigned value at all because you already know it. To get a sequential GUID
value in application code, call the UuidCreateSequential RPC function.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy" <Roy@.discussions.microsoft.com> wrote in message
news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
> Hi,
> We are thinking of changing the primary key of our table from GUID type to
> squential id using the new NEWSEQUENTIALID function. But the issue we are
> having is how to efficiently retrieve the generated value because we need
> to
> pass this information back to the client app. With GUID we have a stored
> procedure that calls NEWID and then we assign the generated value to the
> primary key column. But with NEWSEQUENTIALID it can only be used as
> DEFAULT
> constraint. We came up with a solution to retreive the generated id but
> the
> performance is very slow.
> Has anyone come up with a better strategy in doing this?
> Thanks.|||Thanks the response.
However, this is the solution we tried before but we saw a performance
degradation (during lots of inserts) compare to the old way of using GUIDs.
Roy
"Adam Machanic" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23wYzjzmGHA.1852@.TK2MSFTNGP03.phx.gbl...
> Uri,
> I'm not sure if we have a different rev of BOL (I'm looking at the
> December version), but mine seems to indicate that you CANNOT use a UDF on
> NEWSEQUENTIALID... The best option, IMO, is to use an OUTPUT clause:
> --
> CREATE TABLE x1
> (
> col UNIQUEIDENTIFIER DEFAULT(NEWSEQUENTIALID())
> )
> GO
> INSERT x1
> OUTPUT inserted.col
> DEFAULT VALUES
> GO
> DROP TABLE x1
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
>|||Thanks Dan.
We haven't tried this approach yet. We will it give it a try.
Roy
"Dan Guzman" wrote:

> Another approach is to assign the uniqueidentifier value in application co
de
> rather than in SQL Server. That way, you don't need to retrieve the
> assigned value at all because you already know it. To get a sequential GU
ID
> value in application code, call the UuidCreateSequential RPC function.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy" <Roy@.discussions.microsoft.com> wrote in message
> news:E88F0D41-C460-4E71-932C-56CB5C5AA24F@.microsoft.com...
>
>

Retrieving data from SQL server table to display on button on datagrid table.

I have nine type of buttons,

EnrollAmtBTM

PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button

this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION

THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITION

protectedvoid CollectAgencyInformation()

{

WebLibraryClass ConnectionFinanceDB;

ConnectionFinanceDB =new WebLibraryClass();

string SQLCONDITION ="";

string RUN_SQLCONDITION ="";

SessionValues ValueSelected =null;

int CollectionCount = 0;if (Session[Session_UserSPersonalData] ==null)

{

ValueSelected =new SessionValues();

Session.Add(Session_UserSPersonalData, ValueSelected);

}

else

{

ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);

}

ProcPaymBTM.Visible =false;PaymenLstBTN.Visible =false;

Dataviewlisting.ActiveViewIndex = 0;

TreeNode SelectedNode =new TreeNode();

SelectedNode = AgencyTree.SelectedNode;

SelectedAgency = SelectedNode.Value.ToString();

Agencytxt.Text = SelectedAgency;

Agencytxt2.Text = SelectedAgency;

Agencytxt3.Text = SelectedAgency;

DbDataReader CollectingDataSelected =null;

try

{

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency +"'");

}

catch

{

}

DataTable TableSet =new DataTable();

TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);

int IndexingValues = 0;foreach (DataRow DataCollectedRowin TableSet.Rows)

{

if (IndexingValues == 0)

{

SQLCONDITION ="where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

else

{

SQLCONDITION = SQLCONDITION +" OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() +"'";

}

IndexingValues += 1;

}

SQLCONDITION = SQLCONDITION +")";

ConnectionFinanceDB.DisconnectToDatabase();

if (Dataviewlisting.ActiveViewIndex == 0)

{

Dataviewlisting.ActiveViewIndex += 1;

}

else

{

Dataviewlisting.ActiveViewIndex = 0;

}

SelectedAgency = SQLCONDITION;

ValueSelected.CONDITION = SelectedAgency;

?? this is where i use to get count where in other buttons and are displayed... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the view

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

////this is myCollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.

"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"

here is the function

publicint CollectedFinaceDataCount(String SQLStatement)

{

int DataCollection;

DataCollection = 0;

try

{

SQLCommandExe = FinanceConnection.CreateCommand();

SQLCommandExe.CommandType = CommandType.Text;

SQLCommandExe.CommandText = SQLStatement;

ConnectToDatabase();

DataCollection = (int) SQLCommandExe.ExecuteScalar();

DisconnectToDatabase();

}

catch (Exception ex)

{

Console.WriteLine("Exception Occurred :{0},{1}",

ex.Message, ex.StackTrace.ToString());

}

return DataCollection;

}

So here mu requirement request is to display only the value fronm the view i have against the agency selected

Please help ASAP

Thanks

Santosh

I am getting to display the values

But the problem is that the table has 9 type of payments,

enrollment, placement, WPR, retention 1 month, retention 3 month ,retention 6 month, replacement bonus, satis complete,

Now there are different amouht agains the agency and type of payment above,

So i need do write a for loop can anyone help me

My statement is below ans SQL condition as mentioned in earlier code above posted fetches the value agains each agency, but not against each payment.

my buttons are

EnrollAmtBTM.Text

WPRAmtBTM.Text

PlacAmtBTM.Text

SatisCompAmtBTM.Text

Reten1AmtBTM.Text

Reten3AmtBTM.Text

Reten6AmtBTM.Text

EnrollBonusAmtBTM.Text

and finally

RePlacBonusAmtBTM.Text

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

try /////this is where i need the C# for loop

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

catch

{

}

|||

I am trying to something like this using switch case but still being a newbie i have no idea please help.

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

switch(Dataviewlisting.GetType(Payment_Description).ToString() )

{

case ("Enrollment(5 Days)"):

EnrollAmtBTM.Text = CollectionCount.ToString();

break;case ("Placement"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

case ("Work Participation"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;case ("Satisfactory Complete"):

PlacAmtBTM.Text = CollectionCount.ToString();

break;

default:

case ("Enrollment(5 Days)"):break;

}

|||

Can any body help me with FOR LOOP for this wolode objective of fetching data the whole view has 81 records....9 type agains each type of payment..so i need for loop

Like

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

Foreach........

If statement

then EnrollAmtBTM.Text= value blah blah ...

something like thsi for the first and second posts i hav made here

|||

I am using something like this but still no luck can anybody help...ASAP

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach (V_Payment_Amount_List Rowsin DetailDataList.Rows)

{

if (Rows.EnrollAmtBTM.Text =="EnrollAmtBTM".ToString())

{

Rows["PaymentDescription"] = EnrollAmtBTM.Text;

}

}

// try

// {

// CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

// EnrollAmtBTM.Text = CollectionCount.ToString();

// }

// catch

// {

// }

Thanks,

George

|||

I am trying something like this too but still there is error.My syntax itself is wrong or I am not sure please help,

As you may know from the very first post what i am trying to do...or please ask me if any doubt??

RUN_SQLCONDITION ="SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;

foreach(DataRow Paymentin TABLE1.Rows)

{

if (Rows.EnrollAmtBTM.Text == Payment["Enrollment(5 Days)"].ToString())

{

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I am trying to do like this

collecting the data in datatable then displaying then against the payment description but

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION;

DataTable Payment =new DataTable();

int CollectionCount = 0;

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (e.Row.Cells[2].Text == Payment["Payment_Description"].ToString()) //here i need help for bringing in the type of payment ie. "enrollment" placement etc to display on EnrollAmtBTM.Text and PlacNotPaidBTM.Text respectively

{

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

still error is comming

when i use the following code

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData(("SELECT Payment_Amount_Budgeted,Payment_Description,Project_ID FROM V_Payment_Amount_List") + SQLCONDITION);

DataTable Payment =new DataTable();

int CollectionCount = 0;///pointing here

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow.ToString() == Payment["Payment_Description"].ToString())

{

PaymentData.ToString() = DataCollectedRow["Enrollment(5 Days)"].ToString();

CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(CollectingDataSelected);

EnrollAmtBTM.Text = CollectionCount.ToString();

}

}

|||

I resolved this by the following code fetching the data to a table payment and then using for loop against all 9 type of payments.

ConnectionFinanceDB.DisconnectToDatabase();

CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT Payment_Amount_Budgeted, Payment_Description FROM dbo.V_Payment_Amount_List " + SQLCONDITION);DataTable Payment =new DataTable();

Payment.Load(CollectingDataSelected, LoadOption.Upsert);

foreach (DataRow DataCollectedRowin Payment.Rows)

{

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment(5 Days)")

{

EnrollAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Placement")

{

PlacAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Work Participation")

{

WPRAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="30 days Retention")

{

Reten1AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="3 Months Retention")

{

Reten3AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="6 Months Retention")

{

Reten6AmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Enrollment Bonus")

{

EnrollBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Re-Placement Bonus")

{

RePlacBonusAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

if (DataCollectedRow["Payment_Description"].ToString() =="Satisfactory Complete")

{

SatisCompAmtBTM.Text = DataCollectedRow["Payment_Amount_Budgeted"].ToString();

}

}

retrieve the value that occurs the most

Hi,

I am trying to retrieve the value that occurs the most at a dimension level. For example, I have to analyze the product type mostly used in a specific target segment by number of products.

The mathematical function normally used is MODE. However, in AS2005 there is not such function. I tried to use TopCount but it is not as easy as I thought.

Any suggestion?

Thanks!

As far as I know, TopCount should work.

This is a sample from Adventure Works:

with

set [TopCustomers] as

TOPCOUNT(

[Dim Customer].[Dim Customer].[Dim Customer].members,

1,

[Measures].[Sales Amount]

)

select {[Measures].[Sales Amount]} on 0,

[TopCustomers] on 1

from [Adventure Works DW]

Hope this helps,

Santi

|||I think TopCount would only get you a mode if you were to use it against a count measure. I am guessing that you want to find out which member from the product dimension has the most transactions against it for a given set of criteria. Depending on the granularity of your dimensions you may have difficulty doing this without having some sort of count measure.

Retrieve report parameters names and type programmatically

Hi,
I need to retrieve a report parameters names and their type ( being
string, list, date etc ) programmatically so i can dynamically assign them.
Can anyone help?
Thanks,use the web service that comes with reporting services ...
the method GetReportParameters is exactly what u need ...
"Loui Mercieca" wrote:
> Hi,
> I need to retrieve a report parameters names and their type ( being
> string, list, date etc ) programmatically so i can dynamically assign them.
> Can anyone help?
> Thanks,
>
>

Retrieve length of image or BLOB

I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?Check out the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroups.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>

retrieve Datetime

I have a table with a column tradedate of type datetime in which date
is stored in the format
mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
I tried data, time and timestamp but I do not seem to get the exact
format. Any ideas?
Thanks a lot!
No, your date is NOT stored in that format. If the column is defined as
datetime, it uses an internal format that you never see. The way it gets
displayed is determined by a number of factors, including your client
settings. If you want date to be displayed a certain way, use the CONVERT
function, and specify a format code, which is documented along with the
CONVERT function in the Books Online.
For full details on datetime storage, display and manipulation, please see:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1165512612.451830.77090@.79g2000cws.googlegrou ps.com...
>I have a table with a column tradedate of type datetime in which date
> is stored in the format
> mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
> I tried data, time and timestamp but I do not seem to get the exact
> format. Any ideas?
> Thanks a lot!
>
|||I had to use
convert(char(25),trade_date,131)
That was very helpful. Thank you!
Kalen Delaney wrote:[vbcol=seagreen]
> No, your date is NOT stored in that format. If the column is defined as
> datetime, it uses an internal format that you never see. The way it gets
> displayed is determined by a number of factors, including your client
> settings. If you want date to be displayed a certain way, use the CONVERT
> function, and specify a format code, which is documented along with the
> CONVERT function in the Books Online.
> For full details on datetime storage, display and manipulation, please see:
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
> news:1165512612.451830.77090@.79g2000cws.googlegrou ps.com...

retrieve Datetime

I have a table with a column tradedate of type datetime in which date
is stored in the format
mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
I tried data, time and timestamp but I do not seem to get the exact
format. Any ideas?
Thanks a lot!No, your date is NOT stored in that format. If the column is defined as
datetime, it uses an internal format that you never see. The way it gets
displayed is determined by a number of factors, including your client
settings. If you want date to be displayed a certain way, use the CONVERT
function, and specify a format code, which is documented along with the
CONVERT function in the Books Online.
For full details on datetime storage, display and manipulation, please see:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1165512612.451830.77090@.79g2000cws.googlegroups.com...
>I have a table with a column tradedate of type datetime in which date
> is stored in the format
> mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
> I tried data, time and timestamp but I do not seem to get the exact
> format. Any ideas?
> Thanks a lot!
>|||I had to use
convert(char(25),trade_date,131)
That was very helpful. Thank you!
Kalen Delaney wrote:
> No, your date is NOT stored in that format. If the column is defined as
> datetime, it uses an internal format that you never see. The way it gets
> displayed is determined by a number of factors, including your client
> settings. If you want date to be displayed a certain way, use the CONVERT
> function, and specify a format code, which is documented along with the
> CONVERT function in the Books Online.
> For full details on datetime storage, display and manipulation, please see:
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
> news:1165512612.451830.77090@.79g2000cws.googlegroups.com...
> >I have a table with a column tradedate of type datetime in which date
> > is stored in the format
> > mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
> > I tried data, time and timestamp but I do not seem to get the exact
> > format. Any ideas?
> >
> > Thanks a lot!
> >

retrieve Datetime

I have a table with a column tradedate of type datetime in which date
is stored in the format
mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
I tried data, time and timestamp but I do not seem to get the exact
format. Any ideas?
Thanks a lot!No, your date is NOT stored in that format. If the column is defined as
datetime, it uses an internal format that you never see. The way it gets
displayed is determined by a number of factors, including your client
settings. If you want date to be displayed a certain way, use the CONVERT
function, and specify a format code, which is documented along with the
CONVERT function in the Books Online.
For full details on datetime storage, display and manipulation, please see:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
news:1165512612.451830.77090@.79g2000cws.googlegroups.com...
>I have a table with a column tradedate of type datetime in which date
> is stored in the format
> mm/dd/yyyy hh:mm:sssAM. How do I retrieve this using Java resultset
> I tried data, time and timestamp but I do not seem to get the exact
> format. Any ideas?
> Thanks a lot!
>|||I had to use
convert(char(25),trade_date,131)
That was very helpful. Thank you!
Kalen Delaney wrote:[vbcol=seagreen]
> No, your date is NOT stored in that format. If the column is defined as
> datetime, it uses an internal format that you never see. The way it gets
> displayed is determined by a number of factors, including your client
> settings. If you want date to be displayed a certain way, use the CONVERT
> function, and specify a format code, which is documented along with the
> CONVERT function in the Books Online.
> For full details on datetime storage, display and manipulation, please see
:
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "db-x" <rashmi.ndeshpande@.gmail.com> wrote in message
> news:1165512612.451830.77090@.79g2000cws.googlegroups.com...

Retrieve BLOB from SQL Server

I have a table in a SQL Server database that contains a field(data type image) that contains a text file. I'm trying to retrieve this file and save it onto the hard drive. I'm using the code below, but get I get the error 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.' at line 11. This code worked fine when I tested it on the pubs database and exporting the logo field out of pub_info. I'm not quite sure why this doesn't work for my database. Can anyone see where I'm going wrong?

1 Dim cn As ADODB.Connection
2 Dim rs As ADODB.Recordset
3 Dim mstream As ADODB.Stream

4 cn = New ADODB.Connection
5 cn.Open("Provider=SQLOLEDB;data Source=server;Initial Catalog=database;User Id='userid';Password='password'")

6 rs = New ADODB.Recordset
7 rs.Open("Select * from filesubmissions where bundleId = 'F0000014.bun'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

8 mstream = New ADODB.Stream
9 mstream.Type = ADODB.StreamTypeEnum.adTypeBinary
10 mstream.Open()
11 mstream.Write(rs.Fields("BLOB").Value)
12 mstream.SaveToFile("c:\export.txt", ADODB.SaveOptionsEnum.adSaveCreateOverWrite)

13 rs.Close()
14 cn.Close()

Thanks

May I suggest you start by using ado.net?

|||

try this one...

Dim BlobQuery As String
Dim RG As New Random
Dim InvoiceCSVData As Byte()
Dim strFileName As String = "TestingCSV_" + TextBox1.Text + "_" + CStr(RG.Next(1, 100000)) + ".CSV"

BlobQuery = "select BLOB from YourTableName where Condition'"
myOraConnection.Open()
myOraCmd = New OracleCommand(BlobQuery, myOraConnection)
myDR = myOraCmd.ExecuteReader(CommandBehavior.SequentialAccess)
myDR.Read()
InvoiceCSVData = CType(myDR(0), Byte())
myDR.Close()
myOraConnection.Close()

Response.AddHeader("Content-disposition", _
"attachment; filename=" & strFileName)
Response.ContentType = "application/octet-stream"
Response.BinaryWrite(InvoiceCSVData)
Response.End()

Retrieve Bit type value

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?You're not selecting what you think you're selecting. You will get a 1 or 0 back, not S00817. Do any of the fields in the record have that value? If you miss a comma in your field list, you can get goofy-looking, unexpected data. Also, when the bit comes back, you can feed it right into boolean properties. You don't have to change 1 into true, for example. Pretty handy. I'd run your SQL statement in Query Analyzer, some where outside of ASP. Actually, ADO might convert the 1 to -1. Might want to check that also. Check your query; something's up there I would suspect.|||Yes you were quite right. I was trying to read data from another field. I should have using Mydataset.tables(0).Rows(0)(13)

Thank you for your help