Escaping Quotes In Parameterized Query
Feb 7, 2008
Hi,
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.
Thx.
View 2 Replies
ADVERTISEMENT
Jun 16, 2004
Hi,
I need to have an varchar value with single quotes. For eg: the below code throws compilation error.
Declare @val VARCHAR(20)
SELECT @val = ''+name+''
print @val
Error: Invalid column 'name'
I want to print name enclosed with single quotes. Please guide me.
Regards,
Sam
View 1 Replies
View Related
Jul 11, 2006
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.
View 21 Replies
View Related
Jun 4, 2007
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();
View 2 Replies
View Related
Jan 29, 2008
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.
View 3 Replies
View Related
Feb 5, 2004
Can someone please help me with this parameterized query? Its is not working.
builder.Append("select datepart(dd, datetime) as 'Day', datepart(hh,datetime) as 'Hour', count(*) as 'Count' ");
builder.Append("from @table_name with (nolock) ");
builder.Append("where datetime > @date_time ");
builder.Append("group by datepart(dd, datetime), datepart(hh, datetime) ");
builder.Append("order by datepart(dd, datetime), datepart(hh, datetime");
dateTime = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongTimeString();
cmd.CommandType = CommandType.Text;
cmd.CommandText = builder.ToString();
cmd.Parameters.Add("@table_name", tableName);
cmd.Parameters.Add("@date_time", dateTime);
View 2 Replies
View Related
Mar 11, 2008
Hi,
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.
plz give example or link
thanks
View 5 Replies
View Related
Jan 17, 2007
Help, please.
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
Description: "(null)"
Can anyone tell me what I'm doing wrong?
Thanks
View 3 Replies
View Related
Feb 4, 2007
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
View 13 Replies
View Related
Feb 6, 2008
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!
View 2 Replies
View Related
Dec 8, 2003
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?
Thanks,
Aaron
View 1 Replies
View Related
Apr 30, 2002
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.
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist
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
Alan
View 2 Replies
View Related
Apr 15, 2008
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?
View 7 Replies
View Related
Sep 6, 2007
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?
View 5 Replies
View Related
Feb 27, 2008
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.
Any help would be great.
Thanks
View 4 Replies
View Related
Apr 18, 2008
Hi,
i have a query like
select * from table1 where @variable1
and variable1 id holding the value id=1
so what i want is select * from table1 where id=1
but here the values variable1 is passesed from a c# program which encloses the vaule within single quotes.
so what i get is select * from table1 where 'id=1'
how to correct this?
hope my question is clear.
Thanks
View 5 Replies
View Related
Oct 13, 2006
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));
}
//Contact Details
cmd.Parameters.Add(new SqlParameter("@Re_Address1", Address1TextBox.Text));
if (Address2TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", Address2TextBox.Text));
}
if (Address3TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", Address3TextBox.Text));
}
cmd.Parameters.Add(new SqlParameter("@Re_City", CityTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Postcode", PostcodeTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Country", CountryDropDownList.SelectedItem.Text));
if (HomeTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", HomeTelephoneTextBox.Text));
}
if (MobileTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", MobileTelephoneTextBox.Text));
}
//Job Preferences
for (int i = 0; i < JobTypeCheckBoxList.Items.Count; i++)
{
if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 0));
}
if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 0));
}
if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 0));
}
}
//Hours and Sector
for (int i = 0; i < HoursCheckBoxList.Items.Count; i++)
{
if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 0));
}
if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 0));
}
}
cmd.Parameters.Add(new SqlParameter("@Re_Sector", SectorDropDownList.SelectedItem.Text));
cn.Open();
cmd.ExecuteNonQuery();
thanks,vijay
View 2 Replies
View Related
Feb 27, 2007
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;
con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dtr;
This is the code trying to accomplish the same thing with a Dataset instead.
public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand(queryString, 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.
View 2 Replies
View Related
Apr 16, 2008
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
View 2 Replies
View Related
Jul 19, 2005
Hello.
I have (2) related questions.
#1: I am using a paramterized query, but am unable to make it work if one of the values happens to be null.
if (Request.Form["txtLink1"] != "")
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value =
Request.Form["txtLink1"];
}
else
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value = null;
}
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.
Thanks in advance!
-Brenden
View 2 Replies
View Related
Sep 2, 2014
I am doing parameterized queries from Visual Basic in Visual Studio.
I have a query where I want to do something like this.
Select * from people where city in (<list of cities>)
The problem is I want to build my <list of cities> in Visual Basic and pass it to the SQL as a parameter.
I know I can't do this:
Select * from people where city in (@ListOfCities)
Currently, I'm doing this by writing the <list of cities> out to a separate table, just so I can do the query.
Select * from people where city in (Select CityName from CityTable)
View 4 Replies
View Related
Feb 14, 2006
Hey everyone,
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?
Thanks,
Lee
View 18 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Jan 17, 2008
Hello,
how can I use the following statement with OPENQERY syntax:
SELECT 'hello world' FROM mytable
Maybe I have to transform quite a few and complex SQL-statements to the OPENQUERY syntax. Obviously I have problems with single quotes
SELECT * FROM OPENQUERY(LINKEDSERVER, 'select 'hello world' from mytable')
quotename('hello world', '''') does not help because this will create 'hello world' instead of hello world (w/o quotes).
How can I transform my SQL-statements with quotes to Openquery syntax?
regards
arno
View 4 Replies
View Related
Jul 28, 2006
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!
View 7 Replies
View Related
Jun 11, 2008
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";
}
View 2 Replies
View Related
Feb 4, 2014
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)
[Code] ....
View 9 Replies
View Related
Sep 11, 2006
hi everyone,
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?
View 6 Replies
View Related
Jan 3, 2002
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.
Any Idea why ??
Thanks
Manish
View 2 Replies
View Related
May 19, 2008
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
View 2 Replies
View Related
Jul 21, 2014
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.
View 1 Replies
View Related
May 22, 2015
I have defined a variable Var_Query_SQL and passed the below query using expression but it is showing error. where am i going wrong.
"SELECT
sample_id ,
sample_time ,
trans_date ,
product = mh.[identity] ,
comments = s.m_smp_comment
[URL] ...
View 4 Replies
View Related
Feb 24, 2006
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.
Any idea on what I should try next ?
View 3 Replies
View Related
