Sqlquery Or Storedprocedure Error
Jun 18, 2008
Hello everyone,
I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.
Topics Table Threads Table
TopicID ThreadID
ForumID TopicID
TopicName Subject
TopicDescription Replies
UserID
LastPostDate
now i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.
I am able to fetch topic table data and Total no of threads per topic through the following query.
SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topics
but i am not able to fetch anoter two detail with subquery as i am getting error like
only one expression can be specified in the select list
or
subquery returned more than one value.
can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..
Thanks in advance...
Regards,
Nil
View 8 Replies
ADVERTISEMENT
Sep 3, 2007
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks
View 2 Replies
View Related
Feb 4, 2003
I have a DTS package (AdIns) that inserts to an administrative table. The Administrative table utilizes the "with ignore_dup_key" option on the index. There are other admin jobs in the DTS that are based on the return code of a parent package.
The "3604:duplicate key ignored" is an expected result of the parent package, yet it sends an failure return code to the dependent (AdIns) package, causing erroneous entries to the final audit table.
How can I reset the return code from the parent package?
TIA!:mad:
View 1 Replies
View Related
Feb 21, 2008
create procedure userAuthenticate (@username nvarchar(50), @password nvarchar(50))AS DECLARE @ID IdentityCol DECLARE @actualPassword nvarchar(50)
Select @id, @actualPassword from User where @actualpassword = @password and username = @username
Return 1
The error says incorrect syntax near "user". unable to identify the problem.
View 3 Replies
View Related
Oct 23, 2007
TITLE: Microsoft SQL Server Management Studio
------------------------------
Script failed for StoredProcedure 'dbo.airsp_ValidateArea_China'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Syntax error in TextHeader of StoredProcedure 'airsp_ValidateArea_China'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Microsoft SQL Server Management Studio get this error when trying to modify script the above stored procedure.
SQL server Query Analyzer open the above procedure with no error. And the procedure runs fine.
Does anyone have a solution for this problem?
I have tried dropping and recreating the proc the error still persists.
View 7 Replies
View Related
Oct 18, 2007
What does Invalid object name "MyTable" mean?
I checked the table and column names. They are both correct.
Will the error mean something else, e.g. wrong data type, or no data?
TIA,Jeffrey
Dim strConn As String = ConfigurationManager.ConnectionStrings("MyConnectString").ConnectionStringDim oConn As New OleDbConnection(strConn)Dim oDBCommand As New OleDbCommand("MyStoredProceduret", oConn)oDBCommand.CommandType = CommandType.StoredProcedureoDBCommand.Connection.Open()Dim rtnValue As String = oDBCommand.ExecuteScalar() This is the error source file
This is the SPCREATE PROCEDURE MyStoredProcedure ASSELECT SettingsReqSchdTimeout FROM SettingsGO
View 9 Replies
View Related
Mar 7, 2008
Hi,Here is my SQL Query.SQL = "insert into mnetwork..tblContactCorr(CorrespondentID,Name,Phone,Fax,Email,UserName,rowguid,Status,ChgPassword,lastlogin,MailNotification,USerLevel,LocationID,msrepl_tran_version)"SQL = SQL & " values( '" & drpCorr.SelectedValue & "', '" & txtName.Text & "', '" & txtphone.Text & "', '" & txtFax.Text & "', '" & txtEmail.Text & "', '" & txtUserName.Text & "', '" & mGuid & "', '" & drpstatus.SelectedValue & "', '" & "Null" & "', '" & getdate & "', '" & drpmn.SelectedValue & " ', '" & drpuserlevel.SelectedValue & "', '" & drplocation.SelectedValue & "', '" & "Null" & "')"Dim dbcommand As New SqlCommand(SQL, ObjConn)ObjConn.Open()dbcommand.ExecuteNonQuery()------>here is the problemdbcommand = New SqlCommand("select @@identity", ObjConn)Dim NewUserID = dbcommand.ExecuteScalarAfter executing i am getting error like this:Message "Syntax error converting from a character string to uniqueidentifier." StringI don't know how to convert mguid into uniqueidentifier.here is my batabase fields:ContactID-->this is auto incrementCorrespondentID--intName-varcharPhone-varcharFax-varcharEMail-varcharUserName-varcharrowguid--uniqueiduntifierstatus-tiniintchgpassword-snallintlastlogin--datetimemailnotification--smallintlocationid--intmsrepl_tran_version-uniqueiduntifier -(this is null)let me know whats wrong in this?
View 8 Replies
View Related
Jun 22, 2007
Could you pls let me know that when we use the parameters how the sql server ıs able to keep all the datas on the strıng type?and also how ıt can be provıde that securıty on system(how ıt ıgnore to do sql ınjectıon...).
View 1 Replies
View Related
Dec 23, 2007
Hi All
im using sqlserver2005 and i have sqlquery to get some fileds
now i need to get out put in excel file when i run sqlquery
can anyone explain me how can i do it...this is my query
select ta_targetpath from tblattributes where ta_pagelevel = 2 and ta_description like '%Do you want to watch this configuration in action%'
and ta_targetpath is not null
when i execute this i need to get result set in excel file...
thnx in advance
Hary
View 1 Replies
View Related
Aug 14, 2004
Hi All,
I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read.
So can anyone help as I'm really stuck on this one. The code is as follows :
public void DisplayUserInfo()
{
ArrayList UserIdArrayList = IdentifyUserID();
foreach(string ShowUserIDString in UserIdArrayList)
{
try
{
SqlConnection SqlConn = new SqlConnection(DBConnString);
SqlConn.Open();
Console.WriteLine("Connected to DB");
SqlDataAdapter SqlAD = new SqlDataAdapter();
SqlAD.SelectCommand = new SqlCommand("Select * from UserSystemSpecs where UserName ='+ShowUserIDString.ToString()+'",SqlConn);
DataSet ds = new DataSet();
SqlAD.Fill(ds);
ds.WriteXml(".\ResultsXML.xml", XmlWriteMode.WriteSchema);
}
catch (Exception ex)
{
throw new Exception ("Error Connecting to DB. " + ex.Message);
}
//SqlConn.Close();
}
}
Thanks
Garry
View 2 Replies
View Related
Nov 16, 2006
I have a this SP that inserts values into a table with results from a query, but at the same time I want to insert some default values.But thats NOT working the way I hoped for, actually sqlserver 2005 dont let me create this SP at all."Incorrect syntax near the keyword 'DEFAULT'."Can someone please tell me how I can achieve this? create procedure %PROC% ( @Ordre_ID int
)
asbeginIF NOT EXISTS(SELECT Ordre_ID FROM tbl_Ordre WHERE Ordre_ID = @Ordre_ID AND Ordrestatus IN ('2', '3', '4'))BEGIN return 0;ENDIF EXISTS(SELECT Ordre_ID FROM tbl_Faktura WHERE Ordre_ID = @Ordre_ID)BEGIN return 0; ENDBEGIN TRANSACTIONINSERT INTO tbl_Faktura( Ordre_ID ,PostNummer ,KID ,Fakturastatus ,Kontonummer ,Forfallsdato ,Belop ,BekreftetBetaltDato ,Faktura_GUID ,Adresse ,PostBoks ,Fornavn ,Etternavn ) (SELECT O.Ordre_ID ,K.PostNummer ,DEFAULT
,DEFAULT
,SI.Kontonummer
,(getdate()+14) ,v_OTS.TOTALBELOP ,DEFAULT
,DEFAULT
,K.Adresse
,K.PostBoks
,K.Fornavn
,K.Etternavn
FROM
tbl_Ordre AS O INNER JOIN
tbl_Kunde AS K ON
O.Kunde_ID = K.Kunde_ID
INNER JOIN
v_OrdreTotalSum AS v_OTS ON
O.Ordre_ID = v_OTS.Ordre_ID
,tbl_StatiskeInnstillinger AS SI WHERE
O.Ordre_ID = @Ordre_ID
)
UPDATE
tbl_Ordre
SET
Ordrestatus = '6'
WHERE Ordre_ID = @Ordre_ID COMMIT end
go
View 3 Replies
View Related
Apr 9, 2008
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
View 9 Replies
View Related
Apr 9, 2008
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
View 2 Replies
View Related
Feb 12, 2008
Hi all,
I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me.
Thank You
View 1 Replies
View Related
Aug 27, 2004
Hi
I use the next StoredProcedure in Access, Inserts Users to tblUsers:
INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments )
VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
How do i have to write it in SQL?
Thank's.
View 1 Replies
View Related
Mar 12, 2008
Hi
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
Tables for this storedprocedure:
Table Name:mnetwork..unw_Nola
Table Fields:LoanID,ConditionSet,Status,Revised,RevisedBy,PDFNOLA,MonthlyIncome,DocExpDate,Notes,
Underwriter,rowguid,ApprovedDate.MovedToCentera,FK_UserID,RecordDate
TablName:mnetwork..unw_LoginLookup
Table Fields: [LoginLookupID], [LoginName], [UnderwriterName], [FirstName], [LastName], [Title], [Signature], [Address1], [Address2], [Addr1], [Addr2], [City], [State], [Zip], [Phone], [Phone2], [Fax], [Email], [DefaultSet], [rowguid], [FK_UserID], [RecordDate], [Createdby], [LastUpdated], [UpdatedBy]
2)1. Block this loan
2. Grant me access to this blocked loan.
So, I need you to write either one or two stored procedures that will accomplish the following:
When a loan needs to be blocked, it needs to be added to the mnetwork..sec_BlockedLoans table
When a person needs access to that loan, their username needs to be added to the mnetwork..sec_LoanAccess table.
Tables:
1) SELECT [LoanID], [Username], [Grantor], [GrantDate] FROM [mnetwork].[dbo].[sec_LoanAccess]
2) SELECT [LoanID], [Added] FROM [mnetwork].[dbo].[sec_BlockedLoans
any one can help to write these stored procedure.
Thanks,
JT
View 1 Replies
View Related
Jun 27, 2006
I am using SQL Server 2005 now and I have a table with following columns.
ID, FirstName, LastName, Email
"ID" is the primary key (int) and is set auto generated (1 increment)
I have a StoredProcedure to insert a new record.
CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO
I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
View 3 Replies
View Related
Aug 22, 2007
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:Luca de Angelis
7 -- Create date: 22/08/2007
8 -- Description:Inserimento dei dati contabili nella tabella di log
9 -- =============================================
10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili
11 -- Add the parameters for the stored procedure here
12 @id_gestore tinyint
13 AS
14 SET NOCOUNT ON
15 BEGIN TRANSACTION
16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
17 SELECT CEL_traffico_temp.numero_telefonico
18 , CEL_traffico_temp.anno
19 , CEL_traffico_temp.mese
20 , @id_gestore as id_gestore
21 , 1
22 FROM CEL_traffico_temp
23 INNER JOIN
24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella
25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
26
27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
28 SELECT CEL_traffico_temp.numero_telefonico
29 , CEL_traffico_temp.anno
30 , CEL_traffico_temp.mese
31 , @id_gestore as id_gestore
32 , 2
33 FROM CEL_traffico_temp
34 INNER JOIN
35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella
36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
37
38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
39 SELECT CEL_traffico_temp.numero_telefonico
40 , CEL_traffico_temp.anno
41 , CEL_traffico_temp.mese
42 , @id_gestore as id_gestore
43 , 3
44 FROM CEL_traffico_temp
45 INNER JOIN
46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella
47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
48
49 IF @@error <> 0
50 BEGIN
51 ROLLBACK TRANSACTION
52 END
53 ELSE
54 BEGIN
55 COMMIT TRANSACTION
56 END
Help me please...
View 2 Replies
View Related
May 14, 2008
Visual Studio 2008 Code VB
I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table.
Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
@ReportType varchar(50),@CreatedBy varchar(50),
@OpenDate smalldatetime,@Territory varchar(10),
@Region varchar(10),@StoreNumber varchar(10),
@StoreAddress varchar(200),@TiplineID varchar(50),
@Status varchar(50),@CaseType varchar(200),
@Offense varchar(200)
AS
BEGIN
IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID,
@Status,@CaseType,@Offense)
END
View 12 Replies
View Related
Jun 11, 2005
To all,
I looked at the MS-SQL pubs sample database and execute the example
stored procedure reptq2 and I got 17 results set back. Where can I find
an example using Visual Studio DataGrid or any means to get all these
results from this SP.
Thanks,
Frank
View 3 Replies
View Related
Apr 16, 2006
Hi
public static void ExecuteStoredProcedure(string SPName, ref ArrayList Parameters) { object result=null; ADODB.Connection Connection = new ADODB.Connection(); ADODB.Command Command = new ADODB.Command(); Command.ActiveConnection = Connection; Command.CommandText = SPName; Command.CommandType = CommandTypeEnum.adCmdStoredProc;
if (Parameters != null) { for (int i = 0; i < Parameters.Count; i++) { Command.Parameters.Append(Parameters[i]); } }
try { Connection.Open(ConnectionString, "", "", 0); Command.Execute(out object RecordAffected, ref object parameters, int options ) ;//the second parameter what mean? how set it? } catch (Exception ex) { throw ex; } finally { Connection.Close(); }
}
Thanks
View 2 Replies
View Related
May 8, 2008
Hi,
I want to know how to write stored procedure with parameters. And i want to compare this parameters.
I have DropDownList and RadioButtonList in my Web Application.
How to write Procedure passing this Two control parameters.(DropDown and RadioList).
In RadioButtonList having 5 selections.
If selection 1 happens
-- some condition
if selection2 hapens
-- some condition
similarly 3,4,5
------------------
How to write conditions in storeprocedure.
Please help me i am not having exp on storedprocedure.
Thanks
View 1 Replies
View Related
Mar 23, 2008
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_CustomerDetails]
(@Number varchar(30),
@Name varchar(30),
@City varchar(20),
@SSN varchar(20),
@CustomerID int)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number)
BEGIN
UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID
END
ELSE
BEGIN
print 'CANNOT UPDATE'
END
END
This my storedproc.
My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number
Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number
Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this.
Thankyou
Ramya.
View 4 Replies
View Related
May 11, 2008
Hello ,
When I read about Stored Procedure , I read this Topic
"
First, after SQL Server parses and compiles a stored procedure, it caches
the execution plans in its procedure cache.
I want to know what does it mean about ProcedureCache ??????
another question is :
what is the situations when SqlServer doesnot resuse the StoredProcedure in the ProcedureCache and it must recomplie it again ???
thanks
View 4 Replies
View Related
Mar 12, 2007
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
View 1 Replies
View Related
Feb 9, 2008
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets)
Here is the function to fill the RBL:
1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 cmd.CommandText = "usp_responses_sel"
5 cmd.CommandType = Data.CommandType.StoredProcedure
6
7 ' Fill usp_ with Parameters
8 cmd.Parameters.AddWithValue("QuestionID", QuestionID)
9 cmd.Parameters.AddWithValue("LocaleID", LocaleID)
10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum)
11
12 Dim da As New SqlDataAdapter
13 da.SelectCommand = cmd
14 Dim ds As New DataSet
15 da.Fill(ds, "response")
16 Return ds
17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist.
Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2)
2 rblResponses2.DataSource = ds
3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound?
Thanks
View 5 Replies
View Related
Feb 26, 2008
I'm not getting any error, but I'm not seeing any updates to my database. Here is the code below:
The click event:
protected void btnModify_Click(object sender, EventArgs e) { UpdateRecord(Convert.ToInt32(ViewState["HostNameID"])); }
The method:
private void UpdateRecord(int HostNameID) { try { // TODO // - Call stored procedure to update database table HostName // The storedprocedure is modifyHost using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { SqlCommand cmd = new SqlCommand("modifyHost", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pDesc", txtDeviceDescription.Text.Trim()); cmd.Parameters.AddWithValue("@pSerial", txtSerial.Text.Trim()); cmd.Parameters.AddWithValue("@pSmc", ddlSMC.SelectedItem.Text); cmd.Parameters.AddWithValue("@pID", ViewState["HostNameID"]); cmd.Parameters.AddWithValue("@pMilliSecs", "lastUpdated"); cn.Open(); cmd.ExecuteNonQuery(); } } catch (SqlException err) { lblmsgError.Visible = true; lblmsgError.Text += "<br><b> btn_Delete_SQL_Error </b>" + err.Message; } }
The Storedprocedure:
ALTER PROCEDURE dbo.modifyHost(@pDesc nvarchar(50),@pSerial nvarchar(50),@pSmc nvarchar(50),@pID bigint,@pMilliSecs nvarchar(50))AS UPDATE dbo.HostNameSET Description = @pDesc, DeviceSerialNum = @pSerial, SMCContact = @pSmc, lastUpdated = @pMilliSecsWHERE (HostNameID = @pID)
Is there anything I missed???
View 5 Replies
View Related
Apr 6, 2008
hi
i have a stored procedure witch Returns 0 or 1 dependig if exists some rows
how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works
i need something like if(command.GetReurnValue) .... else ....
thanks in advance
View 3 Replies
View Related
Jun 2, 2008
Can I know the best method to connect the sql server 2008 with Visual studio 2008, it will be helpful if I know how to access the stored procedures in the sqlserver via LINQ.
View 1 Replies
View Related
Jun 12, 2008
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
View 2 Replies
View Related
May 19, 2005
I need to create a StoredProcedure to calculate a field auto-inc.
Who helps me?
I tried many code, but i didn't have sucess.
My sample:
CREATE PROCEDURE SP_CT_ITEM
AS
DECLARE @CONTADOR NUMERIC(008)
SET NOCOUNT ON
BEGIN TRAN
SELECT @CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008))
FROM PARAMETRO (UPDLOCK)
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
UPDATE PARAMETRO SET NM1_PARAMETRO = @CONTADOR + 1
WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
SELECT @CONTADOR
COMMIT TRAN
GO
Thanks.
Marco
mapolitti@stecsoft.com.br
View 5 Replies
View Related
Mar 5, 2008
Hi
I have a question.
I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table.
now my question is :
how to capture the colum value of first select statement and give it as input to second select staement.
my code is here :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
Select*From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
Select * From refunds where CNo = @val1
END
here if i fill CNo text box in my search page its giving the value
but all the time user may not give CNo.He may search customers based firstname ,lastname etc.
so what should i do to capture CNo from first select statement and give it as input to second select statements
anyone help is appreciated.
ramya.
View 10 Replies
View Related
Apr 13, 2007
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
Regards
Riccardo
View 7 Replies
View Related
