SQL Question

30 Mar 2005 3:48 AM

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.

30 Mar 2005 4:51 AM

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

Show quoteHide quote

"Terry Olsen" <tolse***@hotmail.com> wrote in message
news:%23jLIVtNNFHA.3380@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.
>

30 Mar 2005 1:56 PM

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

Show quoteHide quote

"Bernie Yaeger" <bern***@cherwellinc.com> wrote in message
news:%23e5Q1QONFHA.1500@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" <tolse***@hotmail.com> wrote in message
> news:%23jLIVtNNFHA.3380@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.
>>
>
>

30 Mar 2005 7:25 AM

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