Incorrect Sql Statement - Not Reading Variable

Dec 5, 2006

In the funtcion below I am selecting a value from the page -  Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table.....

How do I write this statement to pick up the variable above?????

Thanks in advance for your help!

Clare 

 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim ds As New Data.DataSet

Dim da As Data.SqlClient.SqlDataAdapter

Dim strSQL As String

Dim ProjectID As Int32

ProjectID = Request.QueryString("ProjectID")

strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add("imgFile")

 

For Each tempRow As Data.DataRow In ds.Tables(0).Rows

tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))

Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

 

End Sub

View 12 Replies


ADVERTISEMENT

Error Reading Variable

May 4, 2006

I keep receiving errors while using variables to pass values to different part of my package. For example ...

Error: 2006-05-04 10:31:59.84
Code: 0xC00470EA
Source: EdwPostProcess
Description: Reading the variable "User::GvPathRoot" failed with error code 0xC0010009.
End Error

The way my variables are constructed is :

First the global variables (Gv*) are set by the parent package;
Then local variables (Lv*) are set using the EvaluateAsExpression property and giving it an expression that takes the Gv* variable and concatenate a string to it.

At execution time, while the expressions are resolved, I get the above error while it was resolved correctly in a previous task.

I tried different method including duplicating my variables but without success. I'm running out of ideas

Gilles

View 4 Replies View Related

Reading And Writing Same Variable To A Script

Apr 27, 2006

It looks like its not possible to both read and write the same variable from a script using the conventional Me.Variables.<variable> syntax.

I can only assign a variable as Readonly or ReadWrite and not both. If I assign it ReadOnly I can only access it in the PreExecute subroutine. If I assign it ReadWrite I can only access it in the PostExecute subroutine (in fact doesn't this just make it WriteOnly in fact?). So I can only either read in or read out a variable using this syntax, noth both. Is this right?

So the read and write a variable to a script, the VariableDispenser approach is the only option to use. Is this right? and is it documented somewhere that this is how to use variables in scripts. Thanks.

View 13 Replies View Related

Reading A Variable In A Script Taks Used As Source

Aug 28, 2006

Hi,

I'm using a script task as source and I need to read a global variable.

I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)

This gives an errero on 'Dts.' saying it can not resolve it.

View 2 Replies View Related

Reading An OUTPUT Parameter From A Stored Procedure Into A Variable

Oct 2, 2007

Hello,
I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J.
THE CODE I USE TO CALL THE SPROC
Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty)
 THE STORED PROCEDURECREATE Proc [prs_VerifyUser
 @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT
ASBEGIN
IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid)
BEGIN
SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid
Return 1
END
Return 2
END

View 4 Replies View Related

Incorrect Syntax Near 'nvarchar'. Must Declare The Scalar Variable @CODE.

Jan 6, 2008

I have this issue and I can not figure out the problem. I have 4 other forms from the same database using practly the same code, slight variations based on datavalidation requirements. IIS6 SQL Express 2005.
 
I have tried to defint eh colum for CODE as a bound filed and as a templated field. I get the same error.ASPX Page <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><br />
<table class="mainTable" cellspacing="0" cellpadding="3" align="center">
<tr><td class="mainTableTitle">BSP Codes</td></tr>
<tr><td>
<table align="center">
<tr>
<td><asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"BorderColor="Silver"
BorderStyle="Solid" BorderWidth="1px" HorizontalAlign="Center"
CellPadding="3"DataKeyNames="CODE" DataMember="DefaultView"
DataSourceID="SqlDataSource1"
OnRowEditing="GridView1_OnRowEditing"
OnRowCancelingEdit="GridView1_EndEdit"
OnRowUpdated="GridView1_EndEdit">
<Columns>
<asp:CommandField ShowEditButton="True" EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
<HeaderStyle CssClass="rptTblTitle"></HeaderStyle>
</asp:CommandField>
<asp:BoundField DataField="CODE" HeaderText="Code" ReadOnly="true" HeaderStyle-CssClass="rptTblTitle" />
<asp:TemplateField HeaderText="Bottle Size" SortExpression="Btl Sz">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" MaxLength="10" Columns="10" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="TextBox1"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="TextBox1"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Labeled" SortExpression="Labeled">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" MaxLength="1" Columns="2" runat="server" Text='<%# Bind("LABELED") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Labeled is a required field" Text="*" ControlToValidate="TextBox2"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("LABELED") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Bottles Per Case" SortExpression="Btls Per Case">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" Columns="4" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Bottles per case must be a whole number." Text="*" ControlToValidate="TextBox3"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Liters Per Case" SortExpression="Ltrs Per Case">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" MaxLength="8" Columns="8" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Liters per case must be a number." ControlToValidate="TextBox4" Text="*"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" DeleteText="Delete" HeaderStyle-CssClass="rptTblTitle" >
<HeaderStyle CssClass="rptTblTitle"></HeaderStyle>
</asp:CommandField>
</Columns>
 
</asp:GridView>
 
<table id="tblAddBSP" runat="server" width="100%">
<tr><td colspan="2" align="center"><asp:Label ID="lblAddMessage" runat="server" Text="" style="color:Red;font-weight:bold;"/></td></tr>
<tr>
<td style="font-weight:bold;">Code</td>
<td><asp:TextBox ID="txtAddCode" runat="server" MaxLength="1" Columns="2"></asp:TextBox><asp:RequiredFieldValidator ID="rfv_txtAddCode" runat="server" ErrorMessage="Please Supply a BSP Code." Text="*"
ControlToValidate="txtAddCode" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Bottle Size</td>
<td><asp:TextBox ID="txtAddSize" runat="server" MaxLength="10" Columns="10"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="txtAddSize"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="txtAddSize"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Labeled</td>
<td><asp:TextBox ID="txtAddLabeled" runat="server" MaxLength="1" Columns="2"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Bottles Per Case</td>
<td><asp:TextBox ID="txtAddBottlesPerCase" runat="server" MaxLength="4" Columns="4"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Liters Per Case</td>
<td><asp:TextBox ID="txtAddLitersPerCase" runat="server" MaxLength="8" Columns="8"></asp:TextBox></td>
</tr><tr><td colspan="2" align="right"><asp:Button ID="btnAddNew" runat="server"
Text="Add BSP" onclick="btnAddNew_Click" /></td></tr>
</table>
</td>
</tr>
</table>
</td></tr>
</table><asp:ValidationSummary ID="ValidationSummary1" runat="server"
ShowMessageBox="True" ShowSummary="False" /><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
ProviderName="<%$ ConnectionStrings:SqlConnectionString.ProviderName %>"DeleteCommand="DELETE FROM BSP WHERE CODE = @CODE" InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED, @BOTTLES$PER$CASE, @LITERS$PER$CASE)"
SelectCommand="SELECT CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE FROM BSP order by CODE"
UpdateCommand="UPDATE BSP SET BOTTLE$SIZE = @BOTTLE$SIZE, LABELED = @LABELED, BOTTLES$PER$CASE = @BOTTLES$PER$CASE, LITERS$PER$CASE = @LITERS$PER$CASE WHERE [CODE] = @CODE">
<UpdateParameters>
<asp:Parameter Name="BOTTLE$SIZE" type="String" />
<asp:Parameter Name="LABELED" type="Char" />
<asp:Parameter Name="BOTTLES$PER$CASE" type="Int32" />
<asp:Parameter Name="LITERS$PER$CASE" type="Decimal" />
<asp:Parameter Name="CODE" type="Char" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="txtAddSize" Name="BOTTLE$SIZE" type="String" />
<asp:ControlParameter ControlID="txtAddLabeled" Name="LABELED" type="Char" />
<asp:ControlParameter ControlID="txtAddBottlesPerCase" Name="BOTTLES$PER$CASE" type="Int32" />
<asp:ControlParameter ControlID="txtAddLitersPerCase" Name="LITERS$PER$CASE" type="Decimal" />
<asp:ControlParameter ControlID="txtAddCode" Name="CODE" type="Char" />
</InsertParameters>
</asp:SqlDataSource>
 
</asp:Content>
 
CODE BEHIND
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using reports;
using System.Data.SqlClient;
public partial class MaintainBSP : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{Master.ActiveTab = Helpers.Tabs.Admin;Security.CheckPageAccess(Security.AccessTypes.Administrator);
}protected void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs e)
{tblAddBSP.Visible = false;
}protected void GridView1_EndEdit(Object sender, EventArgs e)
{tblAddBSP.Visible = true;
}protected void btnAddNew_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.Insert();lblAddMessage.Text = "Add BSP '" + txtAddCode.Text + "' successful.";
txtAddCode.Text = String.Empty;txtAddSize.Text = String.Empty;
txtAddLabeled.Text = String.Empty;txtAddBottlesPerCase.Text = String.Empty;txtAddLitersPerCase.Text = String.Empty;
}catch (SqlException ex)
{if (ex.Number == 2627)
{
lblAddMessage.Text = "The code '" + txtAddCode.Text + "' is already in the database.<br />Select another code for this BSP.";txtAddCode.Text = String.Empty;
}
elselblAddMessage.Text = ex.Number + " - " + ex.ErrorCode.ToString() + " - " + ex.Message;
}
catch
{lblAddMessage.Text = "There was an issue inserting the BSP Code '" + txtAddCode.Text + "'. Please check the values and try again.";
}
}
}
 

View 5 Replies View Related

Reading Columns After Select Statement

Apr 28, 2004

Afer I am getting a DataSet from SELECT* statement I would like to know the value of each field
How do I do that?

View 1 Replies View Related

Incorrect Syntax Near ','. Sql Statement

Dec 4, 2007

 SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString2"].ToString());        SqlCommand myCommand = new SqlCommand("SELECT (Deadline, Description, Headline, AddressField, OrganizationField, NameField, FileField, EmailField, CommentField) FROM RegistrationFormDB_Info WHERE (UserName = @UserName AND TournamentName = @TournamentName)", myConnection);        SqlParameter myParam = new SqlParameter();        myParam.ParameterName = "@UserName";        myParam.Value = User.Identity.Name;        myCommand.Parameters.Add(myParam);        myParam = new SqlParameter();        myParam.ParameterName = "@TournamentName";        myParam.Value = Request.QueryString["TournamentName"];        myCommand.Parameters.Add(myParam);        myConnection.Open();        SqlDataReader myReader = myCommand.ExecuteReader();   <======================  I continue to get this error and I can't figure out the problem!  Anything helps.  Thank you very much. 

View 1 Replies View Related

Incorrect Syntax Using IF Statement

Apr 6, 2006

Hi,I'm new to SQL Server Programming, I work with ASP a lot, but latelyI've been trying to create Stored Procedures, etc. I'm having aproblem writing a simple IF statement.. I don't seem to understand whyit's giving me this error. I've search around on Google Groups, but Istill don't get it.=================USE msdbIF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')END=================My error is:Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'END'.Thanks for any help.

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)

Nov 1, 2007

Following is the stored procedure iam trying to create.Here i am trying to

First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.

This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.


CREATE PROCEDURE [dbo].[sp_try]

@TempTable varchar(25)


AS

DECLARE @SQL nvarchar(MAX)

DECLARE @SQLINSERT nvarchar(MAX)



BEGIN


--create temp table

SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +

'(

ContactName varchar (40) NOT NULL ,

ContactId varchar (30) NOT NULL ,

ContactrMessage varchar (100) NOT NULL,



)'

EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable


SELECT @sql= 'Select * from table'




SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )

SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition

EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable

View 8 Replies View Related

SQL Server 2008 :: Incorrect Prefix Error (select Count Statement)

Oct 7, 2015

Naming convention and what am I doing wrong here:

,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:

Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

View 9 Replies View Related

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

Using C# Variable With SQL Statement

Nov 12, 2007

Greetings everyone,
I am trying to use a c# string with an SQL statement in a data adapter (.NET 03)
The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: 
WHERE (login = '" & test &  "')
WHERE (login = '" + test +  "')
 any ideas?
PS: If I change to something like WHERE (login = 'abcdef') I get  a result meaning there's something wrong with the way I am putting the variable in the sql query.
 Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window
THANKS!

View 8 Replies View Related

Variable In An Sql Statement

Dec 14, 2005

Hi,

I've created an sql statement:
select *
from fin_installment
where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial

which is in an Dataset's TableAdapter.
This variable receives its value during the form init and it is an integer.
When I start the page the folowing error message is displayed:

" An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. "

So my question is that how can I use variables in sql statement in dataset?

View 3 Replies View Related

Using Variable In LIKE Statement

Dec 13, 2007

Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code:


Code:

-- this example returns results
SELECT whatever
FROM mytable
WHERE whatever LIKE 'blah%';




Code:

-- this example returns no results
DECLARE @test VARCHAR;
SET @test='blah%';

SELECT whatever
FROM mytable
WHERE whatever LIKE @test;



Any ideas why the version using the variable would not work?

Patrick

View 3 Replies View Related

USE Statement With A Variable?

Jun 17, 2004

I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?

Here is the modified code:

/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/
/* Instead of replacing %Companydb% (in each USE statement) with the name of the
single company database that this script is supposed to work on, I've added
@cCompany to hold the company DB name through each iteration of the outside
cursor/while loop.
*/

declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */
declare @cStatement varchar(255)/* Misc exec string */
declare @DynDB varchar(15)/* DB Name exec string */
declare @DYNGRPgid int/* Id of DYNGRP group */

/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF

use DYNAMICS

/* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */
declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')

OPEN C_cursor
FETCH NEXT FROM C_cursor INTO @cCompany
WHILE (@@FETCH_STATUS <> -1)
begin
use @cCompany
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

set nocount on

OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use @cCompany
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in all company databases.
*/
use @cCompany
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end

end
DEALLOCATE C_cursor

______________________________________
Thanks for any help you have.

View 1 Replies View Related

My Variable In Sql Statement

Aug 11, 2006

Declare @MyCode nvarchar(20);
Set @MyCode='ABC'
set @int_rowcount=(SELECT count(hoten) FROM @MyCode)
I run it but still errors !
How can i implement above statement ?
Thank you very much !

View 6 Replies View Related

USE Statement With Variable

Jul 20, 2005

Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???

View 2 Replies View Related

SQL Statement In Variable

Sep 3, 2007

Hello Everyone,
I wanted to pass a SQL statement thru a variable, and use that variable in my source component.

SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT
FROM DIM_CLINIC
where load_dt > ?

I had created a variable with my SQL statement and mapped that variable in my source component.
Its giving me some error.

Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command.


Please do inform me about the solution for having a parameter in my source SQL Statement.

View 3 Replies View Related

SQL Statement With Variable.

Apr 4, 2008

Hi ,

I am testing a very simple query that use variable for sort direction and sort expression



DECLARE

@SortExp nvarchar(256),

@SortDir nvarchar(10)


Set @SortExp = 'curTime'

Set @SortDir = 'DESC'


Select * from table
where recID < 20
order by @SortExp @SortDir

and i got this error...


The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.


Is there anyway to do this task.

Thanks

Ddee

View 3 Replies View Related

Can Variable Be Used In SQL UPDATE Statement In VB.NET

Dec 16, 2007

Hy, i have this problem in vb.net:
I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:
Dim variable_name As StringDim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
 
When I don't use a variable after SET statement, everything work fine. This code works fine:
 Dim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
 
Please, if someone can help me in this...thanks..

View 2 Replies View Related

Passing Variable To Sql Statement

Apr 28, 2008

could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help   

View 8 Replies View Related

Use A Table Name As A Variable In The From Statement

Oct 26, 2005

I'm curious if anyone knows the correct way to do this pseudo-statement correctly?  I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName

View 4 Replies View Related

Using A Variable In An Update Statement

Jan 2, 2001

I am having difficulties with some sql syntax with sql server 2000.
I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String.
Ex.
Declare @varA varchar(12)
select @varA = (select top 1 Value from #temp)

Update TableX
set @varA = y.ColTest
from TableX x, TableY y
where x.Colid = y.Colid

It sets the variable = to the last value from TableX.ColTest
I want the Update statement to update the value for the Variable which
represents the correct column to update.

Any ideas?

Thanks,

Daniel

View 1 Replies View Related

Want To Use A Variable In A &#39;use DB&#39; Statement In SQL Script

Jan 18, 2002

Hi,
I want to use a variable in a 'use' statement...
but, I cannot figure out the syntax, nor do I know if it is possible...
Here is an example SQL script:
/*-----------------------------------------------------*/

DECLARE @DataBase varchar(60)
--Declare cursor for all DBs except master, MSDB, Model, tempdb
DECLARE curdb CURSOR for select name from master..sysdatabases
where
name not in ('master', 'MSDB', 'Model','tempdb')for read only

--Open and perform initial fetch
open curdb
fetch curdb into @DataBase

--While there are databases to process, process each DB
While @@fetch_status = 0

PRINT @DataBase
use + ' ' + @database --or,
use @database

fetch curdb into @DataBase

end

/*------------------------------*/
Thanks,
Michael

View 1 Replies View Related

USE @dbname (Use Statement With Variable)

Jun 12, 2002

Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.

I want to execute the SP withing each db.

Thanks

View 2 Replies View Related

How To Use Transact SQL Variable In A SQL In Statement

Mar 2, 2007

Hi all,

I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.

Code:


DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval

DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)


OPEN User_Cursor

FETCH NEXT FROM User_Cursor INTO @username

SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username


FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO



I am at a loss any suggestions would be greatly appreciated.

View 4 Replies View Related

Variable In CURSOR Sql Statement (was Please Help Me)

Dec 24, 2004

Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?

Thanks !

View 3 Replies View Related

Variable In A Select Statement

Sep 6, 2006

Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.

select @column_name from table

View 2 Replies View Related

Set Variable Value In CASE Statement?

Jun 29, 2012

Is it possible to set a value to a variable if a case statement is true?

like this:

CASE
WHEN Utable.type = 2 THEN U.Username2 + (@Uname= 2)
WHEN Utable.type = 3 THEN U.Username3 + (@Uname= 3)

Ive tried above and:

"+ (select @Uname = 2)"
"+ (SET @uname = 2)"
"; SET @uname = 2"
...

and several other things but non works.

variable's = INT
"Utable.type" = INT
"U.Username"2/3 = nvarchar(50)

View 2 Replies View Related

Variable In DDL Statement In Procedure

Apr 15, 2008

Create table tbl(title nvarchar(40))

Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End

Execute df_bppr @de = 'NoTitle'

******************

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

--------------------

I created the procedure without any error. But when i execute the procedure it shows the above error

I want to create a proc which dynamically change the default value for more than one column with same default value.

Vijai

View 6 Replies View Related

How To Set A Variable In An If Exists Statement

Feb 28, 2008



Hello,

I would like to set a variable within my if exists statement, however SQL is throwing and error stating:


Incorrect syntax near '='.

If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable

Here is my code block:



Code Snippet

declare @permissionID int;

if exists(select @permissionID = Id from Permission

where [Description] = 'SettlementReport')






Thanks,
Flea#

View 3 Replies View Related







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