SQL Question

[Terry Olsen]

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.

 

[Bernie Yaeger]

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

 

[Cor Ligthert]

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

 

[Terry Olsen]

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...