asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)

SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER

Regards,

Fernando

|||

It sude did!
Thanks!

Assistance please using txt

I have received a table of data that has a field containing date
information. Unfortunately it was derived from a MainFrame dump and
originated as a txt file and was then ported into an Access MDB file
before it became an SQL table. The date format is vchar(50) and
actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
need to run a select statement for a range of dates such as 010104
thru 030104. Unfortunately being a charecter field this returns
incorrect results under a majority of cases. Back in my dBase days
there was a VAL() that could be used in this case but I have been
unable to find anything comperable in SQL. Can anyone help me please?

Thanks in advance

Steve"Steve" wrote:
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Steve,

I've never worked with dBase, but I assume that VAL() would convert to an
integral data type, so that wouldn't work either: 123103 would be greater
than 010104. Also, if you have any dates before Y2K, you'll have issues
there.

One more issue: you mentioned big iron... if the date field in the mainframe
file was PIC 9(6), you might want to verify that somewhere between the file,
Access, and SQL Server, you still have all leading and trailing zeroes...

select min(len(somefld)), max(len(somefld))
from sometable

...Anyhoo, I would recommend using string functions (like LEFT, RIGHT, and
SUBSTRING) and the CONVERT function to create a real datetime column.
Barring that, you could convert the data to a datetime for the query.

Assuming that every field is 6 characters in the format mmddyy, here's a
little test I cobbled together. Note that I'm assuming you're using SQL
Server 2K: otherwise the table variable won't work. Also, I chose what SQL
Server BOL calls the ANSI date format, but YMMV depending on your regional
date/time settings...

set nocount on

declare @.test table (
src varchar(50),
dest datetime
)

insert @.test values ('123198', null)
insert @.test values ('010199', null)
insert @.test values ('123103', null)
insert @.test values ('010104', null)

--Use 2: the ANSI (yy.mm.dd) style for conversion (the
--final parameter in the convert call)
update @.test
set dest = convert(
datetime,
right(src, 2) + '.' +
left(src, 2) + '.' +
substring(src, 3, 2),
2
)

--Out of whack
select * from @.test order by src

--In whack
select * from @.test order by dest

Craig|||Steve,
Datetime fields (there are no Date fields) in SQL Server need to be in
the format yyyymmdd, so you need to format the field first before inserting
it into your database. Do that like this:

declare @.myDate as varchar(50)

set @.myDate = '010104'

select cast(
'20' +
right(@.myDate,2) +
substring(@.myDate,3,2) +
left(@.myDate,2) as datetime)

I hard-coded '20' as the century.
Also, the way to import the file would probably to create a DTS package and
load the data into a temp table, then create a procedure using the above
statement as part of the stored procedure.
OK, now for the pitch. If you are new to SQL Server 2000, a great way to
get up to speed in just a few hours is with our video series on SQL Server
2000 at www.TechnicalVideos.net. Our videos give tips and tricks from
experts in the field, while they show you on the screen just how to do them.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Steve" <shull@.dpd.dallascityhall.com> wrote in message
news:6318603c.0403041803.7a69d4f4@.posting.google.c om...
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning query output to a variable

I am running a query that will return 1 field from 1 row. What is the
syntax to assign that to a variable.
Samplecode:
Declare @.DataFilePath varchar(200)
Declare @.DBName varchar(200)
Set @.DBName = 'TestDB'
@.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
@.DBName
Thanks!
Ron@.DataFilePath = select MAX(FileName) From master.dbo.sysaltfiles WHERE name
=
@.DBName
Or MIN. Since you are getting one row, it does not do much, but it does
tell SQL Server that only one value is coming out of the select.
RLF
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Op0ca4p%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am running a query that will return 1 field from 1 row. What is the
>syntax to assign that to a variable.
> Samplecode:
> Declare @.DataFilePath varchar(200)
> Declare @.DBName varchar(200)
> Set @.DBName = 'TestDB'
> @.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
> @.DBName
>
>
> Thanks!
> Ron
>
>

Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assigning expression results to variables

I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.

At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.

Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation

No, the result of an expression in a Derived Column transform can't be put in a package variable. However, you can nest multiple functions in an expression, so you should be able to do this in one transformation.

For example, if you wanted to get everything in "column" up to the first pipe ("|"), you could use this.

Code Snippet

SUBSTRING(column, 1, FINDSTRING(column, "|", 1))

Obviously, the expressions get a lot more complictated if you have a lot of values to parse. In those cases, I usually use a script transform and the VB Split function.

|||

Thanks John, good stuff

Assign a variable based upon query results...how to do it?

I have the following code which is incomplete. Where it says:
txtVendorID =
I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there?

Dim cmdSelectAs SqlCommand
Dim intRecordID
intRecordID = Request.QueryString("RecordID")
strConn = ConfigurationManager.AppSettings("conn")
conn =New SqlConnection(strConn)
cmdSelect =New SqlCommand("spMfgRepListAddaspxByRecordID", conn)
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.AddWithValue("@.RecordID", intRecordID)
conn.Open()cmdSelect.ExecuteReader()
txtVendorID.Text =
conn.Close()If VendorID is the only field coming back from your SP (ie SELECT VendorID FROM...) then you can do

txtVendorID.Text = cmdSelect.ExecuteScalar().ToString(); // Might have a problem with NULL's if the SP returns no results so guard against this

Otherwise you have to use

SqlDataReader dr = cmdSelect.ExecuteReader();

if ( dr.Read() )
txtVendorID.Text = dr["VendorID"].ToString(); // Assumes VendorID is the name of your field|||

hi.

you need to use datareader as;

Dim

readerAs SqlDataReader

reader = cmd.ExecuteReader()

reader.Reade()

txtVendorID.Text =reader(

"VendorID").ToString() ' VendorID which retrive from the database.

aspx, mssql and datetime

Hi, i am trying to retrive some datetime set from the database (2 posts in this table), senastposted is a datetime field.
when i run:
SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28
i get this result:

senastposted
0 record(s) affected.

and when i run:
SELECT senastposted FROM ftp WHERE senastposted >= 2005-10-28
i get this result:

senastposted
25-10-2005
29-10-2005
2 record(s) affected.
what i want is to retrive the first value based on the date:
25-10-2005
any idea why SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28 dosent work?
( i have tried to change the dates to 28-10-2005 and 28/10-2005)

Hello Pafo,
You might try something similar to this:
<code>
SELECT DATE_FORMAT(datetime,'%d/%m/%Y') AS dt WHERE ...
</code>

|||you might want to actually ask a question here.
|||Please excuse the earlier comment. When i clicked the link the text of the message was a single period.
Most DBMS systems use proprietary extentions for dealing withdates. Stuff like 'before' or 'after'. look up dateoperations in SqlServer.

ASP/SQL Database question

Is there anyway of putting a prefix on an primary key field? I'll try explain with an example.

tblCodes

CodeID [PK] - Integer
CodeName
CodeDesc
CodeType

When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType.

For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same applied the the others, a green code will have a prefix of "GREEN\" which increments.

Really not sure how to go about doing this, maybe a seperate table for CodeTypes is needed? I'm a novice programmer and i'm also new to SQL to please to be gentle!

There is and only needs to be 4 code types, if that's any help.

To give a bit more information on the reasoning for wanting the prefix on the CodeID.

Scenario
A user is inputting the amount of time he has spent on a code. There is a drop down value which he must select the CodeID from. At the moment there is no distinguishing between the CodeTypes, so he will just see 1, 2, 3 ,4 ,5.

If I can do what i'm wanting, the user will see GREEN\1, GREEN\2, RED\1, RED\2 and so on. Making it a fair bit more user friendly.

Any ideas?

I would suggest using a separate field to store the CodeType (Red, Green etc) in addition to the ID.

This will give you the flexibility of using it in different ways e.g. concatenate in sql statement to generate strings like "Green/1" or use it separately to say group by CodeType.

|||

Ideally, you need to create another table to store user/CodeType pair to deal with Many-To-Many relationship. The table at least includes two columns: UserID and CodeId.

|||

I don't really see why you are saying I need to have UserID in there at all.

I'm still not sure how to do this :(

It's only really needed for presentation reasons. So the actual value doesn't need to be stored as GREEN\1.

For example, I want the drop down menu to show GREEN\1 or RED\12 simply to make it easier for the user to tell what time of code his is picking, rather then all the codes looking the same but just with different numbers (1, 2, 3, 4, 5, 6).

Any ideas?

asp.net SQL query between given two time

I have asp.net applicatin with SQL database communicating. in database I have date field and time field. Now I wan to do SQL query which can pull informatin on particular date between given start time to given end time

Can some one show me sample SQL query so I can pull informatin on particular day between two times

thank you

maxmax

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||

select *
from sometable
where datefield between '11/2/07' and '11/15/07'

returns all records from 12:00:00 AM on 11/2/07 (that is, just after midnight of 11/1) through and including midnight of 11/15/07

You can add time values very easily:

select *
from sometable
where datefield between '2007-11-1613:30:31.497' and '2007-11-1613:31:05.670'

asp.net datareader+sql server+null fields=error

Following problem:
I try to select records where a field is null.
i.e.:
MyCommandGeneric.CommandText = "select A, b from object where C is null";
MyDataReader = MyCommandGeneric.ExecuteReader();
If I try now to access the values (string bla = MyDataReader["A"]), I am
getting the error, that datareader can't read, when there is no data.
But there is data!
I tried to query the sql server directly with the above query, and it gives
a record.I think you have to execute read method before accessing the data.
SqlDataReader Class
http://msdn.microsoft.com/library/d...opi
c.asp
AMB
"the friendly display name" wrote:

> Following problem:
> I try to select records where a field is null.
> i.e.:
> MyCommandGeneric.CommandText = "select A, b from object where C is null";
> MyDataReader = MyCommandGeneric.ExecuteReader();
> If I try now to access the values (string bla = MyDataReader["A"]), I am
> getting the error, that datareader can't read, when there is no data.
> But there is data!
> I tried to query the sql server directly with the above query, and it give
s
> a record.|||Oh, good god.
Of course! Now it works.
"Alejandro Mesa" wrote:
> I think you have to execute read method before accessing the data.
> SqlDataReader Class
> http://msdn.microsoft.com/library/d...o
pic.asp
>
> AMB
> "the friendly display name" wrote:
>

Asp.Net and SqlServer data

Hi

can anybody tell me that:
1)How can i retrieve a binary field (image field) that stored in SqlServer2000 with Vb.Net and save
it again in my hard disk that i have again that file?
2)can SqlServer itself convert image field to file with its store procedures?
3)can sqlserver run an exe file on local computer from its storeprocedure?

Best RegardsHi,
1. see http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=718011.
2. Not that I know of.
3. Yes, use xp_cmdshell, seeMSDN.

Asp.net & File

Hi

i use this code to retrieve my image field from sql server
(my file isn't picture !!!)

***********
Dim MyData() As Byte
MyData = Ds.Tables(mytable).Rows(0).Item(myfield) 'For Example
Dim K As Long
K = UBound(MyData)

Dim fs As New FileStream("c:\mkh.xml", FileMode.OpenOrCreate, FileAccess.Write) 'in this line get error
fs.Write(MyData, 0, K)
fs.Close()

fs = Nothing
Ds = Nothing
************
but it doesn't work and give me this error in ASP.Net(With Vb)
<<Access to the path "C:\mkh.xml" is denied.>
i get this code from microsoft msdn and alot of furoms !!!!
then why doesn't work??If we start with the error you're getting, the reason is simple. The Account which Asp.Net use to perform different actions (it's called '<MACHINENAME>\ASPNET'), is not allowed access to your XML file by your system. Locate 'C:\mkh.xml', right click it, choose Security & Sharing, locate your ASPNET account, and let this account have the rights to 'Read and Run', 'Read' and 'Write'.

Begin there, and come back later when that is solved and you need more help.