In-line Code Vs Sproc. Very Different Runtime.
Sep 5, 2007
Hi Guys,
I'm looking into why a particular report is running very slowly.
I call the stored proc which populates the report from query analyzer and request get an execution plan. Runtime is ~12mins
When I take the code inside the stored proc and convert it to inline code (change the parameter declarations to declare statements and procedure call parameters to set statements) and request an execution plan, the runtime is 10 seconds. The results returned are identical.
Comparing the execution plans shows the difference to lie in the SP using one index (returns 100m rows) and the inline code using another (2.5k rows). Apart from this index and the cost for some elements changing by a couple of percentage points the execution plans look very similar.
I've held off pasting the table schema and stored proc in here due to size.
One thing I'd hoped would help, but which didn't make an appreciable change, was to call the stored proc 'with recompile' in case the issue was an old execution plan.
Any suggestions how to stop the query planner making this 'bad' choice? Even some clue as to the source of the difference between the two functionally identical pieces of code might help.
View 11 Replies
ADVERTISEMENT
Jan 20, 2006
{
//setup the connection and connection string
SqlConnection addTutor = new SqlConnection();
addTutor.ConnectionString = "data source=QUAKEMASTER;" +
"initial catalog=DThomas;uid=sa;password=**********;";
string InsertTutor = "INSERT INTO Tutors (firstname, lastname, tutorID, office)Values(@firstName, @lastName,@tutorID,@location)";
SqlCommand insertTutor = new SqlCommand(InsertTutor);
insertTutor.Connection = addTutor;
//populating an array of sqlParameters performs the same function as an SqlParameters.Add
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@firstName",txtFirstName.Text);
param[1] = new SqlParameter("@lastName",txtLastName.Text);
param[2] = new SqlParameter("@tutorID",int.Parse(txtTutorID.Text));
param[3] = new SqlParameter("@location",listOffice.SelectedItem.ToString());
try
{
//open the connection
addTutor.Open();
//execute the NonSqlQuery
insertTutor.ExecuteNonQuery();
//close the connection
insertTutor.Connection.Close();
}
catch (Exception ThatFeckedUp)
{
throw ThatFeckedUp;
}
finally
{
addTutor.Close();
}
}
Sure i've missed something just not sure what.....
View 8 Replies
View Related
Jun 16, 2006
Hi,
I have a problem with the <asp:SqlDataSource. The thing that I try to do is to create a SQL statement. I am not sure if this can be done or not ( just started asp.net).
Something like this:
<%
Dim Test1 As String
Dim Test2 As String
Dim Test3 As String
Dim Test4 As String
Test1 = Request("xmbr")
Test2 = Request("xSEL")
Test3 = Request("xpro")
Test4 = "SELECT [jedan], [dva], [tri], [cetiri], [pet] FROM [pet1] where " & (Test1) & " " & (Test2) & "'" & (Test3) & "'"
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FinGateConnectionString %>" SelectCommand="<%= Test4%>">
The problem is that is giving me an error that I can’t figure out.
-------------------------------------------------
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
-------------------------------------------------
Thanks.
Nbdg_28
View 2 Replies
View Related
Aug 16, 2007
case @namesflag when 3 then 1
else case @namesflag when 2 then names.new else names.old
end
end=1
I understand what it is doing for the second part when @namesflag=2
, but what does end=1 mean
Can somebody please explain it to me.
Thanks
View 13 Replies
View Related
Jun 14, 2007
strSQL = strSQL & ReturnFieldArgument("@DoNotDisplaySSN", Me.DO_NOT_DISPLAY_SSN.Value, False, True)
What would happen if I change one the False value near the end of the code to True?
View 1 Replies
View Related
Sep 14, 2015
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')
[Code] .....
View 3 Replies
View Related
Jan 14, 2007
I have mysql type db in which I need computer to search for a key phrase of text that has several hundred occurances inside product table descriptions of products on live site. Once it finds the text, I would like for it to simply remove it, and look for the next occurance of the phrase and repeat the process until all are flagged and removed. I was told their may be a line of sql code that employs such a feature. Can anyone tell me what such a line would be?
View 1 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Apr 18, 2008
Hi,
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Thanks
Radhika
View 4 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Feb 27, 2007
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
View 1 Replies
View Related
May 4, 2012
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
View 5 Replies
View Related
Aug 31, 2007
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
View 4 Replies
View Related
Nov 8, 2006
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies
View Related
Feb 7, 2007
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies
View Related
Oct 26, 2007
I have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam
View 4 Replies
View Related
Sep 29, 2001
HEllo can anybody tell me how to monitor a long store procedure
line by line. Also how to put progress bar in it to tell user how
much is done.
Sabih.
View 1 Replies
View Related
Dec 12, 2007
Hi,
When creating a line chart, I would like to be able to show Markers for the data points, but no line between these points (as you can do in excel).
I have set the line setting to none (for the lines of interest), however the lines still show.
Is this a bug, or am i missing something obvious settings-wise?
Cheers,
M
View 7 Replies
View Related
Dec 28, 2007
HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
View 3 Replies
View Related
Jan 16, 2008
Hi
Anyone have any idea how to make a line style dashed or dotted in a line chart please?
If I change the series style to dashed or dotted it still appears as a solid line, yet the legend displays a dashed or dotted line....
Thanks
View 7 Replies
View Related
Apr 18, 2008
My question is about coding style for specifying constraints when creating tables.
Two styles for defining constraints:
In-line:
CREATE TABLE Fruit
(
FruitID INT IDENTITY(1,1)
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
FruitName NVARCHAR(50),
FruitTypeID INT
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
REFERENCES FruitTypes (FruitTypeID) ON UPDATE CASCADE,
DateCreated DATETIME DEFAULT GETDATE()
)
Out-of-line:
CREATE TABLE Fruit
(
FruitID INT,
FruitName NVARCHAR(50),
FruitTypeID INT,
DateCreated DATETIME
)
ALTER TABLE Fruit ALTER COLUMN FruitID INT NOT NULL
ALTER TABLE Fruit ADD
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (FruitID),
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (FruitTypeID)
REFERENCES FruitTypes (FruitTypeID),
CONSTRAINT DF_fruit_date_created DEFAULT
GETDATE() FOR DateCreated
Which style do you prefer and why?
View 2 Replies
View Related
Apr 7, 2007
Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.
View 2 Replies
View Related
Jul 27, 2006
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
View 7 Replies
View Related
Mar 28, 2007
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
View 4 Replies
View Related
Feb 24, 2008
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
View 2 Replies
View Related
Oct 16, 2007
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
View 2 Replies
View Related
Jan 25, 2008
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
Thanks!
Amanda
View 1 Replies
View Related
May 2, 2008
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
View 4 Replies
View Related
Apr 10, 2004
hi all,
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.
View 7 Replies
View Related
Dec 2, 2004
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC:
___________________
CREATE PROCEDURE dbo.sp_addMembershipRole
@INCID Int
AS
declare @literal NVarChar (10)
SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role])
VALUES (@INCID, @literal)
GO
___________________
CODE:
___________________
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Web.Security;
namespace admin
{
/// <summary>
/// Summary description for createMembership.
/// </summary>
public class createMembership : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator Vusername;
protected System.Web.UI.WebControls.RequiredFieldValidator Vpassword;
protected System.Web.UI.WebControls.RequiredFieldValidator Vretype;
protected System.Web.UI.WebControls.TextBox username;
protected System.Web.UI.WebControls.TextBox password;
protected System.Web.UI.WebControls.TextBox retype;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label lblDate;
protected System.Web.UI.WebControls.Button btnAdd;
private void Page_Load(object sender, System.EventArgs e)
{
if(! Page.IsPostBack)
{
string StrContactID = Request.QueryString["CID"].ToString();
Session["CID"]= StrContactID;
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public void btnAdd_Click(object sender, System.EventArgs e)
{
int intContactID;
string StrCID = Session["CID"].ToString();
intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text)
{
lblError.Text = "Retyping of your desired password did not match. Please try again.";
return;
}
string pwdSalt = CreateSalt(5);
string pwd = password.Text;
string pwdHash = CreatePasswordHash(pwd, pwdSalt);
string connStr= "server=****; uid=****; pwd=****; database=****";
string cmdStr= "sp_addMembershipUandP";
string cmd2Str= "sp_addMembershipRole";
SqlConnection CONN = new SqlConnection(connStr);
CONN.Open();
SqlCommand cmd = new SqlCommand(cmdStr, CONN);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommand cmd2= new SqlCommand(cmd2Str, CONN);
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50);
param.Value = username.Text;
param = cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50);
param.Value = pwdHash;
param = cmd.Parameters.Add("@salt", SqlDbType.NVarChar, 50);
param.Value = pwdSalt;
param = cmd.Parameters.Add("@CID", SqlDbType.Int, 4);
param.Value = intContactID;
SqlParameter param2;
param2 = cmd2.Parameters.Add("@INCID", SqlDbType.Int, 4);
param2.Value = intContactID;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
CONN.Close();
return;
}
private static string CreateSalt(int size)
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);
return Convert.ToBase64String(buff);
}
private static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "SHA1");
return hashedPwd;
}
}
}
View 2 Replies
View Related
Jan 22, 2004
create procedure
GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
declare @ZipID integer
declare @AddrID integer
set @AddrID=1
if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
return(@AddrID)
end
GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6))
AS
BEGIN
DECLARE @CityID integer
DECLARE @StateID integer
DECLARE @ZipID integer
SET @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
EXEC @CityID = dbo.GetCityID @City
EXEC @StateID = dbo.GetStateID @State
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
Return @ZipID
print @ZIPID
END
GO
Thx in advance
View 1 Replies
View Related
May 17, 2004
ha ha ,
havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?
Use Contacts
GO
CREATE PROC MaserIn
@entreddatetime= GETDATE(),
@initialsvarchar(2)=Null,
@Vendorvarchar(50),
@CkNovarchar(20),
@expTypevarchar(15),
@ckDatedatetime,
@ckAmtmoney,
@mIdintOUTPUT
AS
INSERT INTO [Contacts].[dbo].[Master](
[entered], [initials], [Vendor],
[CkNo], [expType], [ckDate], [ckAmt])
VALUES(@entered,
@initials,
@Vendor,
@CkNo,
@expType,
@ckDate,
@ckAmt)
SELECT @mID=@@iDENTITY
View 6 Replies
View Related
