error 15023 user already exists in current database

This problem occur when i want to restore database.

error 15023 user already exists in current database


sp_change_users_login 'report'




sp_change_users_login 'update_one','[db username]','[db username]'

SQL Parameters - Input, Output, ReturnValue + From store procedure [C#]

Today, i will show you how to write a stored procedure to accept an input, return an output and also return a return value. I used adventureworks database

Here the sql to create a stored procedure

-- ================================================
-- KLIKBLIG.BLOGSPOT.COM
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE klikblig_getProductName
-- Add the parameters for the stored procedure here
@ProductNumber nvarchar(25),
@Name nvarchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

set @Name = ''

begin try
select @Name = p.name
from production.product p
where p.productnumber like @ProductNumber

if(@Name = '')
return 0
else
return 1
end try

begin catch
return -1
end catch

END
GO

Then, on default.aspx; i create a button, textbox and also a label (all id are default). On the default.cs.aspx paste the following code

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


public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

conn.Open();
cmd.Connection = conn;
cmd.CommandText = "klikblig_getProductName";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

//@ProductNumber - INPUT
param.ParameterName = "@ProductNumber";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 25;
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);
cmd.Parameters["@ProductNumber"].Value = TextBox1.Text.Trim();
param = null;

//@Name - OUTPUT
param = new SqlParameter();
param.ParameterName = "@Name";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 50;
param.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param);
param = null;

//@ReturnValue - RETURNVALUE
param = new SqlParameter();
param.ParameterName = "@ReturnValue";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param);
param = null;

reader = cmd.ExecuteReader();

reader.Read();

//GET THE RESULT
String name = "";
int returnValue = 0;

name = (String)cmd.Parameters["@Name"].Value;
returnValue = (int)cmd.Parameters["@ReturnValue"].Value;

if (returnValue == 0)
{
Label1.Text = "No Results Found";
}
else if (returnValue == 1)
{
Label1.Text = name;
}
else if (returnValue < 0)
{
Label1.Text = "Error";
}


}
}

You should get a result in the label

Get Date and Time separately from SQL

Situation: You have a column in your table type DateTime. But you just want to view the date or time only. This is how it can be done.

SQL query

Select convert(varchar, [your column date], 3)
from [your table name]

will return result in format dd/mm/yy

Select select convert(varchar, [your column date], 8 )
from [your table name]

will return result in format hh:mm:ss

Here are list of format which you can refer:

Date Formats
Format No.	SQL Query	Output
1	select convert(varchar, [your column date], 1) from [your table name]	12/30/08
2	select convert(varchar, [your column date], 2) from [your table name]	08.12.30
3	select convert(varchar, [your column date], 3) from [your table name]	30/12/08
4	select convert(varchar, [your column date], 4) from [your table name]	30.12.08
5	select convert(varchar, [your column date], 5) from [your table name]	30-12-08
6	select convert(varchar, [your column date], 6) from [your table name]	30 Dec 08
7	select convert(varchar, [your column date], 7) from [your table name]	Dec 30, 08
10	select convert(varchar, [your column date], 10) from [your table name]	12-30-08
11	select convert(varchar, [your column date], 11) from [your table name]	08/12/30
101	select convert(varchar, [your column date], 101) from [your table name]	12/30/2008
102	select convert(varchar, [your column date], 102) from [your table name]	2008.12.30
103	select convert(varchar, [your column date], 103) from [your table name]	30/12/2008
104	select convert(varchar, [your column date], 104) from [your table name]	30.12.2008
105	select convert(varchar, [your column date], 105) from [your table name]	30-12-2008
106	select convert(varchar, [your column date], 106) from [your table name]	30 Dec 2008
107	select convert(varchar, [your column date], 107) from [your table name]	Dec 30, 2008
110	select convert(varchar, [your column date], 110) from [your table name]	12-30-2008
111	select convert(varchar, [your column date], 111) from [your table name]	2008/12/30

Time Formats
Format No.	SQL Query	Output
8 or 108	select convert(varchar, [your column date], 8 ) from [your table name]	00:40:50
9 or 109	select convert(varchar, [your column date], 9) from [your table name]	Dec 30 2006 12:40:50:840AM
14 or 114	select convert(varchar, [your column date], 14) from [your table name]	00:40:50:840