How To Declare Global Sqlcommands...

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


ADVERTISEMENT

DECLARE In SQL CE

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

Declare @var????

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

How Do I Declare A Recordset ?

Aug 25, 2006

Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy

View 3 Replies View Related

Must Declare Scalar @.....

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

My DropDownList Won't Declare?

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

DECLARE CURSOR

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

Declare @var Text

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

Declare A Concatenated Value.

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

Declare Table

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

How To Declare Variables Per Row

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

Declare Table

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

Why Declare Does Not Work

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

'declare' Syntax In A UDF

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

Declare Variable

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

Help With Declare And Cursor

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

Declare Variable For All In SP

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

How To Declare Integer Var In VB&&>NET

Apr 18, 2008

PLeaset le tme knwo

View 1 Replies View Related

Must Declare The Scalar Variable

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

Must Declare The Scalar Variable

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

Must Declare The Scalar Variable

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

SELECT DISTINCT In DECLARE

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

Declare Cursor With Dynamic SQL?

Jul 23, 2002

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 1 Replies View Related

How Long Declare @ Can Hold On?

Dec 20, 2006

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 4 Replies View Related

Is There A Way To Declare Constant Variables ?

Feb 15, 2005

Or, is the only way to use "declare" and "set" only ?

View 1 Replies View Related

Must Declare The Scalar Variable?

Jun 17, 2013

CREATE PROCEDURE [dbo].[Testing]
@FilteredID VARCHAR (MAX),
@SchoolCode VARCHAR (MAX),

[Code]....

I tried to execute above sproc in SQL Server Management Studio , I received the error: Must declare the scalar variable "@Score1".

View 5 Replies View Related

Declare Or Create Cursor

May 27, 2004

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 2 Replies View Related

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 View Related

Set Declare Query Navision

Mar 26, 2015

I use SQL query in Navision but can't set declare of Company.I set declare :

declare @CompanyName VARCHAR(30)
Set @CompanyName = 'USA'

When query i'm set ['+@CompanyName+'$Item] but error Invalid object name 'dbo.'+@CompanyName+'$Item'.

View 7 Replies View Related

Must Declare Scalar Variable???

Aug 31, 2006

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 10 Replies View Related

How Should We Declare Long Numbers

Oct 21, 2006

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 10 Replies View Related

Must Declare Scalar Variable @ID

Mar 24, 2007

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 1 Replies View Related

Declare Un-static Decimal Value

Oct 12, 2007

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 2 Replies View Related







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