MS SQL Server :: Compilation Error On Store Procedure

Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()

View Replies !

Function Returning Error During Compilation.....
Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN

insert into @temp_qcard values(@t1_all)

declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)

declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.

View Replies !

Store Procedure Error
I am trying to insert a record using stored procedure thr' ASP.NET page. I am getting following error. How to find out what exactly is the problem
Line 4: Incorrect syntax near 'l'. The label 'PD' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'BA' has already been declared. Label names must be unique within a query batch or stored procedure. Unclosed quotation mark before the character string ''.

View Replies !

Error In Store Procedure
HiI've the following SP:-----------CREATE PROCEDURE spServicios@numero int,@maxdif int,@resultado int OUTPUTASBEGINSET ROWCOUNT 1UPDATE control SET registro = getdate()WHERE (fecha >= getdate()AND fecha < dateadd(mi, @maxdif, getdate())AND clientes_codigo IN (SELECTclientes_codigo FROM telefonos WHERE numero = @numero)SELECT @resultado = @@rowcountSET ROWCOUNT 0ENDSELECT @resultado------------But when I try to compile I get the message: Incorrect syntax near thekeyword 'SELECT'Any idea ?Thanks in advanceJ

View Replies !

Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me?
------------------

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader'

Source Error:

Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
Line 17: myConn.Open();
Line 18: SqlDataReader myReader = myConn.ExecuteReader();
Line 19: do
Line 20: {

Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18

void Login(string username, string password)
{
SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels");
SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
myConn.Open();
SqlDataReader myReader = myConn.ExecuteReader();
do
{
while (reader.Read())
{
if (username == myReader.GetString(1) && password == myReader.GetString(2))
{
messages.Text = "Your login was successful!";
}
else
{
messages.Text = " Your login was unsuccessful!";
}
}
}
while (reader.NextResult());
myReader.Close();
myConn.Close();
}
void Submit_Click(Object sender, EventArgs e)
{
Login(username.Text, password.Text);
}

Edit by moderator - NetProfit: Added < code>< /code> tags.

View Replies !

Store Procedure - How To Recognize The Error?
I had copied a sample stored procedure from the website and built a sql file in Visual Studio. There, I send it over to the database.

Since I'm using Sql 2005 - I have trouble finding out where the error log is. So, if there is an error of some sort with the stored procedure then where does the error message go? (While stored procedure get executed behind the scence).

Thanks...

View Replies !

Error Message Of Store Procedure
create procedure Pr_addsupportor(
@supportornumber varchar(10),
@company varchar(100),
@title char(10),
@firstname char(30),
@surname char(30),
@addressline1 varchar(1000),
@addressline2 varchar(1000),
@addressline3 varchar(1000),
@postcode varchar(20),
@town char(100),
@county char(100),
@country varchar(100),
@phonenumber varchar(15),
@faxnumber varchar(15),
@email varchar(100),
@paymenttitle varchar(100),
@barcode varchar(2000),
@collector varchar(3),
@status varchar(10),
@registerdate datetime)
INSERT into
supportor(
[supportor number],
company,
title,
[first name],
surname,
[address line1],
[address line2],
[addess line3],
[post code],
town,
county,
country,
[phone number],
[fax number],
[e-mail],
[payment title],
barcode,
collector,
status,
[register date])
values
(
@supportornumber,
@company,
@title,
@firstname,
@surname,
@addressline1,
@addressline2,
@addressline3,
@postcode,
@town,
@county,
@country,
@phonenumber,
@faxnumber,
@email,
@paymenttitle,
@barcode,
@collector,
@status,
@registerdate);

Server: Msg 156, Level 15, State 1, Procedure Pr_addsupportor, Line 22
Incorrect syntax near the keyword 'INSERT'.
plz help me to find the error

View Replies !

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()

View Replies !

Call Store Procedure From Another Store Procedure
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:

I want to create a table and to insert the result of the store procedure to it.

This is the code: Pay attention to the underlined sentence!

ALTER PROCEDURE [dbo].[test]

AS

BEGIN

SET NOCOUNT ON;

DROP TABLE tbl1

CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )

INSERT INTO tbl1 (first_name,last_name)

VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')

END

PLEASE HELP!!!! and God will repay you in kind!

Thanks!

View Replies !

Putting Many Bcp Codes In A Store Procedure And Using Scheduler To Run The Procedure
hi, Can anyone advice if what I am doing is right,
I have bcp code to import data from a text file into a 9 tables. create the following store procedure which will call other procedures to run

create p_activate
AS

execute p_truncate_tbls -- this will truncate all 9 tables
execute p_drop_all_indexes -- this procedure drop all indexes from 9 tables
execute p_bcp_to_all_tbls -- this procedure will import data from txt files
execute p_create_all_indexes -- this procedure will create index in 9 tables
Go

My question is that is this a good way to do in order to use bcp, as you see, I am getting rid of the old data in the tables,then dropping the indexs, then migrating data from text files into the sql tables, finally, i am building the indexes again....The amout of data are 9 GB....I have not tried this process yet, I 'd like to have feed back from wiz experience sql people to advice me if what I am doing is rigth, If there is anyone who has a better method, please do not hesitate to drop me an email, Iwould really appreciate your input...
regards

Ali

View Replies !

Error &&"Msg 6522, Level 16, State 1&&" Receives When Call The Assembly From Store Procedure To Create A Text File And To Write Text
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below

I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class

2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:

CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]

4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'

5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)

View Replies !

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View Replies !

Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny

View Replies !

Compilation / Re-build Issue

Hi,
We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we do modification in the code it take longer time to build approax it takes around 2 min to display the default page (login page).

Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster.

Regards
K.Karthik Doss

View Replies !

SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.

In oracle, system will let you know whether the the procedure is successfully complied or not?

Thanks/

View Replies !

SQL Compilation And Execution Plan
Hi all,

I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2).

Is SQL server working the way expected or am I missing something?

View Replies !

Compilation Of Stored Procs
Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

View Replies !

How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?

Hi!

   I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit.

   I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected.

   There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link.

   I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example

    =First(Fields!StoredProcedure.Value, "ds_ReportInfo")

That gives an error

   I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried:

Exec (@StoredProcedure)

   In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query.

   Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown.

The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that.

   I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure.

   Any suggestions? Is anyone else trying to do this?

Thanks
Karen

View Replies !

How To Run SQL Store Procedure Using Asp.net
I have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax

View Replies !

Store Procedure?
Hi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir

View Replies !

Store Procedure
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks

View Replies !

Store Procedure Help....
Ok, I am creating a web app that will help a company to help with inventory. The employee's have certain equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee; to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only those employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem I am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when I click on the select it redirect and only shows the first record not the one I selected, and when I change it, a employee that doesn't have equipment will not show up totally. I am confused why this isn't working.... I am very new to this.... so i am sure its something simple...

CREATE PROCEDURE dbo.inventoryinfo @EmployeeID int, @SerialNo varchar(50)
AS
IF(@EmployeeID IS NULL)BEGIN(SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID,E.EmployeeID, E.cwopa_id, eq.IPAddress,eq.Location, eq.DataLine3, eq.DataLine2, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo,eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.CarrierFROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (eq.SerialNo=@SerialNo))END
Else IF( @SerialNo is NULL)BEGIN
(SELECTE.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2,eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber,eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (E.EmployeeID = @EmployeeID))
end
ELSE IF (@EmployeeID is not Null) and (@SerialNo is not Null)BEGIN(SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID,E.EmployeeID, E.cwopa_id, eq.IPAddress,eq.Location, eq.DataLine3, eq.DataLine2, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo,eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.CarrierFROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeIDLEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeIDWHERE (E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo))ENDGO

View Replies !

Store Procedure Maybe????
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me.
Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.

SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo
EmployeeID and SerialNo are primary keys.
I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work
CREATE PROCEDURE dbo.inventoryinfo AS
DECLARE @EmployeeID int DECLARE @SerialNo varchar(50)
IF( @SerialNo is NULL)
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID
ELSE
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier
FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO
Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie

View Replies !

SQL Store Procedure In Asp.net
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure

View Replies !

Store Procedure Help
I am trying to loop through a string of ID's and check if theproductname for the ID is 'Chai' if so change it to 'Tea'. This is inthe northwind db - products table. call it exec sp_UpdateProduct'1,2,3'It does not work, any ideas - syntax, etc....CREATE PROCEDURE dbo.sp_UpdateProduct@IDs varchar(200)ASBEGINdeclare @sStr1 varchar(200)declare @iStrLen1 intdeclare @sParseElm1 char(2)declare @sStatus varchar(200)declare @count intset @sStr1= @IDsset @iStrLen1= len(@sStr1)SET @count = 0while charindex(',',@sStr1) <> 0beginselect @sParseElm1 = substring(@sStr1,1,CHARINDEX(',',@sStr1 ) - 1)set @sStatus = 'select productname from products where productid='+@sParseElm1if @sStatus = 'CHAI'update products set productname = 'TEA'elseupdate products set productname = 'CHAI'set @sStr1 = substring(@sStr1, CHARINDEX(@sStr1,',') + 1, @iStrLen1)endendGO

View Replies !

Help With A Store Procedure
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?

Thanks

Lystra

View Replies !

Get ID From Store Procedure
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:

CREATE PROCEDURE AddEmployee
(@efname nvarchar(20),
@elname nvarchar(20)
)
AS

insert into tblEmployeeName
(EmployeeFName, EmployeeLName)
values (@efname,@elname)
GO

Thanks.

View Replies !

STORE PROCEDURE And DTS
I created a batch file to run a DTS saved as a STRUCTURED STORE FILE. Works fine on the command promt.

Can I use a SPROC to call something like a shell command to execute that batch file? If yes, how is this be possible?

Thanks

View Replies !

Store Procedure
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure

PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);

IF Contr_tot_all = 0 THEN

Select Contr_type into sContractType from ae_contract where db_contract = db_id;

IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;

ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;

View Replies !

Store Procedure
I have file name book.sql. In this script there are 30 procedures with prefix ssp.
When I run with iSQL at blank database, there are store procedure not work name: "ssp_populatechilrenofaccount" with paramter @nodeaccount.
After compile using Enterprise Manager this procedure can work.
Why?

Note:
I use SQL Server 6.5 using SP4

View Replies !

Store Procedure
Hi, All,
I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month

Month salefromagent directsale total
Jan 1100 2300 3400
Feb 800 500 1300
..........
Dec
---------------------------------
I know we can handle this in the program use two queries.
But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue.
Betty

View Replies !

Store Procedure Help
Hi all,
I need to write a sales store procedure.
The sales summary is basically group by agentCode.
But the problem is some agents have subagents.
i.e., in the sales table.
one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales.
what should I do in the store procedure.

Thanks
Betty.

View Replies !

Store Procedure
I want to know when does SQL flushes Store Procedure from the cache.

Thank You,
Piyush Patel

View Replies !

Store Procedure

Hi,

I follow the example for store procedure from this link to deal with large amount of data and it work great.
http://www.4guysfromrolla.com/webtech/042606-1.shtml

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int

-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

my problem is how do i do the Sort for this.

Any help would be appreciated.

Thanks

Ddee

View Replies !

Aid With Store Procedure
Hello, I have a problem, I want to execute in a single transaction three insert in store procedure, as it could implement. Thanks for its attention

View Replies !

Is It Possible To Use Two Store Procedure Within One Store Procedure ??
Dear Frnds,

can anyone tell me is it possibel to use store procedure within the stored procedure ?? is it possible ??? if yes ?? then give me simple example

awaiting for positive reply

Thanks

Hiren

View Replies !

Store Procedure
i am running this procedure, but i have problems with the result. the main idea is to get the closest point to one sample.

even though i have a restriction with the distance, the result that i got is strange becuase my restrcitionis 20 feet and i have result from 50 to 350 feet.

This is the code

ALTER PROCEDURE [dbo].[closestDH]

-- radius in feet

@radius float

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT SAMPLEID,(SELECT HOLEID FROM (

SELECT HOLEID, (sqrt(square(Shovel_A.EAST - EAST ) + square(SHOVEL_A.NORTH - NORTH) )) AS distance

FROM (SELECT HOLEID, EAST, NORTH FROM dbo.Shovel_Hole WHERE (dbo.Shovel_Hole.shotname = dbo.Shovel_A.polygono)) as samepattern

) AS matched

WHERE (distance <= (select min(distancecalc) from (

SELECT (sqrt(square(Shovel_A.EAST - EAST ) + square(SHOVEL_A.NORTH - NORTH))) AS distancecalc

FROM (SELECT HOLEID, EAST, NORTH, RL FROM dbo.Shovel_Hole WHERE (dbo.Shovel_Hole.shotname = dbo.Shovel_A.polygono)) as samepattern

) as ***)) AND (distance <= @radius)

) as DH, polygono

FROM dbo.SHOVEL_A

ORDER BY DH, polygono

END

View Replies !

Store Procedure
hello sir
i want to write store procedure.
I write following code--

drop Proc Analyst

CREATE PROC Analyst
@UserID varchar(20),
@StockName varchar(50)
AS
INSERTtAnalystAssignHistory
(
AnalystAssignHistoryCode,
SecurityCode,
ISIN,
StockName,
UserID,
IsDummyEnabled,
AnalystAssignDate,
StatusCode,
CreatedBy,
CreateDate,
UpdatedBy,
UpdateDate
)
SELECTCOALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1,
SecurityCode,
ISIN,
StockName,
'ShahVis',
IsDummyEnabled,
AnalystAssignDate,
'A',
'Gayatri',
GETDATE(),
NULL,
NULL
FROMtAnalystAssignHistory
WHEREUserId = @UserID
and StockName = @StockName
GO

EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.'

In this procedure i want to runtime insert ShahVis i.e REassignuserid. it will always change so i want runtime insert this field.
and created by this is store in globaly i want to retrive this runtime.
Please help me.

View Replies !

Store Procedure
iam using store procedure to return certain no. of columns frm table.the query is
SELECT empuser_id,FullName,CAddress,PAddress,PhResidence,Mobile FROM employee_master WHERE empuser_id IN (" & txtID.Text & ")

i return it to datagrid in my .aspx(using VB.net) page.but i am getting blank datagrid. when i write the above query in my codebehind it runs normally and datagrid is populated with records.
i tried this in query analyzer as well it runs normally returing rows.
only in store procedure it gives me blank record.
any suggestions what is wrong ?

View Replies !

Store Procedure
hello
iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank.
if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel.
the stroe procedure is

CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100))
AS
SELECT * FROM employee_master WHERE empuser_id IN (@ID)

and this is the statement that i use in my .aspxpage to call store procedure

Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn)
cmd.SelectCommand.CommandType = CommandType.StoredProcedure
cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100))
cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")

any suggestions wht may be wrong

View Replies !

Store Procedure
hello
iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure
SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "

TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page.
depending on querystring the record is selected.

View Replies !

Store Procedure
1. can a stored procedure call itself? if else how ?>
2. how to run a job for a stored procedure to exucte every day.

View Replies !

C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike

View Replies !

SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run?

What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this?

Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job.

Thanks in advance for any help you can give me.

Wayne E. Pfeffer
Sr. Systems Analyst
Hutchinson Technolgy Inc.

View Replies !

Delaying Compilation Real Time
Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?

Thanks

View Replies !

What Value To Pass To Store Procedure
Hello,
I have two controls on a page. One is for cost and other for date.
Both the fields are not mandatory.
User can leave them blank and submit the page.
The problem is when page is submitted values are passed to a stored procedure but if user hasn't entered anything in these fields
then what should i pass to the store procedure.
data type in store procedure are
money for cost and datetime for date
Vinod Suri

View Replies !

SQL Server - Store Procedure

Greetings mate,

I'm new to sql server stored procedures
- I have 2 tables tasksTbl and staff_TasksTbl
- tasksTbl primary key is an identity column
- When inserting a row into tasksTbl I want to get the ID for the new inserted row using SCOPE_IDENTITY() and use it into a new insert command that will add a row into staff_TasksTbl

I need to know whats the problem in this procedure

CREATE PROCEDURE insertTask
(@TaskName varchar(20),
@TaskDescription varchar(20),@Rating int,
@Status varchar(20),@StartAt varchar(20),
@EntAt varchar(20),@EventID varchar(20), @StaffID varchar(100)
)
AS declare @TaskID int
SET NOCOUNT ON
insert into TasksTbl(TaskName, TaskDescription, Rating, Status, StartAt, EntAt, EventID)values(@TaskName, @TaskDescription, @Rating, @Status, @StartAt, @EntAt, @EventID)
select @TaskID = SCOPE_IDENTITY()

insert into staff_TasksTbl(TaskID,StaffID)values (@TaskID, @StaffID)
GO

Thanks in advance.

View Replies !

Problem With Store Procedure
i have error to login in..
my login coding:Protected Sub Login_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Login.Click
Select Case Users.UserLogin(username.Text, password.Text)
Case 0 ' Success For Admin!FormsAuthentication.SetAuthCookie(username.Text, True)
Response.Redirect("MainPage.aspx")
Case 1 ' Success For Maintainer!FormsAuthentication.SetAuthCookie(username.Text, True)
Response.Redirect("MainPage.aspx")
Case 2 ' Success For Customer!FormsAuthentication.SetAuthCookie(username.Text, True)
Response.Redirect("Default.aspx")
Case 3 'Invalid Password
username.Visible = False
password.Visible = True
Msglbl.Text = "Invalid password!"
Case 4 ' Invalid Username
username.Visible = True
password.Visible = True
Msglbl.Text = "Invalid username!"
End Select
End Sub
and
Public Class Users
Public Shared Function UserLogin(ByVal username As String, ByVal password As String) As IntegerDim connection As String = ConfigurationManager.ConnectionStrings("Maintenance").ConnectionStringDim command As SqlDataSource = New SqlDataSource(connection, "Maintenance_Users_Login")
command.SelectCommandType = CommandType.StoredProcedure
command.SelectParameters.Add(New Parameter("@username", username))command.SelectParameters.Add(New Parameter("@password", password))
Return UserLogin(username, password)
End Function
End Class
Finally i got error like this..i don understand what error is about..
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
any suggestion?.Thanks

View Replies !

My Store Procedure Have A Problem
Public Shared Function RemoveUserFromProject(ByVal projectId As Integer, ByVal userName As String) As Boolean Dim connection As String = ConfigurationManager.ConnectionStrings("Maintenance").ConnectionString Dim cmdRemove As SqlDataSource = New SqlDataSource(connection, "Maintenance_Project_RemoveUserFromProject") cmdRemove.SelectCommandType = CommandType.StoredProcedure If String.IsNullOrEmpty(userName) Then Throw New ArgumentOutOfRangeException("userName") End If If projectId <= 0 Then Throw New ArgumentOutOfRangeException("projectId") End If cmdRemove.SelectParameters(New Parameter("@ResultValue", ParameterDirection.ReturnValue) = Nothing) cmdRemove.SelectParameters.Add(New Parameter("@ProjectID", projectId)) cmdRemove.SelectParameters.Add(New Parameter("@UserID", userName)) Dim resultValue As Integer = CInt(ParameterDirection.ReturnValue) If resultValue = 0 Then Return True Else Return False End If Return RemoveUserFromProject(projectId, userName) End Function coding that i underline got error like "property access must assign to the property or use its value "i don no how to fix it..any idea..? and one thing, is that any dif btween this Dim connection As String = ConfigurationManager.ConnectionStrings("Maintenance").ConnectionString Dim cmdRemove As SqlDataSource = New SqlDataSource(connection, "Maintenance_Project_RemoveUserFromProject")and Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Maintenance").ConnectionString) Dim cmdRemove As New SqlCommand("Maintenance_Project_RemoveUserFromProject", connection) which one better..am using mic visual studio 2005Thank

View Replies !