Declaring DataTable In Script Causes Error?
Nov 7, 2006
As discovered when trying to create a custom transformation for this question (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=899895&SiteID=1&mode=1) I tried creating an object of type DataTable and I get this error
dim myTable as DataTable
Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. dts://Scripts/ScriptComponent_3851bc3613714d2d904d79bc006234f9/ScriptMain 19 24 ScriptComponent_3851bc3613714d2d904d79bc006234f9
Even if I add "Imports System.XML" I get the same error. Isn't DataTable part of System.Data (which is imported by default in Script Components)? Everyone else get the same behavior?
View 3 Replies
ADVERTISEMENT
Sep 3, 2007
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks
View 2 Replies
View Related
Feb 6, 2007
I have the below code and have two questions.
1) With the current delete control, the system breaks stating: System.Data.SqlClient.SqlException: Must declare the variable '@doc_area_id'.
2) Currently the Area Type column brings up all records set as '1'. It shows the value '1' for each column. I would like it to state the text 'Shared Area' under the Area Type Column where it is set as '1' in the database.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlManageSharedArea" CellPadding="4" ForeColor="#333333" BorderStyle="Solid" BorderColor="Black" BorderWidth="1px">
<Columns>
<asp:BoundField DataField="doc_area_name" HeaderText="Area Name" SortExpression="doc_area_name" />
<asp:BoundField DataField="doc_area_type" HeaderText="Area Type" SortExpression="doc_area_type" />
<asp:CheckBoxField DataField="doc_area_default" HeaderText="Default" SortExpression="doc_area_default" />
<asp:HyperLinkField HeaderText="Edit" NavigateUrl="~/Admin/EditDocumentArea.aspx" Text="Edit" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:HyperLinkField>
<asp:CommandField ShowDeleteButton="True" HeaderText="Delete" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:CommandField>
</Columns>
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlManageSharedArea" runat="server" ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"
SelectCommand="SELECT [doc_area_id], [doc_area_name], [doc_area_type], [doc_area_default] FROM [document_area] WHERE [doc_area_type] = 1"
DeleteCommand="DELETE FROM document_area WHERE [doc_area_id] = @doc_area_id">
</asp:SqlDataSource>
Any help would be greatful!
View 1 Replies
View Related
Dec 3, 2007
Hi, i am trying to run the following query in SQL Server CE:
SELECT @V1=CAST(SCOPE_IDENTITY() AS bigint)
SELECT @V1
INSERT INTO [ResourceItem] ([PackageId],[ResourceXml]) VALUES (@V1,@V6)
It gives me an error "near SELECT".
I replaced SCOPE_IDENTITY() with @@IDENTITY but it didnt help.
I think it is something related to storing values such as @foo = something.
How can i declare variables in SQL CE?
Pls help
Thank You
View 1 Replies
View Related
May 9, 2007
I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:
We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.
Do I need to declare a parameter, or in SQL is there such thing as a variable?
So if I have:
DECLARE @sql nvarchar(4000)
SELECT AdHocSQL from TheTable
SET @sql=AdHocSQL
Would this work? Is this syntatically correct? Or should I be doing this some other way?
The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.
Thanks for the information.
View 3 Replies
View Related
Oct 22, 2014
I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.
See below.
Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1
[Code] .....
View 2 Replies
View Related
Feb 26, 2008
I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure:
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27
A cursor with the name 'MyCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31
The cursor is already open.
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37
A cursor with the name 'vwCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38
The cursor is already open.
Here is the code causing the problem:
CREATE PROCEDURE [dbo].[BatchNonMons2] AS
BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */
/* Define every field you want to output */
DECLARE @Batch_Type int
DECLARE @Batch_Num int
DECLARE @Sys varchar (4)
DECLARE @Prin varchar (4)
DECLARE @Account_Num varchar(16)
DECLARE @Tran_Code varchar(3)
DECLARE @Clerk_Code varchar(3)
DECLARE @Memo_Text varchar(57)
DECLARE @SubTrans varchar(2)
DECLARE @SubTrans_2 varchar(1)
DECLARE @Terminal_Id varchar(3)
DECLARE @Op_Code varchar(2)
DECLARE @Last4 varchar(6)
DECLARE @vwSys varchar (4)
DECLARE @vwPrin varchar (4)
/* Define Cursor */
DECLARE MyCursor cursor
For SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number, Transaction_Code,
Clerk_Code, Memo_Text, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.tblNonMon_Daily_Trans
Open MyCursor; /*Works like an ARRAY*//*holds current record */
FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code
Declare vwCursor cursor
FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin
/* When @@Fetch_Status = 1 EOF has been reached */
WHILE @@Fetch_Status = 0
BEGIN
WHILE @vwSys = @Sys
BEGIN
Any input would be appreciated.
Thanx,
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1
View 5 Replies
View Related
Jul 20, 2005
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
View 4 Replies
View Related
May 2, 2008
I am entering the following:
declare @iAbsenceCategoryID int;
declare @dEntitlementRenewal datetime;
declare @iAbsenceUserID int;
declare @iMinutesUsed int;
declare @iMinutesNotUsed int;
declare @iEntitlement int;
declare cr cursor forward_only
for
select iabsencecategoryid,
dentitlementrenewal
from TblTAAbsenceCategories
open cr;
fetch next from cr into
@iAbsenceCategoryID, @dEntitlementRenewal
WHILE @@FETCH_STATUS = 0
BEGIN
fetch next from cr into
@iAbsenceCategoryID, @dEntitlementRenewal
if (DATEPART("Month",(GETDATE()))) = (DATEPART("Month", (@dEntitlementRenewal))) and (DATEPART("Day",(GETDATE()))) = (DATEPART("Day",(GETDATE())))
declare usercr cursor FORWARD_ONLY for
select iAbsenceUserID from TblTAAbsenceUsers
where iAbsenceCategoryID = @iAbsenceCategoryID
fetch next from usercr into
@iAbsenceUserID
WHILE @@FETCH_STATUS = 0
BEGIN
fetch next from usercr into
@iAbsenceUserID
select @iMinutesUsed = sum(iMinutesRequested)
from tblTAAbsenceHolidays
where iAbsenceUserID = 13 and iAbsenceActionID != 2
select @iEntitlement = iHolidayEntitlement from TblTAAbsenceUsers where iAbsenceUserID = 13;
set @iMinutesNotUsed = (@iEntitlement) - (@iMinutesUsed);
update TblTAAbsenceUsers
set iMinutesNotUsed = @iMinutesNotUsed
where iAbsenceUserID = @iAbsenceUserID;
END;
END;
close usercr;
close cr;
DEALLOCATE usercr;
DEALLOCATE cr;
and i get the following error message:
Msg 16916, Level 16, State 1, Line 26
A cursor with the name 'usercr' does not exist.
Msg 16916, Level 16, State 1, Line 57
A cursor with the name 'usercr' does not exist.
Msg 16916, Level 16, State 1, Line 60
A cursor with the name 'usercr' does not exist.
I cant see why its not allowing me to declare the usercr cursor, can anyone tell me where im going wrong? thanks!
View 13 Replies
View Related
May 30, 2007
I am trying to use variables that are declared in a report that I am created in SSRS 2005.
For example:
Declare @Month as int
Set @Month = case when month(getdate()) = 1 then 9 else month(getdate()) - 3
When I do this, I get an error message, 'The declare cursor SQL construct or statement is not supported.'
So how do i properly declare a variable that can be used through all my dataset?
Thanks, Iris
View 10 Replies
View Related
Sep 10, 2007
Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?
-Thanks,
Rich
View 5 Replies
View Related
Oct 2, 2006
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
View 5 Replies
View Related
Jan 31, 2006
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:
Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
View 1 Replies
View Related
Jan 31, 2006
I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>
Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_descas [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULLas [grouptype!1!scheme!element],
NULLas [grouptype!1!typevalue!element],
NULLas [description!1!short!element],
NULLas [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destinationas [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_codeas [description!2!short!element],
@p_term_descriptionas [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT
I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?
Thanks in advance!
View 1 Replies
View Related
Mar 28, 2008
hi guys i have a question ..... i would like to know how to delare a compound attribute in this case would be Birthinfo onto a sql server script. from this diagram . thanyou guyt in advance and any suggestion or help would be greatly appreciated .....
View 10 Replies
View Related
Jan 27, 2004
How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Person
View 7 Replies
View Related
Apr 30, 2004
HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-
DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER
BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID
WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-
EXEC MY_PROC
BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-
I AM USING :-
DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC
WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".
AND IF I USE :-
DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "
IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.
I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW
THANKS
View 14 Replies
View Related
Mar 12, 2008
So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?
DECLARE@Prefix VARCHAR(10),
@First_Name VARCHAR(50),
@Middle_Name VARCHAR(50),
@Last_Name VARCHAR(50),
@Suffix VARCHAR (10),
@Title VARCHAR (50);
SET@Prefix = ind_prf_code;
SET@First_Name = ind_first_name;
SET@Middle_Name = ind_mid_name;
SET@Last_Name = ind_last_name;
SET@Suffix = ind_sfx_code;
SET@Title = cst_title_dn;
select coalesce((@Prefix + ' '),'') +
ltrim(rtrim(@First_Name)) +
case when ltrim(rtrim(@Middle_Name)) = '' then ' ' else (' ' +
ltrim(rtrim(@Middle_Name)) + ' ') end +
ltrim(rtrim(@Last_Name)) +
coalesce(' ' + @Suffix, '') +
char(13) +
ltrim(rtrim(@Title)) +
char(13) as label
from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key
Thanks!
View 1 Replies
View Related
Jul 23, 2015
How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.
View 2 Replies
View Related
Feb 2, 2008
Hello,
I have a stored procedure that prompts the user for a year and
a month. Based on the month selected, I need to determine
the number of days in that month.
I have tried declaring variables to use to calculate number
of days in month and a counter, but they are not
working. When I try to run this it says I have to prompt user for
these as well. How do I declare and set a variable inside
a stored procedure.
Sample of my code is below...
REATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4),
@TheMonth integer,
@MyCount integer,
@NumDays integer
AS
SELECT
/* EMAILS (B) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 1)
) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 0)
) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode IS NULL)
) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
) AS Total_All_Emails,
/* PHONE CALLS (C) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 1)
) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 0)
) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode IS NULL)
) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
) AS Total_All_Calls,
/* FAXES (D) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 1)
) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 0)
) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode IS NULL)
) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
) AS Total_All_Faxes
FROM CampaignResponse A
GO
View 2 Replies
View Related
May 6, 2007
Hey
I have created a multi-statement
table valued function
alter
function
fn_x(@x int)returns
@tbl table
(
position int identity primary key, i
int)
as
begin
insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) insert
into
@tbl values
(@x) returnend
Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'"
The other solution is declaring
each udf separately as one statement udf.Thanks
View 2 Replies
View Related
Oct 11, 2014
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
View 4 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related
Dec 11, 2006
Hi I am trying to use the results of a datatable as an input to a ne SQL statement.
The datatable is created and populted by the source below after previously creating MyLookupCommand
MyLookupReader = MyLookupCommand.ExecuteReader(CommandBehavior.CloseConnection)
Dim Mydatatable = New DataTable()
Mydatatable.Load(MyLookupReader)
I now create a new SQL string allong the lines of
SELECT tableA.field1 FROM TableA INNERJOIN Mydatatable ON tableA.field2 = Mydatatable.Filed9
However I get the following error
Invalid object name 'Mydatatable'.
Any suggestions on how to resolve this.
possibley of interest is that I would have like to have done this in 1 SQL but the results in Mydatatable are from a SELECT DISTINCT and Field1 is a text filed that can't be used in a DISTINCT statement, hence using two statements.
Many thanks in advance
View 6 Replies
View Related
Jun 28, 2006
Hi,
I'm wondering if this is possible. I want to use a datatable as a parameter in my SQL query. The error I am getting is "No mapping exists from object type System.Data.DataColumn to a known managed provider native type." Any help is appreciated.
Here is the code.
Private Function GetAvailableTutors(ByVal qualifiedTutorsDataSet As DataTable) As DataTable
Dim TutorAvailabilityDataTable As DataTable = New DataTable()
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ASC_Schedule").ConnectionString)
Dim myCommand As SqlCommand = _
New SqlCommand("SELECT TutorID, TimeIndex FROM T_Hours WHERE SessionID > 0 AND TutorID IN (@TutorList) AND DateDropped > GetDATE() ORDER By T_QualifiedTutors.TutorID", myConnection)
myCommand.Parameters.AddWithValue("@TutorList", qualifiedTutorsDataSet.Columns.Item("TutorID"))
Dim myDataAdapter3 As SqlDataAdapter = New SqlDataAdapter()
myDataAdapter3.SelectCommand = myCommand
myDataAdapter3.Fill(TutorAvailabilityDataTable)
Return TutorAvailabilityDataTable
End Using
End Function
View 2 Replies
View Related
Aug 2, 2006
hi all
the usual way to bid a gridview is to data soursce
is there a way to do the folowing , creat a data table from the gridview shown valus " currunt page "
thanks
View 1 Replies
View Related
Dec 27, 2006
Hi,
I am experimenting to make a datatable in C# code in a page. This table should be a disconected table with Only valid for the present session.
I try the following code:
public partial class Default2 : System.Web.UI.Page
{
DataTable DT = new DataTable("TEST");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataColumn Col1 = new DataColumn("Col1");
Col1.DataType = typeof(Int32);
Col1.AllowDBNull = false;
DT.Columns.Add(Col1);
DataColumn Col2 = new DataColumn("Col2");
Col2.DataType = typeof(string);
Col2.AllowDBNull = true;
DT.Columns.Add(Col2);
DataColumn Col3 = new DataColumn("Col3");
Col3.DataType = typeof(DateTime);
Col3.AllowDBNull = true;
DT.Columns.Add(Col3);
GridView1.DataSource = DT;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
for (int i = 1; i < 20; i++)
{
DataRow MyRow = DT.NewRow();
MyRow["Col1"] = i;
DT.Rows.Add(MyRow);
}
}
}
For one reason or the other. if I click the button I get the message that Col1 dus not make part of the table TEST. It turns out that there are no columns added to the table. altroug the code in the page load part has been run. I suppose I have to do something with the session state to make my DataTable persistent, but I have no idea what. Can somebody help me out?
Thanks!
Rob
View 6 Replies
View Related
Jun 1, 2007
Is the merge method, what will work in this case ? I have two datatables with the exact same structure. How can I append the rows from table 2 onto the bottom of table 1 ? Is looping through the rows collection the only way ?
View 2 Replies
View Related
Sep 11, 2007
Hi,Is it possible to store data that is retrieved by SqlDataSource in a... let's say... DataTable? I mean, If I dragged and dropped a GridView and an SqlDataSource and then I set up those controls in page designer in Visual Studio, is it possible at some time later to retrieve the data retrieved by the SqlDataSource and then store the data in a DataTable?Best regards,Haris
View 7 Replies
View Related
Dec 6, 2007
Is there a built in way of converting a LINQ qurey result into a DataTable or DataView ?
View 4 Replies
View Related
Dec 26, 2007
hai This is rameshi had a small doubt in this public DataTable GetStatelist() { DataTable returnValue = null; SqlCommand selcommand = new SqlCommand(); selcommand.CommandType = CommandType.Text; selcommand.CommandText = "select * from USER_MASTER"; selcommand.Connection = _connectionobject; try { _connectionobject.Open(); DataSet ds = new DataSet(); SqlDataAdapter ada = new SqlDataAdapter(selcommand); ada.Fill(ds); _connectionobject.Close(); return returnValue; ; } catch (Exception exception) { if (_connectionobject.State == ConnectionState.Open) { _connectionobject.Close(); } throw new Exception(exception.Message); } if (_connectionobject.State == ConnectionState.Open) { _connectionobject.Close(); } return returnValue; ; } after compiling its show the return value is nullwhat is error in my codinghow i can return the value to the corresponding function if (!IsPostBack) { GridView1.DataSource=database.GetStatelist(); GridView1.DataBind(); } i am waiting for the result
View 4 Replies
View Related
Feb 12, 2008
I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)"
when i see the datable in "data visualizer" i find no rows.
It is confused eventhough it does not throw any error
View 3 Replies
View Related
