Encounter No Parameterless Constructor Defined ... During The OnObjectCreated Of An ObjectDataSource

Apr 3, 2008

I got the following error when trying to pass a parameter from an ObjectDataSource to a BLL during the OnObjectCreated event:No parameterless constructor defined for this object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.MissingMethodException: No parameterless constructor defined for this object.The ObjectDataSource in the aspx is as follows:
<asp:ObjectDataSource ID="LoadData" runat="server" SelectMethod="GetData" TypeName="Export00.Export" OnObjectCreated="SrcObjectCreating">            </asp:ObjectDataSource>
 The code behind in aspx.cs is as follows: 
protected void SrcObjectCreating(object sender, ObjectDataSourceEventArgs e)       
{            Export sqlcommand = new Export("SELECT [DocTitle], [DocID] FROM [Document]");            e.ObjectInstance = sqlcommand;
}  
 The BLL is as follows: Export.cs   (this is the BLL)
namespace Export00{    public class Export    {        private readonly string connString;        private readonly string sqlCommand;public Export(string command)        {    sqlCommand = command;            connString = WebConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;        }     } 
}Can someone tell me why am I getting this constructor error and how to resolve it? Thanks in advance.sg2000   
 
 
 
 
 

View 3 Replies


ADVERTISEMENT

[Help]I Encounter An Error When Using RDA

Jan 29, 2007

Hi All,

When my customers sync the data using RDA, they got this error message,

I don't know how to avoid it. ( I am using window mobile 5, Palm Treo 700wx, C# 2005, Sql server 2005(remote server) and Sql CE 3.0):



The OLE DB data type information in the SQL Mobile columns does not matchthe information in the SQL server columns for the RDA table (Client type=3,server type =20, Table naame =MyTable)

it's very urgent, could anyone help me out? Thanks.





James

View 4 Replies View Related

ERROR ENCOUNTER CALLING UDF

Mar 6, 2008

I have created a SQL Server Project with table-valued function. I compile and deploy the project to SQL server successfully. I did some testing by calling the function and encounters no error.Then, I decided to add another function which is scalar function. Again, I compile the project and deploy it to SQL server without any problem. Now, when I try to call the second function I created, I encounter some error during execution. But calling the first function, the error did not occur; it execute without any problem.
Please help and tell me what should I do to resolve it. Below is the message return after calling the second function.

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'dgaUDF_GetUserStatus':
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
System.IO.FileLoadException:
at System.Diagnostics.Switch.InitializeWithStatus()
at System.Diagnostics.Switch.get_SwitchSetting()
at System.Diagnostics.TraceSwitch.get_Level()
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
System.TypeInitializationException:
at System.Data.SqlClient.SqlConnectionFactory..ctor()
at System.Data.SqlClient.SqlConnectionFactory..cctor()
System.TypeInitializationException:
at System.Data.SqlClient.SqlConnection..cctor()
System.TypeInitializationException:
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at UserDefinedFunctions.dgaUDF_GetUserStatus(SqlString userid, SqlDateTime workdate)

View 13 Replies View Related

A Query I've Encounter That Has Me Stumped ... Anybody?

Aug 19, 2007

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.


My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit


[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit


Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-------------------------------------------------------------------------------------------------------------
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1


[UserMessageReplies]


ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew
---------------------------------------------------------------------------------------------------------------------------
1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1


Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)



Thanks very much!

Chris

View 4 Replies View Related

Paging With Gridview And ObjectDataSource

Jun 19, 2007

I'm trying to effecinty page through many rows of data with the gridview and objectdatasource. I'm having trouble. I'm using a table adapter with predefined counting and select methods. I have tested all the methods and they all work properly. But when I configure the object datasource to use the table adapter, and set the gridviews datasrouce, the page doesn't load and I wind up getting "time out". Any help?       <asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="objTopics">    <Columns>    <asp:BoundField DataField="topic_title" />    </Columns>    <EmptyDataTemplate>    <p>NOTHING HERE</p>    </EmptyDataTemplate>    </asp:GridView>    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" OldValuesParameterFormatString="original_{0}"        SelectMethod="GetTopics" SelectCountMethod="GetTopicsRowCount" TypeName="TopicsTableAdapters.discussions_GetTopicsSubSetTableAdapter">        <SelectParameters>            <asp:Parameter DefaultValue="1" Name="startRowIndex" Type="Int32" />            <asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" />            <asp:Parameter DefaultValue="1" Name="board_id" Type="Int32" />        </SelectParameters>    </asp:ObjectDataSource>

View 1 Replies View Related

Paging With Gridview And ObjectDataSource

Jun 21, 2007

I have a problem with efficiently paging with gridview and objectdatasoruce. I have GetPosts1(startRowIndex, maximumRow, topic_id) and GetPostsCount(topic_id). I tested each procedure and each are working correctly. The problem is with the controls. Here is the code for the controls.    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames        DataSourceID="ObjectDataSource2">        <Columns>            <asp:BoundField DataField="RowNumber" HeaderText="RowNumber" SortExpression="RowNumber" />            <asp:BoundField DataField="post_id" HeaderText="post_id" SortExpression="post_id" />            <asp:BoundField DataField="post_subject" HeaderText="post_subject" SortExpression="post_subject" />            <asp:BoundField DataField="post_text" HeaderText="post_text" SortExpression="post_text" />            <asp:BoundField DataField="post_time" HeaderText="post_time" SortExpression="post_time" />            <asp:BoundField DataField="topic_id" HeaderText="topic_id" SortExpression="topic_id" />            <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />            <asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />        </Columns>    </asp:GridView>    <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" OldValuesParameterFormatString="original_{0}"        EnablePaging="True" SelectMethod="GetPosts1" SelectCountMethod="GetPostsCount" TypeName="PostsTableAdapters.discussions_GetPostsTableAdapter">        <SelectParameters>            <asp:QueryStringParameter DefaultValue="48" Name="topic_id" QueryStringField="t" Type="Int32" />        </SelectParameters>    </asp:ObjectDataSource> When I run the page, I get "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" and then "The thread '<No Name>' (0xbe0) has exited with code 0 (0x0)." Could the problem be with null or empty values in the returned data? 

View 1 Replies View Related

Get Scope Identity Value Using ObjectDataSource And Vb.Net

Dec 24, 2007

Hi,
I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.
I can't find what event, or how to get the value that the scope identity returns.
Here is my Sproc.

ALTER PROCEDURE dbo.[YourCompany_LanCustomer_Insert]
    (
    @DNNUserID int,
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Address nvarchar(50),
    @Address2 nvarchar(50),
    @City nvarchar(50),
    @State nvarchar(50),
    @Zip nvarchar(50),
    @EmailAddress nvarchar(50),
    @PhoneNumber nvarchar(50),
    @CustomerID int OUTPUT
    )
    AS
INSERT INTO YourCompany_LanCustomer
(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)
VALUES (@DNNUserID, @FirstName, @LastName, @Address, @Address2, @City, @State, @Zip, @EmailAddress, @PhoneNumber, getdate())
SET @CustomerID = Scope_Identity()
RETURN   

View 8 Replies View Related

AffectedRows Different From ObjectDataSource Then SqlDataSource

May 27, 2008

I have a gridview control that accesses either an objectdatasource or a sqldatasource
both of which excute "select * from customers"
 the affected-rows property when using the sqldatasource is always the number of rows returned (what I want)
but i need to use an object data source to access a table-adapter
when using the ODS affectedrows is always -1 no matter how mayn rows were returned
 why is this
 is there another way to get the number of records returned by an ODS

View 1 Replies View Related

SQLDataSource - How To Filter Out Records That Are In An ObjectDataSource?

Jan 16, 2006

I have an SQLDataSource that I would like to filter out some records that are stored in an ObjectDataSource. Is this possible? The data that is filling the ObjectDataSource is being populated by a WebService.
SQL in SQLDataSource----------------------------
SELECT id, accountFROM contactWHERE id NOT IN (SELECT id FROM ObjectDataSource.Records...)
Thanks.

View 1 Replies View Related

Delete Data From GridView And ObjectDataSource

Mar 11, 2006

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.Can anyone see anything wrong with this code:public static void DeleteBlog(int original_BlogID) { string insertCommand = "DELETE FROM Blog WHERE BlogID = @BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@BlogID", original_BlogID)); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close();}

View 3 Replies View Related

How Can You Use SQL Full Text Search CONTAINS() With An Asp.net 2.0 ObjectDataSource Using @Parameters?

Nov 22, 2007

How can you use SQL Full Text Search CONTAINS() with an asp.net 2.0 ObjectDataSource using @Parameters?
MSDN says something like this, but only works directly using like the Query from SQL Manager:
USE TestingDB;GODECLARE @SearchWord NVARCHAR(30)SET @SearchWord = N'performance'SELECT TestTextFROM TestingTableWHERE CONTAINS(TestText, @SearchWord);
I tryed to mak something like that work with the DataSet DataAdapter Query Builder for the ObjectDataSource, but you can't use DECLARE or SET.
SELECT TestTextFROM TestingTableWHERE CONTAINS(TestText, @SearchWord);
But again it says @SearchWord not a valide SQL Construct
Is there anyway to make a DataSet.DataApater.ObjectDataSource work with an SQL FTS CONTAINS() with @Parameters?

View 6 Replies View Related

Display Single Number Using ObjectDataSource Scalar Function

Mar 10, 2008

I wrote a Scalar UDF in SQL2005 that returns an integer.  I want to be able to display this integer in a ASP.Net 2.0 web page.  I typically use a DAL for all data so I added an ObjectDataSource as a Qeury that contains only the UDF.  How do I easily display the value in a Label Control or?
I have tried to use a Repeater with a label, a Formview with a Label, all to no avail.  Any suggestions?

View 12 Replies View Related

How Do You Assign A Data Source To A Page And Which Type Sqldatasource, Objectdatasource Or ?

Mar 5, 2007

Hi
Using ASP.NET 2.0, Sql Server 2005.
I have a simple page (NOT a formview) with some entries textbox's , checkbox and dropdownlistbox's
I want to link a datasource to the 'Item  Page' and bind the datasource's values to the page
The select statement is
Select a.IssueID,
a.ProjectID,
a.VersionID,
a.toincludeversionid,
a.Version,
a.toincludeversion,
a.TypeofEntryID,
a.PriorityID,
a.WorkFlowID,
a.Title,
a.Area,
a.Details,
a.Question,
a.Answer,
a.HowToRepro,
a.DevelopersNotes,
a.TestersNotes,
b.ProjectID,
b.ProjectName,
OldVersion.Version,
ToIncludeVersion.Version,
d.DESCRIPTION,
e.DESCRIPTION,
 
x.TaskID as TaskID,
x.DESCRIPTION as TaskDescription,
z.Taskdone,
CONVERT (char(9),z.TaskAssignedDate, 3) AS Workflowdate,
z.StaffID as StaffID,
w.username,
y.latest_workflowid
from issue as a
Inner join ProjS b on b.ProjectId=a.ProjectID
Left Outer join Version OldVersion on a.VersionID=OldVersion.VersionID
Left Outer join Version ToIncludeVersion on a.VersionID= ToIncludeVersion.VersionID
Inner join TypeOfEntry d on d.TypeOfEntryID=a.TypeofEntryID
Inner join Priority e on e.PriorityID=a.PriorityID
 
inner join workflow z on z.issueid=a.issueid
Inner join (select issueid,max(workflowid) as latest_workflowid from workflow group by issueid) y on y.latest_workflowid=z.workflowid
Inner join task x on  x.taskid=z.taskid
Inner join staffls w on w.StaffID=z.StaffID
 
Where a.IssueID= @IssueID
 
 
I hope I have made query clear, if not I don't mind explaining more.
 
Thanks in advance
 

View 1 Replies View Related

ObjectDataSource 'ObjectDataSource1' Could Not Find A Non-generic Method 'GetRatings' That Has No Parameters.

Jun 2, 2008

I've been studying this tutorial over here at: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/databases.aspx#updatedelete
but I can't seem to get my gridview working.  I want to be able to edit the fields and also delete an entire row from the table if needed.
I hard coded in the update and delete commands into my objectdatasource but I still get the error message. Also, I can't even refresh the aspx page:
<%@ Page Language="VB" MasterPageFile="~/templates/admin.master" AutoEventWireup="false" CodeFile="article_comments.aspx.vb" Inherits="admin_Default2" title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="PageHeading" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="PageContents" Runat="Server">
&nbsp;`<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" Width="536px" DataKeyNames="ratingID">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="ratingID" HeaderText="ratingID" InsertVisible="False"
ReadOnly="True" SortExpression="ratingID" />
<asp:BoundField DataField="rating" HeaderText="rating" SortExpression="rating" />
<asp:BoundField DataField="ip" HeaderText="ip" SortExpression="ip" />
<asp:BoundField DataField="itemID" HeaderText="itemID" SortExpression="itemID" />
<asp:BoundField DataField="comment" HeaderText="comment" SortExpression="comment" />
<asp:CheckBoxField DataField="active" HeaderText="active" SortExpression="active" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetRatings" TypeName="articlesTableAdapters.tblArticleRatingTableAdapter" DataObjectTypeName="articles+tblArticleRatingDataTable" DeleteMethod="DELETE FROM [tblArticleRating] WHERE [ratingID] = @ratingID" UpdateMethod="UPDATE [tblArticleRating] SET [ratingID] = @ratingID, [rating] = @rating, [ip] = @ip, [itemID] = @itemID, [comment] = @comment WHERE [ratingID] = @ratingID">
<UpdateParameters>
<asp:Parameter Name="dataTable" Type="Object" />
<asp:Parameter Name="itemID" Type="Int32" />
</UpdateParameters></asp:ObjectDataSource>
</asp:Content>
>

View 4 Replies View Related

User-defined Fun Or The System-defined Fun

Apr 2, 2008

hai,

how can i identify the function is user defined or the system defined function....................

View 1 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

How Do I Use A Pre-defined Sql Query?

Jul 18, 2006

Hi there, hope you can help me. What should I do if I want to use a pre-defined string as sql query (SelectCommand)? So I want to use  the sqlquery string in the SelectCommand option...The reason is that this page is request like page.aspx?ID=1234, and I want to use the Request.QueryString option to define the sql query...<HTML><head><sript runat"server">Dim sqlquery As String = New String ("SELECT LastName FROM Employees")</script> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT LastName FROM Employees"> </asp:SqlDataSource> </FORM> </BODY></HTML>

View 1 Replies View Related

Seeting Self Defined PK

Apr 10, 2004

If i want to have a primary key with a self defined value for SQL Server 2000, is it possible to achive that ?

For example: i want the PK to start with "PK001" and it will autogenerate everytime a new record insert into the Table by increament the PK to "PK002", "PK003", "PK004".....etc

How can I achieve that ?

View 1 Replies View Related

DTSDestination Not Defined !!!!!

Dec 26, 2006

Hi, i'm a beginner in DTS / Activex , in SQL Server 2000.

I need to update 500.000 records of a TableB (containing 2.000.000 rows) with a TableA (containing 500.000 rows)

With Enterprise Manager I made a new DTS package (my first package!)

I define / drag 2 Connections as OLE (the first-right icon)
(but i don't define any Activities as dts. Then i define the following ActiveX (VBs) :


Function Main()

if DTSDestination("NDG") = (DTSSource("NDG")) THEN

DTSDestination("BANCA") = DTSSource("BANCA")
DTSDestination("NDG") = DTSSource("NDG")
'Pao: LA LOGICA DELLA TRASFORMAZIONE
if (DTSSource("PTF")) > 0 THEN
IF (DTSSource("SEGMENTO")) > 0 THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
ELSE
DTSDestination("CAMPO_N1") = DTSDestination("SEGMENTO_CLIENTE")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
END IF
ELSE
IF (DTSSource("SEGMENTO")) > 0 THEN
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = 0
ELSE
DTSDestination("CAMPO_N1") = DTSSource("SEGMENTO")
DTSDestination("PTF_CLIENTE_OLD") = DTSSource("PTF")
END IF
END IF
END IF
Main = DTSTransformStat_OK

End Function

+++++++++++

but when I run it I obtain the following error message: DTSDestination not defined, idem for DTSSource.


Please help me, it's IMPORTANT !

Thank You.

View 1 Replies View Related

LockoutThreshold Is Defined Where?

Aug 22, 2006

In Books Online, under Alter Login is the text.

A known issue in Windows Server 2003 might prevent the bad password count from being reset after LockoutThreshold has been reached.



What is LockoutThreshold?

View 3 Replies View Related

Help With User Defined Function

Jan 10, 2007

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date  is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE  FUNCTION GetStartDate (@startdate datetime, @days int)   RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules.  If 4 business days means you count starting tomorrow, set counter to 0.  If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date.  we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
   --If the day is between 2 and 6 (meaning it's a week   --day and the day is not in the holiday table, we'll    --increment the counter   IF (DATEPART(dw, @enddate) between 2 and 6) AND       (@enddate not in           (           select HolidayDate            from tFederalHoliday            where [HolidayYear] = datepart(yyyy,@enddate)         )       )   BEGIN      set @counter = @counter + 1   END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------

View 1 Replies View Related

SQL User Defined View

Aug 22, 2007

I've a view in which one of the fields were  DATEDIFF(day,contract date,received) AS AgeOfAccount. I changed it to DATEDIFF(day, m.received, CASE WHEN m.clidlp > m.clidlc THEN m.clidlp ELSE m.clidlc END) * - 1 AS AgeOfAccount. When I execute the view  I'm getting an error. But the results are getting displayed properly. That's what's puzzling me. Could someone tell me what the error is.
 
The following errors were encountered while parsing the contents of the sql pane. 
Your entry cannot be converted to a valid date time value.

View 4 Replies View Related

Type SqlConnection Is Not Defined

Sep 19, 2007

 Hi all.I am using VB.Net 2002 (.Net Framework 1.0) and Sql Server 2005 Express Edition as a database. I am following a tutorial to create a connection to a database.I have written this code to import namespaces at my .aspx file.  <%@ import namespace = system.data%><%@ import namespace = system.data.sqlclient%>And, I also put this code at my Page_Load event that resides at .aspx.vb file.  Dim conn As sqlConnection conn = New sqlConnection("server=SEN-M092082D001SQLEXPRESS;database=test;Trusted_Connection=Yes") conn.open() lblItem.Text = "Connection Opened!" Those codes builds an error which is "Type sqlConnection is not defined". Can someone explain to me why this happen? I already imported the namespaces   

View 2 Replies View Related

User-defined Functions - Help!

Sep 3, 2004

Hi,

I have a search returning many 'car' records (regno, mileage, color, etc)

In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.

Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.

I have written a function as such (with some help!):

ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END

I was hoping that I could simply use this in the 'car' SELECT statement.

ie:

SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc


I'm not even sure if the function works - How can I test in SQL analyzer?

any help much appreciated,

Pete

View 3 Replies View Related

User Defined Function

Aug 17, 2005

Hi everyone,
    I am tring to pass acomma delimited string to a function  and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this.  How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks

View 1 Replies View Related

Sqldatareader Is Not Defined Error

Oct 5, 2005

HiI try to retriveing data from database using following code (code-behind page). then put data into form fields. It tells me SqlDataReader is not defined. What does this mean? I am using Visual Studio.net and new to it. Please help. thnaks.---------------------------------------------------------------------------------------Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myReader As SqlDataReader = myCommand.ExecuteReader()
If myReader.HasRows Then
Do While myReader.Read()
txtfname.Text = myReader("fname")
txtlname.Text = myReader("lname")
Loop
Else
Console.WriteLine("No rows returned.")
End If
myReader.Close()
End Sub

View 7 Replies View Related

User Defined Functions In SQL 7.0

Oct 11, 2000

Hi,

I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.

I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.

Satish.

View 1 Replies View Related

User Defined SQL Function

Dec 14, 1999

How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:

select myFunc(x,y) from table

I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.

View 1 Replies View Related

User Defined Function

Aug 2, 1999

Dear friends,

I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.

Thanks in advance!

Kai

View 2 Replies View Related

Help Pls!! &#39;Not Defined As Valid User&#34;

Dec 16, 1999

all schedulled task on server failed for 18450 Process Exit Code 1. ...t Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 18450: [SQL Server] Login failed- User: sqlexec Reason: Not defined as a valid user of a trusted SQL Server connection.
Any information on how to solve it

View 1 Replies View Related

User Defined Datatypes

Jan 5, 1999

Is it possible to change the owner of a User Defined Dataype? I've
recently had to change the owner of one of my databases and the
tables from a specific user to 'dbo'. The UDD's are the only objects
I am unable to change the ownership of.

Any suggestions anyone?
Thanks for any advice!
Toni Eibner

View 1 Replies View Related

User Defined Function Help!

Jun 23, 2004

I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?

CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers

WHERE Shipments.AccountID = vwAccountTransfers.AccountID

AND vwAccountTransfers.EffectiveMonth =

(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers

WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments

ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END

View 1 Replies View Related

User Defined Function

Nov 10, 2004

I need a UDF that will do the following:

User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'

AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF

I would also like now to be my getdate(), but it seems like you can't use it in UDF

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved