By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,556 Members | 1,600 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,556 IT Pros & Developers. It's quick & easy.

SQL Question

P: n/a
I've read in this group that it is much better to open and close the SQL
Connection each time you need to access the database. I need to do a SET
ANSI_WARNINGS OFF in order to insert records, otherwise I get a "string may
be truncated" message (haven't figured this out. I've checked the data
against the columns and the data fits).

Anyway, is it kosher to open the connection, turn off the ansi warnings, do
my insert, and close the connection each time I need to do an insert?

The way my app is currently running is this:

Open the connection
Set Ansi Warnings Off

do various inserts throughout the day as they are needed

Catch the "state changed" event and if the connection closes, then re-open
it and set ansi warnings off again

Close the connection when the app closes.

What's the best method for doing this?

Thanks.
Nov 21 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Terry,

I know it's a pain, but the right way is to find the data width error.
Eliminate column by column until you hit it.

However, if you want to do it your way, have you confirmed that the data is
added anyway? If it is, then you're ok. But, still, you will truncate data
at some point.

Bernie Yaeger

"Terry Olsen" <to******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
I've read in this group that it is much better to open and close the SQL
Connection each time you need to access the database. I need to do a SET
ANSI_WARNINGS OFF in order to insert records, otherwise I get a "string
may be truncated" message (haven't figured this out. I've checked the data
against the columns and the data fits).

Anyway, is it kosher to open the connection, turn off the ansi warnings,
do my insert, and close the connection each time I need to do an insert?

The way my app is currently running is this:

Open the connection
Set Ansi Warnings Off

do various inserts throughout the day as they are needed

Catch the "state changed" event and if the connection closes, then re-open
it and set ansi warnings off again

Close the connection when the app closes.

What's the best method for doing this?

Thanks.

Nov 21 '05 #2

P: n/a
Terry,

This is in my opinion the general template for SQL handling

dim conn as new SQLconnection(xxxxx)
dim cmd as new SQLCommand(SQL,conn)
try
conn.open
try
cmd.ExecuteWhatever
Catch ex as exception 'or as well the SQL exception
'handle error
End Try
Catch ex as exception
'hanlde connection error
Finally
conn.dispose
End Try
Nov 21 '05 #3

P: n/a
I have not figured it out. I believe it may be in the customers' setup of
their SQL server. They have Ansi Warnings off as well on their apps. I
have verified that all the data is being inserted, nothing is being
truncated. If it was, I'm sure I'd hear the customer screaming. I have
tried trimming the data just to make sure there wasn't any padding, I have
counted characters until I can't count anymore, compared against the table
column lengths, and there is absolutely no reason I should be getting the
truncation warning. They do have code in an Insert Trigger, that may be
causing the warning but it's not for me to try to fix it (liability
reasons). Oh well, it "works" anyway...
"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Hi Terry,

I know it's a pain, but the right way is to find the data width error.
Eliminate column by column until you hit it.

However, if you want to do it your way, have you confirmed that the data
is added anyway? If it is, then you're ok. But, still, you will truncate
data at some point.

Bernie Yaeger

"Terry Olsen" <to******@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
I've read in this group that it is much better to open and close the SQL
Connection each time you need to access the database. I need to do a SET
ANSI_WARNINGS OFF in order to insert records, otherwise I get a "string
may be truncated" message (haven't figured this out. I've checked the
data against the columns and the data fits).

Anyway, is it kosher to open the connection, turn off the ansi warnings,
do my insert, and close the connection each time I need to do an insert?

The way my app is currently running is this:

Open the connection
Set Ansi Warnings Off

do various inserts throughout the day as they are needed

Catch the "state changed" event and if the connection closes, then
re-open it and set ansi warnings off again

Close the connection when the app closes.

What's the best method for doing this?

Thanks.


Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.