Storing Variable Values Whilst Using Cursors

Feb 21, 2007

Hi below is the code I am using.

------------------------------------
SET NOCOUNT ON

DECLARE @emailid varchar(50), @rastype varchar(50),
@message varchar(80)
declare @allrastypes varchar(200)

DECLARE email_cursor CURSOR FOR
SELECT distinct Email
FROM dbo.tblMaintCustomer
ORDER BY Email

OPEN email_cursor

FETCH NEXT FROM email_cursor
INTO @emailid

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = 'Email Address ' +
@emailid

PRINT @message

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

DECLARE rastype_cursor CURSOR FOR
SELECT distinct [RasType]
FROM dbo.tblMaintCase x, dbo.tblMaintCustomer y
WHERE x.caseid = y.caseid AND
y.Email = @emailid
and RasType is not null

OPEN rastype_cursor
FETCH NEXT FROM rastype_cursor INTO @rastype
select @allrastypes = @allrastypes + ',' + @rastype

IF @@FETCH_STATUS <0
PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = @rastype
PRINT @message
select @allrastypes = @allrastypes + ',' + @rastype
FETCH NEXT FROM rastype_cursor INTO @rastype

END

CLOSE rastype_cursor
DEALLOCATE rastype_cursor

insert into dbo.tblTest values(@emailid,@allrastypes)
select @allrastypes = ''
FETCH NEXT FROM email_cursor
INTO @emailid
END
CLOSE email_cursor
DEALLOCATE email_cursor
--------------------------------------

I basically want the value of @allrastypes to accumulate each time it
loops through, which is is not doing.

The result I get is :

Email Address Join Bytes!
G5R
(for here i want @allrastypes to be 'G5R,')

Email Address Join Bytes!
G1
G3
G5O

(for here i want @allrastypes to be 'G1,G3,G5O')

Can someone help

Thanks
Archana

View 1 Replies


ADVERTISEMENT

Storing Values Into Variable From A Distributed Query Result

Mar 25, 2002

Hi.

I am trying to store the column value to a variable from a distributed query.

The query is formed on the fly.

i need to accomplish something like this

declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname


-MAK

View 2 Replies View Related

Out Variable In Cursors

Apr 17, 2008

i want to store the output in out variable .Which gives multiple values.

create procedure usp_test (@AccountID INT)
as
begin
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
end

i get nearly ten rows in the print statement
how can i assign the output to a out variable where i get all ten rows.

Any ideas or suggestions.

View 1 Replies View Related

I Am Trying To Use Nested Cursors. But Is There A Way To Use More Than One @@FETCHSTATUS Variable ?

Apr 18, 2008

I need to use a nested cursor in SQL server 2005. I have never done this before. The problem here is that I need to use "@@FETCH_STATUS' twice.Once for my inner loop and once for my outer loop. If there is a way to use two different @@FETCH_STATUS variables at once, I would like to know how.
Here is my code. The inner loop is currently pseudo code and I just need to figure out how to code it.
DECLARE @mycur1 CURSORDECLARE @mycur2 CURSORDECLARE @InMarketId INTSET @InMarketId=575 -- Hard code for now. Just for testing.DECLARE @test VARCHAR(10)SET @mycur1 = CURSORSET FORSELECT SubDivisionId FROM SubDivision WHERE MarketId=@InMarketIdOPEN @mycur1FETCH NEXT FROM @mycur1 INTO @testWHILE @@FETCH_STATUS = 0 BEGIN
 PRINT @test--       Begin Inner Nested Loop--         FOR EACH SubDivisionId in SubDivisionSubMarket SDSM delete from SubMarket SM Where SDSM.SubMarketId=SM.SubMarketId
--       End of Inner nested Loop
  FETCH NEXT FROM @mycur1 INTO @test    ENDDEALLOCATE @mycur1------------------------------------------Here are my three tables I am scripting agains and a description of each.
SubDivision-----------SubDivisionIdMarketIdNameDescriptionCreatedBy
SubDivisionSubMarket--------------------SubDivisionIdSubMarketId
SubMarket--------------------SubMarketIdCodeDescriptionLastUpdateDate

View 2 Replies View Related

Dynamic Query, Local Cursor Variable And Global Cursors

Oct 3, 2006

Hi all.



I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.



So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.



The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:



SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor



This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.



My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...



The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.



So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.


I guess my concrete questions are:


Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?



Anybody sees another way arround this?Thanks in advance,

Carlos

View 3 Replies View Related

SQL Server 2008 :: Using Cursors To Find First / Highest And Last Values

Sep 20, 2015

I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.

@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal

-- First Cursor

DECLARE TESTCURSOR CURSOR
DYNAMIC

[Code] ....

The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.

SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id

View 9 Replies View Related

Storing Query Results In A Variable

Mar 28, 2001

Can someone tell me how to store the results of a multi-row query in a text or varchar variable so that I might do something like this:

declare @var1 text

select @var1 = {results of select date, count(*) from testtable2 group by date)


Thanks!

joe

View 2 Replies View Related

Storing Part Of An Express To A Variable

Aug 3, 2007

I know this can be done, because I saw it and tried it once. But did not use it at the time and consequently forgot how.

I am storing an expression to a variable @Var Varchar(50)

@Var = ('aa','bb','cc')

I want to:

Select * from Table WHERE Field IN @Var

Substituting my variable for the " ('aa',bb','cc') which I can build dynamically...thus the use.

Purpose:
I have an aggregate query that currently has a CASE statement based on the contents of ('aa',bb','cc'). However, those contents can change. The aggregate will not allow the subquery (SELECT Field FROM Table).

What I remember looked something like:

SELECT * FROM Table WHERE field in ' & @Var &'.

View 1 Replies View Related

Storing The Result Into A Variable W/o Cursor

Feb 1, 2007

Hello All,

A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.

Thank you in advance

View 1 Replies View Related

Storing The Result Of A Select TOP 1 Into A Variable

Sep 7, 2007

hello

Does anybody know how to store the result of a select top 1 into a variable??

I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc


And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc

But none of them work


Any ideas??
Thanks

View 1 Replies View Related

Storing Variable In Command File From SQL Server

Sep 13, 2004

Hi,
i am using command files(.cmd) and osql utility to conneft to a database.
following is the requirement.
I have a query "SELECT top 1 au_fname FROM pubs..authors".

Can i store the value of au_fname in the command file?

Is there any work around for this.

Any help is appreciated.

Thanks.... :)

View 1 Replies View Related

Storing Collumn Data In A Local Variable

Aug 16, 2007

Hi All,
I need to select some collumn data , can i store it in a varable some how.

Select name from Names

how can i assign this value to a variable, I need to manipulate this column value based on few cases.
As always thanks and appreciate your help.

View 1 Replies View Related

DataType Storing Strings Of Variable Length ?

Aug 22, 2007

Hello guys!

I am looking for the best DataType that makes it possible to store strings of variable length (from 15 to 300 caracters, rarely longer).


Thanks a lot for any help !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

Storing Results Of Select Statement In @variable

Feb 11, 2008



I'm new to sql stored procedures but I would like to store the results
of an sql statement in a variable such as:

SET @value = select max(price) from product

but this does not work, can someone tell me how I would go
about in storing the results in a variable.

@value is declared as int

Thanks in advance,
Sharp_At_C

View 1 Replies View Related

Storing Null Values

Oct 4, 2004

I am retrieving a <NULL> value out of a SQL database, displaying it in a text box, then updating the database with the results of the text box.

I am using the following code to make the insert.

.Parameters("@BodilyInjuryPerson").Value = txtBodilyInjuryPerson.Text

When I have values for the text string, everything runs smoothly. However, when the database originally had a <NULL> or when I want to delete the currently existing value and replace it with a <NULL>, I get an error message stating... "Input string was not in a correct format".

Back in the old days, when I was using Random Access databases, you just inserted a Char(0). But that doesn't work any more.

Could somebody help me. I know there is a magic bullet, I just don't know what it is.

View 2 Replies View Related

Storing Old Values After UPDATE

Mar 15, 1999

When I update a field f1 I should store its previous value in the field f2. How can I achieve this?

View 1 Replies View Related

Storing Decimal Values

Aug 7, 2007

Hi,

I'm loading millions of rows into a database table. This data contains a lot of dedcimal values of 4 or 6 decimal places somtimes more.

I've found that storing them as floats is inaccurate, and I'm storing as decimals of the appropriate length.

I'm concerned whether this is the right way to do this ?

Can anyone make any suggestions, perhaps an article that I should take a look at ?


Sean

View 2 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Storing Comma Seperated Values

Mar 7, 2008

Hi I have a text field called testing which shows the selection the user has made from a checked list. I want to save the contents of testing to a datadase with coloum varchar and with a comma to seperate the selections. It stores ok but sql queries do not recognise them as comma seperated values. e.g the selection from the vb code produses: Beauty, Fashion, Travel. Sql takes the lot as one. When I test a query say select from table type where Category = beauy it gives nothing. How do I store it so sql would recognise it as seperate enteries.
my vb code is below
Dim msg As StringDim li As ListItem
msg = ""For Each li In CaregoryCheckBoxList.Items
If li.Selected = True Then
'asp has a security default of no accetping html in input fields br is html code so causes error
'msg = msg & "<br>" & li.Text & " selected."
msg = msg & li.Text & "," & " "
End If
Next
Testing.Text = msg

View 10 Replies View Related

Storing Checkbox Values To Database

Mar 19, 2008

Hi i am trying to store the checkbox values on my page to the database, but im stuck on what to do. I have the following code already;protected void Btn_Subscribe_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
string strSQL = "INSERT INTO Newsletter(emailAddress) VALUES (@emailAddress)";SqlCommand Command = new SqlCommand(strSQL, conn);
Command.Parameters.Add("@emailAddress", SqlDbType.VarChar);Command.Parameters["@emailAddress"].Value = txtEmail.Text;
conn.Open();
Command.ExecuteNonQuery();
conn.Close();
}
How do i now insert the values from my checkbox, this is how i am setting the checkbox;public void Page_Load(object sender, EventArgs e)
{ArrayList values;if(!IsPostBack)
{
//Build array of data to bind to checkboxlistvalues = new ArrayList();
values.Add("Sport");values.Add("Gardening");values.Add("technology");
AlertList.DataSource = values;
AlertList.DataBind();
}
If i am going about it the wrong way please let me know, thanks

View 15 Replies View Related

SQL Server 2012 :: Storing Data From Temp Table Into A Variable

Aug 4, 2015

I have created procedure that creates a temptable with the users from a certain business unit.

I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.

CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)

[Code] ....

View 6 Replies View Related

Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Aug 30, 2007

Hello all:

Here is a sample query:



DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'

DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'



DECLARE @KeyCol NVARCHAR(50);

SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR (1000)


SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +

' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '



The value resulting from Executing @KeyValue is an integer.

I want to store this value in a new variable say @VAR2

When I do this

DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue

its giving me an error.


can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?

View 3 Replies View Related

Problem With Storing Datetime Values In Formview

Jan 20, 2007

Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get  an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now:   Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>"                            SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number">            <DeleteParameters>                <asp:Parameter Name="Ticket_number" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />                <asp:Parameter Name="Ticket_number" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="column1" Type="Int32" />                <asp:Parameter Name="Employee_ID" Type="Int32" />                <asp:Parameter Name="Problem_description" Type="String" />                <asp:Parameter Name="Date_submitted" Type="String" />            </InsertParameters>        </asp:SqlDataSource>    I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate:     <InsertItemTemplate>                column1:                <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'>                </asp:TextBox><br />                Employee_ID:                <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:TextBox><br />                Problem_description:                <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:TextBox><br />                                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                    Text="Insert">                </asp:LinkButton>                <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                    Text="Cancel">                </asp:LinkButton>            </InsertItemTemplate>            <ItemTemplate>                column1:                <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br />                Employee_ID:                <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'>                </asp:Label><br />                Problem_description:                <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'>                </asp:Label><br />                Date_submitted:                <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'>                </asp:Label><br />                Ticket_number:                <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'>                </asp:Label><br />                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"                    Text="Edit">                </asp:LinkButton>                <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"                    Text="Delete">                </asp:LinkButton>                <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                    Text="New">                </asp:LinkButton>            </ItemTemplate>        </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work.     Any help would be greatly appreciated. Thanks! 

View 2 Replies View Related

Storing Multiple Values From Drop Down List

Apr 11, 2012

How can I store multiple selected values (from a dropdown list) in mysql database?

PHP Code:

<form method="post" action="storedetails.php">    Research Interest:<br/>    
<select multiple="yes" size="6" name="interest[]">    
<option id="webt" value="webt">Advanced Web Tech1nologies</option>    
<option id="mobhum" value="mobhum">Mobile and Humanoid Robots</option>    

[code]...

View 5 Replies View Related

Integration Services :: Storing Column Value In SSIS Variable When Skipping Header Row?

Apr 28, 2015

I have a file with a header row which contains the date of the file and under that all the columns without a header.

In SSIS package I skip row and manually name the different columns.

However, I want to use the column in the header row to store the date value in an SSIS variable and use that variable to write it to our staging table. How can I do this when I skip the header row?

View 5 Replies View Related

Storing A Money Data Item (in SQL Server) In A Double SSIS Variable

Nov 5, 2007

Hi

I have a table in SQL Server with following spec
Table1(Grossamount(money))

I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS

Select Sum(Grossamount) from Table1

I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.

it gives me the error :
[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".



I tried the following sql to no avail

Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1


Your help very much appreciated

View 7 Replies View Related

Inserting Global Variables Storing Values In A Table

Oct 5, 2006

Hi,

I need a resolution of the following issue:

Following SQL is to be inserted in an audit table :

INSERT INTO GLOBAL_VARIABLE_LOAD

([LOAD_ID]

,[LOAD_STATUS]

,[START_TIME]

,[END_TIME])

VALUES

(@P_LOAD_ID

,@P_LOAD_STATUS

,@P_START_TIME

,@P_END_TIME)

@P_LOAD_ID, @P_LOAD_STATUS, @P_START_TIME, @P_END_TIME are the 4 parameters mapped to global variables (At package Level) which store values mapped in a previous SQL Task in my control flow.

Following Error Message is thrown on executing the SQL Task:

Invalid object name 'GLOBAL_VARIABLE_LOAD'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone guide me as to how should I go about saving values in global variables and inserting them in a table.

Thanks in advance.

Regards,

Aman

View 4 Replies View Related

Storing Comma Separated Values In A Single Column Of A Table

Jul 13, 2007

Hi,
I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes.
So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns
So in general how do people implement these types of requirements in relational databases?


--Subba

View 11 Replies View Related

The Best Method Of Storing Multiple Values For A Single User Criteria In The Database ?

Mar 31, 2008

Let's say you had a User table and one of the fields was called Deceased.  It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive.  Let's say another field is called EyeColor.  A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well.
Now, let's say I want to store all the languages that a specific user can speak.  This isn't as easy as the previous examples since it's not a yes or no or a single-value answer.  I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe.
In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc.  Now, here are the 2 solutions that came to mind.....
1) Make a field called LanguagesSpoken in the User table.  When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess).  I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used.  Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data.
2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table.  A simple implementation would have 3 fields (primary key, userId, languageId).  A row would associate a user with a language.  So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user).  Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them.  However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time.  I guess I would index the userId field in the LanguagesSpoken table for quicker access?
OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions.  Is there a better way other than those 2 methods?
I haven't work extensively with databases and I'm just familiar with the basics.  I'm just trying to find out the best-practice implementation for this type of situation.  I'm sure in the real world, situations like this is very common and I wonder how the professionals code this.
Thanks in advance.

View 3 Replies View Related

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql

Apr 29, 2007

ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">     protected void runit_Click(object sender, EventArgs e)    {       //SqlDataSource ID = "InsertExtraInfo".Insert();      //SqlDataSource1.Insert();    }      protected void Button1_Click1(object sender, EventArgs e)    {        SqlDataSource newsql;                newsql.InsertParameters.Add("@name", "Dan");        newsql.InsertParameters.Add("@color", "rose");        String t_c = "purple";        string tempname = Page.User.Identity.Name;        Label1.Text = tempname;        Label2.Text = t_c;        newsql.Insert();    }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>mini update</title></head><body>    <form id="form1" runat="server">        &nbsp;name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />        color        <asp:TextBox ID="color" runat="server"></asp:TextBox><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<asp:LoginName ID="LoginName1" runat="server" />        <br />        <br />        <br />        <br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"            ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"            UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">            <DeleteParameters>                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="color" Type="String" />                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </UpdateParameters>            <InsertParameters>        <asp:InsertParameter("@name", "Dan", Type="String" />        <asp:InsertParameter("@color", "rose") Type="String"/>                                       </InsertParameters>        </asp:SqlDataSource>        &nbsp;        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"            AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">            <Columns>                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />                                <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />            </Columns>        </asp:GridView>           </form></body></html>  

View 1 Replies View Related

Error Whilst Adding Foreign Key

Jan 4, 2008

I've been getting what I think is a bizarre error message whilst trying to create a foreign key between two tables.

tblDailyComments has a primary key of DiaryID
tblDailyOperatingData has a column DiaryID.

Both are Int (4).

Yet when I run the following:


ALTER TABLE tblDailycomments
ADD CONSTRAINT FK_tbldailycomments_tblDailyOpData
FOREIGN KEY REFERENCES
tblDailyOperatingData(DiaryID)

I'm getting the error message:

Server: Msg 8139, Level 16, State 1, Line 1
Number of referencing columns in foreign key differs from number of referenced columns, table 'tblDailycomments'.

I also tried to create the relationship in a database diagram and this brought back errors as well.

Any help would be much appreciated.

Peter

View 2 Replies View Related

Retrieving Data Whilst Client Side

Jun 29, 2004

Hi

I know this is a nasty way of doing but I can't think of another way. I'm retrieving an ID from a treeview when it's clicked client side, and I want to pass this to a SQL Procedure and retrieve some other data relating to it and fill a list box. Can this be done?

Thanks
Lbob

View 2 Replies View Related

Changing Field Length Whilst Replicating

Jan 6, 2004

Hi

I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks Ed

View 2 Replies View Related







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