How To Declare And Use Variables In A View
Sep 12, 2014
I have a select statement that works fine. Now I need to create a view with this statement and I have problems with the variables that I need to declare.
Is there a way to include the declare command in a view?
I can not use a stored procedure this time.
This is the statement:
DECLARE @FirstDate DATE,
@SecondDate DATE,
@ThirdDate DATE
SELECT @FirstDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones
---SELECT @SecondDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones WHERE fecha_valor<> @FirstDate
SELECT @SecondDate = dateadd(week,-1,max(fecha_valor)) FROM MPR_Historico_Posiciones
[code]....
View 2 Replies
ADVERTISEMENT
Oct 13, 2014
I know how to set a variable for the entire query, but how do I set one per row?
I have one query which is growing beyond my ability to understand/maintain it. I factor in the sale price of an item based on cost, our markup, shipping & channel fees. The problem is that each one of these has it's own variability. I would like to be able to store inline calculations as a variable such that I can call & add them. Example:
My code currently looks like this
CREATE TABLE #tempPrice (
product NVARCHAR(40)
,price DECIMAL(18,2)
)
insert into #tempPrice (product,price) values('prod1','18.00')
[Code] ....
I would like to be able to work with it this way:
SELECT
product
,price
,Cast(price * 1.1 as decimal(18,2)) as markup
,@markup + 5 as 'markup-and-shipping' -- <-- This factors in all of the previous calculations, plus something new.
FROM #tempPrice
I use Microsoft SQL 2008
View 7 Replies
View Related
Feb 15, 2005
Or, is the only way to use "declare" and "set" only ?
View 1 Replies
View Related
Jan 8, 2008
Hello there,
i have been asked about a thing that, i think, is not possible. But maybe i am wrong.
question:
Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic?
This means, can i get the variable name and the type of it from a database to create
a dynamic stored procedure that changes itself by firing a trigger.
Thanks for all oppinions and answers.
everWantedLINUX
View 5 Replies
View Related
Apr 3, 2007
I have created the following stored procedure in sql server 2005.I m using asp.net with C# 2005.
Can someone please rectify my errors why i m getting such type of the errors...
ALTER PROCEDURE CompanyStoredProcedure
@uspcompanyid INT NULL,
@uspcompanyname VARCHAR(20),
@uspaddress1 VARCHAR(30),
@frmErrorMessage AS VARCHAR(256) OUTPUT,
@RETURNVALUE AS INT OUTPUT,
@RETURNID AS INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
/*
RETURN_VALUE Comments
1Data Inserted
2Data Updated
-9Other errors
*/
DECLARE @companyid INT,
@companyname VARCHAR(20),
@address1 VARCHAR(30)
--validation...
IF ( @uspcompanyname IS NULL OR @uspcompanyname = '' )
BEGIN
SET @RETURNVALUE = -9
SET @frmErrorMessage = 'Company Name is empty'
RETURN -9
END
IF EXISTS ( SELECT * FROM Companymaster WHERE Companyid = @companyid )
BEGIN
UPDATE Company
SET companyname = @companyname,
address1 = @address1
WHERE companyid= @uspcompanyid
SET @frmErrorMessage = 'Company Name/Address has been updated'
SET @RETURNVALUE = 2
END
ELSE
BEGIN
INSERT INTO companymaster ( companyname, address1 )
VALUES (@companyname,@address1)
SET @frmErrorMessage = 'Company Name/Address info has been Inserted'
SET @RETURNVALUE = 1
END
SET NOCOUNT OFF
END
THANXS in advance.
View 3 Replies
View Related
Sep 12, 2005
Hello everyone,
View 11 Replies
View Related
Oct 26, 2005
Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable.
eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9
declare @varname nvarchar(10)
declare @i integer
select @i=0
while @i<10
begin
set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10))
set @i=@i+1
end
Does anyone know of a way to do this?
View 6 Replies
View Related
Sep 30, 2006
dear all
how can i create view within declare ?
if i had one script contains declare if it is possible to combine both table with union ?
declare @a varchar(20)
select @a = ptraceno from phonelevel
where(ptraceno = '0913787170' or otherno = '0913787170')
select * from phonelevel where ptraceno = @a
declare @b varchar(20)
select @b = ptraceno from phonelevel
where(ptraceno = '0913011676' or otherno = '0913011676')
select * from phonelevel where ptraceno = @b
View 4 Replies
View Related
Oct 17, 2006
This one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View 2 Replies
View Related
Jul 28, 2004
Hi All,
I would like to create a view based on a variable (a date).
CREATE VIEW testView (@myDate)
AS
select * from testTable
WHERE testVar = @myDate
--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.
Thanks.
View 1 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Aug 6, 2007
Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?
Matt
View 4 Replies
View Related
Aug 23, 2006
I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it????
CREATE PROCEDURE dbo.Tracking_GetStatus
AS
DECLARE @Date DateTime
DECLARE @Begining DateTime
DECLARE @Ending DateTime
SET @Date = GETDATE()
SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1),
DATEADD(mi,(DATEPART(mi,@Date)*-1),
DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))
SET @Ending = DATEADD(ss,-1,
DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1),
DATEADD(mi,(DATEPART(mi,@Date)*-1),
DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))))
SELECT
Vehicl,
UpdateTi
XCoord,
YCoord,
Status
FROM Track
WHERE UpdateTime >= @Begining
AND UpdateTime <= @Ending
RETURN
GO
View 1 Replies
View Related
Aug 25, 2006
Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy
View 3 Replies
View Related
Oct 26, 2007
Hi everyone,
I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script.
I post the relevent code below:
SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int)) sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@LoginName").Value = userName sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime)) sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection)
C4LConnection.Open() CandidateID = sqlSetCandidateEmail.ExecuteScalar()
Catch Exp As SqlException lblResults.Visible = True lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try
All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition
So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script.
Any help would be appreciated.
View 8 Replies
View Related
Mar 3, 2008
Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong?
Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value
If MyVar = "" Then
ErrorMessage.Text = "Please select a test"
Else
'Insert selection into databaseDim oConnection As New SqlConnection
Dim oCommand As SqlCommand
Dim sConnString As String
Dim sSQL As String
sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString)
sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)"
oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar))
oCommand.ExecuteNonQuery()
oConnection.Close()
ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database."
End If
End Sub
<asp:TemplateField HeaderText="Test">
<EditItemTemplate>
<asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" >
<asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
<asp:ListItem Value="5" >5</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
View 7 Replies
View Related
Oct 4, 2000
Is there any way to create a cursor, based on a dynamically created select_statement? Something like:
DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect
where @strSelect is previously declared as let's say varchar.
I don't want to create a stored procedure for this.
Thanks!
View 2 Replies
View Related
Dec 17, 2001
Hi,
I want to store more than 8000 characters. Ideally it might have been fine if decalre @var text had worked for me.
Can anyone tell is it possible....
thanks,
sajai.
View 3 Replies
View Related
May 5, 2008
OMG i'm so stupid, i edited my original post instead of replying!!
I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.
Example.
Table 1.
Column1 (Modem) Column2 (Highspeed) Column3 (Black)
Concatenated to be MODEM HIGHSPEED BLACK.
The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.
Column1 (MODEM HIGHSPEED BLACK) Column2 (44)
View 7 Replies
View Related
May 29, 2008
When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help.
Declare @pc2 Table
([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)
Insert into @pc2
([prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id])
Select
[prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id]
from property_char
Go
SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM
property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id
WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')
View 13 Replies
View Related
Dec 2, 2007
Dear All,
actually, in our application, we are using so many temporary local variables like @table_name....
and based on these local variables, we are making joins with the regular adtabase tables. is it better thing instead of temp tables?
what exactly i want to know is,
@table_name,#temp_table,##temp_table.....
in these three processes, which one is the best one...
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 3 Replies
View Related
Feb 10, 2008
declare @fdas as varchar(10)
set @fdas = 'master'
use @fdas
i try to execute this one it does not work
said that Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@fdas'. how to fix that one? by not replacing the @fdas
but i got master database
------------
what is the difference actually between
use master than the first querry?
arifliminto86
View 1 Replies
View Related
Jul 20, 2005
Hi, I'm trying to create a function that returns a table, however I wantto use a local variable in there and enterprise manager ain't liking it!The error I get is number 156 'incorrect syntax near the keyword'declare'.. hopefully this is just a simple thing where I've put it inthe wrong place.The code follows:CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@datefromsmalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @typenvarchar(20))RETURNS TABLE ASRETURNdeclare @set nvarchar(150)select "Total Pledged" as info, sum(total) as totFROM AFresponseTotals (@campaign, @datefrom, @dateto,@prospect)Cheers for any help,Chris
View 2 Replies
View Related
Jan 11, 2008
Declare @DBName varchar(25)
select @DBName = 'Production'
Select @DBName = @DBName + '.dbo.'+'sysfiles'
select @DBName
select * from @DBName
When I executes above lines of code in query analyser it give me an error like :
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@DBName'.
give me solution as soon as possible
Thanks
Aric
View 6 Replies
View Related
Aug 13, 2006
I keep getting the message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'declare'.
What am I doing wrong?
declare @dbname varchar(8000),
declare @countyname varchar (200) ,
declare @sql varchar(8000)
declare county_name cursor for
select distinct county from Zipcodes
open county_name
fetch next from county_name
into @countyname
declare dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open dbname_name
fetch next from dbname_name
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end
set @sql = ''
fetch next from dbname_name into @dbname
fetch next from county_name into @countyname
CLOSE county_name
DEALLOCATE county_name
CLOSE dbname_name
DEALLOCATE dbname_name
View 4 Replies
View Related
Oct 10, 2006
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.
The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.
The MemberId field is a varchar(20) in the table.
Create procedure sp_GetClaims_BY_MemberID
@Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @memberid
EXEC sp_GetClaims_BY_MemberID '99999999999'
The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?
I am currently working in SQL 2000.
View 3 Replies
View Related
Apr 18, 2008
PLeaset le tme knwo
View 1 Replies
View Related
Jul 17, 2006
Hello!
I have a aspx page in which I have a Gidview populated by a sqlDataSouce.
This is my code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CostEmployee1.aspx.vb" Inherits="RecursosHumanos_CostEmployee1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee"
UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee"
DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error:
Must declare the scalar variable "@Id_CostEmployee".
I'm tired of "googling" this error, and I've tried all the advices, nothing...
I don't know what is happening here, I have 5 other forms, all simillar and they all work!
Any suggestions, pleeeeaaaase?
Thank's!
Paula
View 10 Replies
View Related
Jan 12, 2007
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function?
Here's what I did...
Dim p_s_syounin2 As New SqlCommand
Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23")
'''' connection string is not placed here acc. to my research
End If
End Sub
Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand
If e.CommandName = "Approve" Then
cnn.Open()
p_s_syounin2.ExecuteNonQuery()
cnn.Close()
End If
End Sub
I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "
Please help me.
Thanks guys.
Audrey
View 1 Replies
View Related
Feb 20, 2007
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.
View 1 Replies
View Related
Mar 30, 2007
Hi with the code below I am getting the error
Error inserting record. Must declare the scalar variable "@contractWHERE"
I removed @contract and it then gave me the error
Error inserting record. Must declare the scalar variable "@zipWHERE"
I was wondering if some can point me in the right direction for fixxing this
protected void cmdUpDate_Click(Object sender, EventArgs e)
{
//Define ADO.NET Objects.
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city,state=@state, ";
updateSQL += "zip=@zip, contract=@contract";
updateSQL += "WHERE au_id@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value);
//Try to open the database and execute the update
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " records inserted.";
}
catch (Exception err)
{
lblStatus.Text = "Error inserting record. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
}
Many Thanks in advance
View 3 Replies
View Related
Jul 29, 2005
Hi
I try to use this code in query analyzer
DECLARE @SendTo VarChar(4000)
SET @SendTo = ''
SELECT DISTINCT @SendTo = @SendTo + UserEmail + ';' FROM dbo.tbl_AccountInfo WHERE (UserEmail <> '')
PRINT @SendTo
The purpose of this code is to build up a ; seperated string of email adresses that I can use sending mail from SQL server.
It works but it only give me one record (should give me 130 records) , but if I remove the DISTINCT part it give me all records, duplicates too. Does anyone know why and how can I get this to work? Or maybe do it in another way?
Best regards
View 4 Replies
View Related
