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.
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.
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();
When I try to add a parameter called findby to the order by part of a query like this: dim q1 as string="select store+' '+customer+' '+left(customer,len(customer))" q1=q1+"+replicate('.',30-len(customer))+' '+cdate as a" q1=q1+" from tblcustomers" q1=q1+" where store='65' and customer like @lookfor" 'eventually want @findby where this says customer q1=q1+" order by @findby" with command1.parameters: .Add(New SQLParameter("@lookfor", textbox1.text+"%")) .Add(New SQLParameter("@findby", dropdownlist2.text)) 'dropdownlist2.text="customer" which is the name of a column end with I get this server error: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Are parameters not good for names of things in a query but ok for values of what those names represent? If not, what am I doing wrong? Thank you very much.
I am new to Parameterize query ... and i want to use above for inserting XML data and to retrive the same as it contains some special character so by using string it is changed.
I'm going crazy trying to figure out how to form this SQL query. I am querying an Informix linked server and I need to pass a variable date. I am using an expression to create the query like so
"Select count(*) from " + @[User::varDBName] + ":informix.doc_tl WHERE " + @[User::varDBName] +":informix.doc_tl.d_received = {D " + @[User::varDate] +"} "
The informix query needs the date to be {D "2007-01-15"} but for the life of me, I can't get the date enclosed in quotes.
The error I get is
An OLE DB record is available. Source: "(null)". HResult: 0x80040E14
can anyone show me where i've done wrong in my coding? because i can't seems to find the error. I've looked through forums and google but just can't understand what they are on about as i'm kind of a beginner. Please help me...thanks in advance...(i dont have any stored pocedure, just using a connectionstring called connectionstringnews)HERES THE ERRORParameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Parameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter @newsid, which was not supplied.Source Error:
Line 37: txtMessage.Text)Line 38: con.Open()Line 39: cmd.ExecuteNonQuery()Line 40: con.Close()Line 41: 1 Imports System.Web.Configuration 2 Imports System.Data.SqlClient 3 Partial Class News_Articles_Default 4 Inherits System.Web.UI.Page 5 6 Protected Sub btnPost_Click( _ 7 ByVal sender As Object, _ 8 ByVal e As System.EventArgs) _ 9 Handles btnPost.Click 10 11 Dim cs As String 12 cs = WebConfigurationManager _ 13 .ConnectionStrings("ConnectionStringNews") _ 14 .ConnectionString 15 Dim insertNews As String 16 insertNews = "INSERT news " _ 17 + "(newsid, author, date, articles) " _ 18 + "VALUES(@newsid, @author, @date, @articles);" 19 20 Dim con As SqlConnection 21 con = New SqlConnection(cs) 22 Dim cmd As SqlCommand 23 cmd = New SqlCommand(insertNews, con) 24 25 Dim newsid As String 26 newsid = Request.QueryString("news") 27 28 cmd.CommandText = insertNews 29 cmd.Parameters.Clear() 30 cmd.Parameters.AddWithValue("newsid", _ 31 newsid) 32 cmd.Parameters.AddWithValue("author", _ 33 txtAuthor.Text) 34 cmd.Parameters.AddWithValue("date", _ 35 DateTime.Now) 36 cmd.Parameters.AddWithValue("articles", _ 37 txtMessage.Text) 38 con.Open() 39 cmd.ExecuteNonQuery() 40 con.Close() 41 42 End Sub 43 44 45 End Class 46
Hi, Below are two methods o passing a parameterized query, are these the same, or is one open to sql injection attacks more than the other?Option 1 - through code behindDim testDataSource As New SqlDataSource()testDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringName").ToString()testDataSource.UpdateCommandType = SqlDataSourceCommandType.TexttestDataSource.InsertCommand = "INSERT INTO test(id) VALUES (@id1)"testDataSource.InsertParameters.Add("@id1", TextBox1.Text) Option 2: through sqldatasource on page and control parameters<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"InsertCommand="INSERT INTO test(id) VALUES (@id1)" <InsertParameters><asp:ControlParameter ControlID="TextBox1" Name="id1" Type="Int32" /></InsertParameters></asp:SqlDataSource> Feedback would be great, thanks!
I am trying to use the following SQL query to return a set of values:SELECT id, submit_date, company_name, request_type, status FROM tblRequestForms WHERE request_type IN (@RequestType) AND status IN (@Status) ORDER BY id ASCI have tried passing an array of string values to both @RequestType and @Status, but It does not work. Is there any way to pass multiple values like this using parameters?
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.
Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?
I am having an issue with a Parameterized Query in Sql Ce 3.5 The Query resembles select * from sometable where ((ID = @someId) or (NAME like @someName))
The first part of the query runs fine, the second returns no results when it should.
What I was hoping is that there is some way to run a server trace against the SqlCe file to see the actual query that is ran with the params replaced.
Hi all, I am using the below parameterized query and get an error while executing it....can anyone please spot the error. Any help will be appreciated. I have gone cross-eyed now looking at it all day. The error I get it isParameterized Query '(@Re_UK_Eligible nvarchar(4000),@Re_Aus_Eligible nvarchar(33),@R' expects parameter @Re_JobType_Temp, which was not supplied. sqlStmt = "UPDATE Re_Users SET Re_UK_Eligible=@Re_UK_Eligible,Re_Aus_Eligible=@Re_Aus_Eligible,Re_Can_Eligible=@Re_Can_Eligible,Re_USA_Eligible=@Re_USA_Eligible,Re_Address1=@Re_Address1,Re_Address2=@Re_Address2,Re_Address3=@Re_Address3,Re_City=@Re_City,Re_Postcode=@Re_Postcode,Re_Country=@Re_Country,Re_Homephone=@Re_Homephone,Re_Mobile=@Re_Mobile,Re_JobType_Per=@Re_JobType_Per,Re_JobType_Temp=@Re_JobType_Temp,Re_JobType_Con=@Re_JobType_Con,Re_Hours_Full=@Re_Hours_Full,Re_Hours_Part=@Re_Hours_Part,Re_Sector=@Re_Sector,Re_StepTwoDone=1 WHERE Re_UserCount=" + Session["ReUserIdentity"]; cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReConnectionString"].ConnectionString); cmd = new SqlCommand(sqlStmt, cn); cmd.CommandType = CommandType.Text;
//Insert UK if (chkUK.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", DBNull.Value)); } if ((chkUK.Checked == true) && (UKRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", UKRadioButtonList.SelectedItem.Text)); }
//Insert AUS if (chkAUS.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", DBNull.Value)); } if ((chkAUS.Checked == true) && (AUSRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", AUSRadioButtonList.SelectedItem.Text)); }
//Insert CAN if ((chkCAN.Checked == false)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", DBNull.Value)); } if ((chkCAN.Checked == true) && (CANRadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", CANRadioButtonList.SelectedItem.Text)); }
//Insert USA if (chkUSA.Checked == false) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", DBNull.Value)); } if ((chkUSA.Checked == true) && (USARadioButtonList.SelectedIndex > -1)) { cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", USARadioButtonList.SelectedItem.Text)); }
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader
public SqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd;
adapter.Fill(dataset); return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null.
I'm trying to do a basic update query which is working on other pages but not on this page. Dim uid As Integer = CInt(Session("uid")) Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn) With cmd.Parameters cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text) cmd.Parameters.AddWithValue("@uid", uid) End With cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards, Mark
If txtLink1 happens to be empty, I want @link1 to enter null. The column in the Sql Server allows for nulls, but I get an error message that says no value was supplied. In short, how do I supply a null value using a parameterized query?
#2: For debugging purposes, how can I view what my SQL string looks like (with all the values entered) before it gets submitted to the database? When I view the string, it still contains the placeholder values (@link1) instead of the actual values.
I have a smart device project in Visual Studio 2005 that has a SQL Mobile data source. I am trying to create a parameterized query that utilizes 'LIKE' and wildcards. My query is below:
SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%')
However, when I test this on my PDA, I get the following error:
SQL Execution Error.
Executed SQL statement: SELECT LocationID, StreetNum, StreetName, rowguid FROM tblLocations WHERE (StreetNum = @StreetNum) AND (StreetName LIKE '%' + @StreetName + '%') Error Source: SQL Server Mobile Edition ADO.NET Data Provider Error Message: @StreetName : deerbrook - FormatException
Does anyone know how to add wildcards to a parameter?
I'm trying to pass through a SQL statement to an Oracle database usingOPENROWSET. My problem is that I'm not sure of the exact syntax I needto use when the SQL statement itself contains single quotes.Unfortunately, OPENROWSET doesn't allow me to use parameters so I can'tget around the problem by assigning the SQL statement to a parameter oftype varchar or nvarchar as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword',@chvSQL)I tried doubling the single quotes as inSELECT *FROM OPENROWSET('MSDAORA','myconnection';'myusername';' mypassword','SELECT *FROM AWHERE DateCol > To_Date(''2002-12-01'', ''yyyy-mm-dd'')')But that didn't work. Is there a way out of this?Thanks,Bill E.Hollywood, FL
I am hoping someone could help me understand why this is happening and perhaps a solution. I am using ASP.NET 2.0 with a SQL 2005 database. In code behind, I am performing a query using a parameter as below: sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)" objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text)) The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query. The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way: sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text) I also get NO records retuned using the same criteria. What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning? Thanks!
Hi, Im struggling with this insert statement, I want to use with a AJAX validation Post Form page. Its quite straght forward, if a search query returns null the insert these values. The search query does work, what I mean by that is that txt field values seem to pass for search but not insert. Any help out there cheers Paul if (RowCount == 0) {String strSQL = "INSERT INTO Mail_List (FirstName, Email) VALUES( @FirstName, @Email )";
try {mySqlConn = new SqlConnection(strSqlConn); mySqlConn.Open();SqlCommand cmd = new SqlCommand(); cmd = new SqlCommand(strSQL, mySqlConn);cmd.Parameters.AddWithValue("@FirstName", Request.Form["FirstName"]);cmd.Parameters.AddWithValue("@Email", Request.Form["Email"]); cmd.ExecuteNonQuery(); lblStatus.Text = "Registration Successful"; }
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
joy mundy alluded in her webcast that it is possible to dynamically specify a table name in a parameterized ole db source query. is this true? if so, how can it be done?
I had a procdure in SQL 7.0 in which I am using both single quote and double quotes for string values. This proceudreused to work fine in SQL 7.0 but when I upgraded SQL 7.0 to SQL 2000, this proceudre stopped working. When I changed the double quotes to single quotes, it worked fine.
Hi All, I am facing quotes problem. Without using the quotes my query is running fine, but I need to use IIF condition so for that I need quotes adjustment. I didn't figured it out how to adjust them, try several techniques but no success. I am using dotnetnuke. {IIF,"[frmradio,form]=text"," SELECT Docs.FileName, Dept_LegalLaw.MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, Dept_LegalLaw.LawDate, Dept_LegalLaw.Agreement, Dept_LegalLaw.Name, Dept_LegalLaw.NameSearch, Dept_LegalLawType.LawType, Dept_LegalLaw.LawNo, Dept_LegalMinistries.RegID, Dept_LegalLaw.IssueNo, Dept_LegalLaw.Attachment, Dept_LegalLaw.Amendment, Dept_LegalLaw.Scanned, Dept_LegalLaw.Html, Dept_LegalMinistries.Description FROM OPENQUERY(LEGALDBSERVER, 'SELECT Filename FROM SCOPE() WHERE Contains('" @FilterAnyWrd ")' ) AS Docs INNER JOIN Dept_LegalLaw ON Docs.FileName = Dept_LegalLaw.FileName INNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegID INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawID ", " "} {IIF,"'[frmradio,form]'='title'"," SELECT MediaID, Dept_LegalLaw.ID, Dept_LegalLaw.LevelID, Dept_LegalLaw.LawID, LawDate, Agreement, Name, NameSearch, Dept_LegalLawType.LawType, LawNo, Dept_LegalMinistries.RegID, IssueNo, Attachment, Amendment, Scanned, Html, Dept_LegalMinistries.Description, Dept_LegalLaw.FileName FROM Dept_LegalLaw LEFT JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID COLLATE DATABASE_DEFAULT = Dept_LegalMinistries.RegID COLLATE DATABASE_DEFAULT INNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID COLLATE DATABASE_DEFAULT = Dept_LegalLawType.LawID COLLATE DATABASE_DEFAULT WHERE @FilterLawNo AND @FilterLawID AND @FilterRegID AND @FilterIssueNo AND @FilterFromDate AND @FilterToDate AND @FilterNtContNew AND @FilterAgreement AND @FilterAllWrdNew AND @FilterExWrdNew AND @FilterAnyWrdNew ORDER BY [SORTTAG] ", " "} Thanks for any help
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from (select * from `kcf`) as refTable where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
