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
 how to handle sqlserver print message in asp.net

Author  Topic 

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 11:16:21
I'm using a Stored procedure to check if a username already exists involving multiple tables.I've declared this temp variable to get the return value
@proceed.How do i call the variable in my code behind and how do i handle the print message in my code behind file.
quote:

ALTER PROCEDURE CheckRecordExists
(
@Name nvarchar(256),
@File nvarchar(256)


)
AS
BEGIN
declare @Path nvarchar(256),

@Proceed bit;

set @Path='http://localhost:56789/Uploads/'+@File





SELECT @Proceed = CASE WHEN EXISTS (select 1 from Items,Products where Items.Path = @path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 from Items_2,Products where Items_2.Path = @Path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 FROM Items_3,Products where Items_3.Path = @Path and Products.Name = @Name) THEN 0
ELSE 1 END

IF @Proceed = 1
BEGIN
print 'Not exists'
end
else
print 'exists'

END

quote:

protected void TextBox1_TextChanged(object sender, EventArgs e)
{
string file = Session["file"].ToString();


if (!string.IsNullOrEmpty(TextBox1.Text))
{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("CheckExists", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = filename;


SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";

lblStatus.Text = "<html><size=12><i>Sorry... Name already exists";

}
else
{
checkusername.Visible = true;
imgstatus.ImageUrl = "green_check_mark_button_image_500_clr.jpg";
lblStatus.Text = "<html><size=12><i>Name Accepted";
}

}
}

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-16 : 11:25:20
Don't use print to send data back to the application, use an output variable. They're described here:

http://msdn.microsoft.com/en-us/library/ms187926.aspx

Graz wrote a great article on returning data to an app here:

http://www.sqlteam.com/article/stored-procedures-returning-data
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 11:30:03
you should declare it as OUTPUT parameter in the stored procedure and return value through it

ALTER PROCEDURE CheckRecordExists
(
@Name nvarchar(256),
@File nvarchar(256),
@Proceed bit OUTPUT

)
AS
BEGIN
declare @Path nvarchar(256),


set @Path='http://localhost:56789/Uploads/'+@File





SELECT @Proceed = CASE WHEN EXISTS (select 1 from Items,Products where Items.Path = @path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 from Items_2,Products where Items_2.Path = @Path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 FROM Items_3,Products where Items_3.Path = @Path and Products.Name = @Name) THEN 0 
ELSE 1 END

END


then in application include logic to capture this return value setting direction to output and add the messages accordingly



protected void TextBox1_TextChanged(object sender, EventArgs e)
{
string file = Session["file"].ToString();


if (!string.IsNullOrEmpty(TextBox1.Text))
{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("CheckExists", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = filename;

SqlParameter parameter = new SqlParameter();
parameter =cmd.Parameters.Add("@Proceed", SqlDbType.bool)
parameter.Direction = ParameterDirection.Output

....

SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";

lblStatus.Text = "<html><size=12><i>Sorry... Name already exists";

}
else
{
checkusername.Visible = true;
imgstatus.ImageUrl = "green_check_mark_button_image_500_clr.jpg";
lblStatus.Text = "<html><size=12><i>Name Accepted";
}

}
}


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 14:05:03

Should i Do something like below code? b'cause my if statement where parameter == 1 is throwing error.How do I handle my sql if @proceed =1 in my code behind.Plz help

SqlParameter parameter = new SqlParameter();
parameter = cmd.Parameters.Add("@Proceed", SqlDbType.Bit);
parameter.Direction = ParameterDirection.Output;


SqlDataReader dr = cmd.ExecuteReader();
if ((dr.HasRows) && (parameter = 1))
{
checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";
}
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-16 : 14:10:53
What is the exact error you're getting? I see a typo in your code:
SqlParameter parameter = new SqlParameter();
parameter = cmd.Parameters.Add("@Proceed", SqlDbType.Bit);
parameter.Direction = ParameterDirection.Output;


SqlDataReader dr = cmd.ExecuteReader();
if ((dr.HasRows) && (parameter = 1))
{
checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";
}
Changing the red part to "==" as you mentioned earlier should fix it.
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 14:13:05
I've error msg on parameter == 1 saying cannot implicitly convert type 'int' to 'system.data.sqlclient.sqlparameter'.

I checked with both = and == no luck
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-16 : 14:18:20
OK, this should work:
if ((dr.HasRows) && (parameter.Value == 1))
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 14:22:23
quote:
Originally posted by robvolk

OK, this should work:
if ((dr.HasRows) && (parameter.Value == 1))



I'm sorry it's again throwing error saying .Value is an object
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-16 : 14:33:09
Then either cast it as an int or boolean, or use ToString() and compare it to "1" as a string.
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 14:39:03
quote:
Originally posted by robvolk

Then either cast it as an int or boolean, or use ToString() and compare it to "1" as a string.


I used below code,still don't show any msg

if ((dr.HasRows) && (parameter.ToString() == "1"))

Is ther any way to change my stored procedure?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 14:42:45
did you debug and see if you're getting output value correctly from procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 14:54:34
yes i did it,I got it correctly when i executed my SP.I even changed my code as below


protected void TextBox1_TextChanged(object sender, EventArgs e)
{
string filename = Session["filename"].ToString();


if (!string.IsNullOrEmpty(TextBox1.Text))
{

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["perlsConnectionString"].ConnectionString))
{

using (SqlCommand cmd = new SqlCommand("CheckRecordExists", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.Add("@ResourceName", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@filename", SqlDbType.NVarChar).Value = filename;
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
bool proceed = dr.GetBoolean(dr.GetOrdinal("@Proceed"));
if (proceed.ToString() == "1")
{
checkusername.Visible = true;
imgstatus.ImageUrl = "green_check_mark_button_image_500_clr.jpg";
lblStatus.Text = "<html><size=12><i>Name Accepted";

}
else
{

checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";

// "<html><size=12>This is RadLabel <br><b><font=Arial>Arial, Bold</b><br><i><color= Red><font=Times New Roman>Times, Italic <u>Underline</u><br><size=9>Size = 9<br><color= 0, 0, 255>Sample Text";
lblStatus.Text = "<html><size=12><i>Sorry...Resource Name relative to the attached File/Link already exists";


}
}

dr.Close();
}
}
}
}
}
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 15:18:15
And now is it working fine?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 15:39:19
No,I'm still stuck
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 15:47:56
now whats the issue you're facing? is it still last error?
Between I cant spot how you're getting value of @Proceed in your posted code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 15:54:51
Below is my SP code where I get the proceed value based on condition

SELECT @Proceed = CASE WHEN EXISTS (select 1 from Items,Products where Items.Path = @path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 from Items_2,Products where Items_2.Path = @Path and Products.Name = @Name) THEN 0

WHEN EXISTS (SELECT 1 FROM Items_3,Products where Items_3.Path = @Path and Products.Name = @Name) THEN 0
ELSE 1 END

IF @Proceed = 1
BEGIN
print 'Not exists'
end
else
print 'exists'

END
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 15:57:59
Below is my SP code where I get the proceed value based on condition.I'm not sure how do i call this @proceed in my Code behind.If you can check my last post I've my code where i set new sql outputparameter as proceed and check it within while loop.

SELECT @Proceed = CASE WHEN EXISTS (select 1 from Items,Products where Items.Path = @path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 from Items_2,Products where Items_2.Path = @Path and Products.Name = @Name) THEN 0

WHEN EXISTS (SELECT 1 FROM Items_3,Products where Items_3.Path = @Path and Products.Name = @Name) THEN 0
ELSE 1 END

IF @Proceed = 1
BEGIN
print 'Not exists'
end
else
print 'exists'

END
[/quote]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 15:58:49
nope..this is not what i suggested. return only value of @Proceed from Sp.

In application code, receive value using a parameter,do the above check inside application and print messages inside it, not in sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bplvid
Starting Member

45 Posts
Posted - 2012-04-16 : 16:45:25
I've made all changes no errors but still dosen't show the message.My code is below

Stored procedure

quote:

ALTER PROCEDURE CheckRecordExists
(
@Name nvarchar(256),
@File nvarchar(256)


)
AS
BEGIN
declare @Path nvarchar(256),

@Proceed bit;

set @Path='http://localhost:1234/'+@File

SELECT @Proceed = CASE WHEN EXISTS (select 1 from Items,Products where Items.Path = @path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 from Items_2,Products where Items_2.Path = @Path and Products.Name = @Name) THEN 0
WHEN EXISTS (SELECT 1 FROM Items_3,Products where Items_3.Path = @Path and Products.Name = @Name) THEN 0
ELSE 1 END
end


My code behind

protected void TextBox1_TextChanged(object sender, EventArgs e)
{
string filename = Session["filename"].ToString();


if (!string.IsNullOrEmpty(TextBox1.Text))
{

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{

using (SqlCommand cmd = new SqlCommand("CheckRecordExists", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@file", SqlDbType.NVarChar).Value = filename;
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
bool proceed = dr.GetBoolean(dr.GetOrdinal("@Proceed"));
if (proceed.ToString() == "1")
{
checkusername.Visible = true;
imgstatus.ImageUrl = "green_check_mark_button_image_500_clr.jpg";
lblStatus.Text = "<html><size=12><i>Name Accepted";

}
else
{

checkusername.Visible = true;
imgstatus.ImageUrl = "NotAvailable.jpg";


lblStatus.Text = "<html><size=12><i>Sorry...Resource Name relative to the attached File/Link already exists";


}
}

dr.Close();
}
}
}
}
}
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-04-16 : 16:48:45
where's the place you retrieve @Proceed value from procedure inside your application?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2012-04-16 : 16:51:59
quote:
I've made all changes no errors but still dosen't show the message
No. You have not set up the stored procedure or app code as visakh suggested (and posted earlier). Please look at his example. @Proceed is not configured as an output variable on either side.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site