String Query
Apr 11, 2008usa,united states - united states
usa,spain - spain
usa,france - france
how to get that?
ADVERTISEMENT
Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
May 22, 2007Hello 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
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
Feb 13, 2006We 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.
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Mar 25, 2008Hi 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
Query String Help
Jun 14, 2007 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
SQL Query To String
Dec 10, 2007Hi,
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
Need Help With Query String
Feb 4, 2008I 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.
Query A String
Nov 20, 2013I have a query on a peson application that poduces a record. Within the application there is a question that allows the applicant to choose several answers: -
Applic_no Question_ID Question Answer
12345 40 Medical 2,5,12
There is a lookup table that tells me what each answer is: -
ID Desc
1 Stairlift
2 Wheelchair
3 Walk-in shower
5 Ramp
12 WC Downstairs
However, how do I query the lookup table if my answer is 2,5,12? I need to somehow split it or query the string array to pick out the values seperated by commas.
Get String From Query
Oct 11, 2004Hi there, im trying to create a string from a query, i got a table like this one
id name
-- -----------
1 Robert DeNiro
2 Will Smith
3 Bruce Willis
4 Al Pacino
Now, i want to get this output
Robert Deniro; Will Smith; Bruce Willis; Al Pacino
I'm wondering if there is a way to acomplish this.
thanks Advanced
Query String Using Web Matrix
Jun 16, 2006Hi folks,
I have two tables in one database.
One table has an automatic numbering primary key named ID and is named rfi.
The other table has a field named rfinumber and is named discussion.
Both ID and rfinumber have a datatype of number.
Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.
Problem is that I am getting the entire discussion table when I use the following query:
"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"
For example
DISCUSSION TABLErfinumber1112
RFI TABLErfi12
Given the query, I should get a discussion table only listing the rfinumber = 1.
When I run the query from my database package it runs fine??
Any clues?
thanks,glenn
How To Retrive A Value Using A Query String?
Jul 7, 2006Hello,I would like to keep some values as session variables while the user is loged in, but i am missing some part of how to implement it.This is what I have:<script runat="server">
Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT users.username, users.password, users.FirstName, users.LastName, users.CompanyId, Company.CompanyName, users.SecurityLvl FROM users LEFT OUTER JOIN Company ON users.CompanyId = Company.CompanyId WHERE (users.password = @Password) AND (users.username = @Username)"
conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
'Response.Write("Invalid credentials")
End If
myReader.Close()
End Sub
</script> I would like to know how can I get now the "user.FirstName" and pass it to a session variable???how should I code it? thanks,
Question About SQL Query String
May 3, 2008I am sorry for my basic question I am sure. I have this SQL Query that attaches to 3 tables and pulls data in:
Select OffNormalInfo.OffNormalID, OffNormalInfo.CompanyID, OffNormalInfo.SiteID, OffNormalInfo.CaskID, DATEPART(mm,EventDate)as Month, DATEPART(dd,EventDate)as Day, DATEPART(yyyy,EventDate)as Year, EventName, Damage, Action, UnitIDVCC, UnitIDTSC FROM OffNormalInfo, DamageInfo, CaskInfo WHERE OffNormalInfo.OffNormalID = DamageInfo.OffNormalID and CaskInfo.CaskID = OffNormalInfo.CaskID
Now, the main table that has the main document that everything else rides off of is the OffNormalInfo table. The CaskInfo table will also always have data in it, but there could be an instance where the DamageInfo table doesn't have a corrisponding record with the same OffNormalID as the OffNormalInfo table.
What is happening right now with this string is that nothing is being returned at all for that OffNormalInfo record if there is nothing in the DamageInfo table. There are also many times that the DamageInfo table has multiple records with the OffNormalID and so it returns the entire row multiple times with the same info from the OffNormalInfo table - this is how it is designed and is working correctly.
So - the question. How do I change my SQL to get it to still return everything in the OffNormalInfo table and CaskInfo table even when there is not a corresponding record in the DamageInfo table? In this event I would like it to show the two columns (Damage and Action) with empty strings in the GridView.
Any thoughts would be appreciated.
Thanks!
Tim
Query String Is Being Truncated
Aug 16, 2004Hi,
I have hit a brick wall with this. My code is as below
public void fillCustomer()
{
string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'";
System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @CustomerID)";
System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter();
param_CustomerID.ParameterName ="@CustomerID";
param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@CustomerID", SqlDbType.Int);
dbCommand.Connection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader();
dbCommand.Connection.Close();
while(dataReader.Read())
{
customerID = dataReader.GetInt32(0);
date = dataReader.GetDateTime(1);
eposCode = dataReader.GetInt32(2);
}
dataReader.Close();
}
The error I am getting is
Prepared statement '(@CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @CustomerID, which was not supplied.
As you can see from my queryString the @CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.
I am really stumped on this and would really appreciate any pointers
String Truncated When Query
Feb 7, 2005Hi! When I run a select statement, it would retrieve a product description. In some rows, it is long. Consequently, the product description was truncated. Did anybody have resulotion for this issue?
View 7 Replies View RelatedSQL Query Using String Condition
Aug 1, 2004My table is as follows and I have a string ‘U2’,’U4’
CustomerID UserID OtherUsers
C1U1#U3~#U5~
C2U2
C3U3
C4U4#U1~#U2~
C5U5
C6U1#U2~#U5~
C7U2
C8U3#U1~#U4~
Now I should issue a sql which should extract the CustomerID where UserID in (‘U2’,’U4’) or (U2 or U4) in OtherUsers.
i.e I should get the CustomerIDs C2,C4,C6,C8
I have trouble in coming out with the sql. Does anyone have idea how to achieve this?
Execute A Query From A String
Jan 27, 2004I've got a string that contains my Insert query
How can I execute it ?
declare @sql char(500)
Select @sql = '"Insert Into T74ACCO Select * From tempdb..##Temp tp Where tp.' + @Key + ' not In (Select ' + @Key + ' From T74ACCO)"'
Thank you in advance
BK,BM or Snail
Concatenate String In Query
Apr 22, 2008Hi,
As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.
I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.
I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.
I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.
regards,
NEIL
Neil
In Query, How Can I Compare String?
Feb 13, 2007Hi I wanna put string in query "where" part.For example,$sql="select VEHICLEFROM databaseWHERE MECHANIC =BRIAN";like above, "mechanic" column is filled with strings. Then how can Iwrite "where" part?Above query does not work.Thanks.
View 2 Replies View RelatedQuery String Encryption In T-SQL
Feb 23, 2008Hi,could you tell me please what are the ways of the query stringencryption in T-SQL ? I would like to have a storage procedure thatencrypts e.g. http://123.23.43.1/pagegen.asp?param1=23¶m2=124 intohttp://pagegen.asp?code=fdgfehiqrzvhe and convert it back to theorginal url.Thanks in advanceOmi
View 1 Replies View RelatedQuery To Convert String To Int
Oct 4, 2007
CREATE TABLE USERTABLE(
USERID int,
USERNAME varchar(20));
INSERT INTO USERTABLE VALUES (1,'x');
INSERT INTO USERTABLE VALUES (2,'y');
INSERT INTO USERTABLE VALUES (3,'z');
CREATE TABLE ACCESSTABLE(
ACCESSTYPE varchar(50),
USERIDS varchar(20));
INSERT INTO ACCESSTABLE VALUES('Enabled Users','1,2');
INSERT INTO ACCESSTABLE VALUES('Disabled Users','3');
Select USERNAME from USERTABLE where USERID IN (select USERIDS from ACCESSTABLE where ACCESSTYPE='Enabled Users');
I get the following error for the above query.
Syntax error converting the varchar value '1,2' to a column of data type int.
Please advice me.
Concatenated String For Each Row In A Query...
Mar 31, 2008So I've run into another problem. I've figured out how to concatenate multiple rows into a single string my only problem is using that on another query with multiple rows...Basically what I'm trying to do is pull up information for each class a student has in his/her profile and while at it pull up any prerequisite classes that are associated with a certain class. So the final query would look something like this...
StudClassID Completed Class ID Name Description Credits Prereq... rest are insignificant...
0 0 CSC200 Cool prog... blah.... 3 CSC160, CSC180
I get the concept of the coalesce and cast just i'm not understanding how to get it to work with each return on the main select...anyways below are the tables and my current query call...
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Classes] Script Date: 03/31/2008 01:32:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student_Classes](
[StudClassID] [int] IDENTITY(0,1) NOT NULL,
[StudentID] [int] NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditID] [int] NULL,
[Days] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Classroom] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Grade] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Semester] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Completed] [tinyint] NULL CONSTRAINT [DF_Student_Classes_Completed] DEFAULT ((0)),
CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED
(
[StudClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID])
REFERENCES [dbo].[Credits] ([CreditID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Prerequisites] Script Date: 03/31/2008 01:32:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Prerequisites](
[PrerequisiteID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Prerequisite] PRIMARY KEY CLUSTERED
(
[PrerequisiteID] ASC,
[ClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_ClassID]
GO
ALTER TABLE [dbo].[Prerequisites] WITH CHECK ADD CONSTRAINT [FK_Prerequisite_Prereq] FOREIGN KEY([PrerequisiteID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Prerequisites] CHECK CONSTRAINT [FK_Prerequisite_Prereq]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Credits] Script Date: 03/31/2008 01:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Credits](
[CreditID] [int] IDENTITY(0,1) NOT NULL,
[ClassID] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Credits] [tinyint] NULL,
CONSTRAINT [PK_Credits] PRIMARY KEY CLUSTERED
(
[CreditID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Credits] WITH CHECK ADD CONSTRAINT [FK_Credits_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Credits] CHECK CONSTRAINT [FK_Credits_ClassID]
SELECT sClass.StudClassID
,sClass.Completed
,sClass.ClassID AS 'Class ID'
,c.LongName AS 'Name'
,c.Description
,cred.Credits
,(SELECT COALESCE(@prerequisites + ', ', '') + CAST(PrerequisiteID AS varchar(7))) AS 'Prerequisites'
,sClass.Grade
,sClass.Days
,sClass.Time
,sClass.Classroom
,sClass.Semester
,sClass.Notes
FROM Student_Classes sClass
INNER JOIN Prerequisites preq
ON preq.ClassID = sClass.ClassID
INNER JOIN Classes c
ON c.ClassID = sClass.ClassID
INNER JOIN Credits cred
ON cred.CreditID = sClass.CreditID
WHERE sClass.StudentID = 0
ORDER BY sClass.ClassID ASC
Query String In A Textbox
Apr 10, 2007As I'm searching for solutions to my problem (see my most recent post before this one if you're interested), I keep coming upon "command-based expressions." I keep seeing examples such as:
= "select a,b from table1 where c=0"
I can't put that in a textbox can I? I tried it and it just spits out the string on report execution, it doesn't actually execute the command.
So what's up? Should that be able to be put right in a textbox? That would solve a world of problems for me if it could be.
Thanks for any info, as always.
cmk
SQL Datasource Passed To Query String?
Aug 8, 2006I have been attempting to build a search engine that searches a database full of invoices. The user will enter in a invoice number and then the results will be returned if the exact invoice exists and if the record belongs to the user.
My first attempt has involved the use of a SQL Datasource to pass a stored procedure based on what is in the search textbox and the information in the cookie about the user. I was hoping that on the btnsearch.click event that I could somehow return the stored procedure in the SQL DataSource. My next step was to make the result (if exists) part of the query string like this:
Response.Redirect("~/Invoice.aspx?Invoice=?"+ SQLRETURNEDVALUE)
I'm looking for any help to a possible way to use my idea or any other ideas to get a invoice number and place it into a query string so that the result can be fetched on the next page. Thanks
I Need Help With Query String Parser Error
Feb 20, 2007
I can not get this query to work can some on help me?
SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST) AS 'tcost,sum(TRANSPORTATION_DISC_COST) as ' dtcost ',avg(TRANSPORTATION_DISC) as "avgTDisc",(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled', (sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTIONFROM job LEFT JOIN payer ON payer.payerid = job.payerid LEFT JOIN states ON job.jurisdiction = states.initialsWHERE (job.transportation = '1') AND ((job.datedcreated = @startdate) AND (job.datecreated = @enddate)) AND states.region = 'GA'GROUP BY job.JURISDICTIONORDER BY PatAvgT DESC
SQL Query - Search Field For String
Feb 28, 2007Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
Query For Substring To Get Some Particular Value In A String (Get Querystring Value By Sql )
Jan 28, 2008I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
View 4 Replies View RelatedHow To Replace Integer With A String In A Query
Apr 15, 2008Hi all, In a sql query I need to replace 0 (zero) with "Not rated" ...Can some one help me to do this.In short: how to replace a integer value to a string in a query? is it possible?Thanks for the HelpRamesh
View 2 Replies View RelatedConvert Text To String In SQL Query
Jun 16, 2008Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
View 4 Replies View RelatedString Manipulation With Web Form Query
Feb 24, 2006I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using:
dim strText = MyTextBox.Textdim intLength = strText.Length
but having problems starting here......
MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text
..............
but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength)
Help appreciated.
Frank
Query To Update Partial String
Mar 21, 2000Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
Thanks in advance, I really appreciate it guys.
Converting String Into Rows Query?
Sep 19, 2014I have a string like below
DECLARE @STR VARCHAR(100) = 'AAAAAA~KKKKK~LLLL~AAAA'
i want to convert the string into rows like
AAAAAA
KKKKK
LLLL
AAAA
Increment An ID Field In Sql Query String
Mar 3, 2006Hello..
can anyone help me with this query string?
String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')";
I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value.
Thanks, Sandy
How Do I Append String To End Of Sql Query Results?
Jul 20, 2005I need to append text to the end of my sql query results.For instance, my query returns a list of user names infirstname.lastname. I need to add @yahoo.com to each record. So theend result should be Join Bytes!. What should byselect statement look like?Any help?
View 2 Replies View Related