Howto Get The Count Value?

Jul 25, 2006

Hello!

I have a question about the SqlDataSource object.
If i make an SqlDataSource with the following sql statement: "SELECT COUNT(id) AS recordCount FROM tblCategory"

How do i get the recordCount value to a Variable.

Im writing in C#.

<asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT COUNT(id) AS recordCount FROM [tblCategory]">
</asp:SqlDataSource>

View 3 Replies


ADVERTISEMENT

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

No Logs - Howto ?

Nov 27, 2006

Hello,

I would like to avoid any logs. Meaning that logs are cleared ones transactions are executed successfully.

Is there any way to achieve this?

Thanks for any feedback!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 20 Replies View Related

HowTo Add ASPNET User?

Jul 6, 2005

The objective is to configure on a dev machine. Selecting New Database User is the easy part as the ***ASPNET user can be selected from the drop down listbox. What follows and what to do next is a myriad of choices. What needs to be done next? Is there a step-by-step document somewhere that you can refer?

View 1 Replies View Related

Howto Reduce Tempdb

Mar 29, 2001

Hello. Can anybody help me with this?

I have a sql server 7.0, where tempdb database has a size of 21 Gb and space available is 2Mb less than 21 Gb.

How can I shrink, reduce, compact ... it?
I have just tried with Backups and truncate log, but nothing

Bye, JuanSa.

View 9 Replies View Related

Howto Do A Fulltext Search ?

Jan 14, 2008

Hi,

I'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.

Any tips ?

View 1 Replies View Related

Howto Use Joins Instead Of Subqueries?

Jul 20, 2005

Hi,Trying to get a grip on the "join" thing :)Up until now, I allways used this kinda method:"(select t1.a from t1 where t1.b in (select t2.ab from t2 where t2.b=0))"How can this be accomplished using joins? And if you have the time pleaseexplain the "bits" :)Thank you.--?TH

View 3 Replies View Related

Howto Use DATEDIFF(); In SQL Express

Oct 16, 2006

Im trying to lern MS SQL Express to my PHP5 websites so now get eyes open for View and i will try to make views in my MS SQL database.

Bot this command here i get the rong result

SELECT DATEDIFF(yy, '1985/10/21', '2006/10/18') AS Fdag
FROM table

the problem is im not 21 year old im only 20 bot about 6 days im 21 bot the SQL say im 21 year old so ist can't rember deferind whit the date.

i hob i get get help here :)



tanks enyway :)

View 9 Replies View Related

DFT - Howto Last Execute A Storedprocedure

Apr 13, 2007



In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?



This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow



Regards



Riccardo

View 7 Replies View Related

Howto Flatten A Mappingtable?

Mar 26, 2008

Hi ,


i have the following Problem: i need to have a database-design in which there are a variable number of customers , a variable numbers of products and a price per product per user. My solution looks like this:

Table Customer:
CustomerID
...

Table Products:
ProductID
...

Table Prices:
CustomerID
ProductID
Price

Now my question: is it possible to get a Pricelist with one customer and all his prices in one row? E.g.:

CustomerID ; PriceProduct1 ; PriceProduct2 ; ....


So one Price-Column per Product, one Row per Customer? Can i do something like that with an sql-statement , view or stored-procedure so the number of Columns in the result depends on the number of Products and should be "dynamic" - which means when i add a new product to the product-table a new price-column is appended to the result?




thanks in advance.

View 6 Replies View Related

Howto Import Into SQLCE 2K5 CE Db...

Oct 8, 2007

Can someone please suggest some quick way to import data into SQL 2005 CE tables from an SQL 2000 server db tables.

Thanks
Rajesh

View 1 Replies View Related

Howto Use COM Objects In CLR. NET Programs

Aug 11, 2006

hi,

i have a vb.net progi which i want to run under sql server 2005 as CLR.

the prob is that when i want to create the assembly in sql 2005 it says

that the create of the Interop.FC_COM.dll (this is the COM interop object) failed.

when i try to create the Interop.FC_COM.dll as an assembly in sql 2005 it fails because this dll is not follwing the structure of a CLR dll.

i've heard somewhere that MS is not supporting COM objects under CLR directly. is that true?



any comments welcome,

thnx

View 2 Replies View Related

HowTo: Use ISQL To Include Several Scripts

May 26, 1999

Hi there:

Using Oracle SQL*Plus, I can have a script to execute other scripts written before such as this:

@script1
@script2
..
@scriptn

Using ISQL or ISQL/w, how can I do this?

Thanks in advance

View 2 Replies View Related

Howto Use A Temporaly Table Created By An Sp?

Oct 12, 2005

Hi all!

I would like to gain data from a temporaly table created by an EXEC command.
e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right.
But I would like to use it:
SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow
WHERE...

I know that a function can return a table but an sp cannot.

How can I do it?

Thx: Gurmy

View 1 Replies View Related

HOWTO Drop Temporary Table ?

Jul 19, 2006

hi, i have one temporary table

#tmp_tbl

before i create a temporary table , i would like to drop it first, but i try

"drop table if exists #tmp_tbl "

it doesn't work , any help ? thanks in advance

View 3 Replies View Related

Howto Add More Column Into Select Record?

Aug 16, 2006

hi, good day,

i have try following to get the records of the existing folder using :


insert into my_table
exec xp_dirtree 'c: emp'


for example , it give the result

subdirectory depth
--------------------
another 1
TEST 1
txtfiles 1
anotherSub 2



my question is , can i have add another column maindirectory into this existing result ?
and my expected result would like to be as follow

main directory subdirectory depth
--------------------------------------

another another 1
TEST TEST 1
txtfiles txtfiles 1
TEST anotherSub 2


is it possible ? thanks for guidance

View 5 Replies View Related

HOWTO: Write Formula Engine Using T-SQL

Mar 29, 2004

This came from our actuaries, - create a formula generation and processing engine.

They have a dozen of well-normalized base tables that contain statistical data on competitors, broken down by state, county, city, and zip.

The result should contain aggregated breakdown by the same set of data elements, but also include along with other things an input formula and its result for the Base Rate.

The whole thing is a mini-Monte Carlo simmulation.

THE TRICK:
If the formula today looks like:

x = ((a + b) * (c - d + e)) / (a * (c - d) * (f - d) * (e + d))

then tomorrow it may look like:

x = a * b * c * d * e * f

THE REAL TRICK: It should not be based on dynamic SQL!!!

Any thoughts?...

View 4 Replies View Related

[howto] View Contents Of Log File

Apr 7, 2004

How to view contents of log file?
i found sp_helplog
however

sp_helplog is no longer supported.

help please

View 1 Replies View Related

Howto Dynamically Change The BulkInsertTableName

Mar 19, 2007

Hi,

I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).

Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?

My test scenario contains:

- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)

- 1 Data Flow task in BIDS (SSIS)

- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)

- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01GWLINST1.TEST

I can dynamically change the name of the database via:

DTExec /F "Package.dtsx" /SET "Package.Connections[SDPM01GWLINST1.TEST].InitialCatalog;DEV"

How can I also dynamically change the table name where the data from the CSV file will be inserted?

DTExec /F "Package.dtsx" /SET ...

Thanks in advance,

Geert

View 8 Replies View Related

Howto Format A String In A Sproc ?

Sep 22, 2005

Hi. I have data which comes as a string like"... Store #61"but sometimes it is"... Store 61"and sometimes it is"... Store 061"For three digits, it might be "... Store 561" or "... Store #561", or"... Store 0561".....The only thing I can be sure of is that the last 2 or 3 (significant)digits of this field represent the StoreNumber.I have to link this table on field StoreNumber with another table wherethe data is ALWAYS like 0061, 0561, etc, so always four digits, paddedwith zeroes.I'd like to use the equivalent of the VB functionFormat(StoreNumber), "0000"), but Format does NOT exist in TSQL.How could I solve this problem ? Please bear with me - I'm a beginnerin SQL...Thank you very muchAlex.

View 5 Replies View Related

Howto: Delete Every Second Record If Duplicates

Jul 19, 2007

Hi.I have a "union" table which results of a union of two tables.Occasionally I could have duplicates, when the same PIN has been addedto both tables, albeit at different Datees/Times, such as:PINNameAdded Date100411A7/11/2007 10:12:58 AM100411A7/17/2007 10:54:23 AM100413B7/11/2007 10:13:28 AM100413B7/17/2007 10:54:39 AM104229C7/6/2007 2:34:13 PM104231D7/6/2007 2:34:25 PM104869E6/10/2007 11:59:12 AM104869E6/22/2007 2:40:18 PMThe question is - how can I delete by queries the first occurence(time-wise) of these duplicates - i.e. I would want to delete thefirst occurence of 100411 (A), the first occurence of 100413 (B), andthe first occurence of 104869 (E) in the example above - records C andD show only once, so they are fine.Is there a MsAccess solution ? Is there a SQL-server solution ?Thank you very much !Alex

View 2 Replies View Related

Import Database To Another Server - Howto

Sep 12, 2007

How import database mysql to mysql server 2005?--magix

View 1 Replies View Related

Howto: Calling Sp_executesql From A Function ?

Jul 20, 2005

I want to execute a dynamically generated sql-statementfrom inside an user-defined-function. Calling functions andextended stored-procs is allowed so I tried sp_executesqlas well as sp_prepare/sp_execute ....but both fail with an error 'only functions and extended stored-procsmay be called from inside a function.'any idea where I might be wrong ?thx in advance,Joerg--************************************************** ***********Joerg ClausmeyerMedizinische Informatik und DatenmanagementCHARITE - Universitätsmedizin Berlin************************************************** ***********

View 3 Replies View Related

HowTo Update Date Field

Jul 20, 2005

Could someone help me with the most efficient way to update a date field. Ihave a field with Date and Time in it but I want to update it with only theDate and strip off the time. Any ideas for the shortest way to do this?

View 4 Replies View Related

Howto Import From Access Database?

Mar 13, 2007

How do I import all the information from an access database to sql server express?

I am moving my .net applications to use sql server instead of access and need a way to put the data in.

View 4 Replies View Related

Howto Improve Performance Of Tempdb?

Aug 27, 2007

Hi,

We are thinking about buying new harddrives to improve sql server performance. Currently TEMPDB is running on a dedicated RAID 0 with 3 harddrives of 136 GB, 10.000 RPM. When running a large bulk insert within a SSIS package to 15 destination tables we notice high numbers in the Avg. and current Read Queue length (above 3000) of the drive where TEMPB is on. No other programs or swap file is using this RAID 0 drive. Can anyone tell me if it is worth buying 4 harddrives of 15.000 RPM each 33 GB big replacing the current 3 drives? How much impact will it have on the Avg. and Current Read queue length and will it improve the time sql server needs to bulk insert data?

Thanks.

Marc

View 6 Replies View Related

HOWTO: Script Object As Execute?

May 30, 2006

(Didn't know in wich group this question belongs....)
Hi group,

In QA there is an option called 'Script object as....' Were developing a lot of sp's.
When I use this option the 'skeleton' of a sp is created.
Eg
Create proc uspMySP
@Message varchar(10)
AS
SELECT @Message

The mention option would generate the following:
DECLARE @RC int
DECLARE @Message varchar(10)
-- Set parameter values
EXEC @RC = [EMGLSP3].[EM3_OWN].[uspMySP] @Message

The question:
Is it possible to customize this? I would like to *** SET statements...
Is this possible?

Regards
Sander

View 5 Replies View Related

Howto Remove Printer Symbol

Aug 10, 2007

I am looking for a way to remove the printer symbol from the report manger because it dows not work on some client machines because they are locked down. For us it is completely sufficient to export to PDF.

Is there a setting or an entry in the web config or something else. A hint would be helpful

Thanks
HANNES

View 3 Replies View Related

Howto Split Input To Outputcolumn?

Mar 8, 2006

Hello

A little question I think.

I have made an outputcolumn and I can also find it in the OutputColumnCollection, but when I use

_outputColumnIndexList.Add((int)BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID));

I only get an index telling me the index of the outputcolumns, but I also have a lot of inputcolumns. How do I get the correct index so I can take some data from an inputcolumn, modify the data and add it to the outputcolumn I specified earlier?

I use the ProcessInput to get the buffer, but as I dont know the correct index it's impossible for me to alter it to the output.

I have something in mind as altering the inputcolumns when overiding the ProcessInput like...

string str = buffer.GetString(columnIndex); //this is a predefined index I know

buffer.SetString(columnIndex, str.ToUpper()); // this should point to the outputcolumn, but I cant get the correct index for that purpose.

Hope You understand what im trying to accomplish.

Kind regards

View 3 Replies View Related

Writing Data To Multiple Tables (Howto)

Sep 23, 2006

Can someone show me the command string required to write data out to 2 or 3 tables at once?  How about how to write 2 entries at once?  Write now my solution writes 1 record to a table and then I write somewhere around 40-200 records to a second child .  Write now I'm writing like this:Open --> Add 1 record in table #1 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeOpen --> Add 1 record in table #2 --> closeand I'm just wondering if there is a better approach, such as writing all the data at once.  Something like:Open --> Add 1 record in table #1 --> Add 5 records in table #2 --> close

View 1 Replies View Related

HowTo Catch The Constraint Errors While Using SqlDataSource

Aug 3, 2007

I have a sqldatasource and I allow deletions that could cause a constraint violation.  I want to capture this error however, I am using the sqldatasource bound to my datacontrol.  I dont know where to catch the error.  Unfortunately there is no OnError event for the control...   There is no code behind for this at this time.  I am hoping there is a way to do this without completely rewiring everything. 
My thanks in advance,

View 1 Replies View Related

HOWTO Show MS Word Doc Stored In SQL Server DB?

May 24, 2008

I have a document that is stored in SQL DB - DBname (doctest.mdf)- Table name(Documents)- Column names(ID, LINK, Content, Adddate, FileType). The doc is named Showme.doc
What I'm trying to do is I have a listview that is linked to the DB. The columns of the listview show LINK and Adddate. The LINK column is a hyperlink.
When the page is loaded the list view shows the info in the DB. The link is a hyperlink so that when I place my cursor over the link it is clickable.
Now how do I make it so that when the user clicks on the LINK the document is retrieved and opened?
I uploaded the document with the fileupload control. If FileUpload1.HasFile Then
Using reader As New BinaryReader(FileUpload1.PostedFile.InputStream)
Dim btcontent As Byte() = reader.ReadBytes(FileUpload1.PostedFile.ContentLength)
cmd3.Parameters.Add("@CONTENT", SqlDbType.VarBinary).Value = btcontent
End Using
end if
I would be thankful for help.
Thanks,
Ty

View 4 Replies View Related

Null Values In Datetime Fields, Howto?

Jul 12, 1999

Hi,
When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?

View 1 Replies View Related







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