Parameterized Or Array With This SQL?

RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.
All,

I am using Reporting Services 2005.  One of my reports is getting the following error when I try to export to Excel.  It will export to .CSV though.

"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."

Any suggestions would be greatly appreciated.  Please copy me at machelle.a.chandler@intel.com.

Machelle

 

View Replies !

---

How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200) 
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
 Thanks

View Replies !

---

Parameterized Queries Running Slower Than Non-parameterized Queries
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 Replies !

---

Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array:
 
Public GroupIRRArray(-1) As Double
Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal)
Dim g As Integer
g = uBound(GroupIRRArray) + 1
ReDim Preserve GroupIRRArray(g)
if g=0 then
 GroupIRRArray(g) =  (CFB+EAB-BAB+BMV)*-1
else if g=1 then
 GroupIRRArray(g) = (BAB-CFB-EAB+EMV)
else
 GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV)
 GroupIRRArray(g) =  (BAB-CFB-EAB+EMV)
end if
End Function
 


It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array?
 
Thanks,
Jam

View Replies !

---

Parameterized AND IN (@Var
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 Replies !

---

Parameterized Query
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 Replies !

---

Parameterized Where Clause
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 Replies !

---

Parameterized Query?
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 Replies !

---

Parameterized SP In WHERE Clause Of Another SP
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 Replies !

---

Link To Parameterized URl
If I bring up a report in the report server (http://reportserver/Reports/Pages/.... and I set the parameters to see the information I want to see and say to myself, hey, my boss would really like to see this, how would I go about sending him the link to the report with the parameters already set. Ideally when I view the report, the parameters would show in the URL automatically as other reporting products do, however, SSRS does not provide this. I do not even see a function in the report designer to display the link on the report itself so I can cut and paste from it. Anyone know how to do this without having to custom craft a URL or set a one time subscription?

Thanks,

View Replies !

---

Parameterized Reports
My report needs to show Top (1-100) violators.... how do i put in this parameter?
 
 

View Replies !

---

Parameterized Queries
Just getting started using SSce and having a few problems

What I want to do is something like this...

Dim Code As Integer

Dim Description As String = txtDescription.Text.Trim

Dim conn As SqlCeConnection = ConnectToLocalDatabase()

Dim ssql As New System.Text.StringBuilder

ssql.AppendLine("INSERT INTO T_Titles (Description)")

ssql.AppendLine("VALUES(@Description)")

ssql.AppendLine("SELECT @Code = @@IDENTITY")

Dim cmd As New SqlCeCommand(ssql.ToString, conn)

Dim sqlCode As New SqlCeParameter("@Code", 0)

sqlCode.Direction = ParameterDirection.InputOutput

cmd.Parameters.Add(sqlCode)

cmd.Parameters.Add(New SqlCeParameter("@Description", Description))

cmd.ExecuteNonQuery()

Code = CInt(sqlCode.Value)

**********************************************************************

The above code doesnt work.  Firstly I am not sure if I can execute the two statements in one go.  Secondly, I am not sure if output parameters are supported. 

I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them.  Any help getting started would be greatly appreciated.

Thanks

View Replies !

---

Parameterized Queries With RDA
Hello..

Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?

 

Thanks in advance.

View Replies !

---

Parameterized Query In T-SQL
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 Replies !

---

Parameterized Viewc
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 Replies !

---

Parameterized Query Question
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 Replies !

---

How To Use Parameterized Queries With IN Clause
Hi,
I need to use parameters with the IN clause in a SQL statement like:
select * from tableX where field IN (1,2,3,4)
I don't know how to do that.
I'm using SQLServer and OleDB.
 
Thanks for your help.
 

View Replies !

---

Debugging Parameterized Queries
How would I debug such a query.
I have a sqlCommand to which I add several parameters for an insert statement.
if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?
 

View Replies !

---

If/then Parameterized Queries Using Tableadapter
Hey fellas.  Here's my situation.  I have two textboxes where the user enters a "start" date and an "end" date.  I want to search a table to find records who's "expired" column date is between those two dates provided by the user.  The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond.  Essentially, I think I'm looking for a SQL statement along the lines of:
  SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE

CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END

AND

CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END

AND (Role.Description = 'Requestor')

 
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around.  Anyway, how do I do this?  Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL?  If I can tackle it with SQL, where have I gone astray?  I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
 Thanks for the help everyone!

View Replies !

---

Help Please!! (Parameterized Query Problem)
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 Replies !

---

Parameterized Query Question
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 Replies !

---

Using DTS Parameterized Query &#39;IN&#39; Where Clause
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 Replies !

---

Parameterized Stored Proc ?
I want to create a stored proc that might or might not recieve a value like below.

create proc cmine @vdate datetime = null
as
select * from table where mydate > @vdate

However if the value is null,( no value was supplied) I would not want to use the where clause. Should (or can you) use an if statement to check and see if no value was given to change the where clause?
Thanks

View Replies !

---

Creating A Table With A Parameterized Name
Hi,

I'm a Transact-SQL newbie, and I would like to create a procedure that creates a table with a parameterized name. I wrote the following, which I thought should do the job:

CREATE PROCEDURE procedure_AddFund
    @FundName varchar(10)
AS
BEGIN
    CREATE TABLE @FundName (Date smalldatetime, Price money)
END
GO

But I am getting the following error in Management Studio:

Msg 102, Level 15, State 1, Procedure procedure_AddFund, Line 5
Incorrect syntax near '@FundName'.

Presumably, I need to convert the @FundName to some appropriate string type. My question is what is the string type, and how do I do the conversion.

Thanks!
Adam Cataldo

View Replies !

---

Parameterized IN Clause In Dynamic SQL
Hi,
 
I am trying to build a parameterized query where I pass a set of integer values into the dynamic sql. Please see below example.
 
  DECLARE @SQLQUERY NVARCHAR(4000)
  DECLARE @PARAMDEF NVARCHAR(1000)
  DECLARE @VALUES VARCHAR(100)

  SET @PARAMDEF = N'@IN_VAL VARCHAR(100)'
 
  SET @VALUES = '1,2,3,4'

  SET @SQLQUERY = 'SELECT * FROM TABLEA WHERE COLUMNA IN (@IN_VAL)'
 
  EXEC SP_EXECUTESQL @SQLQUERY,@PARAMDEF,@IN_VAL=@VALUES
 
This fails with the error "cannot convert varchar to numeric". I believe since ColumnA is numeric, its trying to convert the dynamic paramter to numeric leading to the failure.
 
has someone implemented an In clause as a parameter? Please do not tell me that I can append the values as string and construct a dynamic query. I want to use a parameterized version. I will be calling this repeatedly and dont want recompile overhead.
 
TIA

View Replies !

---

Parameterized Configuratiion In SSIS
Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?

For example, one configuration for each country.

Thankful for any help!

//Patrick

View Replies !

---

Can A Connection Manager Be Parameterized?
 

Can you create one connection manager that accepts the Server name and database name at run time and constantly reuses the server and datbase name at runtime when executing one SSIS solution with Multiple packages.

 

View Replies !

---

Parameterized SSIS Packeges
How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

 exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

View Replies !

---

Using Dateadd In A Parameterized Query
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 Replies !

---

Parameterized Filters On Publication???
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?

I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?

Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?

Thanks,

Patrick Kafka

View Replies !

---

ADO.NET Parameterized Query Security
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 Replies !

---

List Parameterized Dates
Hello,

 

For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin

 

Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?

 

Thanks!

View Replies !

---

Q: Parameterized EXECUTE( @lSqlStr )?
All,
 

I execute dynamic SQL with a return parameter like this:
 

SET @lSqlStr = ' SELECT @oRsltCount = COUNT(*)  FROM foo';

EXECUTE sp_executesql @lSqlStr, N'@oRsltCount int out' @oRsltCount out;
 
The sp_executesql @stmt parameter is type nVarChar. Occationally, the size of @lSqlStr may be greater than 4000 bytes, which exceed the maximum allowable size of type nVarchar.
 
So, I may execute dynamic SQL like this:
 
EXECUTE ( @lSqlStr );
 
where the type of the argument is VarChar(MAX), and size of @lSqlStr may be up to 2^31 -- but cannot parameterize (or bind, if you will) the query result.
 
How can I execute dynamic query with string size greater than 4k bytes AND bind the query result?
 
-Kevin

View Replies !

---

Parameterized Query Issue With Sql Ce 3.5
 

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 Replies !

---

Parameterized Query Driving Me Crazy
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 Replies !

---

Help With Parameterized Query Building Dataset
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 Replies !

---

Problem With Parameterized SELECT Statement
I'm trying to use a parameterized SELECT statement, but I must not have it right - the code below gives this compile error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserID".
string strUserID = (string)Session["UserID"];
string strSelectRatings = "SELECT [CommentID], [GameID], [UserID], [Rating], LEFT(Comment,40) as Comment FROM [Comments] WHERE [UserID] = @UserID";
SqlConnection myConnection = new SqlConnection("...");
SqlCommand myCommand = new SqlCommand(strSelectRatings, myConnection);
myCommand.Parameters.Add("@UserID", strUserID);
MySqlDataSource.SelectCommand = strSelectRatings;
GridView1.DataBind();

View Replies !

---

Expects Parameter......parameterized Query
 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 Replies !

---

C# Parameterized Query With Null Values
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 Replies !

---

Manipulating A Parameterized SQL View From Access Using VBA?
Can anyone advise me if it is possible to pass a parameter value, defined by the user of my VB app to a SQL database view and return the appropriate recorset? e.g.User enters
a date, and the view filters my databse records and returns only those that match the date entered to the data grid. How would I go about writing a VBA code to dictate the value of the parameter in the SQL view?

Any help would be greatly appreciated.


Ray

View Replies !

---

0xC0010001 With Parameterized OLEDB Source
I am trying to call a stored procedure as part of my OLEDBSource.  It takes two parameters.  @StartDate datetime and @EndDate datetime.

View Replies !

---

Parameterized Report And DataSource Security
Hello.
 
I am trying to render a report that I set up with SSRS 2005 from an existing .Net web application.  The report takes in a multi-valued parameter (sequenceNums) and displays data accordingly. 
 
This is the url I am passing from the web application:
 



Code Blockhttp://myservername/ReportServer/Pages/ReportViewer.aspx?%2fProjectDBReports%2fCriticality&rs:Command=Render&rc:parameters=false&sequenceNums=4299,4312
 
 


 
Upon redirect, I receive the following error "one or more data sources is missing credentials"
 
I currently have the following connection settings checked for the DataSource used in the report:
- connect using credentials supplied by the user running the report
- use as windows credentials when connecting to the DataSource
 
When I attempt to view the report with no parameters being passed in, the report renders properly and I receive a prompt asking me for my credentials.
 
Do I have to connect to my DataSource using credentials stored securely on the report server?
 
Thanks in advance.
 
Sam
 
ps - i included the url within a code block so no smiley faces would show
 
 

View Replies !

---

DATETIME, ADO, Parameterized INSERTs And Milliseconds, Oh My
I want to execute a paramterized ADO insert command into a SQL Server DATETIME column without losing the milliseconds.  I can accomplish this without parameters, but that isn't what I want.  Any suggestions?
 
It is OK that DATETIME only has a resolution of 3.33 milliseconds.
 
See the attached code:
 



Code Snippet

#include <string>

#include <iostream>

#import "C:Program FilesCommon FilesSystemadomsado15.dll" rename( "EOF", "ADOEOF" )

int main( int argc, char* argv[] )

{

    ::CoInitialize( NULL );

    try

    {

        ADODB::_ConnectionPtr connection;

        connection.CreateInstance(__uuidof( ADODB::Connection ) );

 
        std::string connectionString;

 
        connectionString.append( "Provider=SQLOLEDB;" );

        connectionString.append( "Data Source=HPSERV1;" ); // Choose your server/instance.

        connectionString.append( "Initial Catalog=tempdb;" );

        connectionString.append( "Integrated Security=SSPI;" );

        connection->ConnectionTimeout = 10;

        connection->Open(

            _bstr_t( connectionString.c_str() ),

            _bstr_t( "" ),

            _bstr_t( "" ),

            ADODB::adOpenUnspecified );

 
        std::string sqlStatement;

 
        sqlStatement = "DROP TABLE TestTable1";

 
        try

        {

            connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

        }

        catch( const _com_error& )

        {

            // Ignore errors as table probably doesn't exist.

        }

 
        sqlStatement = "CREATE TABLE TestTable1 ( ColInt INT NOT NULL PRIMARY KEY, ColDate DATETIME )";

        connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

 

        // ====================================================================

        // Works (datetime resolution is 3.33 milliseconds so rounds to .347)

 
        sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 1, '2007-12-28 20:05:16.345' )";

        connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

 

        // ====================================================================

        // Works (NULL is inserted)

 
        sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 2, NULL )";

        connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

 

        // ====================================================================

        // Works (datetime resolution is 3.33 milliseconds so rounds to .347)

 
        sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 3, CONVERT( DATETIME, '2007-12-28 20:05:16.345' ) )";

        connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );

 

 
        // ====================================================================

 
        unsigned int colInt = 3;

        sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( ?, ? )";

 
        ADODB::_CommandPtr command;

 

 
        // ====================================================================

        // Fails (Operand type clash: ntext is incompatible with datetime)

 
        colInt++;

 
        command.CreateInstance( __uuidof( ADODB::Command ) );

        command->ActiveConnection = connection;

        command->CommandType = ADODB::adCmdText;

        command->CommandText = _bstr_t( sqlStatement.c_str() );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adInteger,

            ADODB::adParamInput,

            4,

            _variant_t( colInt ) ) );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adBSTR,

            ADODB::adParamInput,

            -1,

            _bstr_t( "2005-10-25 09:10:11.012" ) ) );

 
        try

        {

            command->Execute( NULL, NULL, ADODB::adCmdText );

        }

        catch( const _com_error& e )

        {

            std::cout << "Error at colInt=" << colInt << " (ADODB::adBSTR and _bstr_t):"

                << " HRESULT = " << e.Error() << ": " << e.Description() << ""

                << " SQL statement: " << command->CommandText << std::endl;

        }

 

 
        // ====================================================================

        // Fails (Operand type clash: text is incompatible with datetime)

 
        colInt++;

 
        command.CreateInstance( __uuidof( ADODB::Command ) );

        command->ActiveConnection = connection;

        command->CommandType = ADODB::adCmdText;

        command->CommandText = _bstr_t( sqlStatement.c_str() );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adInteger,

            ADODB::adParamInput,

            4,

            _variant_t( colInt ) ) );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adChar,

            ADODB::adParamInput,

            -1,

            _bstr_t( "2005-10-25 09:10:11.012" ) ) );

 
        try

        {

            command->Execute( NULL, NULL, ADODB::adCmdText );

        }

        catch( const _com_error& e )

        {

            std::cout << "Error at colInt=" << colInt << " (ADODB::adChar and _bstr_t):"

                << " HRESULT = " << e.Error() << ": " << e.Description() << ""

                << " SQL statement: " << command->CommandText << std::endl;

        }

 

 
        // ====================================================================

        // Fails (A datetime is inserted to table but milliseconds are zeroed)

 
        colInt++;

 
        ::SYSTEMTIME sysNow;

        ::GetSystemTime( &sysNow );

 
        double myTime;

        ::SystemTimeToVariantTime( &sysNow, &myTime );

 
        // SystemTimeToVariantTime strips milliseconds, so we'll add some more

        // back in as we're testing insert of milliseconds.

 
        double desiredMilliseconds = 456;

        myTime += desiredMilliseconds / 24.0 / 3600.0 / 1000.0;

 
        command.CreateInstance( __uuidof( ADODB::Command ) );

        command->ActiveConnection = connection;

        command->CommandType = ADODB::adCmdText;

        command->CommandText = _bstr_t( sqlStatement.c_str() );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adInteger,

            ADODB::adParamInput,

            4,

            _variant_t( colInt ) ) );

        command->Parameters->Append( command->CreateParameter(

            _bstr_t(),

            ADODB::adDate,

            ADODB::adParamInput,

            -1,

            _variant_t( myTime, VT_DATE ) ) );

 
        try

        {

            command->Execute( NULL, NULL, ADODB::adCmdText );

        }

        catch( const _com_error& e )

        {

            std::cout << "Error at colInt=" << colInt << " (ADODB::adDate and _variant_t VT_DATE):"

                << " HRESULT = " << e.Error() << ": " << e.Description() << ""

                << " SQL statement: " << command->CommandText << std::endl;

        }

    }

    catch ( _com_error& e )

    {

        std::cout << "Unexpected error: "

            << e.Description() << std::endl;

    }

 
    return 0;

}
 


  

View Replies !

---

Parameterized Query Using Wildcards In VS2005
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 Replies !

---

Using Merge Replication, Parameterized Filters
 

Dear ppl,

I am using Merge Replication between SQL Server 2005 (Publisher) and Pocket PC (SQL Mobile 2005-Subscriber). I have a Windows Mobile  appliction on the Pocket PC that replicates data from the server.

I am using SqlCeReplication class on the Pocket PC application to synchronise the data. By default, when i call the Syncrhonise() method, it pulls all the data from the server. What I want is to pass a parameter from the Pocket PC and filter the data based on that paramter. E.g. from the Employee table, I want only those Employee that belongs to a CompanyID that I pass as a parameter.

Is there a way to do this, so that i can pass parameters from my PDA application (Windows Mobile), and make the Filters specified in the Publication to use that parameter to filter out the rows.  

Regards
Nabeel Farid

View Replies !

---

Escaping Quotes In Parameterized Query
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 Replies !

---

Error Inserting To Database: Parameterized Query
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 Replies !

---

How To Fill Dataset With Parameterized SELECT Statement
 Hello,
I have been reading about how you shouldn't build dynamic SQL statements (see TextBox1.Text in line 3)  and should use parameters instead, but I haven't yet found how to create a SELECT statement with a parameter that fills a dataset. If anyone can show me the correct way of doing this I would appreciate it so I can add it to my code snippets for proper coding practices. Thanks in advance for any assistance.           
            string strConnectionString =   ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
            SqlConnection myConnection = new SqlConnection(strConnectionString);
 ->       string sqlSelect = "select * from customers where city = " + "'" + TextBox1.Text + "'";
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, myConnection);
            DataSet ds = new DataSet();
            myConnection.Open();
            da.Fill(ds, "myDataset");
            myConnection.Close();
 
jcfrasco
                    

View Replies !

---

How To Call A Parameterized Stored Procedure Within A Loop In ASP.NET
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET.
Scenario:
 I have a loop  through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables.
 I can not use the following syntax:
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.storedprocedurename"
With cmd
      .Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1))
      .Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2))
End With
 What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?
 
Thanks,
Carlos

View Replies !

---

Parameterized Query Returns One Row With Null Values.
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 Replies !

---

Parameterized Order By Clause: Doesn't Work
Can someone tell me why SQL seems to ignore my order by clause?I tried to run through the debugger, but the debugger stops at theselect statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.THANK YOU!CREATE proc sprAllBooks@SortAscend varchar(4),@SortColumn varchar(10)asIf @SortAscend = 'DESC'Select titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BY au_lnameCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDDESCELSESelect titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BYCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDGO

View Replies !

---

Parameterized Pass-through Queries From Access Front-end?
Is there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.

View Replies !

---