ok the form has a field txtTestimonial.Text
these strings work when i submit them though
thats all folks
that"s (double quotes)
that\"s
that\"s
these fail
that's
that's
that\'s
that\'s
tried up to 7 just to make sure
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ')'.
I figure the " works because it ends up being ' " ' in the sql statement and it doesnt mind that
I tried researching this. I could not find any info stating that perhaps sql escapes things differently. I was on the assumption that in the sql itself the database would reconise the before the '.
I read i can use html decode but it seems that then i will have to undecode everytime I read from the database and it could be a major pain if I am using a datagrid or something and a big mess.
I'm trying to group ages in one report, and Case numbers in another. Both are in string datatype, and require more then 3 choices. Im not keen on coding, so i was wondering, how do you make an IFF statement with more then 3 choices, i need about 7, maybe even a little more. *this is for a column grouping in a matrix.
Here's what i have right now...It works..but i need to add more years.
I am trying to amend an SQL insert statement but it is not working. It is as follows can anyone tell me what i am doing wrong. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" DeleteCommand="DELETE FROM [useridTest] WHERE [userID] = @original_userID AND [userName] = @original_userName AND [listCopy] = @original_listCopy" InsertCommand="INSERT INTO [useridTest] ([userID], [userName], [listCopy]) VALUES (<% user.ProviderUserKey.ToString() %>, @userName, @listCopy)"
and would like to do something like this. !! but i just cant figure that out.
i have a table that containes all my categories in it. And as you can see i have "PARENT" and "CHILD" categories.
I would like to get something like this by using a simple SELECT statement !! is it possible...
=============== =#1 PARENT ID NAME= =============== =#1 CHILD NAME HERE =#2 CHILD NAME HERE =#3 CHILD NAME HERE =============== =#2 PARENT ID NAME= =============== =#1 CHILD NAME HERE =#2 CHILD NAME HERE =#3 CHILD NAME HERE
im not trying to show this in my Query Analyser but on a Web Page by using a datagrid.
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
Hi, I was handed an old application written for sql05, it has this (kind of) statement: Dim CommandText As String = "SELECT c.name FROM db1 c LEFT JOIN "
CommandText += "db2.dbo.Users u ON u.iKey = c.key "
CommandText += "WHERE u.cUserName = '" & User & "'"
As you can see, there are two databases, db1 and db2 and this worked fine in sql05, but when I convert each of the databases to sqlexpress, I get this error: Invalid object name 'db2.dbo.Users'.
So how can I make this work in sqlexpress? Are queries across databases not allowed?
Hi, i'm having problems executing the followingSET @SQLAH = 'SELECT sub_id WHERE 'SET @SQLAH = @SQLAH + 'VENUE_TYPE = Hotel'EXEC(@SQLAH)Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:..SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' '..But this escapes the string, how would i escape an apostrophe in a string?I thought maybe:SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' 'But no joy :confused: Thanks
I have a parameterized query. The parameters contain data from my tables. Some of the parameters could include single quotes. The single quotes are wreaking havoc in my parameterized query. How can I replace single quotes with double quotes inside of my SQL stored procedure?
I know that it's something similar to REPLACE(@variablename, '''''', ''''''''), but I can't get the number of quotes right.
All of the examples that I am seeing are converting the quotes inside of an application. This is not an option for me, as I am calling this stored procedure from a SQL job that will run daily.
I have some code (C#) that runs an SQL update query that sets thevalue of a column to what the user passes. So, this causes an errorwhen anything the user passes in has a ' character in it. I'm surethere's other characters that'll break it too. So, I was wondering,how do I get around this? Is there some commonly accepted regexpattern that will make the value safe to run in an SQL query? How canI take care of any values that need to be escaped?I'm not using any fancy ado.net objects:string sql= [whatever the user passes in]SqlConnection connection = newSqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());connection.Open();SqlCommand command = connection.CreateCommand();command.CommandType = CommandType.Text;command.CommandText = sql;try{int result = command.ExecuteNonQuery();if (result != 1){Response.StatusCode = 500;Response.Write("The file has been uploaded, but wecould not update the DB");Response.End();}}catch (InvalidOperationException){Response.Clear();Response.Write("error");Response.StatusCode = 500;Response.End();}connection.Close();
INSERT INTO PLAN_DEMAND ([YEAR], BOD_INDEX, SCEN_ID) SELECT PLAN_SHIP.[YEAR], PLAN_SHIP.BOD_INDEX, 1 FROM PLAN_SHIP LEFT JOIN PLAN_DEMAND ON PLAN_SHIP.[YEAR]=PLAN_DEMAND.[YEAR] AND PLAN_SHIP.[BOD_INDEX]=PLAN_DEMAND.BOD_INDEX WHERE PLAN_DEMAND.BOD_INDEX IS NULL
When I run the sproc in QA, the statements returns records to the grid, but does not insert them into the table. If I just copy the statement into QA and execute it, it works fine.
I have the following update statement, which when executed, updates zero rows. However, if I replace the first two lines with a SELECT * , I will get records. Can somebody tell me why?
UPDATE [DW_DatamartDB]. [dbo].[FactLaborDollars]
SET [LaborBudget_USD] = Week1
FROM [DW_StagingDB].[ETL].[Transform_FactLaborBudget] BUDGET JOIN
[DW_StagingDB].[ETL].[Transform_FactLaborDollars] LABOR ON
Labor.Location_Code = Budget.Location_Code
JOIN [DW_DatamartDB]. [dbo].[DimDate] DATE ON
Date.FiscalWeekOfPeriod = Labor.FiscalWeekOfPeriod AND
I followed an example using the AdventureWorks database to set up a simple messaging test on one database:
Code Block -- We will use adventure works as the sample database USE AdventureWorks GO -- First, we need to create a message type. Note that our message type is -- very simple and allowed any type of content CREATE MESSAGE TYPE JobRequest VALIDATION = NONE GO -- Once the message type has been created, we need to create a contract -- that specifies who can send what types of messages CREATE CONTRACT JobRequestor (JobRequest SENT BY INITIATOR) GO -- The communication is between two endpoints. Thus, we need two queues to -- hold messages CREATE QUEUE RequestorQueue CREATE QUEUE ReceiverQueue GO -- Create the required services and bind them to be above created queues CREATE SERVICE Requestor ON QUEUE RequestorQueue CREATE SERVICE Receiver ON QUEUE ReceiverQueue (JobRequestor) GO -- At this point, we can begin the conversation between the two services by -- sending messages DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @message NVARCHAR(100) BEGIN BEGIN TRANSACTION; BEGIN DIALOG @conversationHandle FROM SERVICE Requestor TO SERVICE 'Receiver' ON CONTRACT JobRequestor WITH ENCRYPTION=OFF, LIFETIME= 600; -- Send a message on the conversation SET @message = N'Hello, World'; SEND ON CONVERSATION @conversationHandle MESSAGE TYPE JobRequest (@message) COMMIT TRANSACTION END GO -- Receive a message from the queue RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue -- Cleanup DROP SERVICE Sender DROP SERVICE Receiver DROP QUEUE SenderQueue DROP QUEUE ReceiverQueue DROP CONTRACT HelloContract DROP MESSAGE TYPE HelloMessage GO
This all works fine but if I run the section that creates the message and then copy the RECEIVE section to a new query window and execute it, nothing is returned. If I run the RECEIVE section within the same query window it returns the 'Hello World' message as expected. I am new to Service Broker and so am assuming that I am missing something obvious!!
Y'all:I am needing some way, in the SQL Server dialect of SQL, to escape unicodecode points that are embedded within an nvarchar string in a SQL script,e.g. in Java I can do:String str = "This is au1245 test.";in Oracle's SQL dialect, it appears that I can accomplish the same thing:INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a1245 test.");I've googled and researched through the MSDN, and haven't discovered asimilar construct in SQL Server. I am already aware of the UNISTR()function, and the NCHAR() function, but those aren't going to work well ifthere are more than a few international characters embedded within astring.Does anyone have a better suggestion?Thanks muchly!GRB-----------------------------------------------------------------------Greg R. Broderick Join Bytes!A. Top posters.Q. What is the most annoying thing on Usenet?---------------------------------------------------------------------
Much to my surprise and dismay, after burning hours and hours developing various SSIS packages to extract data from my SQL 2005 DB to CSV flat files, I found out the hard way that SSIS doesn't properly escape quotes (") in the data (my output file is required to have quotes as text qualifiers).
It seems like I'm pretty much forced to create derived columns to test for the existence of quotes in my data, then either escape them or replace them with another character.
This is really NOT COOL. Not only will this significantly hinder performance, it makes my packages a heck of a lot more complicated (especially when I'm exporting lots of text columns).
Am I doing something wrong, or does SSIS really not escape the text qualifier in a delimited flat file? If so, someone PLEASE tell me this will be fixed in the very near future.
Hi.. I have inserted couple of data in a particular table which is as follows.. Portfolio Table PortId PlanId PortfolioName PorfolioDescription ClientPortolioId
771 17838 BALPORT NULL NULL
772 17838 HIGHGROW NULL NULL
773 17838 MODGROW NULL NULLMy FundDBF is as follows RowNumber FUND_ID f.ASSETDESC Import
20 BALPORT
Balanced True
21 MODGROW
Moderate Growth True
22 HIGHGROW
High Growth True and this is my Update statement UPDATE Statements..PlanPortfolio SET PlanId = pm.PlanId, PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESC FROM Statements..PlanPortfolio p Join ( SELECT DISTINCT p.PlanId, pd.FUND_ID, f.ASSETDESC ---pd.FUND_ID FROM PartDBF pd INNER JOIN Statements..ClientPlan p on pd.PLAN_NUM = p.ClientPlanId INNER JOIN FundDBF f on pd.FUND_ID = f.FUND_ID WHERE pd.Import = 1 AND NOT ( pd.FUND_ID IS NULL OR Len(pd.FUND_ID) = 0 OR pd.FUND_ID NOT IN ( SELECT PortfolioName FROM Statements..PlanPortfolio pp Where pp.PlanId = p.PlanId ) ) ) pm on p.PlanId = pm.PlanId
I am trying to put the above table with f,AssetDesc in the PorfolioDescription field.. Any help will be appreciated.. Regards Karen
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
can somebody explain to me why the below update fails to update one row and updates the entire table?
Code:
UPDATE addlist SET add_s = 1 WHERE EXISTS (SELECT a.add_s, a.email_address, e.public_name FROM add a, edit e WHERE a.email_address = e.email_address and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
Hi, I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
I'm working on a script to produce XML. There are two parts to the XML: an envelope (called "message") and a payload ("payload). Here's an example of what the result should look like:
(Note that I had to add a space between the '&' and 'lt' and 'gt' to get it to post in the forum without the forum converting them back to < and >!)You can see that SQL escaped the < and >. Also the CDATA escape sequence is missing.I want to see < instead of & lt and > instead of & gt. Also, I need the CDATA escape sequence.
We are facing a problem while passing a string containing the "&" character into Full Text search CONTAINS predicate. The records that do have this character are not being returned by the search.
I'd like to raise two questions:
1) Is there any special way to escape this character? 2) Does FTS index it at all?
We have tried all known (to us) ways of escaping like doubling the character, using char(38), using ESCAPE etc..Nothing seem to work. Any help would be appreciated.
We have a .NET drop down, which gets populated as the user types in letters(last name). If the user types in the single quote we get the error about not escaping the single quote. Question is, which way would it be easier to fix, in the .NET code or in the SQL procedure? I am not to sure if we have full access to the source code since that is a 3rd party control, so if that is not feasible how would I fix that in the stored procedure? This is the current proc that we are using:
Code Snippet select @str = 'SELECT DISTINCT TOP ' + @Top + ' e.DisplayName as DbComboText, e.EmployeeID as DbComboValue FROM DepartmentDirectory.dbo.Employees ee INNER JOIN DataMart.dbo.Employees e ON ee.UIN = e.UIN WHERE e.LastName like ''' + @LastName + ''' AND e.FirstName like ''' + @FirstName + ''' ORDER BY e.DisplayName'
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[code]...
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.
Merge test.dbo.LockCombination AS LKC1 USING (select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber from [Inputtb] A JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
[Code] ....
Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
