SQL Select Command With Where Clause Variable Based On ASP.NET TextBox
Apr 19, 2007
All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View 2 Replies
ADVERTISEMENT
Dec 28, 2004
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list
DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name>
select * from x
where x is a variable and the table name gets substituted.
what is the syntax for it ?
View 7 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Nov 8, 2004
select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.
View 2 Replies
View Related
Mar 28, 2000
Hi
I am ramesh here from go-events.com
I am using sql mail to send out emails to my mailing list
I have difficulty combining a select statement with a where clause stored in a variable inside a cursor
The users select the mail content and frequency of delivery and i deliver the mail
I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them.
Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code
that does not work
For example
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary
How do I combine the select statement with the where clause?
Help me...I need help urgently
View 1 Replies
View Related
Sep 9, 2014
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
View 6 Replies
View Related
Nov 4, 2006
Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Mar 17, 2008
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
View 4 Replies
View Related
Jun 4, 2007
Hello Everyone,
How do you display a dataset query result in a textbox?
Thanks for the easy answer...
View 10 Replies
View Related
Mar 11, 2005
hi im trying to code a login page using asp.net, vb.net. i have 2 web-controlled textboxes whose values i want to compare to userID and password stored on a SQL server database called users. When button clicked, call function checklogin.
heres the code:
Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
If Page.IsValid Then
Dim usersDS As New System.Data.DataSet
usersDS = checklogin(userID.Text, password.Text)
If usersDS.Tables(0).Rows.Count = 1 Then
FormsAuthentication.RedirectFromLoginPage(userID.Text, False)
Else
error_log.Text = "Invalid Credentials: Please try again"
End If
End If
End Sub
Function checklogin(ByVal userID As Char, ByVal password As Char) As System.Data.DataSet
Dim connectionString As String
Dim dbConnection As New SqlConnection
Dim dbCommand As New SqlCommand
Dim dataAdapter As New SqlDataAdapter
Dim ds As New DataSet
connectionString = "server=localhost;user id=sa; password=chaos; Integrated Security=SSPI; database=users"
'server=localhost; database=users; integrated Security=SSPI;Â?@user id=sa; password=chaos
dbConnection.ConnectionString = connectionString
With dbCommand
.Connection = dbConnection
.CommandText = "SELECT COUNT (*)Â?@AS pass FROM tbl_users WHERE ((tbl_users.userId = @userId) AND (tbl_users.password = @password))"
End With
dataAdapter.SelectCommand = dbCommand
dataAdapter.Fill(ds)
Return ds
End Function
Heres the error i get:
Details of exception: System.Data.SqlClient.SqlException: There is a syntax invalid near
line {''1
Source error:
Line 75:. CommandText = "declare@userID As varChar; declare @password As varChar; End With .."Line 76:.. line 77:of SELECT COUNT(*) AS pass FROM tbl_users WHERE((tbl_users.userId=@userId)AND(tbl_users.password=@password))
Line 78: DataAdapter.SelectCommand=dbCommand
Line 79: DataAdapter.Fill(ds)
ive just started using .net, im a lil lost...thanks for any help. cheers
View 1 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
Jun 11, 2010
I have two charts which are based on their own separate datasets. One chart shows data aggregated by MONTH, the other chart shows data aggregated by WEEK. I have put both of these charts inside a rectangle and set their visibility based on whether or not there is data returned by their respective datasets. This part is working just fine.
However, I have added a tablix to the report, deleted all columns and rows leaving only one remaining row/column so it looks like a textbox. This tablix is sitting on top of the two charts. The problem i'm having is that when both charts return no data and they do not show up in the report, the tablix still remains visible. I need to set the visibility but based on a condition between the two different datasets behind each chart.
Here's what I need: If ONE of the charts returns data, then I need the tablix to be visible. If neither chart returns data, then I need the tablix to be invisible. I'm trying to write an IIF statement comparing the two datasets but I get inscope errors. Isn't it possible to compare values which are contained in two separate datasets in SSRS?
Seems like something like the following logic should work:
IIF(CountRows("Monthly_DataSet") > 0 OR CountRows("Weekly_DataSet") > 0 , FALSE, TRUE)
Obviously the above expression will return syntax errors, but you'll get the point as to how i'm thinking. How can I set the visibility of the tablix based on these two charts?
View 4 Replies
View Related
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Jul 5, 2005
Is it possible to do this as i want to select the top based on the value i enter in the textbox. usually we using this
SELECT TOP 10 * FROM Customers
But i want the value is get from my textbox.
View 1 Replies
View Related
Feb 28, 2008
I want to create a details view page without using the DetailsView control. I would prefer to have separate textboxes or labels for each column. I just don't have a clue how to go about after the Select is done putting the data into the boxes. Below is as far as I've gotten. The purpose of the label is just my way of wanting to display the QueryString value, it serves no other purpose. protected void Page_Load(object sender, EventArgs e) { { string tktid = Request.QueryString["id"].ToString(); Label1.Text = tktid.ToString(); } { SqlCommand cmd = new SqlCommand("Select tkt_creator from Ticket where tkt_id = @tktid", HelpdeskConnectionString); HelpdeskConnectionString.Open(); cmd.ExecuteReader(); Label2.Text = ?; } Any help would be appreciated. So far, I've learned a lot from this forum. Y'all have been great.
View 3 Replies
View Related
Jul 31, 2007
i am english very bad sorry
="Parametre: " + IIF(Parameters!TIP.Label="(Select All)","ALL",String.Join(", ", Parameters!TIP.Label))
not working
View 8 Replies
View Related
Aug 30, 2007
Hi,
I have my backup scheduled as shown below
at 9.00am -- full backup
at 9.15am - Transaction log backup
at 9.30am - Transaction log backup
Suppose at 9.20am(say) the user deletes a table and i want to restore that particular table,i will use STOPAT clause when im restoring the transaction backup at 9.30am (WITH RECOVERY).But my doubt is how to find out the exact time the user has deleted the table.I have even checked the SQLServer Error log file but it doesnt give the exact date and time stamp of the deletion activity tht has taken place so that i can use that in STOPAT clause of RESTORE LOG command.
Please tell me where should i look for date and timestamp for all the Deletions/Updations done on a particular database.
Thanks in advance.
Regards
Arvind L
View 5 Replies
View Related
Jun 14, 2014
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
View 2 Replies
View Related
Jun 21, 2008
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**
if no employee is absent in the department it has to display Zero
View 2 Replies
View Related
Sep 13, 2007
I am writing a fairly simple sql, and I would like to write something like
Code Snippet
select
firstname as firstname,
case
when firstname = 'Peter' then 'yes'
else
'no'
end as whatever
from
MyTable
where
whatever = 'yes'
And this should then select out the rows where column number 2 is 'yes'.
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
But why?
View 4 Replies
View Related
Aug 22, 2001
I am trying to use a local variable and case statment in a where clause but I keep getting an error. I'm passing an input parameter into a stored procedure and based on the input parameter I want the WHERE clause to reflect.
spOpenClosedList @openclose varchar(5)
DECLARE @result varchar(12)
if @openclose = 'open'
Begin
set @result = 'is null'
end
else
set @result = 'is not null'
SELECT * FROM WorkOrderTbl
WHERE WorkOrderTbl.CompletedDate @result
AND username = "Thomas"
View 3 Replies
View Related
Jul 7, 2000
Is it possible to have a variable stand for the table names in a FROM clause for a select statement?
If not, does anyone have any suggestions on how do do this instead?
Thanks for any and all help,
Michelle
View 1 Replies
View Related
May 5, 2013
How can I achieve the following in SQL:
SELECT
@CURRCOLUMN,
COUNT(*)
FROM
CUSTOMERS
WHERE
@CURRCOLUMN = ""
It gives me an error. I basically need to loop through an entire tables variables to find out occurances of the column being either "" or null and return me a count of the number of occurances. This is just a small part of that task.
View 14 Replies
View Related
Oct 10, 2006
I have a table name emp(empid) having one record as emp1.
Now i am writing
DECLARE @EmpID VARCHAR(8000)
SET @EmpID = '''Emp1'',''Emp2'''
Select * from emp
where empid in(@empid)
But there are no rows returned
can't i use variable in IN Clause. I have to achiev this backend only.
View 6 Replies
View Related
Jan 11, 2008
Hi Gurus,
Can any one help me making thhe following query work without using dynamic sql?
use northwind
declare @fc varchar
set @fc = ' CustomerID '
Select * from dbo.Customers
where @fc like 'a%'
Thanks a lot in adv
Korr
View 9 Replies
View Related
Sep 21, 2007
I have a problem....SOMEONE PLEASE HELP!
Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet11 = New links.DataSet1
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _
"ersist security info=False;initial catalog=DTR"
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _
"ummary "
'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')"
'THE STATEMENT ABOVE DOESNT WORK
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
'Open the connection
SqlConnection1.Open()
TextBox1.Text = "Connection Open"
'Populate DataSet11
SqlDataAdapter1.Fill(DataSet11)
TextBox1.Text = "DataSet11 has been filled!"
'Display Data
DataGrid1.DataBind()
TextBox1.Text = "Here is your requested information"
Catch ex As Exception
TextBox1.Text = ex.Message
End Try
'Close the connection
SqlConnection1.Close()
End Sub
View 6 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Nov 29, 2007
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View 1 Replies
View Related
Nov 18, 2003
OK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
View 1 Replies
View Related
Dec 30, 2003
Hello DBAs:
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL
WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
How do I construct this logic. Please help
View 6 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum             PolicyStartdate
123GÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/01/2012Â Â Â Â
456DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 02/16/2012Â Â Â Â Â
789AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 01/21/2012
163JÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex:Â policynum ='456D'Â Â Â Â It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Apr 28, 2012
Code:
CREATE TABLE myTable
(
Id int primary key
,iLow money
,iHigh money
,iClose money
[code]...
View 3 Replies
View Related
Jun 14, 2007
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
View 4 Replies
View Related
