IS there ny substitute for this ?
=First(Fields!TouchedBy.Value, "Dataset1")
instead of keyword 'First' i need the current record , but also i need to refer dataset name also , is there any way to do that ?
Any idea?
thanks
I only want to select few records from table
Keyword "Top" can't be used in SQL Mobile, is there any substitute?
Hello folks,
I've got a table like this
PRODUCT_ID LABEL_ID
----------- -----------
6 1
6 2
7 1
1 1
2 1
I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.
I'm currently working on something like a search engine. Hope to have some good advice here :)
I'd like to change query:SELECT DM.*, 'condition1', NULL FROM DMWHERE (condition1)UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE (condition2)to one SELECT like thisSELECT DM.*, WasCondition1, WasCondition2 FROM DMWHERE (condition1) or (condition2)but how to fill in the WasConditionX column?The UNION version was bad because in case a row fulfilled 2conditions, it was repeated instead of joining them, such like this:SELECT DM.*, 'condition1', 'condition2' FROM DMWHERE condition1 AND condition2UNIONSELECT DM.*, 'condition1', NULL FROM DMWHERE condition1 AND NOT condition2UNIONSELECT DM.*, NULL, 'condition2' FROM DMWHERE NOT condition1 AND condition2or (say DM has columns A, B, C):SELECT A, B, C, SUM(cond1), SUM(cond2) FROM(SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DMWHERE condition1UNIONSELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DMWHERE condition2) AS DMGROUP BY A, B, C
View 12 Replies View Related
Hi,
I'm using sql server 2005, i want to know is there any substitute for BCP in sql server 2005,
like how we have DTS in 2000 and SSIS in 2005,
I heared that BCP will be deprecated in the next version of Sql server.
so i dont want to implement BCP in 2005 version,
Will someone please tell me whether i have to implement BCP in sql server or we have some other option,
i dont want to use SSIS instead of BCP because latter is faster.
Thanks
Altaf Hussain Nizamuddin
Do anyone know how I can replace or Substitute the current value of a form textbox with another value.For example, the string "YES" appears on the textbox but hit the SAVE button I would like "TRUE" to be entered in the SQL Server database table. Thanks.
View 6 Replies View RelatedHi
I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.
I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.
But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.
In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:
if (txtrangefrom.Text != "") {
objQuestion.RangeFrom=float.Parse(txtrangefrom.Text);
objQuestion.RangeTo=float.Parse(txtrangeto.Text);
}
else {
objQuestion.RangeFrom=Convert.ToSingle(null);
objQuestion.RangeTo=Convert.ToSingle(null);
}
And this is what my Business object look like. It sets up the parameters and calls the Data Access Application Block to create an entry in my table:
// fieldslist
float cvintRangeFrom;
float cvintRangeTo;
//properties
public float RangeFrom {
get {
return cvintRangeFrom;
}
set {
cvintRangeFrom = value;
}
}
public float RangeTo {
get {
return cvintRangeTo;
}
set {
cvintRangeTo = value;
}
}
// some code deleted for readability....
public int AddOption() {
string cvstrSpName = "addOption";
SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true);
//lines deleted for readability...
//check if the optional fields have a value associated with them. if not, assign dbnull.value.
cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value);
cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value);
//lines deleted for readability...
SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams);
return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value));
}
I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.
The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!
objQuestion.AddOption();
//txtrangefrom.Text=""; on the next line I test the value in the business object...
txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!!
//txtrangeto.Text="";
txtrangeto.Text=objQuestion.RangeTo.ToString();
So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser...
Any help or ideas will be greatly appreciated...
Hello once again,
My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed.
I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code.
What are my options? Any help would be greatly appreciated.
Pettrer
hi,
the query ----> select format(Total,"0.00") from mytable works very well in MS Access 2000/97 but the same query dosen't work in MS SQL Server 2000.
i need the substitute for this query. please help me it's very urgent
thanks a lot for consideration
Vikas
Hi,
I need to store large note in SQL Server field, what is substitute of Access's Memo filed in SQL Server?
Appreciated...
Objective
Do a manual installation of SQL Server 2005 (which could be an upgrade from SQL Server 2000) and record the steps. Replay the steps for an unattended installation of SQL Server 2005.
Problem
There isn't a setup.ini file for SQL Server 2005. There is a template.ini for unattended installations of SQL Server 2005, but it doesn't obviously map to selections on manual installation dialog boxes.
Desired workaround
Capture the steps of a manual installation and use them to populate template.ini.
Relevant background for a potential solution
A manual installation creates log files. So does an unattended installation. But the unattended installation writes all of the log files into a single zip file.
What is important to know is that an unattended installation creates an additional file, a *datasource.xml file. This XML file is found in the zip file with all of the other log files. If you open this XML file, you'll see that the attribute names correspond to keywords in template.ini.
Potential solution
If a manual installation could be forced to generate a *datasource.xml file, then it should be possible to map the settings in the file to keywords in template.ini.
So, how can a *datasource.xml file be created? In post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359678&SiteID=1, Jeffrey Baker says "add LOGNAME=<path to cab> and run setup again". Unfortunately, these instructions are too vague. Where is this added? To the command line, to a setup.ini (if so, in which directory, and in which section should he addition be made?). I did lots of searches and couldn't find the answers.
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544'
HourPlanned = 14
new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
How can I do that?
Is it possible to change any line breaks or newlines for a string ...
In a select statement in a store procedure, in this way I can reproduce text in a label on a webpage complete with line breaks ....
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows
2 If row.RowType = DataControlRowType.DataRow Then
3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox)
4 If chk IsNot Nothing AndAlso chk.Checked Then
5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text)
6 ids &= "," & id
7 End If
8 End If
9 If ids.Length > 1 Then ids = ids.Substring(1)
10 'Submit to sql with ids as param...
11 Next
I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
I need to retrieve records where the date is in between the current date and 4 days previous.
I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...
Can someone help out?
It's blue in SQL Studio but I can't find it in SQL Books on-line. Iwanted to use it as a field name but don't like using [].Is it reserved?
View 6 Replies View RelatedHi guys,
Just a newbie question here.
Do we really need to use the GO keyword?
Thanks,
MeTitus
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View 7 Replies View RelatedHi everyone,
I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that:
private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}}
The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL();
userDAL.DoSomethingAndAccessConnectionProperty();
// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
So, if I do the following:using (UserDAL userDAL = new UserDAL())
{
userDAL.DoSomethingAndAccessConnectionProperty();
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection);
documentDAL.AlsoDoSomething();
}
I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct?
ie,
SELECT DISTINCT fieldA, fieldB
FROM tableA
but not
SELECT fieldA, DISTINCT fieldB
FROM tableA
or
SELECT DISTINCT fieldA, DISTINCT fieldB
FROM tableA
thanks again, this is a great forum
ddave
hi guys i have a query that contains several table joins
when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds
is adding distict keyword or order by acts as an index for the query or what ?
.
.
.
.
here is my query :
SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT
FROM dbo.TECHNICAL_MAIN p INNER JOIN
(SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j
FROM technical_main m
WHERE revision_no IN ('0', '1', '2')
GROUP BY char_field1, char_field2_ar, subcat_id) b
ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2')
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID
INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j
Good luck for all the folks
Hi all,
I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).
The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.
Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:
SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID
But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.
and how do I write an if phrase?
Shimi
I heard a claim recently at a SQL Server users group meeting that theTOP keyword forces sorting on a database server. I can't find anyreason this might be true and the Books Online say nothing about it.Can someone verify this claim one way or the other?
View 5 Replies View RelatedI am studying for the MSCE/MCDBA exam 70-229. In the book I am using("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000Database Design and Implementation Exam 70-229, Second Edition") I amlooking at the section on the ANY/ALL keyword.<QUOTE>USE PubsSELECT TitleFROM TitlesWHERE Advance > ANY(SELECT AdvanceFROM Publishers INNER JOIN TitlesON Titles.Pub_id = Publishers.Pub_idAND Pub_name = 'Algodata Infosystems')This statement finds the titles that received an advance larger thanthe minimum advance amount paid by Algodata Infosystems (which, in thiscase, is $5,000). The WHERE clause in the outer SELECT statementcontains a subquery that uses a join to retrieve advance amounts forAlgodata Infosystems. The minimum advanceamount is then used to determine which titles to retrieve from theTitles table.</QUOTE>I don't understand why this references the "minimum advance". If yourun the subquery on its own, it returns the following values:5000.00005000.00005000.00007000.00008000.0000NULL[color=blue]>From my limited understanding, the "ANY" keyword applies to at least[/color]one value, but which one? How is this determined?Any help gratefully received.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 4 Replies View RelatedI am in the process of importing an Oracle database into SQL Server.Once of the tables has a field called "EXEC".SQL Server seems to reject any queries that include that particularfield because EXEC is a keyword. For eg.SELECT ID, EXEC from USERSresults in a syntax error near keyword EXEC.I can't change the fieldname becuase it will require reworking of awhole bunch of scripts.What can I do to adjust the query?Bijoy
View 3 Replies View RelatedIs possible in SQLSERVER to use a syntax like this?select x,y from table_1 where (x,y) not in (select h,k from table_2)I've tried, but it doen't work.Do you know any workaround?Thank youFede
View 3 Replies View RelatedI've been trying to figure out the usage of "Nothing" as the scope parameter, and the more I try, the more I can confused.
It says on the RDL spec:
"For expressions inside data regions:
.......
Specifying the keyword Nothing as the scope is equivalent to specifying the
outermost data region containing the report item in which the aggregate is used."
It seems that what's it saying is that if I have an expression in a table as
= last(somefield, "the name of the table")
is the same as
= last(somefield,Nothing)
But, apparently it;s not. (I tried last, first,sum, count,CountRows, min, max...........)
it doesn't matter where I put "=last(somefield,Nothing)" in the table (i tried table header,footer, detail, and table group header, footer), none of them worked.
and I tried it everywhere in matrix, in charts.
It's just not working.
It always complains about the invalid scope not being the containing data region, containing grouping or dataset name.
However, the only way I get the "Nothing" as the scope to work is in a RunningValue function, not in any other
aggregate funcitons.
Anyone help me with this, please... I need a complete definition on the usage of Nothing as scope.
Hi,
I have a table like
ProductId, Description, Description2
where Description and Description2 are text datatypes.
I'm trying to return all records where myKeyword exists as a singular word in either of these two fields.
Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table?
Many thanks for any pointers
Dan