How do I keep the SQL Statement readable

Is there a way to prevent VS from collapsing the formatting that I do to keep
an SQL statement readable.
I go through all the trouble of writing the statement keeping it nice and
neat, then VS combines it all into one lump of code. This makes editing it
later difficult.Use the generic query window (two pane) instead of the default graphical
query window (4 pane). To switch over to the generic there is a button to
the right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <David@.discussions.microsoft.com> wrote in message
news:D4BFC88D-D107-4F49-8125-7F750FC60A4C@.microsoft.com...
> Is there a way to prevent VS from collapsing the formatting that I do to
> keep
> an SQL statement readable.
> I go through all the trouble of writing the statement keeping it nice and
> neat, then VS combines it all into one lump of code. This makes editing it
> later difficult.

How do I hide zeros w/o a conditional stmt

I need a way to format fields without having to write a conditional statement
based on each textbox's value. I'm dealing with hundreds of fields.
Something like a HideZeros setting on the field. Any help would be
appreciatedWhat I do is change the Format property of each textbox to a custom value:
#,##0.0;(#,##0.0);-
the 1st group is how to display positive values;
the 2nd is for negatives;
and the 3rd is for zeroes (I am just displaying a dash here).
good luck,
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:64AAF2A7-F92B-4E91-8F96-3A9AAB2088FC@.microsoft.com...
>I need a way to format fields without having to write a conditional
>statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||Thanks!
Worked great. Any idea on how to use that with a Percentage field.
"Blake Gremillion" wrote:
> I need a way to format fields without having to write a conditional statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||I would try custom again and this time use:
0.0%;(0.0%);-
I pesonally use custom with percentages anyways and use
P1
to get just one decimal place. I think the standard "percentage" option,
gives you two. Of course neither hides zeroes. ;(
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> Thanks!
> Worked great. Any idea on how to use that with a Percentage field.
>
>
> "Blake Gremillion" wrote:
>> I need a way to format fields without having to write a conditional
>> statement
>> based on each textbox's value. I'm dealing with hundreds of fields.
>> Something like a HideZeros setting on the field. Any help would be
>> appreciated|||Yeah, I think that's what I'll have to do instead of using the Px format.
Thanks Again!
"Greg Burns" wrote:
> I would try custom again and this time use:
> 0.0%;(0.0%);-
> I pesonally use custom with percentages anyways and use
> P1
> to get just one decimal place. I think the standard "percentage" option,
> gives you two. Of course neither hides zeroes. ;(
> Greg
>
> "Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
> message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> > Thanks!
> >
> > Worked great. Any idea on how to use that with a Percentage field.
> >
> >
> >
> >
> > "Blake Gremillion" wrote:
> >
> >> I need a way to format fields without having to write a conditional
> >> statement
> >> based on each textbox's value. I'm dealing with hundreds of fields.
> >> Something like a HideZeros setting on the field. Any help would be
> >> appreciated
>
>

How do I group this query here

I have the following sql statement below;

DECLARE @.val int
SET @.val = 1
WHILE (@.val <= 7 )
BEGIN
SELECT TOP(1) id, queue
FROM itn_articles WHERE asection = @.val
ORDER BY queue DESC
SET @.val = @.val + 1
END

Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable

Why do you need to do it in a loop? You can get the same results as

SELECT Id,Queue FROM itn_articles WHERE asection <= 7

|||

OMG, I feel like such a douche, lol..Thanks for the help guys I really appreciate it

How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I format data in the SQL statement

It has been a couple of years but I remember being able to format numbers,
dates and text in SQL Server statements, can't find much in online books ,
Where can I relearn this, thanks
JimThat's a little vague but I think you are after CONVERT(). See BooksOnLine
for more details.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
> It has been a couple of years but I remember being able to format numbers,
> dates and text in SQL Server statements, can't find much in online books
> ,
> Where can I relearn this, thanks
> Jim
>|||Sorry
How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do I
convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement of
some kind
Thanks
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That's a little vague but I think you are after CONVERT(). See
> BooksOnLine for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Warren" <jmwarren@.msu.edu> wrote in message
> news:u0WUchlCFHA.392@.TK2MSFTNGP14.phx.gbl...
>|||Yes. It's called CONVERT().
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_2f3o.htm
However, CONVERT will not handle your first requirement. You will need to do
that in your client application.
PS. dates are not stored with any format. They are stored as pairs of
integers. You really need to start reading in Books Online (BOL)
Jim Warren wrote:
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2.
> How do I convert dates from 2005-1-14 to Jan 1, 2005. Is there a
> format statement of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||As I stated and Bob confirmed you would want to look at CONVERT() in
BooksOnLine. You can convert the decimal into an INT and that will loos the
decimal places.
Andrew J. Kelly SQL MVP
"Jim Warren" <jmwarren@.msu.edu> wrote in message
news:OI$46h3CFHA.1836@.tk2msftngp13.phx.gbl...
> Sorry
> How do I make Number into zero decimal point i.e. 2.0000000 into 2. How do
> I convert dates from 2005-1-14 to Jan 1, 2005. Is there a format statement
> of some kind
> Thanks
> Jim
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O9sU4llCFHA.1292@.TK2MSFTNGP10.phx.gbl...
>

How do I force upper case in a select statement

I want all of the columns in a select statement to be converted to upper
case. What is the proper syntax for that?SELECT UPPER(col1), UPPER(col2), UPPER(col3), ...
FROM dbo.YourTable;
"Thirsty Traveler" <nfr@.nospam.com> wrote in message
news:ubO0jhqeGHA.3692@.TK2MSFTNGP03.phx.gbl...
>I want all of the columns in a select statement to be converted to upper
>case. What is the proper syntax for that?
>

How do I fire a sql statement from being seen by a trace session

Hi
I am trying to do an OPENROWSET call to an ODBC driver which needs a key
to work but i need to keep the KEY hidden from being seen/ traced.
Is there a way to execute an sql statement and disable a tracing session or
another way stop a OPENROWSET argument from being traced and duplicated.
Thank you in advance for your time..
EdwinEdwin,
You can't hide anything away from the Profiler. It's not guaranteed that
the Profiler will get every statement but you can't hide statements
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Exonet Developer" <Exonet Developer@.discussions.microsoft.com> wrote in
message news:C5F9A585-7362-41AC-8C2D-6D7574FFF60E@.microsoft.com...
> Hi
> I am trying to do an OPENROWSET call to an ODBC driver which needs a key
> to work but i need to keep the KEY hidden from being seen/ traced.
> Is there a way to execute an sql statement and disable a tracing session
> or
> another way stop a OPENROWSET argument from being traced and duplicated.
> Thank you in advance for your time..
> Edwin|||you can add sensitive text like "sp_addlogin" as comment in your sql
statements and sql profiler will hide this statement
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Exonet Developer" wrote:

> Hi
> I am trying to do an OPENROWSET call to an ODBC driver which needs a key
> to work but i need to keep the KEY hidden from being seen/ traced.
> Is there a way to execute an sql statement and disable a tracing session o
r
> another way stop a OPENROWSET argument from being traced and duplicated.
> Thank you in advance for your time..
> Edwin|||Hi Aleksandar,
Can you give me an example of this. I don't really understand what you mean
kind regards
Greg O
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:FD863E94-89C2-45D3-B682-F7AA03E0C07C@.microsoft.com...
> you can add sensitive text like "sp_addlogin" as comment in your sql
> statements and sql profiler will hide this statement
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Exonet Developer" wrote:
>

How do I fire a sql statement from being seen by a trace sessi

:o) Your tip works great for me.. Thank you very much Aleksandar.
"Aleksandar Grbic" wrote:
> you can add sensitive text like "sp_addlogin" as comment in your sql
> statements and sql profiler will hide this statement
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Exonet Developer" wrote:
>But that was bad advice indeed.
Infact another security enhancement in SP4 suppress every statement that
contains the text 'password'
But these things may change in the future.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Exonet Developer" <ExonetDeveloper@.discussions.microsoft.com> wrote in
message news:9AD6E379-C45C-4480-9125-963C17BD952F@.microsoft.com...
> :o) Your tip works great for me.. Thank you very much Aleksandar.
> "Aleksandar Grbic" wrote:
>

How do I fire a sql statement from being seen by a trace sessi

try
select * from <table> -- sp_addlogin
and this statement will not be show in profiler
Aleksandar Grbic
MCDBA, Senior Database Administrator
"GregO" wrote:

> Hi Aleksandar,
> Can you give me an example of this. I don't really understand what you me
an
> kind regards
> Greg O
> "Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
> message news:FD863E94-89C2-45D3-B682-F7AA03E0C07C@.microsoft.com...
>
>Hi ,
Well I didn't know that worked. Anyway you can still find the SQL in
syscacheobjects
select * from dbo.syscacheobjects
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:055AE9C0-055A-4543-AFE4-BAD470D25771@.microsoft.com...
> try
> select * from <table> -- sp_addlogin
> and this statement will not be show in profiler
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "GregO" wrote:
>|||answer is:
Aleksandar Grbic
MCDBA, Senior Database Administrator
"GregO" wrote:
> Hi ,
> Well I didn't know that worked. Anyway you can still find the SQL in
> syscacheobjects
> select * from dbo.syscacheobjects
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
>
> "Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
> message news:055AE9C0-055A-4543-AFE4-BAD470D25771@.microsoft.com...
>
>|||Hi Aleksandar ,
Not disputing your post but being seen/traced can mean being seen (which it
can via syscacheobjects) or being traced
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:E33A74C8-4BA6-4909-8D41-B828416B6C53@.microsoft.com...
> answer is:
>
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "GregO" wrote:
>|||;)
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"GregO" wrote:

> Hi Aleksandar ,
> Not disputing your post but being seen/traced can mean being seen (which i
t
> can via syscacheobjects) or being traced
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
> message news:E33A74C8-4BA6-4909-8D41-B828416B6C53@.microsoft.com...
>
>

How do I exclude null fields?

Is there a way to write a select statement that will pull only fields that are not null?

SELECT col1, col2 FROM yourtable WHERE yourcol IS NOT NULL

col1, col2, and yourcol are columns in your table.

If you have specific question, please post it here.

|||Thanks!

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

Public Class frmSales

Dim dt As New DataTable()

Dim connstr As New SqlClient.SqlConnection

Dim RowIndex As Integer = 0

Dim PurNo As Integer

Dim NewBalPurQty As Integer

Dim tr As SqlClient.SqlTransaction

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales As String

Dim SelectPurchases As String

Dim InsertPurAppropriation1 As String

Dim InsertPurAppropriation2 As String

Dim SaleNo As String = TextBox1.Text

Dim BillNo As String = TextBox2.Text

Dim SaleDate As String = TextBox3.Text

Dim Broker As String = TextBox4.Text

Dim Share As String = TextBox5.Text

Dim Quantity As String = TextBox6.Text

Dim Rate As String = TextBox7.Text

Dim Amount As String = TextBox8.Text

Dim BalPurQty As String

Dim FullPurQty As Integer

Dim PurDate As Date

Dim PurRate As Double

Dim PurAmount As Double

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i As Integer = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

If CInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

Exit For

ElseIf CInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

Exit For

ElseIf CInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

End If

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

End Try

ClearTextBoxes()

End Sub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

End Sub

Sub MarkEmpty()

Dim UpdatePurchases1 As String

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

Sub MarkPartial()

Dim UpdatePurchases2 As String

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

End Class

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

PublicClass frmSales

Dim dt AsNew DataTable()

Dim connstr AsNew SqlClient.SqlConnection

Dim RowIndex AsInteger = 0

Dim PurNo AsInteger

Dim NewBalPurQty AsInteger

Dim tr As SqlClient.SqlTransaction

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales AsString

Dim SelectPurchases AsString

Dim InsertPurAppropriation1 AsString

Dim InsertPurAppropriation2 AsString

Dim SaleNo AsString = TextBox1.Text

Dim BillNo AsString = TextBox2.Text

Dim SaleDate AsString = TextBox3.Text

Dim Broker AsString = TextBox4.Text

Dim Share AsString = TextBox5.Text

Dim Quantity AsString = TextBox6.Text

Dim Rate AsString = TextBox7.Text

Dim Amount AsString = TextBox8.Text

Dim BalPurQty AsString

Dim FullPurQty AsInteger

Dim PurDate AsDate

Dim PurRate AsDouble

Dim PurAmount AsDouble

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd AsNew SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 AsNew SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i AsInteger = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

IfCInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

ExitFor

ElseIfCInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

ExitFor

ElseIfCInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 AsNew SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

EndIf

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 AsNew SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

EndTry

ClearTextBoxes()

EndSub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

EndSub

Sub MarkEmpty()

Dim UpdatePurchases1 AsString

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 AsNew SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

Sub MarkPartial()

Dim UpdatePurchases2 AsString

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 AsNew SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

EndClass

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.