Hello...
This group has been really helpful on a couple of recent qwuestions I have posted - thanks to all!
Now, I have another question. I have a table set up to automatically generate a uniquie number when a record is added(INT NOT NULL IDENTITY).
Now, when this number is generated, I need it returned back to my perl script (thorough DBD::ODBC). Any ideas how to do that?
Thnaks....
I try to set a default value (getdate()) for a datetime culomn in sql mobile,
but got error when i insert record:
there was a syntax error in the date format. [expression = getdate()]
may be I can only set a exict date ?
Hi All :-)
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function.
Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT
AS
Insert INTO TM_TalentInvites
(
TalentID
)
Values
(
@TalentID
)
SELECT @@IDENTITY AS TalentInviteID
RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID)
{
// Initialize SPROCstring connectString = "Data Source=bla bla";
SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID);
conn.Open();int talentUnique = (int)cmd.ExecuteScalar();
conn.Close();return talentUnique;
}
Any help you can give me will be greatly appreciated thanks!
I have a page that inserts customers into the client database. After the data is inserted it redirects to the customer's policy page using the customer's ID set by @@Identity.
The SQL Command is:
ALTER PROCEDURE [dbo].[AddBasic]
(
@ln NVarchar(50),
@fn NVarchar(50),
@mAdd NVarchar(50),
@mCity NVarchar(50),
@mState NVarchar(50),
@mZip NVarchar(50),
@pAdd NVarchar(50),
@pCity NVarchar(50),
@pState NVarchar(50),
@pZip NVarchar(50),
@sAdd NVarchar(50),
@sCity NVarchar(50),
@sState NVarchar(50),
@sZip NVarchar(50),
@hPhone NVarchar(50),
@cPhone NVarchar(50),
@wPhone NVarchar(50),
@oPhone NVarchar(50),
@eMail NVarchar(50),
@DOB NVarchar(50),
@SSN NVarchar(50),
@liState NVarchar(50),
@liNum NVarchar(50),
@acctSource NVarchar(50),
@active NVarchar(50),
@County NVarchar(50)
)
AS
DECLARE @custNum int
INSERT basicInfo
(lastName, firstName, mailingAddress, mailingCity, mailingState, mailingZip, physicalAddress, physicalCity, physicalState, physicalZip,
seasonalAddress, seasonalCity, seasonalState, seasonalZip, homePhone, cellPhone, workPhone, otherPhone, email, DOB, SSN, liscenceState,
driverLiscense, acctSource, [status], county)
VALUES (@ln,@fn,@mAdd,@mCity,@mState,@mZip,@pAdd,@pCity,@pState,@pZip,@sAdd,@sCity,@sState,@sZip,@hPhone,@cPhone,@wPhone,@oPhone,@eMail,@DOB,@SSN,@liState,@liNum,@acctSource,
@active, @County)
SET @custNum = @@Identity
SELECT @custNum
The ASPX page has two parts, the SQL Data Source: <asp:SqlDataSource ID="sqlInsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="AddBasic"
SelectCommand="SELECT acctNumber FROM basicInfo WHERE (acctNumber = 5)" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:FormParameter FormField="lName" Name="ln" />
<asp:FormParameter FormField="fName" Name="fn" />
<asp:FormParameter FormField="mAdd" Name="mAdd" />
<asp:FormParameter FormField="mCity" Name="mCity" />
<asp:FormParameter FormField="mState" Name="mState" />
<asp:FormParameter FormField="mZip" Name="mZip" />
<asp:FormParameter FormField="pAdd" Name="pAdd" />
<asp:FormParameter FormField="pCity" Name="pCity" />
<asp:FormParameter FormField="pState" Name="pState" />
<asp:FormParameter FormField="pZip" Name="pZip" />
<asp:FormParameter FormField="sAdd" Name="sAdd" />
<asp:FormParameter FormField="sCity" Name="sCity" />
<asp:FormParameter FormField="sState" Name="sState" />
<asp:FormParameter FormField="sZip" Name="sZip" />
<asp:FormParameter FormField="hPhone" Name="hPhone" />
<asp:FormParameter FormField="cPhone" Name="cPhone" />
<asp:FormParameter FormField="wPhone" Name="wPhone" />
<asp:FormParameter FormField="oPhone" Name="oPhone" />
<asp:FormParameter FormField="eMail" Name="eMail" />
<asp:FormParameter FormField="DOB" Name="DOB" />
<asp:FormParameter FormField="SSN" Name="SSN" />
<asp:FormParameter FormField="dlState" Name="liState" />
<asp:FormParameter FormField="dlNum" Name="liNum" />
<asp:FormParameter FormField="aSource" Name="acctSource" />
<asp:FormParameter FormField="txtCounty" Name="County" />
<asp:Parameter Name="active" DefaultValue="Active" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
and the redirect:Public Sub Redirect()
Dim x As Integer
x = SqlDataSource1.Insert()
Response.Redirect("~/Main/Main.aspx?q=" & x)
End Sub
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
Anyone have any ideas?
CREATE PROCEDURE [iguser].[newticket]
(
@Category nvarchar(80),
@Description nvarchar(200),
@Detail nvarchar(3000),
@OS nvarchar(150),
@Browser nvarchar(250),
@Internet nvarchar(100),
@Method nvarchar(50),
@Contacttime nvarchar(50),
@Knowledge int,
@Importance int,
@Sendcopy bit,
@Updateme bit,
@ClientID int,
@ContactID int,
@TicketID integer OUTPUT
)
AS
INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@Category,
@Description,
@Detail,
@OS,
@Browser,
@Internet,
@Method,
@Contacttime,
@Knowledge,
@Importance,
@Sendcopy,
@Updateme
)
SELECT
@TicketID = @@Identity
INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@ContactID,
@TicketID
)
INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@ClientID,
@TicketID
)
Hello all,I have a sqldatasource that inserts data to the database but I want to get the ID from an Identity field after I am done inserting the data. How can I best do that? I'm not using a stored procedure to do this. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:onepduConnectionString %>" InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address], [city], [state], [zip], ,[phone],[credits]) VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits)> <InsertParameters> <asp:FormParameter Name="fname" FormField="FirstNameTextBox" Type="String" /> <asp:FormParameter Name="lname" FormField="LastNameTextBox" Type="String" /> <asp:FormParameter Name="address" FormField="AddressTextBox" Type="String" /> <asp:FormParameter FormField="cityTextBox" Name="city" Type="String" /> <asp:FormParameter FormField="stateTextBox" Name="state" Type="String" /> <asp:FormParameter FormField="zipTextBox" Name="zip" Type="String" /> <asp:FormParameter FormField="emailTextBox" Name="email" Type="string" /> <asp:FormParameter FormField="phoneTextBox" Name="phone" Type="string" /> <asp:Parameter Name="credits" DefaultValue=0 /> </InsertParameters> </asp:SqlDataSource>
View 2 Replies View RelatedHi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
Sub AddTrack()
Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
End Try
myCommand.Connection.Close()
End Sub
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
Anyone remember how they did this ?
I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked.
Here is my code that works for the insert....
conClasf.Open()
strSQLInsert = "Exec spInsNewCAR '" _
& calDateInitiatedPopup.SelectedDate _
& "', '" & calResponseDatePopup.SelectedDate _
& "', '" & elbPartName.Selectedtext _
& "', '" & elbPartName.Item(elbPartName.SelectedValue).Text2 _
& "', '" & calSupplyDatePopup.SelectedDate _
& "', '" & elbMRBApproval3.Selectedtext _
& "'"
cmdAd = New OleDbCommand(strSQLInsert, conClasf)
cmdAd.ExecuteNonQuery( )
conClasf.Close
Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value...
Thanks,
JOE
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
class file
public class Cotacoes
{
public int codigoCotacao;
}
public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes();
public CotacaoAtualiza() { }
public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
...
SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao);
...
myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader();
while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; }
myConnection.Close(); }
calling into code-behind file:
CotacaoAtualiza ca = new CotacaoAtualiza();
Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
Hi,
I have upgraded database from sql6.5 to sql 7.o
I have a table tblnetwork on which identity property
is defined on column networkid.
When I issue the following command new record is getting inserted into
tblnetwork and identity column is getting incremented properly.
But, I am not able to get the @@identity value
It is returned as NULL.
insert tblnetwork(formalname,networktype)
values ("name2",11897)
select @@identity
I need this value to insert into some other table.
Pls suggest me why @@identity is returning NULL
thanks,
MMS
Hi All,
I have a createStudent SProc. When this is called it calls a createContact SProc. This in turn calls a Create Address Stored procedure. Now when I create the address, an Identity column automatically creates a new Identity for me. I need to return this value back to the calling stored procedure. I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably? I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...
Thanks heaps
Chris
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
View 3 Replies View RelatedI am trying to get the identity of an inserted record using this SP:
<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
@AuctionID Int OUTPUT,
@StartDate char(255),
@StartTime char(255),
@Location char(255),
@Title char(255),
@Description char(255),
@Images char(255)
)
AS
INSERT INTO Auctions
(
StartDate,
StartTime,
Location,
Title,
Description,
Images
)
VALUES
(
@StartDate,
@StartTime,
@Location,
@Title,
@Description,
@Images
)
SELECT AuctionID AS ID
FROM Auctions
WHERE (AuctionID = @@IDENTITY)
</code>
Then, I am using this class for a file called dataaccess.cs to return the ID:
<code>
public string SaveImageName(string ImageName, string AuctionID)
{
SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;
SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
paramImagePath.Value = ImageName;
this.newSqlCommand.Parameters.Add(paramImagePath);
SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
paramAuctionID.Value = AuctionID;
this.newSqlCommand.Parameters.Add(paramAuctionID);
SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
paramImageID.Direction = ParameterDirection.Output;
this.newSqlCommand.Parameters.Add(paramImageID);
//Return SqlDataReader Struct
this.newSqlCommand.Connection.Open();
this.newSqlCommand.ExecuteNonQuery();
this.newSqlCommand.Connection.Close();
int returnID = (int)paramImageID.Value;
return returnID.ToString();
}
</code>
The above code returns null.
Can anyone tell me what I am doing wrong?
THanks, Justin.
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:
INSERT INTO table_name
(first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ;
SELECT SCOPE_IDENTITY() AS [Scope_Identity]
This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.
(The table I'm adding to does have a autonumbered key field)
Thanks in advance!
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = @@IDENTITY
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
any help greatly appreciated,
Cheers,
Craig
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedHi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View RelatedOn cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom // Getting the Correct Answer from the Database. int QuiziD = Convert.ToInt32(Session["QuizID"]); int QuestionID = Convert.ToInt32(Session["QuestionID"]); SqlConnection oConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\quiz.mdf;Integrated Security=True;User Instance=True"); SqlCommand cmd = new SqlCommand("usp_Question_Count", oConn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter QuizParam = new SqlParameter("@QuizId", SqlDbType.Int); QuizParam.Value = QuiziD; cmd.Parameters.Add(QuizParam); SqlParameter QuestionParam = new SqlParameter("@QuestionID", SqlDbType.Int); QuestionParam.Value = QuestionID; cmd.Parameters.Add(QuestionParam); SqlParameter countParam = new SqlParameter("@CorrectAnswer", SqlDbType.Int); countParam.Direction = ParameterDirection.Output; //cmd.Parameters.Add(workParam); cmd.Parameters.Add(countParam); oConn.Open(); cmd.ExecuteNonQuery(); // get the total number of products int iCorrectAnswer = Convert.ToInt32(cmd.Parameters["@CorrectAnswer"].Value); oConn.Close();Heres the stored ProcedureALTER PROCEDURE dbo.usp_Find_Correct_Answer @QuizID int, @QuestionID int, @CorrectAnswer int OUTPUT /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */AS /* SET NOCOUNT ON */ SELECT @CorrectAnswer=CorrectAnswer FROM Question WHERE QuizID=@QuizID AND QuestionOrder=@QuestionID
View 2 Replies View RelatedI have a web application that is data driven from a SQL 2005 database. The application needs to prompt the user to enter some information that will be logged into a SQL table.
It should always be the last row in the table that is being worked on at any one time. Over a period the user will need to enter various fields. Once the data is entered into a field they will not have access to amend it.
Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited.
As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it?
Regards
Clive
I'm hoping someone can help me w/this query. I'm gathering data from two tables and what I need to return is the following: An employee who is not in the EmployeeEval table yet at all and any Employee in the EmployeeEval table whose Score column is NULL. I'm So lost PLEASE HELP. Below is what I have. CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID ( @deptID nvarchar(20), @Period int)ASSELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName, ev.Score FROM Employee AS eLEFT JOIN EmployeeEval as ev ON e.EmployeeID = ev.EmployeeIDWHERE e.DeptID = @deptId OR (e.deptid = @deptID AND ev.Score = null AND ev.PeriodID = @Period)GO
View 3 Replies View Relatedhello,
I have a small problem. i'm adding records into the DB. the primary key
is the company name which is abviously unique. before saving the record
i check in the stored procedure if the company code is unique or not.
if unique then the record is added & an output parameter is set to
2 & should b returned to the data access layer. if not unique then
3 should be returned. but everytime it seems to be returning 2 whether
it is unique or not. can u plz help me? here is the code of the data
access layer:
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Value = ParameterDirection.ReturnValue;
//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters;
cmd.ExecuteNonQuery();
status = (int)cmd.Parameters["@Status"].Value;
here is the stored procedure:
CREATE PROCEDURE spOrganizationAdd(
@OrgCode varchar(10),
@OrgName varchar(50),
@AddressLine1 varchar(30),
@AddressLine2 varchar(30),
@City varchar(15),
@State varchar(15),
@Country varchar(15),
@PinCode varchar(7),
@Phone varchar(20),
@Fax varchar(20),
@Website varchar(30),
@Email varchar(50),
@CreatedBy int,
@LastModifiedBy int,
@Status INTEGER OUTPUT) AS
BEGIN TRAN
IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @OrgCode)
BEGIN
SET @Status = 3
END
ELSE
BEGIN
INSERT INTO tblOrganizationMaster VALUES(
@OrgCode,
@OrgName,
@AddressLine1 ,
@AddressLine2 ,
@City ,
@State,
@Country,
@PinCode,
@Phone,
@Fax ,
@Website,
@Email,
@CreatedBy ,
GETDATE(),
@LastModifiedBy ,
GETDATE())
SET @Status = 2
END
IF @@ERROR = 0 COMMIT TRAN
ELSE ROLLBACK TRAN
plz reply as soon as possible.
Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.
I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT COUNT(*) AS TodayCount FROM Counter WHERE DATEPART(YEAR, visitdate) = Year(GetDate()) AND DATEPART(MONTH, visitdate) = Month(GetDate()) AND DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?
View 6 Replies View RelatedOkay so here's a wierd one. I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString)
Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn)
With Cmd.Parameters
.Add(New MySqlParameter("?CurrentPage",thisPage))
End With
Conn.Open()
Exists = Cmd.ExecuteScalar()
End Using
End Using Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?