How To Fill Dataset With Parameterized SELECT Statement

Dec 17, 2007

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


ADVERTISEMENT

Problem With Parameterized SELECT Statement

Dec 30, 2006

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

Execute Parameterized Select Statement From Data Flow

Aug 25, 2006

I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.

Query is sort of

Select field1,fiel2... from table 1 where id = @id

@id is each ID from lookup

Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.

Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.

View 8 Replies View Related

Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts

Jul 30, 2007

I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this:    query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)"    ....    if ReqSkill1 <> "" then                level1 = Convert.ToInt32(Request.form("minskilllvl1"))                query = query & pickclmleft & ReqSkill1 & pickclmright1                cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text)                cmd.Parameters("@ReqSkill1").value = ReqSkill1                cmd.Parameters.Add("@level1", SqlDBtype.int)                cmd.Parameters("@level1").value = level1    end if   above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this:     query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)"     ASPqry = " AND (ASPlevel >= "    try             con = new SqlConnection()            con.ConnectionString = “**************string was correct****************â€?                        cmd = new SqlCommand()            cmd.Parameters.Add("@english", SqlDBtype.int)            cmd.Parameters("@english").value = english                                    if ReqSkill1 <> "" then                if ReqSkill1 = "ASPlevel" then                    query = query + " AND (ASPlevel >= "                    level1 = Convert.ToInt32(Request.form("minskilllvl1"))                    if level1 = 0 then                        query = query + "0)"                    end if                    if level1 = 1 then                        query = query + "1)"                    end if                    if level1 = 2 then                        query = query + "2)"                    end if                    if level1 = 3 then                        query = query + "3)"                    end if                end if            end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser 

View 4 Replies View Related

How To Select A Specific Value From Dataset To Fill A Specific Cell ?

Mar 27, 2007

Hi there !

Thanks for taking the time to read this thread.

I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.

My situation is this...

I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-


Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658

I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.

so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)

TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")

Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")


Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")


I only expect this report to print out one page holding the previous values.

However, it ended up printing like this

----------------------------------------------------------

Table1
Column1
DetailRow1 1
DetailRow2

Column1
DetailRow1
DetailRow2 2000


Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4

Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658

------------------------------------------------------

I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.

Is this the correct expression ?

=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")

and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Appreciate any advice or suggestion for this scenario !

Thanks!

Bernard

View 3 Replies View Related

Problem With Dates In Dataset Select Statement

Mar 14, 2007

I'm using the designer to create/modify strongly typed datasets.  I have one select statement that I'm having considerable trouble with.  The user selects search parameters from a form.  These search parameters are used to filter the data sent to a gridview control.  Three of these parameters are almost enough to make me abandon the dataset in this case.The first two are minimum and maximum age.  I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated.  In ASP I'd do something like this:strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.The third parameter is astrological sign.  Again, I calculate it based on the birth date.  I don't have a field for it.  I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign.  I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?  I need to get the age issue fixed.  The sign is a nice to have, since I can do it another way if I have to. BTW:  I did decide to abandon the dataset in favor of a SqlDataSource control.  This allowed me to build my select string in the code behind the way I would have in ASP.  This resulted in paging and sorting not working properly.  Sorting would be nice, paging is necessary.  I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn).   Any comments or feedback on this would be avidly read and appreciated.Diane 

View 8 Replies View Related

How To Fill A Dataset?

May 11, 2006

Can a SqlDataSource be used to fill a DataSet? If so, will you show me how it's done?

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

Unable To Fill Dataset

Apr 26, 2008

I'm trying without success to do a select command against the dataadapter and then fill a dataset. I'm using VS 2008, and I've set my project to the 2.0 framework. What is wrong is I get an empty dataset (The count on the dataset = 0. )with the code below. The database is connecting fine, so I assume it's something to do with the commandtext. The database is SQL CE 3.5.

Any ideas would be appreciated.





Code Snippet

Public Shared Function Init() As DataSet
'Todo: need to encrypt the password
Dim instance As New SqlCeConnection("Data Source=|DataDirectory|PBdata.sdf")
Dim selectcmd As SqlCeCommand = instance.CreateCommand
selectcmd.CommandText = "Select * From [dusers]"
Dim adp As New SqlCeDataAdapter(selectcmd)
Dim ds As New DataSet("dusers")
adp.Fill(ds)

Return ds

End Function

View 1 Replies View Related

Dynamic Parameterized MDX Dataset In Report Table

Oct 10, 2007

Hi, I was wondering if there is a way to solve this issue.

I have the following MDX to retrieve specialised time related data from a cube



Code Block

SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[2007 Q1], [Time].[Quarter].[2007 Q1].lag(1), [Time].[Quarter].[2007 Q1].lag(2), [Time].[Quarter].[2007 Q1].lag(3), [Time].[Quarter].[2007 Q1].lag(4), ytd( [Time].[Quarter].[2007 Q1]), ytd( [Time].[Quarter].[2007 Q1].lag(4)),ytd([Time].[Quarter].[2007 Q1].parent.lag(1)),ytd([Time].[Quarter].[2007 Q1].parent.lag(2)) } } } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS

FROM [QMS]

WHERE ( [Overall].[Overall].&[Y] )






I can populate a table with the columns i need from the resulting dataset, however it is not dynamic. i.e.

The dataset field names come up as say,

Measures___Vacancy_Time_All_Time_2007_2007_Q1
Measures___Vacancy_Time_All_Time_2006_2006_Q4

Which i can place in the report...

But if I then want to parameterize [2007 Q1] like as follows




Code Block
="SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[" + Parameters!Quarter.Value + "], [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(1), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(2), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(3), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "]), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(1)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(2)) } } } ON COLUMNS ,NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS FROM [QMS] WHERE ( [Overall].[Overall].&[Y] )"





so that everything is then driven from a single selected Quarter value, the table report no longer gets populated, as it has hardcoded field values such as

=Fields!Measures___Vacancy_Time_All_Time_2007_2007_Q1.Value

and if the Quarter selected is [2006 Q1] for example, this field will not exist in the dataset.

Is there a way to accomplish this? I am using SSRS 2005 against SSAS 2000 cubes

View 5 Replies View Related

DataSet Fill Using Cmd.Parameters - Legal?

Jan 2, 2007

A - Must Declare variable @ZipEntry - error generates when I run the code below.  If I hard code the "WHERE ZipCode = 72364" it works (display messed up but it works - don't know how to code in the line breaks yet).  But when I try to pass in the value of a TextBox called txtZipEntry on the page I get this error.  Am I not delcaring @ZipCode in the correct spot ?
Thank you,
public partial class ContractorResultsDisplay : System.Web.UI.Page{string ZipEntry;protected void Page_Load(object sender, EventArgs e){
ZipEntry = txtZipEntry.Text;SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);string sql = "Select [City], [StateCode], [AreaCode], [Latitude], [Longitude], [ZipCode] From [ZipCensus11202006] Where ZipCode > @ZipEntry";SqlCommand cmd = new SqlCommand(sql, con);cmd.Parameters.AddWithValue("@ZipEntry", ZipEntry);SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();da.Fill(ds, "ZipCodeRefs");StringBuilder htmlStr = new StringBuilder("");
foreach (DataRow dr in ds.Tables["ZipCodeRefs"].Rows){htmlStr.Append(dr["City"].ToString());}
ZipContent.Text = htmlStr.ToString();
}
}

View 1 Replies View Related

Fill DataSet Using Stored Procedure

Jul 21, 2005

The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms
@email   NVARCHAR ,@num    INT OUTPUT ,@userEmail    NVARCHAR OUTPUT ASBEGIN DECLARE @errCode   INT
SELECT   fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
 SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read.....
Dim errCode As Integer
Dim conn = dbconn.GetConnection()
Dim sqlAdpt As New SqlDataAdapter
Dim DS As New DataSet
Dim command As SqlCommand = New SqlCommand("Comms", conn)
command.Parameters.Add("@email", Trim(sEmail))
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
sqlAdpt.SelectCommand = command
Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int)
pNum.Direction = ParameterDirection.Output
Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar)
pUserEmail.Size = 256
pUserEmail.Direction = ParameterDirection.Output
sqlAdpt.Fill(DS)
Return DS
Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way....
Dim DScomm As New DataSet
DScomm = getPts.getBabComm(sEmail)
dgBabComm.DataSource = DScomm.Tables(0)
And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath

View 7 Replies View Related

Dataset.fill Error In Sqlserver2000 + ASP.net 2.0

Jun 12, 2006

Hi,I am unable to load data in dataset via sqlconncetion object. Always i get error at ds.fill.Thanks in advance. PLS Help.Sudipta Datta www.geocities.com/dattasudipta 

View 1 Replies View Related

Fill A Dataset In Visual Studio 2005

Oct 13, 2006

this may seem like a real newbie question, but I got no clue how to solve this problem.  When I was working in Visual Studio 2003, whatever I wanted to fill a dataset with data, I was using a SqlDataAdapter that I created like this adapTest.Fill(dsTest); But now, I cant seem to find the sqldataadapter anywhere, I can just create (on the visual interface of one of my page of my aspx project) dataset, but cant find anywhere a adapter to put an SQL instruction into...maybe theres a new way to do this in Visual Studio 2005 that I dont know about.  Im not sure if I was clear enough, but what I want in the end is to be able to use my dataset like that dsTest.Tables.Rows[0]["a column"].ToString()by filling the dataset with data like I was able to do in VS2003.  Thanks for taking the time to read this.

View 3 Replies View Related

Fill DataSet Takes Forever, Query Db 7 Sec

Jan 16, 2007

Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.

View 2 Replies View Related

Counting Result Set From SQL Query Before Fill()ing A DataSet

Mar 6, 2007

I need to display something like "Results x-y of z."  The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set.  How can I get the right value for z?

View 5 Replies View Related

Character String Query Doesn't Fill Dataset

Mar 15, 2007

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end.  I have a strongly typed dataset in the application that calls a stored procedure for the select.  I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer.  The results return and display correctly.  However, when I run the application, the dataset does not get filled.  It is like the results do not output to the application.
If I change the query to be a normal select it works.  For example:
select * from tblEmample
That works fine.  What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

View 1 Replies View Related

How Do I Get The OUTPUT Parameter Value From A Stored Procedure That I Am Using To Fill A Dataset?

May 10, 2007

View 6 Replies View Related

SQL Express Goes Out To Lunch In Visual Studio After Failing To Fill A DataSet

Aug 24, 2006

I have an off the wall situation that occurs when I try to fill a dataset that has already been filled with data. The issue with the DataSet in and of itself was a mistake I made and is not relevant to this forum.

What would appear to be relevant though, is the fact that after the exception was caught, I could not longer query data from the SQL Express database that was included in my project. In other words, when I tried to view the data in the IDE by selecting "Show Table Data," I received a message telling me that the query was running and eventually it timed out.

To fix the problem in my application, I obviously eliminated the bad code. But to be able to query the database again, I had to physically restart my computer (yes, I probably could have restarted the service, I know).

So my question is, has anyone run into this phenomenon before? If so, is there a way to prevent it other than the obvious, which is to clear the DataTables in my DataSet before filling them?

It's not that big a deal, but it is kind of a pain in the rear to have to restart everything because of a stupid coding mistake on the client side while I'm developing an app.

View 1 Replies View Related

Wild Search NText And NVarChar In Parameterized Inline Statement

Dec 14, 2004

I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?

What I use currently:

Dim strSQL As String
strSQL = "SELECT ArticleID, "
strSQL &= "ISNULL(Body, '') AS Body, "
strSQL &= "ISNULL(Subject, '') AS Subject "
strSQL &= "FROM Articles "
strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') "
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlDataAdapter(strSQL, con)
cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword
...


I'm not so sure where should I place the letter "N", I use :

SELECT ArticleID,
ISNULL(Body, '') AS Body,
ISNULL(Subject, '') AS Subject,
FROM Articles
WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')

in Query Analzyer, it works! But it failed in my program... oh my god...

Thanks a lot!

View 4 Replies View Related

Unknown Ammount Of Parameters, Parameterized Statement/split Strings In Transact Sql

Oct 26, 2007

Hello,
Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs.
Instead of doing the following:
FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'"
NEXT
wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil 

View 3 Replies View Related

Stored Procedure To Update A Table Using Parameterized CASE Statement - Erroring Out

May 2, 2008

I am trying to create a stored procedure that will take a text value passed from an application and update a table using the corresponding integer value using a CASE statement. I get the error: Incorrect syntax near the keyword 'SET' when I execute the creation of the SP. What am I missing here? This looks to me like it should work. Here is my code.


CREATE PROCEDURE OfficeMove

-- Add the parameters for the stored procedure here

@UserName nvarchar(10),

@NewLocation nchar(5),

@NewCity nvarchar(250)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Execute as user = '***'

DELETE FROM [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments

WHERE User_Name = @UserName

INSERT INTO [SQLSZD].[SZDDB].dbo.Employee_Office_Assignments

SET User_Name = @UserName,

Room_ID = @NewLocation

UPDATE [SQLSZD].[SZDDB].dbo.Employee_Locations

SET Office_ID =

CASE

WHEN @NewCity = 'Columbus' THEN 1

WHEN @NewCity = 'Cleveland' THEN 2

WHEN @NewCity = 'Cincinnati' THEN 4

WHEN @NewCity = 'Raleigh' THEN 5

WHEN @NewCity = 'Carrollwood' THEN 6

WHEN @NewCity = 'Orlando' THEN 7

END

WHERE User_Name = @UserName

END

GO

View 4 Replies View Related

Reporting Services :: IF Statement If Dataset Field Value Equals Value Of Dataset Field

Sep 3, 2015

Using this IIF statement:

=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date  - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015

What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.

View 4 Replies View Related

How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist

Apr 9, 2008

HI
I need help 
how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist
my controls are as follows
<asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>"
SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee]&#13;&#10;where CurrentEmployee_YN=1&#13;&#10;order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%;
border-collapse: collapse; height: 32px; visibility: hidden;" id="table0">
<tr>
<td style="width: 159px; visibility: hidden;">
</td>
<td style="width: 170px">
</td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Order No:</strong></td>
<td bgcolor="#eeeddb" style="width: 26%; height: 25px">
&nbsp;<asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Account No:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo"
Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td>
<td bgcolor="#eeeddb" style="width: 20%; height: 25px">
<strong>
Today's Date:</strong></td>
<td>
<asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 159px; height: 25px">
<strong>
Travel Consultant:</strong></td>
<td bgcolor="#eeeddb" style="width: 170px">
<asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant"
Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td>
</tr>
</table>
<center>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
</center>
<center>
&nbsp;</center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%;
border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2">
<tr>
<td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px">
<font color="#000000" size="5">Enter Passenger(s) Details</font></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Surname:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
&nbsp; &nbsp;<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 20px">
<strong>
Name:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 20px">
<asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name"
Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Initials:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox>
 
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Title:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="156px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="Mr"></asp:ListItem>
<asp:ListItem Value="Mrs"></asp:ListItem>
<asp:ListItem Value="Ms"></asp:ListItem>
<asp:ListItem Value="Dr"></asp:ListItem>
<asp:ListItem Value="Prof"></asp:ListItem>
<asp:ListItem Value="Min"></asp:ListItem>
<asp:ListItem Value="Other"></asp:ListItem>
</asp:DropDownList>&nbsp;
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1"
Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator>
</td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Department</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="Department" runat="server"></asp:TextBox></td>
</tr>
<tr><td bgcolor="#eeeddb">
<strong>
Cost Centre</strong>
</td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Tel:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
&nbsp;&nbsp;
</td>
</tr>
<tr>
<td bgcolor="#eeeddb" style="width: 31%; height: 25px">
<strong>
Fax:</strong></td>
<td bgcolor="#eeeddb" style="width: 57%; height: 25px">
<input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false"
style="width: 143px" type="text" />
</td>
</tr>
</table>
cost centre and department are from cost table
and the rest are from employee table

View 10 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

If Statement In Dataset

Apr 27, 2007

hi,



Can I use if statement in Dataset?



I have to achive following functionality,



Select

*

From

Table

Where

Column1=value

AND

Iif(Param==0,

Colm2='value1' ,

colm2= 'value1' AND Colm3='value2')





Is this possible? or are there any alternatives?



thanks,

Anand

View 1 Replies View Related

Can I Select * From DataSet?

Apr 16, 2008

I used codes below:
sqlA = "select * from order where orderby = 'Mike'"
myDataset = makeDataset(sqlA) // I created a function to return a dataset
I knew the code below is working:
select * from Product where OrderID in ( "select * from order where orderby = 'Mike'")
Can I do the way below?
select * from Product where OrderID in myDataset

View 2 Replies View Related

Assigning A Select Value To Each Row Of A Dataset

Oct 4, 2007

I have the following problem:
in a data flow, if inserting new records, there are columns that take some default values. These default values are kept in a table in case the user wants to change them some day. Def. values could not be assigned at a table level because there's another dataflow that populates the same table, but the rules for the default values are different.

Since I want to extract these values only if there is at least one new row, I'm not fond of the idea to use Execute SQL Task (to save the default values in a variable) before the actual Data Flow. What are my options in getting these values in a Data Flow right before inserting? Thank you for the help.

View 5 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related







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