Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
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
View Complete Forum Thread with Replies
Related Forum Messages:
Find In String And Return Part Of String
I am trying to find a way to find a certian character in a string and then select everything after that character. for example i would look for the position of the underscore and then need to return everthing after it so in this case yes_no i would return no
View Replies !
Question About Query Part Of A String
I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this?? thanks
View Replies !
Query - Check For A String In Stored Procedure
Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View Replies !
Part Of String
Need help.. I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas? Thanx
View Replies !
Searching Part Of String With Sql
I have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View Replies !
How To Extract Part Of A String
Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.
View Replies !
How To Extract Part Of String
Hi, I have 2 questions. 1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM". I want to extract just "Name" from this string. The length of "Name" varies and I want to extract all characters included for "Name". Can anyone advise what the query/SQL statement would look like? 2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel. So I want to include a code that checks if file "file.xls" is open on user's machine. If file is open, then message "file "File.xls" is already open. Generating File_1.xls" Run the code but create the file with file name "file_1.xls" If file doesn't exist, then run code and create file with file name "File.xls" Please advise.
View Replies !
Return Part Of A String
how can you return part of a string and convert it into an integer value? Maybe like this: convert(left(column, 3) as int) does that work? The Yak Village Idiot
View Replies !
Extracting Part Of A String
Hello, I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example: "the text looks like this but has two numbers 55555/66666 within the string" I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
View Replies !
Extrach Part Of A Column String Into Another Field
hi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column. thanks Ahmed
View Replies !
Removing Or Replacing Part Of A String In A Field?
Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have: - A Table called: Table_1 - A Field called: MyField - MyField contains the value: Hello I am on the msdn forums Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this? Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums. Appreciate the responses, Onam.
View Replies !
Updating Only A Small Part Of A Text String In A Field
Hello all, I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like: Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test. I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field. It is also important to note that the rest of the body of the emails stored here is different depending on the email. So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field. Thanks for the help in advance!
View Replies !
Sql2k: Increment Numeric Part In Arbitrary String
CREATE FUNCTION fctisnumericex(@c varchar(1)) RETURNS int AS BEGIN RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int) RETURNS varchar(255) AS BEGIN DECLARE @@posr int DECLARE @@posl int DECLARE @@c varchar(1) DECLARE @@token1 varchar(255) DECLARE @@token varchar(255) DECLARE @@token3 varchar(255) DECLARE @@i int /* emulates parts of the behaviour of s_modformatting::substringincrement */ /* 1. find the place where the numeric token starts from the right */ /* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */ IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1 BEGIN SELECT @@posr=DATALENGTH(@string) END ELSE BEGIN SELECT @@i=DATALENGTH(@string) SELECT @@c=SUBSTRING(@string,@@i,1) WHILE dbo.fctisnumericex(@@c)!=1 BEGIN SELECT @@i=@@i-1 IF @@i<1 BEGIN BREAK END SELECT @@c=SUBSTRING(@string,@@i,1) END SELECT @@posr=@@i END /* so have we got any numeric part inside that string? */ IF @@posr>0 BEGIN /* yep. see how long it lasts */ SELECT @@i=@@posr SELECT @@c=SUBSTRING(@string,@@i,1) WHILE dbo.fctisnumericex(@@c)=1 BEGIN SELECT @@posl=@@i SELECT @@i=@@i-1 IF @@i<1 BEGIN BREAK END SELECT @@c=SUBSTRING(@string,@@i,1) END /* separate now the parts of the string */ IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1) IF @@posr<DATALENGTH(@string) BEGIN SELECT @@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END /* increment the numeric part */ SELECT @@token=convert(varchar(255),convert(int,@@token)+1) END ELSE BEGIN /* no numeric part at all. start with 1 at the end */ SELECT @@token1=@string SELECT @@token='1' SELECT @@token3='' END /* recompose the string and trim to max length if necessary */ RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen) END
View Replies !
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
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.
View Replies !
Please Check This 'not Null' SQL String
the SQL string below worked, and then started bringing up every record. it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused. "SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL" Any ideas how I could implement this more robustly? cheers M
View Replies !
Check The SSIS FTP Connection String
Hi, i have a table like No FileLocation UserName Password 1 ftp://sarvi3/test/ xyz xyz 2 ftp://pandit3/test/test1 abc abc 3 ftp://katta1/test/test2 klm klm i want to check the filelocation is valid (exist) or not, Before transfering the file from the location. can anyone help me out to resolve thanks Sun
View Replies !
Check The First Occurence Of A Numeric In A String
Hi All, I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph. Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working? Thanks a lot in advance and let me know if the question is not clear. Manoj Deshpande.
View Replies !
Is There A Sql Database Function To Check Connection String?
Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?Thanks,Cedric
View Replies !
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this: IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE('')) Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression" thanks
View Replies !
Anyway To Check If A Text Field Is Blank (not Null But Just A Empty String '') ?
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this: Code: IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE('')) Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression" thanks
View Replies !
String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
I am trying to insert a row into a table of Microsoft SQL Server 2000. There are various columns. [SNO] [numeric](3, 0) NOT NULL , [DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:- StaleConnecti A CONM7007I: Mapping the following SQLException, with ErrorCode 0 and SQLState 08S01, to a StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters? Can anyone please tell me what's wrong?
View Replies !
Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters
The following query in the query string: execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007' produces following error: There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'. When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window. The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?
View Replies !
Adding String To Database, But Name Of String Is Added, Not Data
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated! Imports System.Data Imports System.Data.Common Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser 'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true") 'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text) Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text) insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con) '2nd Command Data Dim selectSQL As String selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con) Dim reader As SqlDataReader '3rd Command Data Dim insertSQL2 As String insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con) 'First CommandDim added As Integer = 0 Try con.Open() added = cmd1.ExecuteNonQuery() lblResults.Text = added.ToString() & " records inserted."Catch err As Exception lblResults.Text = "Error inserting record." lblResults.Text &= err.Message Finally con.Close() End Try '2nd Command Try con.Open() reader = cmd2.ExecuteReader()Do While reader.Read() Dim CompanyKey = reader("CompanyKey").ToString() Loop reader.Close()Catch err As Exception lbl1Results.Text = "Error selecting record." lbl1Results.Text &= err.Message Finally con.Close() End Try '3rd Command Try con.Open() added = cmd3.ExecuteNonQuery() lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception lbl2Results.Text = "Error inserting record." lbl2Results.Text &= err.Message Finally con.Close()End Try End Sub End Class
View Replies !
Capitalize A Text String/String Function Related
Hi Folks: How can I capitalize a string like 'JOHN DOE' into 'John Doe' with one SQL statement. SQL does provide string function like LOWER(char_expr) which would change the whole text string to 'john doe' and I don't want that. So far, in order to do that, I have to use a front-end development language like 'Omnis' which has a string function 'cap()' to capitalize the whole string, then update the back-end SQL with the new string. Thank you in advance for your time and advice. David Nguyen
View Replies !
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Hello All, I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database. There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example: 2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1 The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed. How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table? In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND(). Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion. Thanks a bunch in advance, Chutikorn
View Replies !
Input String -&> Table -&> Output String?
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View Replies !
Converting String To Unicode String In T-SQL
Hi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash
View Replies !
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
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
View Replies !
Need Help With Query String
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.
View Replies !
SQL Query To String
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
View Replies !
Query String Help
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
View Replies !
Get String From Query
Hi 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
View Replies !
Question About SQL Query String
I 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
View Replies !
How To Retrive A Value Using A Query String?
Hello,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,
View Replies !
Query String Using Web Matrix
Hi 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
View Replies !
String Truncated When Query
Hi! 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 Replies !
Query String Is Being Truncated
Hi, 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
View Replies !
In Query, How Can I Compare String?
Hi 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 Replies !
Query String Encryption In T-SQL
Hi,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 Replies !
Execute A Query From A String
I'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
View Replies !
Query String Too Long
Hi,friends, I created a dynamic query and saved the query in a varchar variable. The problem is the length of the query string is greater than 255. So I split the query string into @strSQL1, @strSQL2, but in this case, the following statement doesn't work: execute(@strSQL1 + @strSQL2) What should I do in order to run my long query? Thanks. Miranda.
View Replies !
|
TRACKER
