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?
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.
One example of why I need this .....
I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.
So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.
This is too slow for big data sets since the find does a record by record search.
I cannot just save and restore the list control offset, the table may have changed.
The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.
Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)
Hello! I've begin to do a tutorial of this site: Working with Data in ASP.NET 2.0 c#, but in the 3. step I can't make something: In this step I can't add parameterized methods to the TableAdapter. The problem come up when I want to add Sql query. SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE CategoryID = @CategoryID The error message: Error in WHERE clause near '@'.Unable to parse query text. So, how can i use parameter in this method? tnx for the help Simpson
I have a method like follows: string EmpCount = null; DateTime _dtstart = Convert.ToDateTime(txtStart.Text.Trim()); DateTime _dtend = Convert.ToDateTime(txtEnd.Text.Trim());SQLProvider sqlp = new SQLProvider(System.Configuration.ConnectionStrings["ConString"].ConnectionString);string SqlText = @" select count(*) from employee where activeemp=1 and startdate BETWEEN @dtstart and @dtend; using (sqlp.Connection) { sqlpm [] param = { new sqlpm("dtStart", _dtstart), new SqlP("dtEnd", _dtend) }; }EmpCount = sqlp.ExecuteScalar(SqlText, param).ToString(); return Convert.ToInt32(mbrCount);
Then the method I am calling is:public Object ExecuteScalar(String sqlText, Sqlp[] param) {try { //Some Code here } } So in the calling method (ExecuteScalar), the second parameter is defined as an array, is it ok to have an array in the called method too?
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
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
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();}
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?
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
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.
Hi, I just have a Dataset with my tables and thats it I have a grid view with several datas on it no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error Unable to find nongeneric method... I've try to create an Update query into my table adapters but still not working with this one Also, try to remove the original_{0} and got the same error... Please help if anyone has a solution
In general (I know.. lots of factors) but in general will adding more RAM to a SQL box help the performance? I'm working w/ 256MB right now, and deciding whether to add 256MB or 512MB? Any advice
Thanks and sorry so generic but this is a vendored app and they know of design issues but for now that can't be changed.
I want to be able to query with a start date(@start) and then calculate 1 year from there. I figured since the date is of type integer just add 364 and it will give me the day before 1 year. So if its 1998-05-01, Id get 1999-04-31.
Here's the scenario: whenever a change (insert/update/delete) has occured in any of the tables, it needs to be logged by adding the changed table's name, and changed columns' names (if any) into the special ChangeLog table. So, is it possible for me to write a generic trigger that would get the name of the table it just executed upon and names of every column affected by the UPDATE and then insert those into the Log table? If this is not possible, how do you suggest it should be done?
I'm familiar with standard SQL but T-SQL is completely new to me, so please bear with me if my question makes no sense...
How to disappear the generic bar in the report, or to disappear some options (example export). I speak about the bar which in the top of the report or there are the numbers of pages.
Hello,I have a DataSet which results from accessing an SQL database.Each row is an Article and has various columns like Title, Description, etc.I have created a Generic List as follows:Dim Articles As Generic.List(Of Article)Article is a class with various properties. Each property is the same as each column in the dataset.I want to fill the Generic List with the data from my DataSet.How can I do this?Thanks,Miguel
Hello, Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way. Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties. This way, I design my database once, and customize it to fit any type of applications I need. Is there something like that or just dreaming? thanks
I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.
I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.
For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @tablename, @code, @desc, and then create a new record in the table specified by @tablename.
I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.
I am attempting to create a generic stored procedure in SQL Server 6.5 which returns a record count of a specific table passed in as a parameter. The stored procedure looks like this"
CREATE PROCEDURE asp_GetRecordCount @tablename varchar(30), @recordcount integer OUTPUT AS /* Generic stored procedure which returns number of records in a table. */ SELECT @recordcount = COUNT(*) FROM @tablename RETURN GO
The problem is that when I save the stored procedure, SQL Server balks at the FROM @tablename entry. Any suggestions? Thanks.
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:
Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.
Ready? The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to: - Find all fixtures in that season - Find all members who played in those fixtures - Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches - Sort the resulting data
I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.
I'm receiving "a generic error occurred in gdi+" processing error while running a preview of my report inside visual studio. When this report is opened by a client, their browser will just hang (Not Responding).
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
We want our database to be unicode complaint and i need a generic collation that should do the work irrespective of the fact what data is entered in the database. Can someone suggest me any generic collation for that.
Hey I'm using this DAL to populate a generic list called assetDetailListHowever I'm adding a 2nd result set to my stored proc, protected void GenerateAssetDetails<T>(DbDataReader dataReader, ref List<AssetDetails> assetDetailList) { while (dataReader.Read()) { // Create a new culture AssetDetails assetDetail = new AssetDetails(); // Set the culture properties assetDetail.FileName = dataReader.GetString(dataReader.GetOrdinal("filename")); assetDetail.Title = dataReader.GetString(dataReader.GetOrdinal("title")); assetDetail.AltTag = dataReader.GetString(dataReader.GetOrdinal("alt_tag")); assetDetailList.Add(assetDetail); dataReader.NextResult();// this is the 2nd result set AssetContent assetContent = new AssetContent(); assetContent.content_id = dataReader.GetInt32(dataReader.GetOrdinal("content_id")); how do I creatre another list .Add(assetContent); } // Close and dispose of the reader dataReader.Close(); dataReader.Dispose(); } #endregion }
