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
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.
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)
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)
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>
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?
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
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
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.
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();}
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?
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?
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.
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"> `<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> >
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?
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>
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
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.
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 ---------------------------------------------------------------------------------------------
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.
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
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?
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
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
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.
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.
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
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
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
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