Hey fellas. Here's my situation. I have two textboxes where the user enters a "start" date and an "end" date. I want to search a table to find records who's "expired" column date is between those two dates provided by the user. The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond. Essentially, I think I'm looking for a SQL statement along the lines of:
SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE
CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END
AND
CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END
AND (Role.Description = 'Requestor')
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around. Anyway, how do I do this? Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL? If I can tackle it with SQL, where have I gone astray? I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
How would I debug such a query. I have a sqlCommand to which I add several parameters for an insert statement. if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?
Hi, I need to use parameters with the IN clause in a SQL statement like: select * from tableX where field IN (1,2,3,4) I don't know how to do that. I'm using SQLServer and OleDB.
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
I have an application where users can enter data into any (or all) of 6 search fields, to produce a filtered query.
This works fine using my Access version(see code below), but as SQLS2k cannot use "IIF", I tried to replace these bits with "CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
FROM (((tAudit LEFT JOIN tCategories ON tAudit.CategoryID = tCategories.CategoryID) LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID) LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID) LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType])) AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID])) AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])) AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID])) AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID])) AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
I've been driving myself nuts trying to get a sensible product search going. The existing live site search is just a LIKE %searchterm% on the Title field in our Products table. Fast, but not great ;) Talks between IT and Marketing have resulted in this being the desired results and order:
Exact Title match Substring Title match Substring Keywords match Substring Description match
OK, I can easily do this with UNION queries using LIKE (and a virtual "weight" column in each query), but the query takes too long. So I'm trying out SQL Server full text indexing, in an attempt to get the speed up (and the natural language stuff is just plain cool).
Where I'm running into problems is doing a FULLTEXT match on Description. It seems that to do a phrase match (e.g. "new york" should match only Descriptions where the phrase "new york" occurs) I need to enclose the search term in quotation marks in the query (or maybe single AND double quotes).
But using parameters in ASP.NET (which I'm supposed to do to avoid SQL injection attacks, yes?) I don't really have full control of the quoting - ASP.NET and/or SQL Server automagically quotes strings for me before passing them into the query. I think.
For example, this doesn't find any description matches: ========================================== Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york" set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight FROM Product m(NoLock) WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight FROM Product m(NoLock) WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight FROM Product m(NoLock) WHERE CONTAINS(*, '"@theSearchTerm"'))
ORDER BY theWeight DESC, m.ShortName ==========================================
But this one (where I put in the actual string instead of using the parameter) *does* get the desired results, including matches in the Description: ========================================== Declare @theSearchTerm varchar(100), @theSearchTerm1 varchar(100)
set @theSearchTerm = "new york" set @theSearchTerm1 = "%new york%"
SELECT m.TitleCode, m.ShortName, m.ShortDescription, 50 as theWeight FROM Product m(NoLock) WHERE m.ShortName = @theSearchTerm
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 40 as theWeight FROM Product m(NoLock) WHERE m.ShortName LIKE @theSearchTerm1)
UNION
(SELECT m.TitleCode, m.ShortName, m.ShortDescription, 30 as theWeight FROM Product m(NoLock) WHERE CONTAINS(*, '"new york"'))
ORDER BY theWeight DESC, m.ShortName ==========================================
Trying various permutations of quotes around the parameter gives me either syntax errors or undesirable results.
Has anybody tried this sort of thing? How did you do it?
Ok, just made a TableAdapter and the SELECT statement works fine and pulls data. But doing an INSERT is something different, it craps out wanting a login. No neat Wizard (or so I haven't found yet) that lets me give the TableAdapter the login info for the SQL Server, which I have. Do I need to write code with the login and password for the SQL connection instead? -Ed
Hi, I have a TableAdapter created in a Dataset. I'm creating a search function for a table, and here's how my code looks like in the "Add Query" wizard: Select * from Event where eventname like '%@EventName%' But when I click on preview data, I do not get the prompt to enter a value for the @EventName parameter, where went wrong?
This thread has also been posted under '.NET Data Access and Storage'. However, I have realized that the same code contruction using SQL Server connection- and tableadapter objects work fine so I am trying to get answers here also.
I am attempting to do transactional updates to SQL Compact Edition database using TableAdapter and TransactionScope like this:
using (TransactionScope ts = new TransactionScope())
{
SqlCeConnection sqlConn = new SqlCeConnection(connectionString);
myTableTableAdapter ta = new myTableTableAdapter();
ta.Connection = sqlConn;
ta.Update(dsmyTable.myTable); ts.Complete();
}
dsmyTable is a strongly typed dataset created through the Dataset Designer and populated with data from the database prior to the code sample above.
This all works fine. However, when removing the call to 'ts.Complete()' to simulate the transaction rolling back, data is still stored into the database.
Am I missing something here or does TransactionScope not support SQL Compact Edition? Any help is appreciated!
A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows: ------ TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter(); // Add a new modem modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN ); -------- And the error I get when loading the ASP.NET page is as follows: Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments --------------- Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter? -Ed
I created a new DataSet object using the wizard and had no probs, it's very straightforward. I created a GetProducts() method and also added a GetProductCount() method. I read somewhere that when the DataSet is saved, it will generate the TableAdapter classes and store them in the project nested under the DataSet object. This isn't happening, the files aren't there and yes I'm displaying all files. There *is* however the TableAdapter class but it's in not in the project, it's a temporary file w/ the following path:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.
I saved the DataSet several times, compiled the entire solution and even closed the solution yet this temporary file isn't getting saved where it should be. Does anyone have any ideas abt this? I'm eager to start developing my DAL and def want to use these TableAdapter classes.
hii am using the nothwind database for a current exercise, i am wanting to user to be able to search for products based on two input methods (product name, category).here is the code i currently have: SELECT DISTINCT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, ProductName, ProductID, CategoryIDFROM ProductsWHERE (CategoryID = @categoryID) OR (ProductName LIKE '%' + @ProductName + '%')i'm wanting to have it set so the user can use both input methods, or either, when one input method is used by the user it works fine, but when both are used it generates results for 2 seporate queriesi'm wanting to have it set so the user can search by product name in certain categories (when both search input methods are used)how would i go about doing this? is there some kind of equivalent to an AND/OR statement?eg. WHERE (CategoryID = @categoryID) AND OR (ProductName LIKE '%' + @ProductName + '%') thanks in advance!
Below is my code right now Im using a direct sqlconnection for each request. I would like to use Tableadapters and/or my BLL code like the rest of my sites pages. I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)Thanks for the help.NeilPrivate Sub GetAllEvents(ByVal SqlString)    Response.Clear()    Response.ContentType = "text/xml"    Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)    objX.WriteStartDocument()    objX.WriteStartElement("Events")    Dim objConnection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|*********.mdf;Integrated Security=True;User Instance=True")    objConnection.Open()    Dim sql As String = SqlString    Dim objCommand As New SqlCommand(sql, objConnection)    Dim objReader As SqlDataReader = objCommand.ExecuteReader()    While objReader.Read()      objX.WriteStartElement("Event")      objX.WriteAttributeString("EventId", objReader.GetInt32(0))      objX.WriteAttributeString("EventName", objReader.GetString(1))      objX.WriteAttributeString("EventDescription", objReader.GetString(2))      objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))      objX.WriteAttributeString("CurrentDate", Date.Now.ToString)      If Not objReader.IsDBNull(12) Then        objX.WriteAttributeString("EventImage", objReader.GetString(12))      End If      objX.WriteEndElement()    End While    objReader.Close()    objConnection.Close()    objX.WriteEndElement()    objX.WriteEndDocument()    objX.Flush()    objX.Close()    Response.End()  End Sub
Hello, I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file. How can I get it to do so? It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.
I like to run a query from my tableadapter, but if i insert a row i like to know the id of it.So i was looking on asp.net and found a tutorial for it. But it doesnt work! My inset query: "INSERT INTO Klanten (BedrijfsNaam, Contactpersoon, Adres, Postcode, Woonplaats, Telefoon, Mobiel, [E-mail], Website, Kvk, BTWnr)VALUES (?,?,?,?,?,?,?,?,?,?,?); Select SCOPE_IDENTITY()" As database i user MS SQL Express.Also i tried it with @@Identity, but that works eighter.If i try the insert code with the scope identity above, and i put it in the Query builder from MS Visual Studio, it returns an error.. : "The following errors were encountered while parsing the contents of the SQL pane: Unable to parse query text."This error is only there if i add scope_idititiy or @@identity to it.Also if i run it in the SQL part of SQL Studio Express i get the same error. So should i change this?Or how can i get the id of it ?Of course i can read the database after the insert, but its not save enough i think.
In most places the code works fine. It saves updates to the table. However, in two tables it is not working. It does not throw any exceptions, but does not save the record.
The subs that work and the ones that dont are coded exactly the same.
Hi,I always use the following sql statement to check whether an email address exists in a database. With my latest project I tried to do this within the TableAdapter Configuration Wizard and it gave me an error: IF NOT EXISTS (SELECT Email FROM tblEmailList WHERE Email=@Email) BEGIN INSERT INTO tblEmailList (Email, DateRegistered) VALUES (@Email, @DateReg) SELECT 1 AS RESULT END ELSE BEGIN SELECT 0 AS RESULT END first it gave an error saying I must declare the variables @Email and @DateReg then when I removed the part of insert and just wanted to check the email address existence it gave me this error:The Compound statement SQL construct or statement is not supportedAny idea anyone?
I want to select rows that have an integer value IN a parameter list value I want to pass an integer list to my tableadapter's select as in Select * from sometable Where myIntValue IN (@intParamList )
I cannot figure out how to get my list into @intParamList It works fine as a single value - eg 3, but how do I set it to 1,2,3 ? Thanks Bill
I am designing my first database (Visual Basic & SQL Server Express 2005) and it appears as if the database isn't updating. I am running it by hitting F5, closing by hitting the "X" and then hitting F5 again to check if the changes have stuck. They don't. These are the instructions given in the tutorial.
Any ideas? Thanks.
In particular I have tried two step-by-step tutorials distributed by Microsoft: 1. Absolute Beginner's Video Series, Lesson09; and 2. the Help tutorial: Managing Your Records (ms-help://MS.VSExpressCC.v80/MS.NETFramework.v20.en/dv_vbcnexpress/html/1ccdb8f1-4162-4a54-af17-231007eb529b.htm)
The code for the form is:
Public Class Form1
Private Sub AddressesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddressesBindingNavigatorSaveItem.Click
i have a problem with my smart device application that i develop.
To simplify:
1) C# smart device appliccation 2)a database file *sdf 3)a typed dataset using the wizard to do so 4)TableAdapter with connection string with no problems(cool) 5)use of Insert method that returns 1 ( also cool) [--> MyTableAdapter.Insert("blabla","blablabla"); <------] 6)no saved records in the DB file(baaaaaad)
Hi,I have a table adapter that I am using to call a stored procedure on a SQL server database to insert a record.Everything works ok, except I cannot figure out how to pass a null value to a DateTime field. In my SQL server database, the column allow nulls. In my typed dataset, the column is set to allow nulls.When I try to run the insert procedure with a null value: ListTableAdapter da = new ListTableAdapter(); da.InsertList(System.Data.SqlTypes.SqlDateTime.Null, Name); I get this error: Error 14 Argument '1': cannot convert from 'System.Data.SqlTypes.SqlDateTime' to 'System.DateTime?I've also tried passing it DBNull.Value, but I get an exception with that too.Can anyone steer me in the right direction with this?
Hello, I have a query that works in query analyzer; it looks that a certain date is between the start and end date of a certain value. I also have a status field, which can be null, but if provided, provides the appropriate status to filter by. Again, the query works in QA, but not in the application. I test in SQL by using start date = '1/1/1900', end date = '12/31/9999', and status = null. Results are returned. But, not when the results are done through code. In code, I set the begin date to new DateTime(1900, 1, 1), the end date to DateTime.MaxValue, and the status to a null string. But, no results are returning. Why isn't that mapping over correctly? In the function, it has the two dates as Nullable(Of DateTime), which I provide a date, and the string is getting passed Nothing. Any ideas? Can't post any code on this one... Thanks.
I have a stored procedure which returns 3 different kind of values. I am checking whether a certain value entered by user is present in one of the columns of database table. Accordingly the SP returns 1 if present, -1 if not present and third value is SQL server 2005 error.But the problem is that I am only getting -1 everytime even if the value is present.I executed the SP alone to find out if it is the one which is returning the INCORRECT value. I found that that SP is returning the correct value.Therefore I came to the conclusion that it is the Table ADapter which got corrupted.I deleted the TableAdapter and created it again, but then it didn't solve the problem.I have now run out of ideas. The code of the SP is:ALTER PROCEDURE spcheck_ServerName (@Server_Name nvarchar(50)
) ASDECLARE @Result int IF EXISTS ( SELECT NULL FROMServerDetails WITH (UPDLOCK) WHERE
[SERVER NAME] = @Server_Name ) BEGINSELECT @Result = 1 END ELSE BEGIN SELECT @Result = -1 END
IF @@ERROR <> NULL BEGIN SELECT @Result = @@ERROR END RETURN @Result And I am calling the tableAdapter method in the code behind file of the web form in the following manner:private int chkServerName(string sname1) { try {Serverlist1TableAdapters.SERVERDETAILSTableAdapter nwAdapter = new Serverlist1TableAdapters.SERVERDETAILSTableAdapter(); int snval = (int)nwAdapter.spcheck_SName(sname1);return snval; }catch (Exception ex) {return ex.GetHashCode(); } }
