Parameterized Query Question

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


ADVERTISEMENT

Parameterized Query

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

Parameterized Query?

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

Parameterized Query In T-SQL

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

Help Please!! (Parameterized Query Problem)

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

Parameterized Query Question

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

Using DTS Parameterized Query &#39;IN&#39; Where Clause

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

Using Dateadd In A Parameterized Query

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

ADO.NET Parameterized Query Security

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

Parameterized Query Issue With Sql Ce 3.5

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

Expects Parameter......parameterized Query

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

Help With Parameterized Query Building Dataset

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

Parameterized Query Driving Me Crazy

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

C# Parameterized Query With Null Values

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

Parameterized Query For A List Of Items

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

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 View Related

Parameterized Query Using Wildcards In VS2005

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

Parameterized Query Returns One Row With Null Values.

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

Error Inserting To Database: Parameterized Query

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

SQL Server 2012 :: Using Parameterized Query With Like In Where Clause

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

Dynamic Table Name In Parameterized Ole Db Source Query?

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

SQL 2012 :: How To Run Query Execution Plan For Parameterized Queries

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

Integration Services :: Passing Parameterized Query Through Variable In SSIS

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

Can You Cache A SSIS Lookup Component To Foxpro V7 With Parameterized Query?

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

Parameterized Query For Microsoft OLEDB Provider For Oracle Using OLE DB Source In SSIS

Apr 26, 2007

Hi,



Urgent Help required..........

Can anyone explain me steps how to parameterized query to send oracle.

If you know any other control which help to do this rather than OLEDB source.

Please let me know.



THanks

View 18 Replies View Related

Parameterized Query That Counts The Number Of Null Values In An Int32 Column

Jul 18, 2006

Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));

cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));

cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));


cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));

cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));


cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));


cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";

cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;

cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));



cmd.Parameters["@IntColumn"].Value = DBNull.Value;

cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";



cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));




cmd.Parameters["@IntColumn"].Value = DBNull.Value;


cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";

Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.

View 5 Replies View Related

Parameterized Queries Running Slower Than Non-parameterized Queries

Jul 20, 2005

HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)

View 1 Replies View Related

The Parameterized Query '(@contactdate Nvarchar(4000),@dnbnumber Nvarchar(4000),@prospect' Expects The Parameter '@futureopportunity', Which Was Not Supplied.

Jan 10, 2008

HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub

View 6 Replies View Related

Parameterized AND IN (@Var

Nov 12, 2004

I am trying to create a list of values for an IN clause. How does one do it with parameterized queries?

If I use var = "val','val2','val3"

then the ' are replaced with '' - so how?

View 5 Replies View Related

Parameterized Or Array With This SQL?

Jun 2, 2008

I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes.  Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click        Dim ACSelections As Boolean = False        Dim ACItem As ListItem        For Each ACItem In cbl_CustomerSearchAREA.Items             If ACItem.Selected Then                ACSelections = True            End If        Next        If ACSelections = True Then            Dim ACSqlString As String            ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE "            For Each ACItem In cbl_CustomerSearchAREA.Items                If ACItem.Selected Then                    ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR "                End If            Next            ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4)            ACSqlString &= "ORDER By Customers.CustomerName"            sql_CustomerSearchGrid.SelectCommand = ACSqlString        End IfEnd Sub 

View 7 Replies View Related

Parameterized Where Clause

Jul 5, 2005

Hello,I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data.  So it looks like:create procedure ....set transaction isolation level serializablebegin transactiondeclare @ID   intselect @ID = rowid from tblBusinessInformation where Name = @Name and Rules = @Rulesif ( @ID is NULL ) begin   insert into tblBusinessInformation (..) values (@Name, @Rules) endcommit transactionThe problem is the values could be:Name   RulesNULL   'Test''Test'      NULL'Test'      'Test'When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...Thanks.

View 3 Replies View Related

Parameterized Viewc

Oct 4, 2006

can someone plz explain to me wats a parameterized view or may be a link somewhere on the web or a few examples of parameterized view. Thanks

View 4 Replies View Related

Parameterized SP In WHERE Clause Of Another SP

Jun 6, 2006

I've got this SP:CREATE PROCEDUREEWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear char(11)ASSELECTContactIDFROMdbo.EWF_tblCustom_CompanyProfileWHERESchoolYear = @prmSchoolYearI'd like to be able to reference that in the where clause of anotherSP. Is that possible?I'd like to end up with something like this:CREATE PROCEDUREMyNewProc@prmSchoolYear2 char(11)ASSELECTContactID, SomeOtherFieldsFROMtblContactWHEREContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear2)How would I make that happen?If this isn't possible, what else might I try?Thanks much for any pointers.JeremyPS: I accidentally crossposted this in another group(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved