stored procedure....help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure....help

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 16:21:20

how can I capture userID from the session and then pass it into the stored procedure.

there will be two types of users in the session ,guest users and logged in users.

I tried writing in my c# code ...
Session("@userName") = Membership.GetUser;

but this is giving me compilation error ,"The name 'Session' does not exist in the current context"

please help

thank you

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 16:46:07

Session["userName"] == Membership.GetUser;

This should be moved to .net not sql. And the title of the topic is wrong

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 16:46:41

i am sorry for that.
I thought as I have to pass value in SP,i posted here and also I get here response very quickly compare to .net forum.

neway if anybody know the solution then please help me.

I will also post the same question in .net forum

thanks

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 17:23:51

If i understand you correctly.

1. You first get the variable name in c#
2. You pass it to your stored procedure ?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 17:45:29

yes but how to get that variable name in c#,whats the code for that,because parameters.Add will not work in this case.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 18:06:18

Please confirm which is which ?

Are you passing the variable from your SP to your .net page or the reverse.

Also it will be nice to see some of your code.

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 18:15:12

no I want to pass the captured UserID n username from the session to my stored procedure.

well I am performing a search function.In this search I have to implement user security.If the user is guest he will be able to get search result only from public docs,and if it is logged in then will get result from both public and docs created by him.

This is my code.

USE [charlotte]
GO
/****** Object: StoredProcedure [dbo].[sp_SystemSearch] Script Date: 09/01/2008 15:19:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SystemSearch]

@text as varchar(300),
@userName as varchar(64)
AS

BEGIN
SET NOCOUNT ON;

declare @srh as nvarchar(50)
SET @srh = 'FORMSOF(INFLECTIONAL,'+@text+')'

select * from (

SELECT s.SystemElementID,m.DocumentID,DocumentTitleText,SETitleText as "Title",SESummaryText as "SearchElement"
FROM SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
where Freetext(SESummaryText,@srh) AND s.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

Union all
Select t.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",ElementText as "SearchElement"
FROM TextElement t,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where t.SystemElementID=s.SystemElementID AND Freetext(ElementText,@srh) AND t.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select v.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",VideoMimeType as "SearchElement"
FROM VideoElement v,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where v.SystemElementID=s.SystemElementID AND Freetext(VideoMimeType,@srh) AND v.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select tm.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",MetatagText as "SearchElement"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

UNION ALL

Select b.SystemElementID,m.DocumentID,DocumentTitleText, SETitleText as "Title",BookmarkText as "SearchElement"
FROM Bookmark b,SystemElement s,TOCMap tp,TableOfContents tc,MasterDocument m
Where b.SystemElementID=s.SystemElementID AND Freetext(BookmarkText,@srh) AND b.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsCurrentBit=1

)a inner join

(

select MasterDocument.DocumentID
From MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
where UserName=@userName

Union all

select MasterDocument.DocumentID
From MasterDocument
INNER JOIN UserGroupRole ON MasterDocument.DocumentID = UserGroupRole.DocumentID
INNER JOIN UserGroup ON UserGroupRole.GroupID = UserGroup.GroupID
INNER JOIN UserGroupMap ON UserGroup.GroupID = UserGroupMap.UserGroupID
INNER JOIN [User] ON UserGroupMap.UserID = [User].UserID
where UserName='Guest'
) b ON a.DocumentID = b.DocumentID

END

thank you

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 18:19:51

First thing, I would say is that

Your error above is from you .net page. Which is not posted. So still keeps me/us in the dark about that.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 18:30:32

Here is a simple class that will call your stored proc and pass the value of your c# variable to the stored procedure

1. replace your connection string in the web.config file
2. i have taken the liberty to put your variables in the class ie text and username. Which should be set at runtime in your code behind.

3. create a class called calldb and paste this code inside.


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public class calldb
{
	public calldb(string text, string username)
	{
       
               SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Your_Connectionstring"].ConnectionString.ToString());


        SqlCommand command = new SqlCommand("Your_Connectionstring", con);
        command.CommandType = CommandType.StoredProcedure;
        command.CommandTimeout = 30;

        con.Open();

        command.Parameters.Add(new SqlParameter("@text", SqlDbType.VarChar,300,"text"));
        command.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 64, "username"));
        command.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        command.Parameters[0].Value = text;
        command.Parameters[1].Value = username;
        command.Parameters[2].Value = 0;

        int i = command.ExecuteNonQuery();

        string retunvalue = command.Parameters["@Return"].Value.ToString();

        
        con.Close();

       
        
	}
}

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 18:32:21

okay this is my compilation error

"The name 'Session' does not exist in the current context"

and it points to ..Session("@userName") = Membership.GetUser;

and here is my C# code.
protected void btnSearch_Click(object sender, EventArgs e)
{
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@text", txtSearch.Text);
Session("@userName") = Membership.GetUser;
dboperations db = new dboperations();
DataSet ds = new DataSet();

ds = db.GetDataset("sp_SystemSearch", parameters);
dgItems.DataSource = ds;
dgItems.DataBind();
}

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 18:38:32

As i said

Session["userName"] == Membership.GetUser;

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 18:51:58

Try

{
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@text", txtSearch.Text);
Session["userName"] == Membership.GetUser;
dboperations db = new dboperations();
DataSet ds = new DataSet();

ds = db.GetDataset("sp_SystemSearch", parameters);
dgItems.DataSource = ds;
dgItems.DataBind();
}

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 19:06:28

I did what you said,but still giving error.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 19:10:05

You have to be specific.

What error ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources