Getting GUID Value From StoredProcedure.ExecuteScalar()

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


ADVERTISEMENT

ExecuteScalar

Mar 23, 2007

private void buttonLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\PEService.mdf;Integrated Security=True;User Instance=True";
conn.Open();
string strSQL = "Select Count(*) as ctr From Cust Where Email=" + textBoxEmail + "and Passwd=" + textBoxPW;

SqlCommand cmd = new SqlCommand(strSQL,conn);
int ctr=(int)cmd.ExecuteScalar();
if (ctr == 1)
MessageBox.Show("Correct");
else
MessageBox.Show("Wrong");
conn.Close();
}

i have this code for my login form. when i remove conn.Open(); in the code
it says... ExecuteScalar requires an open and available Connection. The connection's current state is closed.

and when i put conn.Open();
it says... An attempt to attach an auto-named database for file C:... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

what is the problem?

View 6 Replies View Related

ExecuteScalar() Not Returning Value?

Dec 12, 2007

Okay 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?

View 3 Replies View Related

ExecuteScalar() Returns -1

Oct 15, 2004

The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.

Any ideas on what I might be doing wrong... ?

View 1 Replies View Related

Get Count With Executescalar()

Apr 18, 2005

Howdie y'all!
I'm trying to do an executescalar() on the next stored procudure...
SELECT COUNT(*) FROM tblUsers WHERE UserEmail = @UserEmail;
Strangely enough I get SqlServer exception that tells me there's a syntax error.
Is there something I'm overseeing?
Cheers,
Wes

View 2 Replies View Related

ExecuteScalar Problems. Need Help

Apr 26, 2006

Hi all
I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.
'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:
...
<asp:TemplateField HeaderText="Client" SortExpression="c_id">
   <ItemTemplate>
      <asp:Label ID="lblClient" runat="server" Text='<%#GetClient(Eval("c_id")) %>' />
   </ItemTemplate>
</asp:TemplateField>
...
This then calls: GetClientName - Which is as follows.
...
Public Function GetClientName(ByVal ClientID)
Dim ScalarValue As String = ""
Dim myConnection As New SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")
Dim myCommand As New SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @ClientID", myConnection)
myCommand.Parameters.Add("@ClientID", SqlDbType.Int)
myCommand.Parameters("@ClientID").Value = ClientID
   Try
      myConnection.Open()
      ScalarValue = myCommand.ExecuteScalar
   Catch ex As Exception
      Console.Write(ex.Message)
   End Try
 
   If ScalarValue > "" Then
      Return ScalarValue.ToString
   Else
      Return "<span style='color: #CCCCCC'>- NULL -</span>"
   End If

 
End Function
...
This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.
 
Any help in this matter would be great and really appreciated. Thanks.
 
David
 

View 2 Replies View Related

SqlCommand.ExecuteScalar()

May 4, 2006

Is there documentation on what ExecuteScalar() will return if the SQL statement is returning an image?

View 1 Replies View Related

ExecuteScalar - Count(*)

Mar 25, 2007

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!

View 3 Replies View Related

How To Store ExecuteScalar Value Into Variable ?

Oct 16, 2006

I having a strange problem, my code is as below: cmd.connection = cnncmd.commandtext = "select count(*) from member" dim i as integeri = cInt(cmd.executescalar) However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".I have no idea abt wat is goin on, can anyone gv me some guide ?  Thanks.

View 2 Replies View Related

ExecuteScalar And 2 Rows In My DB On Every Write?

May 30, 2008

Hey folks, I was just learning how to work with ScopeIdentity and I found out I needed somthing called executescalar. Things seem to be working really well, and I think I did it write, except I'm having one strange little oddity. Everytime my page writes to my databases, it writes rows. I'm really curious to know what I did wrong. Any ideas? Thank you as always :-)
 Public Sub newoptic(ByVal sender As Object, ByVal e As System.EventArgs) Handles newpostBTN.Click
If newpostTXTBX.Text = "" Then
Exit Sub
End If
Dim mySQL As String = "insert into msg_topics (topic_title, topic_forum_id) values (@topicTITLE, @forumID); Select Scope_Identity()"Dim myConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("DBconnect"))
Dim cmd As New SqlCommand(mySQL, myConn)cmd.Parameters.AddWithValue("@topicTITLE", newposttitleTXTBX.Text)
cmd.Parameters.AddWithValue("@forumID", HDN_topic_forum_ID_LBL.Text)
myConn.Open()Dim topic_ID_holder As Integer = cmd.ExecuteScalar()
HDN_topic_id_holder_LBL.Text = (topic_ID_holder)
cmd.ExecuteNonQuery()
myConn.Close()
End Sub
 
Oh, one more quick question if whoever responds knows the answer. Why do I need a semi-colon here "@forumID); Select" ? The websites I learned about this from didn't explain that, and I've never used a semicolon in my select statements before, so I figured there must be something special about it.
 
 Thank you

View 2 Replies View Related

ExecuteScalar Returns Null

Oct 25, 2006

I am using the following C# code and T-SQL to get result object from aSQL Server database. When my application runs, the ExecuteScalarreturns "10/24/2006 2:00:00 PM" if inserting a duplicated record. Itreturns null for all other conditions. Does anyone know why? Doesanyone know how to get the output value? Thanks.------ C# -----aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};object oRtnObject = null;StoredProcCommandWrapper =myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);------ T-SQL ---ALTER PROCEDURE [dbo].[procmyCalendarInsert]@pBegin datetime,@pEnd datetime,@pUserId int,@pOutput varchar(200) outputASBEGINSET NOCOUNT ON;select * from myCalendarwhere beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserIdif @@rowcount <0beginprint 'Path 1'set @pOutput = 'Duplicated reservation'select @pOutput as 'Result'return -1endelsebeginprint 'Path 2'-- check if upperlimit (2) is reachedselect rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))),count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))))from myCalendargroup by rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))having count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))) =2and (rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))) =rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd asvarchar(20))))-- If the @@rowcount is not equal to 0 then-- at the time between @pBegin and @pEnd the maximum count of 2 isreachedif @@rowcount <0beginprint 'Path 3'set @pOutput = '2 reservations are already taken for the hours'select @pOutput as 'Result'return -1endelsebeginprint 'Path 4'--safe to insertinsert dbo.myCalendar(beginTime, endTime,userId)values (@pBegin, @pEnd, @pUserId)if @@error = 0beginprint 'Path 4:1 @@error=' + cast(@@error as varchar(1))print 'Path 4:1 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Reservation succeeded'select @pOutput as 'Result'return 0endelsebeginprint 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Failed to make reservation'select @pOutput as 'Result'return -1endendendEND

View 1 Replies View Related

Getting NullReferenceException When Executing ExecuteScalar()

Nov 20, 2007

I'm trying to add user information to the database however I'm getting a NullReferenceException.

Here's the code:


SqlCommand UserAddCommand = new SqlCommand();

int AssignedUserID = 0;

UserAddCommand.CommandType = CommandType.StoredProcedure;

UserAddCommand.Connection = m_DBConnection;

UserAddCommand.CommandText = "SP_UserAdd";

UserAddCommand.Parameters.AddWithValue("@User_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Street_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@City", DbType.String);

UserAddCommand.Parameters.AddWithValue("@State", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Zip_Code", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Email_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Phone_Number", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Login_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@User_Password", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Referrer_Name", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Type", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters["@User_Name"].Value = UserInfo.Name;

UserAddCommand.Parameters["@Street_Address"].Value = UserInfo.StreetAddress;

UserAddCommand.Parameters["@City"].Value = UserInfo.City;

UserAddCommand.Parameters["@State"].Value = UserInfo.State;

UserAddCommand.Parameters["@Zip_Code"].Value = UserInfo.ZipCode;

UserAddCommand.Parameters["@Email_Address"].Value = UserInfo.EmailAddress;

UserAddCommand.Parameters["@Phone_Number"].Value = UserInfo.PhoneNumber;

UserAddCommand.Parameters["@User_Login_Name"].Value = UserInfo.UserName;

UserAddCommand.Parameters["@User_Password"].Value = UserInfo.UserPassword;

UserAddCommand.Parameters["@Referrer_Name"].Value = UserInfo.ReferrerName;

if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FAMILY)

{

UserAddCommand.Parameters["@User_Type"].Value = "Family";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FRIEND)

{

UserAddCommand.Parameters["@User_Type"].Value = "Friend";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.MANAGER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Manager";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.OWNER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Owner";

}

try

{

AssignedUserID = (int)UserAddCommand.ExecuteScalar(); this line of code produces the NullReferenceException

UserInfo.UserID = AssignedUserID;

m_UserInfo = UserInfo;

}

catch (Exception Ex)

{

Console.WriteLine(Ex.ToString());

}



Please help me figure out what I am doing wrong.

View 1 Replies View Related

Calling Method To Do Executescalar With Parameters

Feb 15, 2008

I have a method like follows:
string EmpCount = null;
DateTime _dtstart = Convert.ToDateTime(txtStart.Text.Trim());
DateTime _dtend = Convert.ToDateTime(txtEnd.Text.Trim());SQLProvider sqlp = new SQLProvider(System.Configuration.ConnectionStrings["ConString"].ConnectionString);string SqlText = @"
select count(*) from employee
where activeemp=1 and startdate BETWEEN  @dtstart and @dtend;
using (sqlp.Connection) {
sqlpm [] param = {
 new sqlpm("dtStart", _dtstart),
 new SqlP("dtEnd", _dtend)
 };
 }EmpCount = sqlp.ExecuteScalar(SqlText, param).ToString();
return Convert.ToInt32(mbrCount);
 
Then the method I am calling is:public Object ExecuteScalar(String sqlText, Sqlp[] param)
{try
{
//Some Code here
}
}
So in the calling method (ExecuteScalar), the second parameter is defined as an array, is it ok to have an array in the called method too?

View 1 Replies View Related

ExecuteScalar Returns 0 (null) But INSERT Is Successful.

Sep 25, 2007



I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:


RecordID = cmd.ExecuteScalar


I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

View 7 Replies View Related

Populate GUID Column In Table B With Values From GUID Column In Table A

Mar 4, 2008


How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.

Does any one have a script to accomplish this task. thanks

View 4 Replies View Related

StoredProcedure

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

StoredProcedure

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

StoredProcedure

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

StoredProcedure Return Value

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

StoredProcedure In A Join

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

IF NOT EXISTS StoredProcedure

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

How To Get Results From A Storedprocedure

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

Use C# Through ADO Execute StoredProcedure

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

StoredProcedure With Parameters

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

Storedprocedure Not Updating The Row

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

StoredProcedure - Cashed

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

SqlDataSource, StoredProcedure, And Caching...

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

StoredProcedure And DataSet Return

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

Update A Database With A Storedprocedure

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

Acces Return Value From A StoredProcedure - How?

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

Accessing StoredProcedure From LINQ

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

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 TableTopicID                                          ThreadIDForumID                                        TopicIDTopicName                                    SubjectTopicDescription                            Replies                                                    UserID                                                    LastPostDatenow 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_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery 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 View Related

Error Trapping In StoredProcedure

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







Copyrights 2005-15 www.BigResource.com, All rights reserved