Right the problem is that i have an sql query that returns multiple rows and i want to be able to join all these rows into one so that i can use it. so for example
Rows returned
Row1
Row2
Row3
Rolled up rows
Row1 Row2 Row3
using sql query
please help me out
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies View RelatedHi:
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8462</Code>
<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>
</Error>
Why am i gettting this error after the conversation.
Thanks,
Pramod
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
Can anyone tell me what is wrong with this line of code..
I am missing someting on both line of code
exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
exec ("dbcc inputbuffer (" + @spid + ")")
I am getting the following errors...
Server: Msg 170, Level 15, State 1, Procedure sp_blocker_pss70, Line 146
Line 146: Incorrect syntax near 'print 'DBCC INPUTBUFFER FOR SPID '.
Server: Msg 170, Level 15, State 1, Procedure sp_blocker_pss70, Line 147
Line 147: Incorrect syntax near 'dbcc inputbuffer ('.
I can not get this code to work, I dey try to convert a non relational database, catcde na the name of the field in the non conversional database and i have a table in the relational database that called DuesCategory, that it's DuesCategoryname has the following fields,
Retired
On Leave
No Dues
Agency Fee Payer
Student
Full Dues
3/4 Dues
1/2 Dues
1/4 Dues
1/8 Dues
the fields in the non relational are not d same that is why i am trying to map it using the case statements, and since i will be converting alot of databases i what it to be based on which peremeter they put in.
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
This is all the code
CREATE Procedure _RunIndividual_5 @tablename varchar(100) as
Exec ('declare cur_individual cursor for
SELECT last, first, mi, address1, address2, city, state, zip,
bdate, sex, soc, mstat, depnum, hometel, status, sefdte, catcde,
cefdate, poscd, poedte, rfdlt, duespthru, paydeduc, classcode,
location, deptcode, tenuresub, ocertsub, bassalary, salarystep,
salarycol, startdate, initdate, seniordate, worktel, votecope, votedate,
voteded, polparty, polactiv, precinct, condst, sendst, asmdst, rschd,
loccode, extra1, extra2, extra3, extra4, extra5, extra6, extra7,
extra8, extra9, extra10, access, pctype, os, pclocation, internet,
email, show, county, localname, localnum, bargin, offtitle2, offtitle,
regvoter, reshigh, reselem, resunit, rescollege, employer, empnum, workfax,
offtitle3, work_phone, const_code, class_titl, faxnumber, constit
FROM Conversion.dbo.'+ @tablename+' order by last,first')
----Declare variables
declare @last nvarchar (50) ,
@first nvarchar (50) ,
@mi nvarchar (50) ,
@address1 nvarchar (50) ,
@address2 nvarchar (50) ,
@city nvarchar (50) ,
@state nvarchar (50) ,
@zip nvarchar (255) ,
@bdate nvarchar (255) ,
@sex nvarchar (255) ,
@soc nvarchar (255) ,
@mstat nvarchar (255) ,
@depnum float ,
@hometel nvarchar (255) ,
@status nvarchar (255) ,
@sefdte nvarchar (255) ,
@catcde nvarchar (255) ,
@cefdate nvarchar (255) ,
@poscd nvarchar (50) ,
@poedte nvarchar (255) ,
@rfdlt nvarchar (255) ,
@duespthru nvarchar (255) ,
@paydeduc nvarchar (255) ,
@classcode nvarchar (50) ,
@location nvarchar (255) ,
@deptcode nvarchar (255) ,
@tenuresub nvarchar (255) ,
@ocertsub nvarchar (255) ,
@bassalary float ,
@salarystep float ,
@salarycol nvarchar (255) ,
@startdate nvarchar (255) ,
@initdate nvarchar (255) ,
@seniordate nvarchar (255) ,
@worktel nvarchar (255) ,
@votecope float ,
@votedate nvarchar (255) ,
@voteded nvarchar (255) ,
@polparty nvarchar (255) ,
@polactiv nvarchar (255) ,
@precinct nvarchar (255) ,
@condst nvarchar (255) ,
@sendst nvarchar (255) ,
@asmdst nvarchar (255) ,
@rschd nvarchar (255) ,
@loccode int ,
@extra1 nvarchar (255) ,
@extra2 nvarchar (255) ,
@extra3 nvarchar (255) ,
@extra4 nvarchar (255) ,
@extra5 nvarchar (255) ,
@extra6 nvarchar (255) ,
@extra7 nvarchar (255) ,
@extra8 nvarchar (255) ,
@extra9 nvarchar (255) ,
@extra10 nvarchar (255) ,
@access bit ,
@pctype nvarchar (255) ,
@os nvarchar (255) ,
@pclocation nvarchar (255) ,
@internet nvarchar (255) ,
@email nvarchar (255) ,
@show bit ,
@county nvarchar (255) ,
@localname nvarchar (255) ,
@localnum nvarchar (255) ,
@bargin nvarchar (255) ,
@offtitle2 nvarchar (255) ,
@offtitle nvarchar (255) ,
@regvoter nvarchar (255) ,
@reshigh nvarchar (255) ,
@reselem nvarchar (255) ,
@resunit nvarchar (255) ,
@rescollege nvarchar (255) ,
@employer nvarchar (255) ,
@empnum nvarchar (255) ,
@workfax nvarchar (255) ,
@offtitle3 nvarchar (255) ,
@work_phone nvarchar (255) ,
@const_code nvarchar (255) ,
@class_titl nvarchar (255) ,
@faxnumber nvarchar (255) ,
@constit nvarchar (255) ,
@DuesCategoryName nvarchar (255) ,
@DuesCategoryName1 nvarchar (255) ,
@DuesCategoryName2 nvarchar (255) ,
@DuesCategoryName3 nvarchar(255) ,
@DuesCategoryName4 nvarchar(255) ,
@DuesCategoryName5 nvarchar(255) ,
@DuesCategoryName6 nvarchar(255) ,
@DuesCategoryName7 nvarchar(255) ,
@DuesCategoryName8 nvarchar(255) ,
@DuesCategoryName9 nvarchar(255) ,
@DuesCategoryName10 nvarchar(255) ,
@DuesCategoryName11 nvarchar(255) ,
--- other variables
@prefix varchar(5),@prefixid uniqueidentifier,@worksiteid uniqueidentifier,
@chapterid uniqueidentifier,@PaymentMethodName varchar(50),@PaymentMethodID uniqueidentifier,
@DuesCategoryID uniqueidentifier,@DuesCategoryLocalID uniqueidentifier,
@DeactivateReasonID uniqueidentifier,@DeactivateReasonLocalID uniqueidentifier,
@JobClassID uniqueidentifier,@LocalJobClassID uniqueidentifier,
@MaritalStatusID uniqueidentifier,@MemberStatusID uniqueidentifier,
@SubjectID uniqueidentifier,@PoliticalPartyID uniqueidentifier,
@EmployerID uniqueidentifier, @LocalUnionID uniqueidentifier,@LocalUnionNbr char(5),
@addressID uniqueidentifier,@StateTerritoryId uniqueidentifier,
@CountryId uniqueidentifier,@PoliticallyActiveTF bit,@IndividualId uniqueidentifier
begin
open cur_individual
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @prefix= case @sex when 'Male' then 'Mr.'
when 'Female' then 'Ms.'
when ' ' then null
end
select @prefixid=prefixid from prefix where prefixname=@prefix
select @worksiteid=worksiteid,@EmployerID=EmployerID from worksite where worksitenumber=@loccode
select @chapterid=chapterid from chapter where localunionid in (select localunionid from localunion)
select @PaymentMethodName= case @paydeduc when 'YES' then 'Payroll Deduction'
end
Select @PaymentMethodID=PaymentMethodID from PaymentMethod where PaymentMethodName=@PaymentMethodName
select @DuesCategoryID=DuesCategoryID from DuesCategory where DuesCategoryName=@catcde
WHILE (@@FETCH_STATUS <>-1)
BEGIN
select @DuesCategoryID= case @catcde when '@DuesCategoryName1' then 'Per Diem'
when '@DuesCategoryName2' then 'Retired.'
when '@DuesCategoryName3' then 'On Leave.'
when '@DuesCategoryName4' then 'No Dues.'
when '@DuesCategoryName5' then 'Agency Fee Payer.'
when '@DuesCategoryName6' then 'Student.'
when '@DuesCategoryName7' then 'Full Dues.'
when '@DuesCategoryName8' then '3/4 Dues.'
when '@DuesCategoryName9' then '1/2 Dues.'
when '@DuesCategoryName10' then '1/4 Dues.'
when '@DuesCategoryName11' then '1/8 Dues.'
when ' ' then null
end
select @DeactivateReasonID=DeactivateReasonID,@Deactivate ReasonLocalID= DeactivateReasonLocalID
from DeactivateReasonLocal
where DeactivateReasonLocalName=@rfdlt
select @MaritalStatusID=MaritalStatusID from MaritalStatus where MaritalStatusName=@mstat
select @MemberStatusID=MemberStatusID from MemberStatus where MemberStatusName=@Status
select @SubjectID=SubjectID from Subject where SubjectName=@tenuresub
select @PoliticalPartyID=PoliticalPartyID from PoliticalParty where PoliticalPartyName=@polparty
select @LocalUnionID=LocalUnionID,@LocalUnionNbr=LocalUni onNbr from Localunion
select @StateTerritoryId=StateTerritoryId,@CountryId=Coun tryId from Address where StateCode=@State
Insert into Address values (newid(),@Address1,@Address2,@City,null,@State,@zi p,
null,null,'USA',@StateTerritoryId,@CountryId,NULL, 0,@county,NULL,0,default,default,
default,default,default,@LocalUnionNbr,@LocalUnion Id)
Select @AddressId=AddressId from Address where uid=@@Identity
select @LocalJobClassId=LocalJobClassId from LocalJobClass where LocalJobClassCode=@classcode
---- Inserting Individual rows
insert into individual values (
newid(), @soc, null, @last,@First, @mi,null,CONVERT(DATETIME,@bdate),@PrefixId,@sex,
null, 1, @MaritalStatusId,null,null,0,null,null,null, null,@WorksiteId,null,
@JobClassId,@LocalJobClassId, CONVERT(DATETIME,@STARTDATE), null, null, CONVERT(DATETIME,@seniordate), null, @location,@deptcode,
null,null, 0, @condst, @regvoter, null, @sendst, @rschd,@asmdst, @PolActiv,@PoliticalPartyId,
@precinct,null, null, 0, @SubjectId, null,default, 0,null, @Hometel,null, @Worktel,null,
@MemberStatusId, null,null, @Email,null,@ChapterId,@DuesCategoryId,
null, @AddressId, CONVERT(DATETIME,@CEFDATE),0 , NULL,@PaymentMethodId,@DUESPTHRU,
NULL,CONVERT(DATETIME,@InitDate), @DuesCategoryLocalId,null,@DeactivateReasonId,
null, null, @LocalUnionNbr, null, null, @LocalUnionId, null, null, 0, null,
null, @WorkFax, null, null, null, null, @DeactivateReasonLocalId, null,
null, null, null, null,null,null,null,null,null,null,null,null,null, null,
null,null,null,null, null,null,null,null,null,null,default,default,defa ult,default,
default,@EmployerId, null, null, null,null,null,null,null,null,default,default,
default,default,default,default,default,default)
select @IndividualId=IndividualId from Individual where LastName=@Last and FirstName=@first and
MiddleName=@mi and ssn=@soc and dob=@bdate and Gender=@sex
insert into COPE values (newid(),null,@IndividualId,null,@votecope,convert (datetime,@votedate),
default,default,default,default,default,@LocalUnio nNbr,@LocalUnionId)
Select @prefixId=null
fetch from cur_individual into @last,@first, @mi,@address1,@address2,@city,@state,
@zip,@bdate,@sex,@soc,@mstat,@depnum,@hometel , @status , @sefdte ,
@catcde , @cefdate , @poscd , @poedte ,
@rfdlt , @duespthru , @paydeduc ,
@classcode ,@location ,@deptcode ,
@tenuresub ,@ocertsub ,@bassalary ,@salarystep ,
@salarycol ,@startdate ,@initdate ,
@seniordate ,@worktel ,@votecope ,@votedate ,
@voteded ,@polparty ,@polactiv ,
@precinct ,@condst ,@sendst ,
@asmdst ,@rschd ,@loccode ,@extra1 ,
@extra2 ,@extra3 ,@extra4 ,@extra5 ,
@extra6 ,@extra7 ,@extra8 ,@extra9 ,
@extra10 ,@access ,@pctype ,@os ,
@pclocation ,@internet ,@email ,
@show ,@county ,@localname ,@localnum ,
@bargin ,@offtitle2 ,@offtitle ,
@regvoter ,@reshigh ,@reselem ,
@resunit ,@rescollege ,@employer ,
@empnum ,@workfax ,@offtitle3 ,
@work_phone ,@const_code ,@class_titl ,
@faxnumber ,@constit
END
CLOSE CUR_individual
DEALLOCATE CUR_individual
END
update individual set homeaddressid=addressid where addressid is not null -- set default address as home
Thanks
Hi, I like to write a function similar to sum(), does anyone have the code for it? what I like to do is write a function that concatenate a group of strings those having the same group as defined by a user and return a string as varchar datatype.
Thanks,
Benjamin
Is there a store procedure to determine if you have outlook on your server to send mail.
Lystra
I have this code in a DTS package which is:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
drop proc VerifyRequest
go
/*
* VerifyRequestTransfer - run a command that looks for @filename in the output
*
*
* This proc looks for a file matching 'tbl_%' in the output of an ftp command.
* The output message reports success/failure of transfer.
* A return code of 1 indicates success
* Return code = 0 indicates failure.
*
* How it Works:
* ftp is executed using @ftpcommandfile as input to the -s parameter.
* The output of ftp is written to a table
* The table is cleared of garbage records
* The count of records matching @filename is checked
* if the count = 1 then there success!
*/
CREATE proc VerifyRequest
@filename varchar(200),
@ftpcommandfile varchar(1000)
as
declare @rc int
declare @rows int, @errcode int, @rows2 int
set @rc = 0
set @rows = -9998
set nocount on
-- build a table containing list of files in Request directory
if exists (select * from tempdb.dbo.sysobjects where name='RequestFiles' and type = 'U')
drop table tempdb.dbo.RequestFiles
create table tempdb.dbo.RequestFiles (
line_no int identity(1,1) Primary key clustered,
Filename varchar(200) NULL
)
declare @cmd varchar(2000)
--Get list of remote files
set @cmd = 'ftp -i -s:' + @ftpcommandfile
Insert into tempdb.dbo.RequestFiles (Filename)
Exec master.dbo.xp_cmdshell @cmd
select @rows = @@rowcount, @errcode = @@error
if @rows = 0 OR @errcode != 0
begin
set @rc = -1
goto done
end
-- remove non-files and already processed files( there might have been old files on remotesystem )
Delete
From tempdb.dbo.RequestFiles
Where coalesce(Filename, '') not like '%tbl_%'
-- check count
select @rows = (select count(*) from tempdb.dbo.RequestFiles
Where tempdb.dbo.RequestFiles.Filename like '%'+@filename+'%' )
if @rows = 1
set @rc = 1
done:
return @rc
go
Now the message I am getting is:
The task reported failure on execution. Procedure 'VerifyRequest' expects Parameter '@filename', which was not supplied.
I don't know where to set this parameter.
I hope someone can help.
Thanks
Lystra
Hi,
The script below is just the beginning of something a bit more complicated, which I'm not sure how I'll explain so depending on the amount of posts I'll explain further.
For the time being can anybody see if there's anything wrong with how I've scripted the SP below particulary the loop (Check thread - Stored Procedure Programming)?
Thanks for your input in advance.
:)
CREATE PROCEDURE newimr_sp_duplicate_report
(
@countryid as INT,
@marketsectorid as INT, -- marketsector to transfer from
@specialreportid as INT
)
AS
Declare
@other_marketsectorid as INT
if (@marketsectorid = 1)
set @other_marketsectorid = 2
else
set @other_marketsectorid = 1
declare topicids cursor for
select id, description from tbl_topics where specialreportid = 7 and marketsectorid = @marketsectorid
open topicids
while 1 = 1
begin
declare
@other_topicid as int,
@description as varchar(255)
fetch next from topicids into @other_topicid, @description
if @@fetch_status <> 0
break
select @description
end
close topicids
deallocate topicids
GO
I'm fairly new to SQL
Should be easy for someone
Example dataset:
Table1
ID Code
1 A
1 B
2 A
Result set I want
ID Code
2 A
so I only want ID's that = A but don't also have B
I was trying intersects but T-SQL has issues with it.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '{'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.
What does this mean?
This is what I am trying to do.
Which students are enrolled in Database (Introduction to Relational Databases) and Networking? (Hint: Use the SECTION_ID for each class so you can determine the answer from the IS_REGISTERED table by itself.)
Student_ID Student_Name
38214 Letersky
54907 Altvater
66324 Aiken
70542 Marra
STUDENT (Student_ID, Student_Name)
Faculty_ID Faculty_Name
2143 Birkin
3467 Berndt
4756 Collins
FACULTY (Faculty_ID, Faculty_Name)
Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001
IS_REGISTERED (Student_ID, Section_ID, Semester)
Course_ID Course_Name
ISM 3113 Syst Analysis
ISM 3112 Syst Design
ISM 4212 Database
ISM 4930 Networking
COURSE (Course_ID, Course_Name)
Section_ID Course_ID
2712 ISM 3113
2713 ISM 3113
2714 ISM 4212
2715 ISM 4930
SECTION (Section_ID, Course_ID)
Faculty_ID Course_ID Date_Qualified
2143 ISM 3112 9/1988
2143 ISM 3113 9/1988
3467 ISM 4212 9/1995
3467 ISM 4930 9/1996
4756 ISM 3113 9/1991
4756 ISM 3112 9/1991
IS_QUALIFIED (Faculty_ID, Course_ID, Date_Qualified)
This is what I did and got the error message at the top. What did I do wrong?
Select * FROM Student WHERE Student_ID IN{
Select Student_ID FROM Is_Registered WHERE Section_ID IN(
Select Section_ID FROM Section WHERE Course_ID IN(
Select Course_ID FROM Course WHERE Course_ID LIKE '964212')))
AND Student_ID IN(
Select Student_ID FROM Is_Registered WHERE Section_ID IN(
Select Section_ID FROM Section WHERE Course_ID IN(
Select Course_ID FROM Course WHERE Course_ID LIKE '964930')))
albanie
Hi,
How can write this in a query:
If @maxLastUpdate is later than LastUpdate, call stored procedure usp_RubricReportUpdate @DataYears, @County, @Distirct to update the report.
Hi,
How can I code this:
Set the values of GoalMet comparing the LocalPerf value and SppTarget.Target value with the CompareMethod. If CompareMethod = 1, then LocalPerf >= Target means GoalMet = 1; othwerwise, LocalPerf <= Target means GoalMet = 1.
Thanks in advance.
Hello everyone!
I need some help with my code. It says that (the blood marked) is wrong.
I want to enter a kund number and it will show his first and last name and what product he/she has ordered.
SELECT Kunder.Kundnr, Kunder.Fnamn, Kunder.Enamn, Lager.Artnr
FROM Kunder
INNER JOIN Lager
ON Lager.Artnr=Order.Artnr AND Kunder.Kundnr=Order.Kundnr
WHERE Kunder.Kundnr= [Ange Kundnr];
I appreciate all the help I get.
Deeo
Is it possible to run this code in a stored procedure ? if so how ? TIA
Dim sw As StreamWriter = New StreamWriter("Transactions.txt")
Dim line As String
Try
cn.Open()
cnCmd.CommandText = "Select description, document, part_num, cur_qty, cond_code, generic1, generic2 from tblinventory"
cnCmd.Connection = cn
drDB = cnCmd.ExecuteReader
Dim description As String, document As String
Dim part_num As String, cur_qty As String
Dim cond_code As String, generic1 As String
Dim generic2 As String
While drDB.Read
description = drDB.Item("description")
document = drDB.Item("document")
part_num = drDB.Item("part_num")
cur_qty = drDB.Item("cur_qty")
cond_code = drDB.Item("cond_code")
generic1 = drDB.Item("generic1")
generic2 = drDB.Item("generic2")
line = description.PadRight(30) & document.PadRight(25) & "LINE," _
& part_num.PadLeft(10, "0") & ",RIC,UI," & cur_qty.padleft(5, "0") _
& "," & cond_code.PadRight(3) & "," & document.PadRight(25) _
& "," & generic1.PadRight(20) & "," & generic2.PadRight(20)
sw.WriteLine(line) ' change padding parameter accordingly!
End While
drDB.Close()
cn.Close()
Does anyone know if the source code for the ASP.Net application thatcomes with Reporting Services is available for viewing somewhere?
View 1 Replies View RelatedHello.
I wrote somw code in VB in the code section of a report.
I saw in a few places thet u can wrtie in C#.
Is it true? When I tried I got an error.
Thanks
Hi,
This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.
The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.
Create table #TmpResults
( rowid int IDENTITY,
PlanId int,
PlanName varchar(200),
InvestmentName varchar(500),
InvestmentType char(1),
IsPortfolioFundOnly bit,
InvestmentId int)
Declare @PlanId int
set @PlanId = 682
Insert Into #TmpResults
Exec ICCStatements..rpt_SelectInvestments @PlanId
I am also creating a Internal parameter called Split which is an integer which has the following expression
select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.
but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.
Some one please please help me
So what can i do in order to by pass it.
Regards,
Karen
Hi everyone,
When I use the following talbe for my database, while adding this database a full record, it always give this error;
String or binary data would be truncated.The statement has been terminated.
The bad thing is that I can not overcome this error.
So what did I do wrongly is the following ?
CREATE TABLE Muhasebe
(
KimlikNo int CONSTRAINT PK_Kimlik PRIMARY KEY CLUSTERED NOT NULL,
Ad nvarchar NOT NULL,
GirişTarih datetime CONSTRAINT CH_Tarih CHECK (GirişTarih > '1/1/2006' AND GirişTarih < '06/07/2006'),
Bütçe money NOT NULL)SELECT * FROM Muhasebe
The above error causing code;
INSERT Muhasebe VALUES(12, 'mert','3/3/2006',120.000)
I did not write this stored procedure but have been asked to edit the code and display new data. The column name is H.BilletUOM. It only contains only three values. B71,B72, and B73. I was able to select the column in the stored procedure easily so the data is available for the report but the next step is where I am having difficulty. If the value is B71 then I want to display 28ft. B72 display 34ft. B73 display 40ft. Can someone please take a look at this code and try to help me figure this out. I highlighted the H.BilletUOM in red so you can quickly see where I am selecting this column. Any assistance is greatly appreciated. I am new to SQL and have not started classes yet. Thanks again.
CREATE PROCEDURE SP_Melt_HeatReport_HeatList
@HeatNo varchar(50) = NULL,
@Date varchar(50) = NULL
AS
UPDATE WebPageData
SET Hits = Hits + 1
WHERE SPName = 'SP_Melt_HeatReport_HeatList'
DECLARE @Site varchar(100) SET @Site = dbo.UDF_SystemParameter('Site')
DECLARE @SiteName varchar(100) SET @SiteName = dbo.UDF_SystemParameter('SiteName')
DECLARE @MinHeatNo int SET @MinHeatNo = CSCMelting.dbo.UDF_SystemParameter('MinClevelandHeat')
DECLARE @MaxHeatNo int SET @MaxHeatNo = CSCMelting.dbo.UDF_SystemParameter('MaxClevelandHeat')
DECLARE @HeatsPerPage int SET @HeatsPerPage = 40
IF @Date IS NOT NULL
BEGIN
SELECT @MinHeatNo = MIN(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @Date AND HeatNo BETWEEN @MinHeatNo AND @MaxHeatNo
SELECT @MaxHeatNo = MAX(HeatNo)
FROM CSCMelting.dbo.MS_HeatLog
WHERE RptDate = @Date AND HeatNo BETWEEN @MinHeatNo AND @MaxHeatNo
SELECT @HeatNo = @MinHeatNo
SELECT @HeatsPerPage = (@MaxHeatNo - @MinHeatNo) / 10 + 1
END
IF PATINDEX('%,%', @HeatNo) > 0
SET @HeatNo = SUBSTRING(@HeatNo ,PATINDEX('%,%', @HeatNo) + 1, 100)
DECLARE @ColorRed varchar(7) SET @ColorRed = '#FF0000'
DECLARE @ColorRedDark varchar(7) SET @ColorRedDark = '#AA0000'
DECLARE @ColorBlue varchar(7) SET @ColorBlue = '#0000FF'
DECLARE @ColorGreen varchar(7) SET @ColorGreen = '#008800'
DECLARE @ColorYellow varchar(7) SET @ColorYellow = '#999900'
DECLARE @ColorWhite varchar(7) SET @ColorWhite = '#FFFFFF'
DECLARE @ColorBlack varchar(7) SET @ColorBlack = '#000000'
DECLARE @ColorGray varchar(7) SET @ColorGray = '#eeeeee'
IF @HeatNo IS NULL OR @HeatNo = '' OR ISNUMERIC(@HeatNo) = 0 OR @HeatNo = '0'
SELECT @HeatNo = MAX(HeatNo) - (@HeatsPerPage * 10) + 10
FROM CSCMelting.dbo.MS_HeatLog
WHERE HeatNo BETWEEN @MinHeatNo AND @MaxHeatNo
SELECT Tag = 1, Parent = NULL,
[Title!1!Title1] = 'Charter Steel ' + @SiteName + ' Melting',
[Title!1!Title2] = 'LEVEL II HEAT REPORTS'
FOR XML EXPLICIT
SELECT Tag = 1, Parent = NULL,
[PageVars!1!Site] = @Site,
[PageVars!1!WebPageID] = 20500,
[PageVars!1!HeatNo] = @HeatNo,
[PageVars!1!HeatNoNext] = @HeatNo + (@HeatsPerPage * 10) ,
[PageVars!1!HeatNoPrev] = @HeatNo - (@HeatsPerPage * 10) ,
[PageVars!1!HeatNoLast] = 0,
[PageVars!1!HomeLink] = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20500'
FOR XML EXPLICIT
SELECT HeatNo,
Job = JOB_NUMBER,
RowColor = CASE WHEN (Heatno /10) % 2 = 0 THEN @ColorGray ELSE @ColorWhite END,
Grade = CONVERT(varchar(20), GRADE + ' ' + CS_CMDESC),
Status = CASE Status WHEN 4 THEN
CASE WHEN ChemCert = 1 THEN 'Certified'
WHEN EXISTS(SELECT * FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo AND Location = '99') THEN 'Inspection'
WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN 'CCM'
ELSE 'Inspected'
END
WHEN 3 THEN 'CCM'
WHEN 2 THEN 'LRF'
WHEN 1 THEN 'EAF'
ELSE ''
END, --+ CASE WHEN EXISTS (SELECT * FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo) THEN ' (PB)' ELSE '' END,
H.NonConforming,
PourBackTons = (SELECT MIN(PourBackAmt) / 2000 FROM CSCMelting.dbo.MS_FceHeatLog WHERE PourbackHeatNo = H.HeatNo),
Location = (SELECT MAX(Location) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntCounted = H.BilletsMade1 + H.BilletsMade2 + H.BilletsMade3 + H.BilletsMade4,
ScrapCount = (SELECT ScrapCount FROM CSCMelting.dbo.VW_Prod_ScrapBilletsByHeat Sc WHERE Sc.Heatno = H.HeatNo),
CntInv = (SELECT COUNT(*) FROM CSCMelting.dbo.MS_BilletInventory WHERE HeatNo = H.HeatNo),
CntRolled = (SELECT SUM(Quantity) FROM CSCRolling.dbo._Lot WHERE Heat = H.HeatNo),
CntSched = (SELECT SUM(IC_QUANTITY) FROM CSCRolling.dbo._Lot_Scheduled WHERE IC_LOT_NUMBER = H.HeatNo),
CCM.SEQ_COUNTER,
SeqHeat = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER AND CCM2.REPORT_COUNTER <= R.REPORT_COUNTER), --CCM.SEQ_HEAT_COUNTER,
SeqTotal = (SELECT COUNT(*) FROM CSCMelting.dbo.REP_CCM CCM2 WHERE CCM2.SEQ_COUNTER = CCM.SEQ_COUNTER),
RptDate = CONVERT(varchar(10), RptDate, 120),
EndTapTime = H.EndTapTime,
H.BilletUOM,
ConsumptionsLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20501&HeatNo=' + CONVERT(varchar(20), HeatNo),
FCERepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 400),0),
FCERepCntLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20100&HeatNo=' + CONVERT(varchar(20), HeatNo),
-- VADRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
-- VADRepCntLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
LRFRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 600),0),
LRFRepCntLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
VODRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 800),0),
VODRepCntLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20200&HeatNo=' + CONVERT(varchar(20), HeatNo),
CCMRepCnt = ISNULL((SELECT MAX(REPORT_COUNTER) FROM CSCMelting.dbo.REPORTS WHERE Heat_ID = H.HeatNo AND Area_ID = 1100),0),
CCMRepCntLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20300&HeatNo=' + CONVERT(varchar(20), HeatNo),
RunoutOrderLink = 'http://mesweb/xml.aspx?Site=' + @Site + '&WebPageID=20301&HeatNo=' + CONVERT(varchar(20), HeatNo)
INTO #Heats
FROM CSCMelting.dbo.MS_HeatLog H
LEFT JOIN CSCMelting.dbo.REPORTS R ON R.HEAT_ID = H.HeatNo AND R.AREA_ID = 1100
LEFT JOIN CSCMelting.dbo.REP_CCM CCM ON CCM.REPORT_COUNTER = R.REPORT_COUNTER
WHERE HeatNo >= @HeatNo AND HeatNo < @HeatNo + (@HeatsPerPage * 10) AND HeatNo % 10 = 0
SELECT *,
EndTapTimeStr = CONVERT(varchar(25), EndTapTime, 100),
MissChemStr = CASE WHEN NonConforming = 1 THEN 'Y' ELSE '' END,
PourBackTonsStr = CASE WHEN PourBackTons = 0 THEN '' ELSE CONVERT(varchar(10), PourBackTons) + 'tn' END,
CntCountedStr = NULLIF(CntCounted, 0),
ScrapCountStr = NULLIF(ScrapCount, 0),
CntInvStr = NULLIF(CntInv, 0),
CntRolledStr = NULLIF(CntRolled, 0),
CntSchedStr = NULLIF(CntSched, 0),
HeatSeqStr = '' + char(CONVERT(int, SEQ_COUNTER) % 26 + 65) + ' - ' + CASE WHEN SeqTotal <= 1 THEN 'single' ELSE CONVERT(varchar(2), SeqHeat) + ' of ' + CONVERT(varchar(2), SeqTotal) END,
StatusColor = CASE
WHEN PourBackTons > 0 THEN @ColorBlack
WHEN Status = 'EAF' THEN @ColorBlue
WHEN Status = 'LRF' THEN @ColorGreen
WHEN Status = 'CCM' THEN @ColorYellow
WHEN Status = 'Inspection' THEN @ColorRedDark
WHEN Status = 'Inspected' THEN @ColorRed
WHEN Status = 'Certified' THEN @ColorBlack
ELSE ''
END
FROM #Heats Heats
ORDER BY HeatNo
FOR XML AUTO
print @HeatNo + (@HeatsPerPage * 10) + 10
DROP TABLE #Heats
GO
Hi
My name is Kavya. I am actually new to SQL server Programming so I am trying my best to understand the concepts. I have this issue with respect to a code , I mean i am not getting the logic clear. Its like this: In a table say T with columns no name , smthng like dis
no name
1 Angelina
2 Brad
3 Jennifer
Now using a procedure (call it p1) i shud obatin the name corresponding to no 1 and print that output
say while executing procedure i give
p1 '1'
and i get angelina now i shud print output angelina as
A
An
Ang
Ange
Angel
Angeli
Angelin
Angelina
but i am not getting the exact logic, I am aware that i shud use the functions substring,len and the loop while but i am unsure as to how many parameters i shud have and how exactly shud i manipulate my loop,parameters and functions to obtain the output in that format. can someone help me?
Hi,
When I execute the following procedure, SQL Server creates an error called 'must declare @table variable'. So I did not overcome this error.
Would you please help me ?
CREATE PROCEDURE erase
@column nvarchar(25),
@erasable nvarchar (25),
@table nvarchar(25)
AS
delete from @table
where @column=@erasable
I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?
I am writting a very simple ASP.NET code, I am simlply trying to
establish a connection with the database in SQL Server 2000 and than
closing the connection but still I am getting an exception,the name of
the database is mydatabase, SQL Server 2000 is running in the windows
authenticationfiltered=SSPI. The name of SQL Server 2000 running
onfiltered=xyz,
I checked the name from SQL Server Service Manager.Also I would like to
state here that I also tryed to establish a connection using C# and the
connection was successfully establish which means that there is no
problem with SQL Server also I sucessfully established the connection
with MS Access and my ASP.NET application which proves that there is no
problem with IIS also.Also please check that the connection string
which I am providing is correct or not.The exception which I am getting
is as follows:
///////////////////////////////////////////////////////////////////////////////
An exception occured while establishing connection
Login failed for user 'xyzASPNET'.
////////////////////////////////////////////////////////////////////////////
Please also note that I have a user with the name xyz/Administrator in
the logins of Sql Server 2000. Also note one more thing that the name
of my PC is xyz, I think that there is some problem with the connection
string which i am providing so please especially check the connection
string that either I have missed some thing or some thing has gone
wrong,also please tell that are there any security restructions for
ASP.NET to access SQL Server 2000 or some thing like that which I have
to remove.The code is as follows:
//////////////////////////////////////////////////////////////////////////////////////
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
void Page_Load(Object sender , EventArgs e)
{
try
{
SqlConfiltered=new SqlConnection("server=xyz;Integrated Security=SSPI;database=mydatabase;");
connection.Open();
Response.Write("<b>Connection is successfully established</b>");
connection.Close();
}
catch(Exception ex)
{
Response.Write("<b>An exception occured while establishing connection</b><br>"+" "+ex.Message.ToString());
}
}
</Script>
The same above code is also present in the attachement file data.aspx ,
please guide me where I am making mistake and please tell me that how
can I correct this mistake so that the code executes correctly.Also
please tell that what things should be included in the connection
string and also please check the connection string of this code.
my code was working fine but i changed a few things and now i cant figure it out.
the problem is that when i click my edit button none of the items go into the listbox. i added a label to track th error and i narrowed it done to a smaller ssection. Please help
//-----------------------------------------------------------------------------
/////////////////////////////////////////////////////////////////////////////////
protected void EditButton1_Click(object sender, EventArgs e)
{
int counter = 0; //counter for inserting into array
bool Validate = checknumberValidation(TextBox1.Text.ToString());
if (Validate == false)
throw new Exception(InvalidCheckNumber + TextBox1.Text);
Label4.Text = GridView1.Rows.Count.ToString(); //--------RETURNS 0-----SHOULD BE NUMBER OF ITEMS----//
//get # of checkboxes and set array size;
int x = 0;
x = SetStringArraySize(); //set x to number of checkboxes that are checked
String[] updateString = new String[x]; //create string array of size x
Label4.Text = GridView1.Rows.Count.ToString(); //--------RETURNS 0-----SHOULD BE NUMBER OF ITEMS----//
//for loop that will insert update statements into array
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
bool isChecked = ((CheckBox)row.FindControl("UpdateCheckBox1")).Checked;
if (isChecked == true)
{
if (counter == 0)
updateString[counter] = "[batchid] = '" + GridView1.Rows[i].Cells[3].Text + "' AND [loanid] = '" + GridView1.Rows[i].Cells[5].Text + "' AND [historycounter]='" + GridView1.Rows[i].Cells[8].Text + "'";
if (counter > 0)
updateString[counter] = "[batchid] = '" + GridView1.Rows[i].Cells[3].Text + "' AND [loanid] = '" + GridView1.Rows[i].Cells[5].Text + "' AND [historycounter]='" + GridView1.Rows[i].Cells[8].Text + "'";
counter++;
}
}
//Add items to listbox and set listbox to visible.
Panel1.Visible = true;
PanelGridView.Visible = false;
Panel2.Visible = false;
updateCount.Text = updateString.Length.ToString();
updateCheckNumber.Text = TextBox1.Text;
int q = 0;
while (q < updateString.Length)
{
ListBox1.Items.Add(updateString[q]);
q++;
}
//Label1.Text = temp.Length.ToString(); //Test print to label
}
//-----------------------------------------------------------------------------
//COMPLETE
protected int SetStringArraySize()
{
Label4.Text = GridView1.Rows.Count.ToString(); //--------RETURNS 0-----SHOULD BE NUMBER OF ITEMS----//
int count = 0;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
bool isChecked = ((CheckBox)row.FindControl("UpdateCheckBox1")).Checked;
if (isChecked == true)
{
count++;
}
}
Label4.Text = GridView1.Rows.Count.ToString(); //--------RETURNS 0-----SHOULD BE NUMBER OF ITEMS----//
return count;
//returns number of checkboxes that are checked
}
/////////////////////////////////////////////////////////////////////////////////
//-----------------------------------------------------------------------------
I am using MVWD 2005 Express and Sql Server 2005 Express. I am using SqlDataSource control and GridView control to disply a ata table, and let user to input data into the database through dropdownlist and TextBox. When I run the application I always get exception:"Conversion failed when converting character string to smalldatetime data type." I could not find anything wrong. Could anybody out there help me find out what's wrong with my code. Much appreciate it.Here is the code:<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"DataSourceID="SqlDataSource2" DataKeyNames="DocumentID"><Columns><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /><asp:BoundField DataField="DocumentName" HeaderText="DocumentName" ReadOnly="True"SortExpression="DocumentName" /><asp:BoundField DataField="DateInput" HeaderText="Date" SortExpression="DateInput" /><asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" /><asp:BoundField DataField="DocumentCategory" HeaderText="Category" SortExpression="DocumentCategory" /><asp:CheckBoxField DataField="TopDoc" HeaderText="TopDoc" SortExpression="TopDoc" /></Columns><EmptyDataTemplate>There is no data to be displayed!</EmptyDataTemplate></asp:GridView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>" DeleteCommand="DELETE FROM [Documents] WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"InsertCommand="INSERT INTO [Documents] ([DocumentName], [DateInput], [Comments], [DocumentCategory], [TopDoc]) VALUES (@DocumentName, @DateInput, @Comments, @DocumentCategory, @TopDoc)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Documents]"UpdateCommand="UPDATE [Documents] SET [DocumentName] = @DocumentName, [DateInput] = @DateInput, [Comments] = @Comments, [DocumentCategory] = @DocumentCategory, [TopDoc] = @TopDoc WHERE [DocumentID] = @original_DocumentID AND [DocumentName] = @original_DocumentName AND [DateInput] = @original_DateInput AND [Comments] = @original_Comments AND [DocumentCategory] = @original_DocumentCategory AND [TopDoc] = @original_TopDoc"><DeleteParameters><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></DeleteParameters><UpdateParameters><asp:Parameter Name="DocumentName" Type="String" /><asp:Parameter Name="DateInput" Type="DateTime" /><asp:Parameter Name="Comments" Type="String" /><asp:Parameter Name="DocumentCategory" Type="Int32" /><asp:Parameter Name="TopDoc" Type="Boolean" /><asp:Parameter Name="original_DocumentID" Type="Int32" /><asp:Parameter Name="original_DocumentName" Type="String" /><asp:Parameter Name="original_DateInput" Type="DateTime" /><asp:Parameter Name="original_Comments" Type="String" /><asp:Parameter Name="original_DocumentCategory" Type="Int32" /><asp:Parameter Name="original_TopDoc" Type="Boolean" /></UpdateParameters><InsertParameters><asp:ControlParameter Name="Comments" Type="String" ControlID="TextBox2" /><asp:ControlParameter Name="DocumentCategory" Type="Int32" ControlID="DropDownList1" PropertyName="SelectedValue" /><asp:ControlParameter Name="TopDoc" Type="Boolean" ControlID="RadioButton1" PropertyName="Checked" /></InsertParameters></asp:SqlDataSource>Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.ClickDim savePath As String = "C: empuploads"Dim fileName As String = FileUpload1.FileName Dim dt As DateTime = DateTime.Now SqlDataSource2.InsertParameters.Add("DocumentName", fileName) SqlDataSource2.InsertParameters.Add("DateInput", dt)SqlDataSource2.Insert()If (FileUpload1.HasFile) ThensavePath += FileUpload1.FileName FileUpload1.SaveAs(savePath) Label1.Text = "Your file was uploaded successfully."DisplayFileContents(FileUpload1.PostedFile)ElseLabel1.Text = "You did not specify a file to upload."End IfEnd Sub
View 4 Replies View RelatedHello,I Have a code:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [shop_clients] ([ID], [IMIE]) VALUES (@ID, @IMIE)"> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="IMIE" Type="String" /> </InsertParameters> </asp:SqlDataSource> SO this code It will allow me insert to database SQL textbox - name ?If yes How I can:If I click the button My textbox - name, insert to databasePlease me help :)
View 1 Replies View RelatedHow do I programically (in the code-behind-file) assign a value from the database to a variable using a sqldatasource?...
I will do something like this:
Dim MyCity as string = SqlDataSource.MyCityColumnInTheDatabase
(The sqldatasource select everything in my "CityTable")
Hope you understand what I mean...