Hi I wanna put string in query "where" part.
For example,
$sql="select VEHICLE
FROM database
WHERE MECHANIC =BRIAN
";
like above, "mechanic" column is filled with strings. Then how can I
write "where" part?
Above query does not work.
Thanks.
When i use the following querry i get no result when i know i should be getting one.
Select *
from table1
where '16bbbT1' >= StartStr and '16bbbT1' <= EndStr
When i use
Select *
from table1
where '16bbbT1' >= StartStr
I get all the values above 16bbbT1
and conversly when i use
Select *
from table1
where '16bbbT1' <= EndStr
i get all the values less then 16bbbT1
I need to plug both where clause
can anyone help
need to look through hundreds of rows and compare two columns, and pull out rows where the date difference is 7 days
problem is one column stores the date as a string and values in that column look like this:
01/12/2007 10:15 pm
or
01/12/2007
the other column stores the date as seconds since 1/1/1970, which is good and easy to work with..
how do I write the select?
any ideas
thnx
tony
Ok, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
I have used a query statement with the following WHERE string to 'Fill' a dataset.
"AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found:
In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15).
My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime?
TIA,Jeffrey
Hi All,
I have a column in my table like so:
'D4B00 L2A00 L3A00 L6C00 P1C00 L2A28 P4B00 '
How do I check in SQL if any pieces have the first 3 character the same. In the above case, L2A is present twice. I need to do this because I need display disctinct items, therefore L2A needs to be displayed only once.
Any help is appreciated.
Thanks
I need to compare to string in sql
requirement is
Select Reson_id from reason_data where reason='ryete / werewr ddsad '
is there any way without string function? bcz string function take more exaction time
CREATE FUNCTION [dbo].[Compare]
(
@usrVal varchar(100),
@dbVal varchar(100)
)
RETURNS BIT
AS
BEGIN
DECLARE @flag BIT
DECLARE @userStrLen INT
DECLARE @dbStrlen INT
DECLARE @counter INT
--Default assignments
SET @counter=1
Set @userStrLen=len(@usrVal)
Set @dbStrlen=len(@dbVal)
IF (@userStrLen != @dbStrlen)
BEGIN
return 0
END
ELSE
BEGIN
WHILE @counter <= @userStrLen
BEGIN
IF (substring(@usrVal,@counter,1) != substring(@dbVal,@counter,1))
RETURN 0
SET @counter = @counter + 1
END
return 1
END
RETURN @flag
--SELECT dbo.Compare('sqlserver user defined functions','sqlserver is the best backend')
END
is there a way to have a select statement which compares a value is like ('%a%','%b%','%c%','%d%','%f%','%l%')
so :
select address
from customers
where address like ('%a%','%b%','%c%','%d%','%f%','%l%')
???
Hi,I have a funny Error in our sql server 7 & 2000. When I compare twostring in Unicode format. I receive that it is the same when I add newnchar.I don’t understand why?Could you help me ?declare @str nvarchar(128), @str2 nvarchar(128)Set @str =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)Set @str2 =nchar(21121)+nchar(49352)+nchar(47811)+nchar(48256 )+nchar(4966)+nchar(25736)+nchar(1788)+nchar(51220)+nchar(17733)+nchar(4 1273)select @strselect @str2SELECT DIFFERENCE(@str, @str2)if soundex(@str) = soundex(@str2)BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'ENDif @str = @str2BEGINSELECT 'OK'ENDELSEBEGINSELECT 'KO'END-=zoltux=-*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
View 2 Replies View RelatedHi,
I am trying to write a query to compare the same column in each table with "not equal" expression.
My query is like this:
select tableOne.empl_ser_no from tableOne, tableTwo
where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)
I am getting the following message from SQL Server:
Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.
I am new to writing the queries in SQL.I want to write a query which will compare the data of two tables which are resides in DEV server and PROD Server.For the conncetivity purpose we are creating the DB link between DEV and PROD server.query to compare the data of table in DEV and table in PROD
View 2 Replies View RelatedI have a database I want to query. It is a medical billing system. I basically want to compare a list of Names and Birthdates in an Excel spreadsheet to the table of patients in the database that have that insurance type and return only the rows from the spreadsheet that don't exist in the database.
This is an eligibility list, so we need to find those that don't have this insurance set up for them in our billing system and update their records.
We will probably want to do the update manually so I don't mess it up too bad , but would like to zero in on who needs the updates rather than having to look at every single record.
Any ideas on how to approach this?
Would this be a join, or would I need to do a select from table 1 where pt/insurance not in select pt/ins from table 2?
I have a table with the following columns
company_Id employee_id logon_time_id logoff_time start_valid_period end_valid_period
Employee's working time should only be counted if it is between start_valid_period and end_valid_period
So, if I have for employee1 from company1
logon_time_id = 04/07/2006 11:00
loggoff_time = 04/07/2006 12:20
start_valid_period = 04/07/2006 12:10
end_valid_period = 04/07/2006 12:30
I should consider 04/07/2006 12:10 as the initial datetime, 04/07/2006 12:20 as the final datetime, and count only 10min of work to him. In code:
if(logon_time_id < start_valid_period) initialDatetime = start_valid_period else initialDatetime = logon_time_id
if(logoff_time < end_valid_period) finalDatetime = logoff_time else finalDatetime = end_valid_period
Is there anyway I can do this in a query, without using a stored procedure with "ifs" and everything else?
Thank you!
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.
The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.
My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.
I'm very new at using SQL and my knowledge of the query syntax is very basic.
Thanks for any help in advance!
~Lacy
Okay all I have a problem. I have two list of adresses and phonenumbers. I do not have control over the contents of the lists The onlyunique field between the two is the phone number. I need to be able toinner join the two lists on phone number.This would normally be straigt forward but the problem is that they areformated different and one of them does't even have a control on theformating.*Phone numbers are US phone numbers only.The one list (table) that does have a control uses this formatAAA3334444where AAA is the area code333 is the 3 digit prefix4444 is the four digit suffixthe second list (table) does not have any standardized formating andcan be filled with extraneous spaces, parentheses and dashes not tomention the leading 1 in some instances.I thought that I could do some kind of regular expression to do acomparison but I havn't as yet found a good resource to tell me how todo it or if it is even possible. Or maybe break up the one I know hasa standardized format into something like this:'%AAA%333%4444%' and doing my comparison that way. however It is veryimportant that only those list items in both list that are truly thesame place be listed.suggestions and solutions are apreciated
View 3 Replies View RelatedCan We Compare alias of column(Derived column) within the same query??
Ex:
Select (abc+50)*100 as 'WXY' from XYZ where WXY>150
.....
I cant execute such statement ... Can anyone help me how to comapre the alias within the same query..?
dear all
i need to write SQL Query that compares 2 tables as folows:
lets say i have:
table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)
table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)
i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A
For Example
Table A
A B C
12 15 hello
15 17 adv
asd 19 23
14 rer 89
Table B
A B C
12 15 hello
15 19 adv
*** 19 23
14 rer 89
the result is the records which was changed from A to B
A B C
15 19 adv
*** 19 23
Thnks alot!
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
View 4 Replies View RelatedI have a query that ranks. Once I get the ranked fields is there a way to compare the 2 total_income fields?
Here's the query:
select * from
(
select cardholderid, appcnum, total_income ,Rank() over (PARTITION BY A.APPCNUM
ORDER BY A.EFFSTARTDATE DESC) as Rank
from
TBL_EPIC_BILLSTATUS A
) tmp
where Rank in (2,3) and CARDHOLDERID = '704355'
--------------and rank ((2),total_income) <> rank (3),total_income))
Looking to do something like this to see if the income is different
I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedHi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date
Price Status
1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore
3 11/12/2015 100 High Value
create table testmaster
(
ID int,
Threshold int
)
create table testdetail
(
ID int,
Date varchar(20),
Price float
)
[Code] ...
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
I am trying to use the Import Wizard to setup a daily job to import new records based on an ID field (PK). The source database is remote and a replica. I am inserting new records to update my table called the same thing. Both are SQL Native Client
Code Snippet
select *
from [CommWireless].[dbo].[iQclerk_SaleInvoicesAndProducts] as S1
join [IQ_REPLICA].[dbo].[iQclerk_SaleInvoicesAndProducts] as S2
on S1.SaleInvoiceID = S2.SaleInvoiceID
where S1.SaleInvoiceID > S2.SaleInvoiceID
When I parse the query, I keep getting an error message.
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name 'IQ_REPLICA.dbo.iQ_SaleInvoicesAndProducts'. (Microsoft SQL Native Client)
Anyone know an easy why to get this to work? Or should I add a create table to verify new records?
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
I have got a grid view which i want to query i have added a WHERE for it WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice)) I have made a search page with 4 textboxes and a search button but what i cant same to get working is the code to take the infor from my text boxes and run the query on the grid view page.If i just had a query with (([carinfo] = @carinfo) i can get that to work by doing this Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click '~/Default2.aspx Response.Redirect("search.aspx?man=" + Carmake.Text) End Sub After that i just dont know what to do, my asp code for the Data Source is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:addcar %>"
SelectCommand="SELECT [AdId], [carinfo], [carmake], [cartype], [carprice], [other1], [enginesize], [fuel], [listdate], [adtitle] FROM [classifieds_ex] WHERE (([carinfo] = @carinfo) AND ([carmake] = @carmake) AND ([postcode] LIKE '%' + @postcode + '%') AND ([carprice] <= @carprice))"> <SelectParameters> <asp:QueryStringParameter Name="carinfo" QueryStringField="man" Type="String" /> <asp:QueryStringParameter Name="carmake" QueryStringField="make" Type="String" /> <asp:QueryStringParameter Name="postcode" QueryStringField="postcode" Type="String" /> <asp:QueryStringParameter Name="carprice" QueryStringField="pricerange" Type="Decimal" /> </SelectParameters> if anyone could help me with this would be great i been trying to work this out for two days now. Keep safeNick
Hi,
I'm self learning asp.net and have a little experience in vb.net. What i'm trying to do is run an SQL query and write the results into variables. The SQL query's im running will only ever return 1 row (specifing primary key / uniqueID). This is becasue i dont want to output the results as a table, rather to other objects. (using VS Web Developer express 2008)
Currently my code is:
------------------------------------------------------------------------------------------------------------------------------------------------------
Imports System
Imports System.Data.SqlClientPartial Class details
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Dim VariablesDim job_id As Integer
Dim myConnection As SqlConnection
Dim var_jobid As String = ""
Dim var_2 As String = ""
Dim var_3 As String = ""Dim SQL_read As New Object
'Write valus to Variables
job_id = Request.QueryString(job_id)SQL_read = "SELECT * FROM TABLE_NAME WHERE job_id = " & job_id
'Write Variables and Text to labelsLbl_title.Text = "Details for Job " & job_id
Lbl_jobid.Text = job_id
'Try to Establish link to SQL Server
'===================================
TrymyConnection = New SqlConnection("server= [my server here] ; database=ServerLog ; User Id = sa; Password= [my password here]")
myConnection.Open()
--------------------------------------------------------------------------------------------------------------------------------
I realise the quality of the code may be pretty abismal, due to pretty much guessing my way through. I dont know how to basically 'run' my sql query for starters, and then to output the results into variables. -IE- coloumn 1 (row1) of my results being put into var_1, column 2(row1) being put into var_2 etc etc.
Thanks in advance
Luke
I inherited a project at work in which I have to diagnose a bad query string that should be passing a value. Below are what I hope are relevant pieces of code. Your help ASAP will help insure job protection for yours truly. Thanks.
Dim strSQL as String = "SP2 "'strSQL = strSQL & Request.Cookies("ODM")("User_ID_NO") & ", "strSQL = strSQL & Request.Querystring("queue_id")strSQL = strSQL & ",'" & Request.Querystring("subtype")+ "'" ''xx no subtype is being passed herePart of SP2:SELECT
'<A target="new" href="../document-ds.aspx?dcn=' + CAST(A.DCN AS VARCHAR(25)) + '">' + CAST(A.DCN AS VARCHAR(25)) +
'</A>' AS 'DCN', QUEUE_NAME AS 'Queue Name', DOC_SUBTYPEDESC as 'Department' , DOC_CLASSDESC as 'WorkGroup',
DOC_TYPEDESC as 'Doc Type' , WKF_SUBMIT as 'Received Date', QI.QUEUE_DATE as 'Queue Date',
dbo.MIN2PARTS(DATEDIFF(mi,A.WKF_SUBMIT, GETDATE())) as 'Doc Age',
CASE
WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) > 0 THEN
'<font color = red> ' + CAST(DBO.SLA_HOURSDIFF_FORMAT( DBO.SLA_HOURSDIFF_DCN
(A.WKF_SUBMIT, GETDATE(),A.DCN)) AS VARCHAR(25)) + '</font>'
WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) < = 0 THEN
'<font size = 2 color = green><b> 0 </b></font>'
WHEN (DBO.SLA_HOURSDIFF_DCN(A.WKF_SUBMIT, GETDATE(), A.DCN) - SLA_HOURS) IS NULL THEN 'No SLA Configured'
END AS 'Out of SLA', (DBO.SLA_HOURSDIFF(A.WKF_SUBMIT, GETDATE()) -SLA_HOURS) as 'SLA_HOURS'
FROM
T_WF_DOC_TYPES DT
INNER JOIN T_WF_AP_TRACKING A ON A.DOC_ID = DT.DOC_ID
INNER JOIN T_WF_QUEUE_INV QI ON QI.DCN = A.DCN
INNER JOIN T_WF_QUEUES Q ON Q.QUEUE_ID = QI.QUEUE_ID
WHERE Q.QUEUE_ID = @QUEUE_ID
AND DT.DOC_SUBTYPE = @DOC_SUBTYPE
My main task is to discover where the @DOC_SUBTYPE comes from and why it's not passing the value to the stored proc or beyond. Let me know if you have any other questions since I'm unsure if I gave you the right info or not.