How To Declare Global Sqlcommands...

How Can I Declare A Global Variable In A Report
 

Hi,
    How can i declare a global variable in a report, Can some one please some kinda example for doing it.
 
Regards
Karen

View Replies !

---

Compilingerror - Codebehindsite With Sqlcommands
Hi,

View Replies !

---

Declare @var????
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 Replies !

---

DECLARE In SQL CE
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 Replies !

---

My DropDownList Won't Declare?
 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 Replies !

---

Must Declare Scalar @.....
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 Replies !

---

How Do I Declare A Recordset ?
Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy

View Replies !

---

'declare' Syntax In A UDF
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 Replies !

---

Declare @var Text
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 Replies !

---

DECLARE CURSOR
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 Replies !

---

How To Declare Integer Var In VB&&>NET
 PLeaset le tme knwo

View Replies !

---

Declare Variable For All In SP
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 Replies !

---

Help With Declare And Cursor
 

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 Replies !

---

Declare Variable
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 Replies !

---

Why Declare Does Not Work
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 Replies !

---

Declare Table
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 Replies !

---

Declare Table
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 Replies !

---

Declare A Concatenated Value.
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 Replies !

---

Must Declare The Scalar Variable
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 Replies !

---

Must Declare The Scalar Variable
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 Replies !

---

Must Declare The Scalar Variable
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 Replies !

---

Must Declare The Scalar Variable
Hi,
I found a nice tutorial on the net on how storing images to a SQL DB (http://blogs.msdn.com/jdixon/articles/495408.aspx) but have some troubles in retreiving the image back from the database.
Once I click the View details link, I receive an error message "Must declare the scalar variable @..."
Can someone tell me how I can define this scalar variable and/or how I can display an image from my database ?
Thanks to all !!!
Bart

View Replies !

---

Declare Variable Dynamically
I'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe

View Replies !

---

Need To Declare Multiple Values
Alright, so I have this problem. I want to make it easy for me andothers to be able to run a query and easily choose whether we wantMerchants or NonMerchants. Previously, we would have to comment outbits of code and would leave things messy (it would also leave room forerror). So, I'm thought DECLARE and SET would work. Wrong.This is what I have....DECLARE @Merchant VARCHARSET @Merchant = (Select CONVERT(VARCHAR, Id) + ','FROM AdminAdvertiserTypesDDLWHERE Id IN (1,3,4,5)) // Includes Active, Out of Business, Cease to do business, InactiveI've also tried...SET @Merchant = '1,3,4,5'Then, in the query itself I try:WHERE AdminAdvertiserTypesDDL.Id = @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN @MerchantorWHERE AdminAdvertiserTypesDDL.Id IN (@Merchant)orWHERE AdminAdvertiserTypesDDL.Id LIKE @MerchantEither way, it will ONLY show me the merchants whose Id is 1. When Imake the query:WHERE AdminAdvertiserTypesDDL.Id IN (1,3,4,5)I finally get the desired results.Any ideas or tips?Thank you so much!

View Replies !

---

Declare An Array Of Int In SQL Server
Hi All,I want declare an array of int in SQL Serverplease help me to convert the following code from VB to TSQL------------Dim md_mon(12) As Integermd_mon(1) = 31-------------Thanks

View Replies !

---

Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

View Replies !

---

Declare Cursor With Dynamic SQL?
Hello..

Can you declare a cursor with dynamic SQL?

I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.

these attempts did not work:

DECLARE crsCursor CURSOR FOR @vchrSQL
DECLARE crsCursor CURSOR FOR (@vchrSQL)

Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?

View Replies !

---

SELECT DISTINCT In DECLARE
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 Replies !

---

How Long Declare @ Can Hold On?
Currently i have a long long query may run 10 hours
but i wonder if i declare :
DECLARE @rows int

how long it will hold on its value??

View Replies !

---

Is There A Way To Declare Constant Variables ?
Or, is the only way to use "declare" and "set" only ?

View Replies !

---

How Can I Create View Within Declare ?
 

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 Replies !

---

Syntax Error On Declare.
 
I am trying to create a view or a table with in SQL system and it keeps coming up with incorrect syntax near the word declare.
It runs fine as long as I do not try and create a table or a view, which unfortunatly I need to do as I have to export the data into Excel for the finance guys.
 
 
The syntax is below....
 

Create View dbo.Z_Cashflow_Forc_Paymsdue
As
Declare @Firstday Int
Declare @Nextday Int
Set @Firstday = '10'
Set @Nextday = '25'
Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode,
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))
End AS [Paymdate],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymWk],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymYr]
from Z_Cashflow_Purchorders, Wsuppliers
Where [Supplier Code] = Supp

Union

Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode,
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))
End AS [Paymdate],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymWk],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymYr]
from Z_Cashflow_Schedorders, Wsuppliers
Where [Supplier Code] = Supp

View Replies !

---

Must Declare The Scalar Variable
 

create PROCEDURE [Update_Purged]



AS

Declare @Stg_Purged_union table

(PurgedAccountUnionID int IDENTITY,

Coid char(50),

FacilityId int,

AccountID int,

PatientName char(50),

PatientNo char(50),

PT char(50),

ST char(50),

PurgeDt datetime);

BEGIN

INSERT INTO @Stg_Purged_Union

([Coid]
,null
,null

,[PatientName]

,[PatientNo]

,[PT]

,

,[PurgeDt])

select coid, patientname, patientno, pt, st, purgedt from

[Stg_PurgedAccount]
 

--updating the facilityid in @stg_purged_account_file_union
 

update @stg_purged_union set facilityid=(select

facilityid from appfacility as b

where b.unitnum=(select

unitnum from appfacility as c

where c.unitnum=b.unitnum) and @Stg_Purged_Union.coid=b.unitnum)

 
 
I am getting the following error.

Must declare the scalar variable "@Stg_Purged_Union"

View Replies !

---

Declare Variables In A SP Dynamic?
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 Replies !

---

Declare Variable Error
Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing?

------error---------------
Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85
Must declare the variable '@DatabaseFrom'.

-----sp----

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create Procedure dbo.sp_CopyData
(@ClientAbbrev nchar(4) )
AS
DECLARE @DatabaseFrom varchar(100)

Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus'

--------------------------------------------------------------
delete from sherrisplayground.dbo.tsn_ClaimStatus
where csclientcode = @ClientAbbrev


---Insert Data from Original table into copied table---------
Insert into [AO3AO3].sherrisplayground.dbo.tsn_ClaimStatus (
CsClientCode,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName)
select
@ClientAbbrev,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName
from @DatabaseFrom


return


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View Replies !

---

Using A Variable In SQL Script (Declare @x)
I am not sure why the following does not work...

I am declaring a variable to hold a string to be used in my script. The contents of the variable looks perfect and works independently, the variable it does not work. (BTW, do you call this a variable in T-SQL too?). The idea is to pass the value to the script to generate different results sets.

//Parameter
DECLARE @Codes varchar(8000);
SET @Codes = '''07-1110_CHA,1'',''07-1110_DCV,2''';

//Examine contents - results: '07-1110_CHA,1','07-1110_DCV,2'
Select @Codes;

//Works fine
Select screening_cd,check_amount
From accounting
Where screening_cd in ('07-1110_CHA,1','07-1110_DCV,2');

//This does not work
Select screening_cd,check_amount
From accounting
Where screening_cd in (@Codes);

TIA

View Replies !

---

Error 137: Must Declare Variable ...
Hello,

I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error.

This is the important part of the code:
.
.
.
-- DECLARE TABLE VARIABLE
DECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100))

-- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE
-- NO PROBLEM HERE
INSERT INTO @tmp_return_tbl
SELECT TOP 1 * FROM @tmp_tbl
ORDER BY NEWID()

-- TRYING TO UPDATE A TABLE
UPDATE xTable
SET xTable.fieldY = xTable.fieldY + 1
WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR
.
.
.

I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong?

Your help is much appreciated.

View Replies !

---

Declare Un-static Decimal Value
I try to declare a variable with un static decimal point in the following statement:-

declare @mpt int;
declare @mpq nvarchar (1)
SELECT @mpq = (SELECT mpq FROM ims.parm)

exec('declare @MM1 decimal (10,'+ @mpq +');')

set @mm1 = 1
print @mpt
when i print the declare statement looks like the following:
declare @MM1 decimal (10,2);
but with exec the statement, I have the following error:-
Msg 137, Level 15, State 1, Line 18
Must declare the scalar variable "@mm1".

Best regards

View Replies !

---

Must Declare Scalar Variable @ID
the following is my code
can anybody rectify my problem that i get when running my application
"Must declare scalar variable @ID"
<asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg"
BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ButtonType="Button" ShowSelectButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Answers" HeaderText="Answers" SortExpression="Answers" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YahooConnectionString7 %>"
DeleteCommand="DELETE FROM Answers WHERE (ID = @ID)" InsertCommand="INSERT INTO Answers(ID, Answers) VALUES (@ID, @Answers)"
SelectCommand="SELECT Answers.* FROM Answers" UpdateCommand="UPDATE Answers SET ID = @ID, Answers = @Answers WHERE (ID = @ID)">
<DeleteParameters>
<asp:Parameter Name="ID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</InsertParameters>
</asp:SqlDataSource>

View Replies !

---

How Should We Declare Long Numbers
when dealing with long numbers in appllications like: 1000000000 up to 9999999999999
in what type is it better to declare them (varchar, bigint.....)
Thanks

View Replies !

---

Must Declare Scalar Variable???
OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:-
ERROR
"Must declare scalar variable @iuser"

Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!

//BEGIN

ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))

AS
Declare @sql nvarchar(1000)

SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (@iuser)'

EXECUTE (@sql)

RETURN
//END

And i know my VB.NET code is working but i will put it in anyway:-

//BEGIN

'variables
Dim user As String
user = Profile.UserName
Dim intenduser As String
intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()

'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)
'parameters
Dim cmd As New SqlCommand("putpending", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@cuuser").Value = user
cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@iuser").Value = intenduser
'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Any ideas why i am getting this error message?

View Replies !

---

Must Declare The Scalar Variable
Following stored proc uses dynamic sql but it gives the error
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@ProjectBenefitID".
though its declared .please. tell the workaround



ALTER PROCEDURE [dbo].[spPMPT_GetBenefit]

@ProjectBenefitID INT,

@OrderBYVARCHAR(40),
-- Parmeters for Paging [Start]
@TotalPages INT OUT ,
@CurrentPageNumber INT OUT ,
@NumberOfRecordsINT = 5, /*PageSize*/
@CurrentPage INT = 0/*PageNumber*/

-- Parmeters for Paging [End]

AS

SET NOCOUNT ON

DECLARE @TMPFLOAT
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)


----- Paging declarations start
DECLARE @SQLFinal NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SC VARCHAR(4000)
----- Paging declarations end


DECLARE@SelectASVARCHAR(4000)
DECLARE@FromASVARCHAR(4000)
DECLARE@WhereASVARCHAR(4000)
DECLARE@LsOrderBy ASVARCHAR(4000)



-- Initialize vars
SET @SC= ''
SET @From= ''
SET @Where= ''
SET @Select= ''
SET @SQLFinal= ''
SET @Count= 0


IF (@CurrentPage = 0 OR @CurrentPage IS NULL)
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL )
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@Orderby IS NULL OR @Orderby = '')
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END




CREATE TABLE #TEMP_BENEFIT1
(

AssessBenefitID INT,
ProjectBenefitID INT,
ExpectedQuantity INT,
ExpectedQuality VARCHAR(2000),
Comments VARCHAR(2000)
)

INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,
Quantity,Quality,
Comments
FROM PMPT_AssessBenefit
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID

CREATE TABLE #TEMP_BENEFIT2
(

AssessBenefitID INT,
ProjectBenefitID INT,
ActualQuantity INT,
QtyFileID INT,
QtyFileName VARCHAR(100),
QtyFilepath VARCHAR(100),
ActualQuality VARCHAR(2000),
QuaFileID INT,
QualFileName VARCHAR(100),
QualFilepath VARCHAR(100),
Comments VARCHAR(2000),
refAssessBenefitID INT,
DateasON DATETIME
)

INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,
PAB.Quantity,pab.qtyFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,
PAB.Quality,pab.quaFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,
PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON
FROM PMPT_AssessBenefit PAB
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A'


DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)

IF @UNIT IS NULL
SET @UNIT = ''
SET @Select='
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+'
SELECTT1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,
CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,
T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasON
FROM#TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERET1.AssessBenefitID = T2.refAssessBenefitID'

View Replies !

---

Where Do I Declare SQL Connection Variable In My Application
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
Please give me some examples
thank you
maxmax

View Replies !

---

Where Do I Declare SQL Connection Variable In My Asp.net Application
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
thank you
maxmax
 

View Replies !

---

Error Message: Must Declare Variable @ID
I’m having trouble with a datalist. The default view is the Item Template which has an Edit button. When I click the Edit button, I run the following code (for the EditCommand of the Datalist):
 
DataList1.EditItemIndex = e.Item.ItemIndex
DataBind()
 
It errors out with the message “Must declare variable @ID�.
 
I’ve used this process on other pages without problem.
 
The primary key for the recordsource that populates this datalist is a field named “AutoID�. There is another field named ID that ties these records to a master table. The list of rows returned in the datalist is based off the ID field matching a value in a dropdown list on the page (outside of the datalist). So my SQLdatasource has a parameter to match the ID field to @ID. For some reason, it's not finding it and I cannot determine why. I haven't had this issue on other pages.
 
Here’s my markup of the SQLDataSource and the Datalist/Edit Template:
 
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>"
    DeleteCommand="DELETE FROM [tblSalesSupport] WHERE [NBID] = @NBID"
    InsertCommand="INSERT INTO [tblSalesSupport] ([ID], [NBNC], [NBEC], [Description], [Estimate], [CompanyID], [CompanyName], [ProjectNumber]) VALUES (@ID, @NBNC, @NBEC, @Description, @Estimate, @CompanyID, @CompanyName, @ProjectNumber)"
    SelectCommand="SELECT * FROM [tblSalesSupport] WHERE ([ID] = @ID)"
    UpdateCommand="UPDATE [tblSalesSupport] SET [ID] = @ID, [NBNC] = @NBNC, [NBEC] = @NBEC, [Description] = @Description, [Estimate] = @Estimate, [CompanyID] = @CompanyID, [CompanyName] = @CompanyName, [ProjectNumber] = @ProjectNumber WHERE [NBID] = @NBID">
        <DeleteParameters>
            <asp:Parameter Name="NBID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="ID" Type="Int32" />
            <asp:Parameter Name="NBNC" Type="Boolean" />
            <asp:Parameter Name="NBEC" Type="Boolean" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Estimate" Type="Decimal" />
            <asp:Parameter Name="CompanyID" Type="Int32" />
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ProjectNumber" Type="String" />
            <asp:Parameter Name="NBID" Type="Int32" />
        </UpdateParameters>
        <SelectParameters>
           <asp:ControlParameter ControlID="ddlFind" Name="ID" PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
        <InsertParameters>
            <asp:Parameter Name="ID" Type="Int32" />
            <asp:Parameter Name="NBNC" Type="Boolean" />
            <asp:Parameter Name="NBEC" Type="Boolean" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Estimate" Type="Decimal" />
            <asp:Parameter Name="CompanyID" Type="Int32" />
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ProjectNumber" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
   
       <asp:DataList CssClass="MainFormDisplay" ID="DataList1" runat="server" DataKeyField="NBID" DataSourceID="SqlDataSource1" width="100%">
        <HeaderTemplate>….</HeaderTemplate>
        <ItemTemplate>….</ItemTemplate>
        <EditItemTemplate>
            <table border="0"  style="width: 100%">
                <tr class="MainFormDisplay"  valign="top">
                    <td colspan="8">
                        <asp:TextBox ID="txtNBID" runat="server" Text='<%# Eval("NBID") %>' Visible="true"></asp:TextBox>
                        <asp:TextBox ID="txtID" runat="server" Text='<%# Bind("ID") %>' Visible="True"></asp:TextBox></td>
                </tr>
                <tr class="MainFormDisplay">
                    <td valign="top" style="width: 100px"><asp:Checkbox ID="chkNBNC" runat="server" Checked='<%# Bind("NBNC") %>' /></td>
                    <td style="width: 100"><asp:CheckBox ID="chkNBEC" runat="server" Checked='<%# Bind("NBEC") %>' Width="100px" /></td>
                    <td style="width: 100px"><asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Bind("CompanyName")%>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtProjectNumber" runat="server" Text='<%# Bind("ProjectNumber") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtDescription" runat="server" Text='<%# Bind("Description") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtEstimate" runat="server" Text='<%# Bind("Estimate","{0:N2}") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 55px"><asp:CheckBox ID="ckDeleteFlag" runat="server" /></td>
                    <td style="width: 100px"><asp:Button ID="ItemSaveButton" runat="server" CommandName="Update" Text="Save" />
                    <asp:Button ID="ItemCancelButton" runat="server" CommandName="Cancel" Text="Cancel" /></td>
                </tr>               
            </table>
            &nbsp;
        </EditItemTemplate>
    </asp:DataList><br />

View Replies !

---

Must Declare The Scalar Variable @RefNo
ive encounter this problem when i install the application at client side..enfect, i try it on all the PC at my office, i wouldnt encounter this problem..can anyone tell me what is possibly wrong with this?here is the code possible code effectedASPDim myDataset As Data.DataSet = New Data.DataSet        Dim conn As Connection = New Connection("AbnormalControlDataConnString", "P_CreateAbnormalFormSect0")        conn.AddParameter("Pending", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80)        conn.AddParameter("CreateBy", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80)        conn.executeSproc(myDataset)        Me.Session.Add("RefNo", myDataset.Tables(0).Rows(0).Item(0))        conn = Nothing        myDataset = Nothing       gridview1.dataBind() <~~~~~~ i have this gridview bind another sproc                                                                  named[P_GetAbnormalFormSect0)conn.vb    Public Sub New(ByVal con As String, ByVal sprocName As String)        configCon = con        connectionString = System.Configuration.ConfigurationManager.ConnectionStrings(configCon).ConnectionString '        myConnection = New System.Data.SqlClient.SqlConnection(connectionString)        myCommand = New System.Data.SqlClient.SqlCommand()        myCommand.Connection = myConnection        myCommand.CommandType = CommandType.StoredProcedure        myCommand.CommandText = sprocName    End Sub    Public Sub AddParameter(ByVal name As String, ByVal value As String, ByVal type As     SqlDbType, ByVal size As Int16)        myCommand.Parameters.Add(name, type, size)        myCommand.Parameters.Item(myCommand.Parameters.Count - 1).SqlValue = value    End Sub    Public Sub executeSproc(ByRef dataset As Data.DataSet)        myConnection.Open()        myDataSet = New System.Data.DataSet()        myDataSet.CaseSensitive = True        dataAdapter = New System.Data.SqlClient.SqlDataAdapter()        dataAdapter.SelectCommand = myCommand        If Not dataset Is Nothing Then            dataAdapter.TableMappings.Add("asd", "UserInfo")            dataAdapter.Fill(myDataSet)            dataset = myDataSet            myConnection.Close()        Else            myCommand.ExecuteNonQuery()        End If    End SubSprocset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_GetAbnormalFormSect0]     -- Add the parameters for the stored procedure here    @REFNO CHAR(11) = NULLASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    DECLARE    @ErrorCode int    SET @ErrorCode = 0        SELECT * FROM dbo.AFStatusInfo        WHERE dbo.AFStatusInfo.RefNo = @REFNO        IF(@@ROWCOUNT = 0)        BEGIN            SET @ErrorCode = -1        END        IF( @@ERROR <> 0 )        BEGIN            SET @ErrorCode = @@ERROR        END                RETURN @ErrorCodeEND-----------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_CreateAbnormalFormSect0]     -- Parameters for the stored procedure    @Pending CHAR(80),    @CreateBy CHAR(80) ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;        -- @ErrorCode : 0    No Error    --                1    No Data (Ignore when no ResultSet required)    --                2    Parameter not complete    --                3    Conflict with existing data    -- Other error code please refer to @@ERROR code    DECLARE    @ErrorCode int    SET @ErrorCode = 0    DECLARE @Id VARCHAR(11)    DECLARE @Temp VARCHAR(4)    SET @Temp = CAST((SELECT COUNT(*) FROM [AFStatusInfo] WHERE RefNo like ('BS200607%'))AS CHAR(3))    SET @Temp = @Temp + 1000    SET @Id = 'BS'    SET @Id = @Id + CONVERT(CHAR(4),GETDATE(),20)    SET @Id = @Id + CONVERT(CHAR(2),GETDATE(),10)    SET @Id = @Id + SUBSTRING(@Temp,2,3)            INSERT INTO [AFStatusInfo]               (RefNo               ,FlowStatus               ,[Pending]               ,[CreateBy]               ,[DateCreated])            VALUES               (@Id               ,'New'               ,@Pending               ,@CreateBy               ,CONVERT(CHAR(8), GETDATE(), 3))    SELECT @Id AS "RefNo"        IF( @@ERROR <> 0 )        BEGIN            SET @ErrorCode = @@ERROR        END                RETURN @ErrorCode        OnError:            RETURN @ErrorCodeEND----------------------------------------------------------------------------------------------------------------

View Replies !

---