Newbie question on BULK INSERT of text file

A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:

> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:

> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql

newbie question insert photos in sql 2k

Hello,
I am interested on how to insert a .jpg file into sql server 2k. Does
anyone have an example of this? I would greatly appreciate it.
Jakejake wrote:
> Hello,
> I am interested on how to insert a .jpg file into sql server 2k.
> Does anyone have an example of this? I would greatly appreciate it.
There are many approaches, but you might want to see:
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/defaul...b;en-us;Q258038
FileToBlob - Loading a file into a SQL Server's BLOB
http://www.devx.com/vb2themax/Tip/19669
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
Hi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>
|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after[vbcol=seagreen]
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
transaction
>
>
|||I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas

"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri
|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit dans le message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline conditions. The following is a general layout that I use, but the one you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status of @.@.ERROR after each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters on code launch since transactions are highly sensitive to these settings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas

"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a crit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
transaction[vbcol=seagreen]
>
>|||I like to defer my exception handling and sometimes have inline conditions.
The following is a general layout that I use, but the one you describe is t
ypical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status o
f @.@.ERROR after each DML statement if you wish to properly trap errors. Als
o, it is VERY important that you initialize the appropriate environmental pa
rameters on code launch since transactions are highly sensitive to these set
tings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
--
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK
2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henri|||Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a crit dans le message de
news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline conditions.
The following is a general layout that I use, but the one you describe is t
ypical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
..
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
**
*/
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode = @.@.ERROR
IF @.intErrorCode = 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry = @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode = @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
UPDATE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
DELETE ...
SET @.intErrorCode = @.@.ERROR
END
IF @.intErrorCode = 0 BEGIN
INSERT ...
SET @.intErrorCode = @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode = 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the status o
f @.@.ERROR after each DML statement if you wish to properly trap errors. Als
o, it is VERY important that you initialize the appropriate environmental pa
rameters on code launch since transactions are highly sensitive to these set
tings. Being explicit will help you in any debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
--
"Henri" <hmfireball@.hotmail.com> wrote in message news:eC9loV10EHA.3484@.TK2M
SFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
Henrisql

newbie question about transactions

I'm thinking of using transactions but there's something I don't know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if there are
lots of them...
Can you tell me what the minimum code is for a procedure using transaction
to be valid?
Thanks
HenriHi
You have to check after each statement as the @.@.error variable gets reset
every time. In your example, you need the error handler 7 times, once after
each statement (you could get away with 6, excluding the BEGIN TRAN as you
should not error on that).
SQL Server 2005 brings structured exception handling, but until then, that
is the only way.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Henri" <hmfireball@.hotmail.com> wrote in message
news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> I'm thinking of using transactions but there's something I don't know.
> Consider that kind of code:
> BEGIN TRANS
> INSERT ...
> INSERT ...
> UPDATE ...
> DELETE ...
> INSERT...
> COMMIT
> My question is:
> do I have to write after *each* insert, update or delete
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> RETURN
> END
> for my procedure to work well?
> It's not a big deal if there are only 2 or 3 operations, but if there are
> lots of them...
> Can you tell me what the minimum code is for a procedure using transaction
> to be valid?
> Thanks
> Henri
>
>|||Thanks a lot for your answer Mike :-)
Henri
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> a écrit dans le message de
news:eIDrTa20EHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You have to check after each statement as the @.@.error variable gets reset
> every time. In your example, you need the error handler 7 times, once
after
> each statement (you could get away with 6, excluding the BEGIN TRAN as you
> should not error on that).
> SQL Server 2005 brings structured exception handling, but until then, that
> is the only way.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
> > I'm thinking of using transactions but there's something I don't know.
> > Consider that kind of code:
> >
> > BEGIN TRANS
> >
> > INSERT ...
> > INSERT ...
> > UPDATE ...
> > DELETE ...
> > INSERT...
> >
> > COMMIT
> >
> > My question is:
> > do I have to write after *each* insert, update or delete
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > RETURN
> > END
> >
> > for my procedure to work well?
> >
> > It's not a big deal if there are only 2 or 3 operations, but if there
are
> > lots of them...
> > Can you tell me what the minimum code is for a procedure using
transaction
> > to be valid?
> > Thanks
> >
> > Henri
> >
> >
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for your help Anthony :-)
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> a =E9crit dans le =message de news:O7n%23T9K1EHA.1076@.TK2MSFTNGP09.phx.gbl...
I like to defer my exception handling and sometimes have inline =conditions. The following is a general layout that I use, but the one =you describe is typical as well.
CREATE PROCEDURE DataModificationTransaction1
@.Param1 AS DataType1
,@.Param2 AS DataType2
...
,@.ParamN AS DataTypeN
AS
/*
**
** Procedure Information Comment Block
** */
DECLARE @.intTranCountOnEntry AS INT
,@.intErrorCode AS INT
-- Environment Configuration.
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Variable Initialization.
SET @.intErrorCode =3D @.@.ERROR
IF @.intErrorCode =3D 0 BEGIN
-- Capture transaction state before beginning.
SET @.intTranCountOnEntry =3D @.@.TRANCOUNT
BEGIN TRANSACTION
SET @.intErrorCode =3D @.@.ERROR
END
-- Only continue if error free.
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
UPDATE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
DELETE ...
SET @.intErrorCode =3D @.@.ERROR
END
IF @.intErrorCode =3D 0 BEGIN
INSERT ...
SET @.intErrorCode =3D @.@.ERROR
END
-- Only commit if transaction initiated
-- and error free.
IF @.@.TRANCOUNT > @.intTranCountOnEntry BEGIN
IF @.intErrorCode =3D 0 BEGIN
COMMIT TRANSACTION
END
ELSE BEGIN
ROLLBACK TRANSACTION
END
END
RETURN @.intErrorCode
You can also nest the conditional statements but you MUST check the =status of @.@.ERROR after each DML statement if you wish to properly trap =errors. Also, it is VERY important that you initialize the appropriate =environmental parameters on code launch since transactions are highly =sensitive to these settings. Being explicit will help you in any =debugging situations.
Hope this helps.
Sincerely,
Anthony Thomas
-- "Henri" <hmfireball@.hotmail.com> wrote in message =news:eC9loV10EHA.3484@.TK2MSFTNGP09.phx.gbl...
I'm thinking of using transactions but there's something I don't =know.
Consider that kind of code:
BEGIN TRANS
INSERT ...
INSERT ...
UPDATE ...
DELETE ...
INSERT...
COMMIT
My question is:
do I have to write after *each* insert, update or delete
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
RETURN
END
for my procedure to work well?
It's not a big deal if there are only 2 or 3 operations, but if =there are
lots of them...
Can you tell me what the minimum code is for a procedure using =transaction
to be valid?
Thanks
Henri
--=_NextPart_000_0015_01C4D4EB.600E75E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for your help Anthony =:-)
"AnthonyThomas" a =E9crit dans le message de news:O7n%23T9K1EHA.=1076@.TK2MSFTNGP09.phx.gbl...
I like =to defer my exception handling and sometimes have inline conditions. The =following is a general layout that I use, but the one you describe is typical as = well.

CREATE =PROCEDURE DataModificationTransaction1 @.Param1 AS =DataType1 ,@.Param2 AS DataType2 ... ,@.ParamN AS DataTypeN AS

/***** =Procedure Information Comment Block** */

DECLARE @.intTranCountOnEntry AS INT ,@.intErrorCode AS =INT -- Environment Configuration.SET XACT_ABORT OFFSET =IMPLICIT_TRANSACTIONS OFFSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Variable Initialization.SET @.intErrorCode =3D @.@.ERROR

IF =@.intErrorCode =3D 0 BEGIN -- Capture transaction state before =beginning. SET @.intTranCountOnEntry =3D @.@.TRANCOUNT BEGIN =TRANSACTION SET @.intErrorCode =3D @.@.ERROR END -- Only continue =if error free.IF @.intErrorCode =3D 0 BEGIN INSERT ... SET = @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode ==3D 0 BEGIN INSERT ... SET @.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN UPDATE ... SET @.intErrorCode =3D =@.@.ERROR END IF @.intErrorCode =3D 0 BEGIN DELETE ... SET =@.intErrorCode =3D @.@.ERROR END IF @.intErrorCode =3D 0 =BEGIN INSERT ... SET @.intErrorCode =3D =@.@.ERROR END -- Only commit if transaction initiated-- and error free.IF =@.@.TRANCOUNT > @.intTranCountOnEntry BEGIN IF @.intErrorCode =3D 0 BEGIN COMMIT TRANSACTION END ELSE BEGIN ROLLBACK =TRANSACTION END END =RETURN @.intErrorCode
You can =also nest the conditional statements but you MUST check the status of @.@.ERROR after =each DML statement if you wish to properly trap errors. Also, it is VERY important that you initialize the appropriate environmental parameters =on code launch since transactions are highly sensitive to these =settings. Being explicit will help you in any debugging situations.

Hope =this helps.

Sincerely,


Anthony = Thomas



--
"Henri" =wrote in message news:eC9loV10EHA.3484=@.TK2MSFTNGP09.phx.gbl...I'm thinking of using transactions but there's something I don't know.Consider that kind of code:BEGIN =TRANSINSERT ...INSERT ...UPDATE ...DELETE ...INSERT...COMMITMy question is:do I have =to write after *each* insert, update or deleteIF @.@.ERROR 0 = BEGIN ROLLBACK RETURNENDfor my =procedure to work well?It's not a big deal if there are only 2 or 3 =operations, but if there arelots of them...Can you tell me what the =minimum code is for a procedure using transactionto be =valid?ThanksHenri

---

--=_NextPart_000_0015_01C4D4EB.600E75E0--

Newbie question

I would like to know how to be able to insert into one table for example and
have that table linked to another table.
For example, I have a Wrrok Order system VIA the Web, what happens is
someone will create a work order, and the records are created. I would like
to be able to dynamically add notes to it so that whenever a note is added
to a certain work order, it is added into a second table with a record
called notes (I have primary Keys on both Tables called "wo_id")
Then we I retrieve the Work Order using the Primary Key I want it to display
all of the notes in the secondary table related to that Primary Key.
Any help would be great, thanks and let me know if I did not ask it
correctly!> For example, I have a Wrrok Order system VIA the Web, what happens is
> someone will create a work order, and the records are created. I would
like
> to be able to dynamically add notes to it so that whenever a note is added
> to a certain work order, it is added into a second table with a record
> called notes (I have primary Keys on both Tables called "wo_id")
Just execute an INSERT statement with the wo_id for the work order and the
note for the second column.
> Then we I retrieve the Work Order using the Primary Key I want it to
display
> all of the notes in the secondary table related to that Primary Key.
Either first select the relevant columns from the WorkOrder table and
another SELECT that select the relevant columns from the other table.
SELECT col1, col2...
FROM WorkOrder
WHERE wo_id = ...
SELECT col1, col3
FROM SecTable
WHERE wo_id = ...
Or, do a join:
SELECT wo.col1, wo.col2, sec.col1, sec.col2
FROM WorkOrder AS wo INNER JOIN SecTable AS sec ON sec.wo_id = wo.wo_id
WHERE wo_id = ...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JC" <Josh@.Network-Medics.Com> wrote in message
news:uWTMO8JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I would like to know how to be able to insert into one table for example
and
> have that table linked to another table.
> For example, I have a Wrrok Order system VIA the Web, what happens is
> someone will create a work order, and the records are created. I would
like
> to be able to dynamically add notes to it so that whenever a note is added
> to a certain work order, it is added into a second table with a record
> called notes (I have primary Keys on both Tables called "wo_id")
> Then we I retrieve the Work Order using the Primary Key I want it to
display
> all of the notes in the secondary table related to that Primary Key.
> Any help would be great, thanks and let me know if I did not ask it
> correctly!
>|||Thanks!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:evoRiLKpDHA.372@.TK2MSFTNGP11.phx.gbl...
> > For example, I have a Wrrok Order system VIA the Web, what happens is
> > someone will create a work order, and the records are created. I would
> like
> > to be able to dynamically add notes to it so that whenever a note is
added
> > to a certain work order, it is added into a second table with a record
> > called notes (I have primary Keys on both Tables called "wo_id")
> Just execute an INSERT statement with the wo_id for the work order and the
> note for the second column.
>
> > Then we I retrieve the Work Order using the Primary Key I want it to
> display
> > all of the notes in the secondary table related to that Primary Key.
> Either first select the relevant columns from the WorkOrder table and
> another SELECT that select the relevant columns from the other table.
> SELECT col1, col2...
> FROM WorkOrder
> WHERE wo_id = ...
> SELECT col1, col3
> FROM SecTable
> WHERE wo_id = ...
> Or, do a join:
> SELECT wo.col1, wo.col2, sec.col1, sec.col2
> FROM WorkOrder AS wo INNER JOIN SecTable AS sec ON sec.wo_id = wo.wo_id
> WHERE wo_id = ...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "JC" <Josh@.Network-Medics.Com> wrote in message
> news:uWTMO8JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > I would like to know how to be able to insert into one table for example
> and
> > have that table linked to another table.
> >
> > For example, I have a Wrrok Order system VIA the Web, what happens is
> > someone will create a work order, and the records are created. I would
> like
> > to be able to dynamically add notes to it so that whenever a note is
added
> > to a certain work order, it is added into a second table with a record
> > called notes (I have primary Keys on both Tables called "wo_id")
> >
> > Then we I retrieve the Work Order using the Primary Key I want it to
> display
> > all of the notes in the secondary table related to that Primary Key.
> >
> > Any help would be great, thanks and let me know if I did not ask it
> > correctly!
> >
> >
>

Newbie help

OK, this is probably very basic, but to me it is all new.

I have three tables...tbl_photos, tbl_customers, tbl_register

I want to Insert INTO the cust_id field of tbl_register the cust_id field from tbl_customers where the email field from tbl_customers = str_email (from a form) and I also want to Insert into the photo_id field of tbl_register the photo_id field from tbl_photos where the code1 field from tbl_photos = str_code1 (from a form)

What would be the correct syntax for this?

I hope that description makes sence.You cant do this using single query. Use 2 steps:
a. Get email from tbl_customers table. Since you are using values from user input in query, you have to preconstruct sql query, like:

str='select cust_id
from tbl_customers
where email=' & value_from_field

row=dbobject.execute (str)

b. Insert value from step a into tbl_register

newbie having trouble with SQL connection, insert/update

Thanks to anyone who helps!
I'm building a data entry windows form that requires the data to
be sent to a SQL Server table (the table's name is "Local") when the
form's "SAVE" button is clicked. I've built the form using the windows
form designer, and now I'm attempting to use SQL statements to
insert/update data into the table. There are six fields that will use
SQL statements to send data to the Local table, and none of the fields
are permitted to be null values. Two of the fields are comboboxes that
are populated through the use of their own respective datasets
(DisasterType and Dwelling); the datasets were built using the forms
designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
WorkerName, and DamageDescGeneral) that require the user to manually
enter data. Finally, there is a checkbox (Utilities) that should be
checked if the parameter is "yes/true."
My main problem is that I don't know the correct SQL code that
will allow
the data to be inserted/updated into the Local table. Or, can the
issue be solved using the forms designer? There are also two other
issues.
1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
be changed before being sent to the Local table. I am using the more
user-readable names and descriptions of the respective fields to
populate the form, rather than their Primary Key ID's. However, their
Primary Keys are also Foreign Keys in the Local table, so their
datatypes have to be changed. You'll see this attempted conversion in
the "//convert Disaster_Type to Disaster_ID" and "//convert
Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
recognize the While statement I'm using. Here is the error: Cannot
find method 'While(boolean)' in 'ArcMaster.frmLocal'
2. The datatype for the Utilities column in the Local table is a bit
value, but the datatype on the windows form is a boolean checkbox. I
need to be able to convert from boolean to bit through the use of an If
statement, but I keep getting errors. Here are my compile errors under
the current configuration:
Type 'boolean' is not assignable to 'Object'
Type 'int' is not assignable to 'Object'
I've included my code. I KNOW it's wrong, and I'm hoping
someone can
assist me. Thanks again!!!
import System.Drawing.*;
import System.Collections.*;
import System.ComponentModel.*;
import System.Windows.Forms.*;
import System.Data.*;
import System.Data.SqlClient.*;
import System.*;
/**
* Summary description for Local.
*/
public class frmLocal extends System.Windows.Forms.Form
{
//windows forms designer variables
private System.Windows.Forms.Label lblTitle;
private System.Windows.Forms.TextBox txtFamilyName;
private System.Windows.Forms.Label lblFamilyName;
private System.Windows.Forms.TextBox txtWorkerName;
private System.Windows.Forms.Label lblWorkerName;
private System.Windows.Forms.Label lblDisasterType;
private System.Windows.Forms.ComboBox cboDisasterType;
private System.Windows.Forms.Label lblDwellingType;
private System.Windows.Forms.ComboBox cboDwellingType;
private System.Windows.Forms.Label lblUtilities;
private System.Windows.Forms.Label lblDegreeOfDamage;
private System.Windows.Forms.Label lblDamageDescGeneral;
private System.Windows.Forms.TextBox txtDamageDescGeneral;
private System.Windows.Forms.Button cmdCloseWindow;
private System.Windows.Forms.Button cmdClearEntry;
private System.Windows.Forms.Button cmdSave;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
private ArcMaster.dsDisasterType dsDisasterType1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
private ArcMaster.dsDwelling dsDwelling1;
private System.Windows.Forms.CheckBox chkUtilities;
/**
* Required designer variable.
*/
private System.ComponentModel.Container components = null;
public frmLocal()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent
call
//
}
/**
* Clean up any resources being used.
*/
protected void Dispose(boolean disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
super.Dispose(disposing);
}
#region Windows Form Designer generated code
private void frmLocal_Load (Object sender, System.EventArgs e)
{
//populate disaster type combobox with data from Disaster_Type
table
dsDisasterType1.Clear();
oleDbDataAdapter3.Fill(dsDisasterType1);
oleDbConnection1.Close();
//populate dwelling type combobox with data from Dwelling table
dsDwelling1.Clear();
oleDbDataAdapter4.Fill(dsDwelling1);
oleDbConnection1.Close();
}
private void cmdSave_Click (Object sender, System.EventArgs e)
{
//SQL variables
Object sql;
SqlConnection cn;
DataSet dsLocal = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand myCommand;
SqlDataReader reader;
//initialize the variables that handle data to be bound to
Local table;
Object FamilyName = txtFamilyName.get_Text();
Object Dwelling = cboDwellingType.get_SelectedItem();
Object DisasterType = cboDisasterType.get_SelectedItem();
Object WorkerName = txtWorkerName.get_Text();
Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
Object Utilities = chkUtilities.get_CheckState();
//Set the connection string of the SqlConnection object to
connect to the ARC database
cn = new SqlConnection("Server=METZLER"+
"Integrated security=SSPI;" +
"database=ARC");
myCommand= new SqlCommand(sql, cn);
cn.Open();
//convert Disaster_Type to Disaster_ID
sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
((Disaster_Name = " + DisasterType + " ))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
DisasterType = reader.GetValue(0);
}
reader.Close();
//convert Dwelling_Desc to Dwelling_Type
sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
= " + Dwelling + "))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
Dwelling = reader.GetValue(0);
}
reader.Close();
//Initialize the SqlCommandBuilder object to automatically
generate and
//initialize the UpdateCommand, the InsertCommand, and the
DeleteCommand
//properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
da.Fill(dsLocal, "Local");
//convert Utilites data from text datatype to bit datatype
If (Utilities = true);
{
Utilities = 1;
}
If (Utilities = false);
{
Utilities = 0;
}
//SQL statement to insert data into Local table
sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
"VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
+ "',
'" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
Utilities;
da.Update(dsLocal, "Local");
//oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
//oleDbConnection1.Close();
//dsLocal1.Clear();
//oleDbDataAdapter4.Update(dsLocal1);
//oleDbDataAdapter4.Fill(dsLocal1);
//oleDbConnection1.Close();
//oleDbConnection1.Close();
//Close the database connection.
cn.Close();
MessageBox.Show("Local Detailed Damage Assessment has been
updated.");
}
private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
{
Close();Hi
You insert statement does not have a closing bracket, you also seem to be
making values strings by enquoting them when they are not character data
types. You could form the sql string differently depending on whether
utility is true or not.
John
"pmetz" <p1metzler@.yahoo.com> wrote in message
news:1144497354.135933.54280@.i40g2000cwc.googlegroups.com...
>
> Thanks to anyone who helps!
> I'm building a data entry windows form that requires the data to
> be sent to a SQL Server table (the table's name is "Local") when the
> form's "SAVE" button is clicked. I've built the form using the windows
>
> form designer, and now I'm attempting to use SQL statements to
> insert/update data into the table. There are six fields that will use
> SQL statements to send data to the Local table, and none of the fields
> are permitted to be null values. Two of the fields are comboboxes that
>
> are populated through the use of their own respective datasets
> (DisasterType and Dwelling); the datasets were built using the forms
> designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
> WorkerName, and DamageDescGeneral) that require the user to manually
> enter data. Finally, there is a checkbox (Utilities) that should be
> checked if the parameter is "yes/true."
> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table. Or, can the
> issue be solved using the forms designer? There are also two other
> issues.
>
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
>
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
>
> statement, but I keep getting errors. Here are my compile errors under
>
> the current configuration:
> Type 'boolean' is not assignable to 'Object'
> Type 'int' is not assignable to 'Object'
>
> I've included my code. I KNOW it's wrong, and I'm hoping
> someone can
> assist me. Thanks again!!!
>
> import System.Drawing.*;
> import System.Collections.*;
> import System.ComponentModel.*;
> import System.Windows.Forms.*;
> import System.Data.*;
> import System.Data.SqlClient.*;
> import System.*;
>
> /**
> * Summary description for Local.
> */
> public class frmLocal extends System.Windows.Forms.Form
> {
>
> //windows forms designer variables
> private System.Windows.Forms.Label lblTitle;
> private System.Windows.Forms.TextBox txtFamilyName;
> private System.Windows.Forms.Label lblFamilyName;
> private System.Windows.Forms.TextBox txtWorkerName;
> private System.Windows.Forms.Label lblWorkerName;
> private System.Windows.Forms.Label lblDisasterType;
> private System.Windows.Forms.ComboBox cboDisasterType;
> private System.Windows.Forms.Label lblDwellingType;
> private System.Windows.Forms.ComboBox cboDwellingType;
> private System.Windows.Forms.Label lblUtilities;
> private System.Windows.Forms.Label lblDegreeOfDamage;
> private System.Windows.Forms.Label lblDamageDescGeneral;
> private System.Windows.Forms.TextBox txtDamageDescGeneral;
> private System.Windows.Forms.Button cmdCloseWindow;
> private System.Windows.Forms.Button cmdClearEntry;
> private System.Windows.Forms.Button cmdSave;
> private System.Data.OleDb.OleDbConnection oleDbConnection1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
> private ArcMaster.dsDisasterType dsDisasterType1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
> private ArcMaster.dsDwelling dsDwelling1;
> private System.Windows.Forms.CheckBox chkUtilities;
>
> /**
> * Required designer variable.
> */
> private System.ComponentModel.Container components = null;
>
> public frmLocal()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
>
> //
> // TODO: Add any constructor code after InitializeComponent
> call
> //
>
> }
>
> /**
> * Clean up any resources being used.
> */
> protected void Dispose(boolean disposing)
> {
> if (disposing)
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> super.Dispose(disposing);
>
> }
>
> #region Windows Form Designer generated code
> private void frmLocal_Load (Object sender, System.EventArgs e)
> {
> //populate disaster type combobox with data from Disaster_Type
> table
> dsDisasterType1.Clear();
> oleDbDataAdapter3.Fill(dsDisasterType1);
> oleDbConnection1.Close();
>
> //populate dwelling type combobox with data from Dwelling table
>
> dsDwelling1.Clear();
> oleDbDataAdapter4.Fill(dsDwelling1);
> oleDbConnection1.Close();
>
> }
>
> private void cmdSave_Click (Object sender, System.EventArgs e)
> {
> //SQL variables
> Object sql;
> SqlConnection cn;
> DataSet dsLocal = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> SqlCommand myCommand;
> SqlDataReader reader;
> //initialize the variables that handle data to be bound to
> Local table;
> Object FamilyName = txtFamilyName.get_Text();
> Object Dwelling = cboDwellingType.get_SelectedItem();
> Object DisasterType = cboDisasterType.get_SelectedItem();
> Object WorkerName = txtWorkerName.get_Text();
> Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
> Object Utilities = chkUtilities.get_CheckState();
>
> //Set the connection string of the SqlConnection object to
> connect to the ARC database
> cn = new SqlConnection("Server=METZLER"+
> "Integrated security=SSPI;" +
> "database=ARC");
>
> myCommand= new SqlCommand(sql, cn);
>
> cn.Open();
>
> //convert Disaster_Type to Disaster_ID
> sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
> ((Disaster_Name = " + DisasterType + " ))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> DisasterType = reader.GetValue(0);
> }
> reader.Close();
>
> //convert Dwelling_Desc to Dwelling_Type
> sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
>
> = " + Dwelling + "))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> Dwelling = reader.GetValue(0);
> }
> reader.Close();
>
> //Initialize the SqlCommandBuilder object to automatically
> generate and
> //initialize the UpdateCommand, the InsertCommand, and the
> DeleteCommand
> //properties of the SqlDataAdapter.
> cmdBuilder = new SqlCommandBuilder(da);
>
> da.Fill(dsLocal, "Local");
>
> //convert Utilites data from text datatype to bit datatype
> If (Utilities = true);
> {
> Utilities = 1;
> }
> If (Utilities = false);
> {
> Utilities = 0;
> }
>
> //SQL statement to insert data into Local table
> sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
> Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
> "VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
> + "',
> '" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
> Utilities;
>
> da.Update(dsLocal, "Local");
>
> //oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
> //oleDbConnection1.Close();
> //dsLocal1.Clear();
> //oleDbDataAdapter4.Update(dsLocal1);
>
> //oleDbDataAdapter4.Fill(dsLocal1);
> //oleDbConnection1.Close();
> //oleDbConnection1.Close();
>
> //Close the database connection.
> cn.Close();
>
> MessageBox.Show("Local Detailed Damage Assessment has been
> updated.");
> }
>
> private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
> {
> Close();
>

newbie having trouble with SQL connection, insert/update

Thanks to anyone who helps!
I'm building a data entry windows form that requires the data to
be sent to a SQL Server table (the table's name is "Local") when the
form's "SAVE" button is clicked. I've built the form using the windows
form designer, and now I'm attempting to use SQL statements to
insert/update data into the table. There are six fields that will use
SQL statements to send data to the Local table, and none of the fields
are permitted to be null values. Two of the fields are comboboxes that
are populated through the use of their own respective datasets
(DisasterType and Dwelling); the datasets were built using the forms
designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
WorkerName, and DamageDescGeneral) that require the user to manually
enter data. Finally, there is a checkbox (Utilities) that should be
checked if the parameter is "yes/true."
My main problem is that I don't know the correct SQL code that
will allow
the data to be inserted/updated into the Local table. Or, can the
issue be solved using the forms designer? There are also two other
issues.
1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
be changed before being sent to the Local table. I am using the more
user-readable names and descriptions of the respective fields to
populate the form, rather than their Primary Key ID's. However, their
Primary Keys are also Foreign Keys in the Local table, so their
datatypes have to be changed. You'll see this attempted conversion in
the "//convert Disaster_Type to Disaster_ID" and "//convert
Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
recognize the While statement I'm using. Here is the error: Cannot
find method 'While(boolean)' in 'ArcMaster.frmLocal'
2. The datatype for the Utilities column in the Local table is a bit
value, but the datatype on the windows form is a boolean checkbox. I
need to be able to convert from boolean to bit through the use of an If
statement, but I keep getting errors. Here are my compile errors under
the current configuration:
Type 'boolean' is not assignable to 'Object'
Type 'int' is not assignable to 'Object'
I've included my code. I KNOW it's wrong, and I'm hoping
someone can
assist me. Thanks again!!!
import System.Drawing.*;
import System.Collections.*;
import System.ComponentModel.*;
import System.Windows.Forms.*;
import System.Data.*;
import System.Data.SqlClient.*;
import System.*;
/**
* Summary description for Local.
*/
public class frmLocal extends System.Windows.Forms.Form
{
//windows forms designer variables
private System.Windows.Forms.Label lblTitle;
private System.Windows.Forms.TextBox txtFamilyName;
private System.Windows.Forms.Label lblFamilyName;
private System.Windows.Forms.TextBox txtWorkerName;
private System.Windows.Forms.Label lblWorkerName;
private System.Windows.Forms.Label lblDisasterType;
private System.Windows.Forms.ComboBox cboDisasterType;
private System.Windows.Forms.Label lblDwellingType;
private System.Windows.Forms.ComboBox cboDwellingType;
private System.Windows.Forms.Label lblUtilities;
private System.Windows.Forms.Label lblDegreeOfDamage;
private System.Windows.Forms.Label lblDamageDescGeneral;
private System.Windows.Forms.TextBox txtDamageDescGeneral;
private System.Windows.Forms.Button cmdCloseWindow;
private System.Windows.Forms.Button cmdClearEntry;
private System.Windows.Forms.Button cmdSave;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
private ArcMaster.dsDisasterType dsDisasterType1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
private ArcMaster.dsDwelling dsDwelling1;
private System.Windows.Forms.CheckBox chkUtilities;
/**
* Required designer variable.
*/
private System.ComponentModel.Container components = null;
public frmLocal()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent
call
//
}
/**
* Clean up any resources being used.
*/
protected void Dispose(boolean disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
super.Dispose(disposing);
}
#region Windows Form Designer generated code
private void frmLocal_Load (Object sender, System.EventArgs e)
{
//populate disaster type combobox with data from Disaster_Type
table
dsDisasterType1.Clear();
oleDbDataAdapter3.Fill(dsDisasterType1);
oleDbConnection1.Close();
//populate dwelling type combobox with data from Dwelling table
dsDwelling1.Clear();
oleDbDataAdapter4.Fill(dsDwelling1);
oleDbConnection1.Close();
}
private void cmdSave_Click (Object sender, System.EventArgs e)
{
//SQL variables
Object sql;
SqlConnection cn;
DataSet dsLocal = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand myCommand;
SqlDataReader reader;
//initialize the variables that handle data to be bound to
Local table;
Object FamilyName = txtFamilyName.get_Text();
Object Dwelling = cboDwellingType.get_SelectedItem();
Object DisasterType = cboDisasterType.get_SelectedItem();
Object WorkerName = txtWorkerName.get_Text();
Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
Object Utilities = chkUtilities.get_CheckState();
//Set the connection string of the SqlConnection object to
connect to the ARC database
cn = new SqlConnection("Server=METZLER"+
"Integrated security=SSPI;" +
"database=ARC");
myCommand= new SqlCommand(sql, cn);
cn.Open();
//convert Disaster_Type to Disaster_ID
sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
((Disaster_Name = " + DisasterType + " ))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
DisasterType = reader.GetValue(0);
}
reader.Close();
//convert Dwelling_Desc to Dwelling_Type
sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
= " + Dwelling + "))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
Dwelling = reader.GetValue(0);
}
reader.Close();
//Initialize the SqlCommandBuilder object to automatically
generate and
//initialize the UpdateCommand, the InsertCommand, and the
DeleteCommand
//properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
da.Fill(dsLocal, "Local");
//convert Utilites data from text datatype to bit datatype
If (Utilities = true);
{
Utilities = 1;
}
If (Utilities = false);
{
Utilities = 0;
}
//SQL statement to insert data into Local table
sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
"VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
+ "',
'" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
Utilities;
da.Update(dsLocal, "Local");
//oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
//oleDbConnection1.Close();
//dsLocal1.Clear();
//oleDbDataAdapter4.Update(dsLocal1);
//oleDbDataAdapter4.Fill(dsLocal1);
//oleDbConnection1.Close();
//oleDbConnection1.Close();
//Close the database connection.
cn.Close();
MessageBox.Show("Local Detailed Damage Assessment has been
updated.");
}
private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
{
Close();Hi
You insert statement does not have a closing bracket, you also seem to be
making values strings by enquoting them when they are not character data
types. You could form the sql string differently depending on whether
utility is true or not.
John
"pmetz" <p1metzler@.yahoo.com> wrote in message
news:1144497354.135933.54280@.i40g2000cwc.googlegroups.com...
>
> Thanks to anyone who helps!
> I'm building a data entry windows form that requires the data to
> be sent to a SQL Server table (the table's name is "Local") when the
> form's "SAVE" button is clicked. I've built the form using the windows
>
> form designer, and now I'm attempting to use SQL statements to
> insert/update data into the table. There are six fields that will use
> SQL statements to send data to the Local table, and none of the fields
> are permitted to be null values. Two of the fields are comboboxes that
>
> are populated through the use of their own respective datasets
> (DisasterType and Dwelling); the datasets were built using the forms
> designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
> WorkerName, and DamageDescGeneral) that require the user to manually
> enter data. Finally, there is a checkbox (Utilities) that should be
> checked if the parameter is "yes/true."
> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table. Or, can the
> issue be solved using the forms designer? There are also two other
> issues.
>
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
>
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
>
> statement, but I keep getting errors. Here are my compile errors under
>
> the current configuration:
> Type 'boolean' is not assignable to 'Object'
> Type 'int' is not assignable to 'Object'
>
> I've included my code. I KNOW it's wrong, and I'm hoping
> someone can
> assist me. Thanks again!!!
>
> import System.Drawing.*;
> import System.Collections.*;
> import System.ComponentModel.*;
> import System.Windows.Forms.*;
> import System.Data.*;
> import System.Data.SqlClient.*;
> import System.*;
>
> /**
> * Summary description for Local.
> */
> public class frmLocal extends System.Windows.Forms.Form
> {
>
> //windows forms designer variables
> private System.Windows.Forms.Label lblTitle;
> private System.Windows.Forms.TextBox txtFamilyName;
> private System.Windows.Forms.Label lblFamilyName;
> private System.Windows.Forms.TextBox txtWorkerName;
> private System.Windows.Forms.Label lblWorkerName;
> private System.Windows.Forms.Label lblDisasterType;
> private System.Windows.Forms.ComboBox cboDisasterType;
> private System.Windows.Forms.Label lblDwellingType;
> private System.Windows.Forms.ComboBox cboDwellingType;
> private System.Windows.Forms.Label lblUtilities;
> private System.Windows.Forms.Label lblDegreeOfDamage;
> private System.Windows.Forms.Label lblDamageDescGeneral;
> private System.Windows.Forms.TextBox txtDamageDescGeneral;
> private System.Windows.Forms.Button cmdCloseWindow;
> private System.Windows.Forms.Button cmdClearEntry;
> private System.Windows.Forms.Button cmdSave;
> private System.Data.OleDb.OleDbConnection oleDbConnection1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
> private ArcMaster.dsDisasterType dsDisasterType1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
> private ArcMaster.dsDwelling dsDwelling1;
> private System.Windows.Forms.CheckBox chkUtilities;
>
> /**
> * Required designer variable.
> */
> private System.ComponentModel.Container components = null;
>
> public frmLocal()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
>
> //
> // TODO: Add any constructor code after InitializeComponent
> call
> //
>
> }
>
> /**
> * Clean up any resources being used.
> */
> protected void Dispose(boolean disposing)
> {
> if (disposing)
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> super.Dispose(disposing);
>
> }
>
> #region Windows Form Designer generated code
> private void frmLocal_Load (Object sender, System.EventArgs e)
> {
> //populate disaster type combobox with data from Disaster_Type
> table
> dsDisasterType1.Clear();
> oleDbDataAdapter3.Fill(dsDisasterType1);
> oleDbConnection1.Close();
>
> //populate dwelling type combobox with data from Dwelling table
>
> dsDwelling1.Clear();
> oleDbDataAdapter4.Fill(dsDwelling1);
> oleDbConnection1.Close();
>
> }
>
> private void cmdSave_Click (Object sender, System.EventArgs e)
> {
> //SQL variables
> Object sql;
> SqlConnection cn;
> DataSet dsLocal = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> SqlCommand myCommand;
> SqlDataReader reader;
> //initialize the variables that handle data to be bound to
> Local table;
> Object FamilyName = txtFamilyName.get_Text();
> Object Dwelling = cboDwellingType.get_SelectedItem();
> Object DisasterType = cboDisasterType.get_SelectedItem();
> Object WorkerName = txtWorkerName.get_Text();
> Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
> Object Utilities = chkUtilities.get_CheckState();
>
> //Set the connection string of the SqlConnection object to
> connect to the ARC database
> cn = new SqlConnection("Server=METZLER"+
> "Integrated security=SSPI;" +
> "database=ARC");
>
> myCommand= new SqlCommand(sql, cn);
>
> cn.Open();
>
> //convert Disaster_Type to Disaster_ID
> sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
> ((Disaster_Name = " + DisasterType + " ))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> DisasterType = reader.GetValue(0);
> }
> reader.Close();
>
> //convert Dwelling_Desc to Dwelling_Type
> sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
>
> = " + Dwelling + "))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> Dwelling = reader.GetValue(0);
> }
> reader.Close();
>
> //Initialize the SqlCommandBuilder object to automatically
> generate and
> //initialize the UpdateCommand, the InsertCommand, and the
> DeleteCommand
> //properties of the SqlDataAdapter.
> cmdBuilder = new SqlCommandBuilder(da);
>
> da.Fill(dsLocal, "Local");
>
> //convert Utilites data from text datatype to bit datatype
> If (Utilities = true);
> {
> Utilities = 1;
> }
> If (Utilities = false);
> {
> Utilities = 0;
> }
>
> //SQL statement to insert data into Local table
> sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
> Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
> "VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
> + "',
> '" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
> Utilities;
>
> da.Update(dsLocal, "Local");
>
> //oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
> //oleDbConnection1.Close();
> //dsLocal1.Clear();
> //oleDbDataAdapter4.Update(dsLocal1);
>
> //oleDbDataAdapter4.Fill(dsLocal1);
> //oleDbConnection1.Close();
> //oleDbConnection1.Close();
>
> //Close the database connection.
> cn.Close();
>
> MessageBox.Show("Local Detailed Damage Assessment has been
> updated.");
> }
>
> private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
> {
> Close();
>

newbie having trouble w/ insert/update statments

Thanks to anyone who helps!
I'm building a data entry windows form that requires the data to
be sent to a SQL Server table (the table's name is "Local") when the
form's "SAVE" button is clicked. I've built the form using the windows
form designer, and now I'm attempting to use SQL statements to
insert/update data into the table. There are six fields that will use
SQL statements to send data to the Local table, and none of the fields
are permitted to be null values. Two of the fields are comboboxes that
are populated through the use of their own respective datasets
(DisasterType and Dwelling); the datasets were built using the forms
designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
WorkerName, and DamageDescGeneral) that require the user to manually
enter data. Finally, there is a checkbox (Utilities) that should be
checked if the parameter is "yes/true."
My main problem is that I don't know the correct SQL code that
will allow
the data to be inserted/updated into the Local table. Or, can the
issue be solved using the forms designer? There are also two other
issues.
1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
be changed before being sent to the Local table. I am using the more
user-readable names and descriptions of the respective fields to
populate the form, rather than their Primary Key ID's. However, their
Primary Keys are also Foreign Keys in the Local table, so their
datatypes have to be changed. You'll see this attempted conversion in
the "//convert Disaster_Type to Disaster_ID" and "//convert
Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
recognize the While statement I'm using. Here is the error: Cannot
find method 'While(boolean)' in 'ArcMaster.frmLocal'
2. The datatype for the Utilities column in the Local table is a bit
value, but the datatype on the windows form is a boolean checkbox. I
need to be able to convert from boolean to bit through the use of an If
statement, but I keep getting errors. Here are my compile errors under
the current configuration:
Type 'boolean' is not assignable to 'Object'
Type 'int' is not assignable to 'Object'
I've included my code. I KNOW it's wrong, and I'm hoping
someone can
assist me. Thanks again!!!
import System.Drawing.*;
import System.Collections.*;
import System.ComponentModel.*;
import System.Windows.Forms.*;
import System.Data.*;
import System.Data.SqlClient.*;
import System.*;
/**
* Summary description for Local.
*/
public class frmLocal extends System.Windows.Forms.Form
{
//windows forms designer variables
private System.Windows.Forms.Label lblTitle;
private System.Windows.Forms.TextBox txtFamilyName;
private System.Windows.Forms.Label lblFamilyName;
private System.Windows.Forms.TextBox txtWorkerName;
private System.Windows.Forms.Label lblWorkerName;
private System.Windows.Forms.Label lblDisasterType;
private System.Windows.Forms.ComboBox cboDisasterType;
private System.Windows.Forms.Label lblDwellingType;
private System.Windows.Forms.ComboBox cboDwellingType;
private System.Windows.Forms.Label lblUtilities;
private System.Windows.Forms.Label lblDegreeOfDamage;
private System.Windows.Forms.Label lblDamageDescGeneral;
private System.Windows.Forms.TextBox txtDamageDescGeneral;
private System.Windows.Forms.Button cmdCloseWindow;
private System.Windows.Forms.Button cmdClearEntry;
private System.Windows.Forms.Button cmdSave;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
private ArcMaster.dsDisasterType dsDisasterType1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
private ArcMaster.dsDwelling dsDwelling1;
private System.Windows.Forms.CheckBox chkUtilities;
/**
* Required designer variable.
*/
private System.ComponentModel.Container components = null;
public frmLocal()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent
call
//
}
/**
* Clean up any resources being used.
*/
protected void Dispose(boolean disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
super.Dispose(disposing);
}
#region Windows Form Designer generated code
private void frmLocal_Load (Object sender, System.EventArgs e)
{
//populate disaster type combobox with data from Disaster_Type
table
dsDisasterType1.Clear();
oleDbDataAdapter3.Fill(dsDisasterType1);
oleDbConnection1.Close();
//populate dwelling type combobox with data from Dwelling table
dsDwelling1.Clear();
oleDbDataAdapter4.Fill(dsDwelling1);
oleDbConnection1.Close();
}
private void cmdSave_Click (Object sender, System.EventArgs e)
{
//SQL variables
Object sql;
SqlConnection cn;
DataSet dsLocal = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand myCommand;
SqlDataReader reader;
//initialize the variables that handle data to be bound to
Local table;
Object FamilyName = txtFamilyName.get_Text();
Object Dwelling = cboDwellingType.get_SelectedItem();
Object DisasterType = cboDisasterType.get_SelectedItem();
Object WorkerName = txtWorkerName.get_Text();
Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
Object Utilities = chkUtilities.get_CheckState();
//Set the connection string of the SqlConnection object to
connect to the ARC database
cn = new SqlConnection("Server=METZLER"+
"Integrated security=SSPI;" +
"database=ARC");
myCommand= new SqlCommand(sql, cn);
cn.Open();
//convert Disaster_Type to Disaster_ID
sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
((Disaster_Name = " + DisasterType + " ))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
DisasterType = reader.GetValue(0);
}
reader.Close();
//convert Dwelling_Desc to Dwelling_Type
sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
= " + Dwelling + "))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
Dwelling = reader.GetValue(0);
}
reader.Close();
//Initialize the SqlCommandBuilder object to automatically
generate and
//initialize the UpdateCommand, the InsertCommand, and the
DeleteCommand
//properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
da.Fill(dsLocal, "Local");
//convert Utilites data from text datatype to bit datatype
If (Utilities = true);
{
Utilities = 1;
}
If (Utilities = false);
{
Utilities = 0;
}
//SQL statement to insert data into Local table
sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
"VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
+ "',
'" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
Utilities;
da.Update(dsLocal, "Local");
//oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
//oleDbConnection1.Close();
//dsLocal1.Clear();
//oleDbDataAdapter4.Update(dsLocal1);
//oleDbDataAdapter4.Fill(dsLocal1);
//oleDbConnection1.Close();
//oleDbConnection1.Close();
//Close the database connection.
cn.Close();
MessageBox.Show("Local Detailed Damage Assessment has been
updated.");
}
private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
{
Close();> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table.
Rather than build a SQL statement string by concatenating the values, I
suggest you always use parameters. This is more secure and simplifies your
code since you don't need to format data, escape quotes or enclose values.
For Example:
sql = "INSERT INTO Local" +
" (" +
" Local_rowguid, " +
" ARC_Worker, " +
" Description, " +
" Disaster_ID, " +
" Dwelling_Type, " +
" Family_Name, " +
" Utilities)" +
" VALUES" +
" (" +
" @.Local_rowguid, " +
" @.ARC_Worker, " +
" @.Description, " +
" @.Disaster_ID, " +
" @.Dwelling_Type, " +
" @.Family_Name, " +
" @.Utilities" +
" )";
SqlCommand insertCommand =
new SqlCommand(sql, cn);
insertCommand.Parameters.Add(@.Local_rowguid, Guid.NewGuid());
insertCommand.Parameters.Add(@.FamilyName, FamilyName);
insertCommand.Parameters.Add(@.Dwelling, Dwelling);
insertCommand.Parameters.Add(@.DisasterType, DisasterType);
insertCommand.Parameters.Add(@.WorkerName, WorkerName);
insertCommand.Parameters.Add(@.DamageDescGeneral, DamageDescGeneral);
insertCommand.Parameters.Add(@.DamageDescGeneral, Utilities);
Note that SqlCommandBuilder will generate parameterized INSERT/UPDATE/DELETE
SQL commands for the DataAdapter so you don't need to code those yourself.
You could use NEWID() to assign the Local_rowguid value (instead of
Guid.NewGuid()) and omit that parameter but my personal preference is to
supply the Guid value on the client side with single-row inserts. This way,
the value is known by your app so you don't retrieve the assigned value.

> Or, can the issue be solved using the forms designer?
It's unclear how you intend your app to work because the code is incomplete
are using a variety of data access techniques. Since you are a newbie, I
suspect you're just learning how to use these classes.
The DataAdapter Update method will execute all the necessary
INSERT/UPDATE/DELETE commands depending on the changes made
to the DataSet. In order to use Update, the dataset needs to contain both
before and after data images.
Normally, one uses the Fill method to load existing data into the dataset.
Your
application can then manipulate data in the dataset (adding rows,
changing values, deleting rows), This can be done manually or by binding
controls /datasets. Finally, the DataSet Update method is called to save
changes
to the database.
The 'Local' table dataset should probably be a member variable rather than a
local
variable if you want to use DataSet Update within your save method.
Otherwise,
your save method can be used only to insert new data.

> There are also two other
> issues.
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
I expect that these are just a few of many compile errors because your code
is a mix of VB, C# and Java.
Assuming you are using C#, that language is case-sensitive. Specify 'while'
instead of 'While' and ditch the semi-colon after the predicate. For
example:
while(reader.Read())
{
Dwelling = reader.GetValue(0);
}
C# is a strongly-typed language so you should specify the proper types when
possible and convert explicitly. To access property values, don't call the
accessor method directly; specify only the property name.
string FamilyName = txtFamilyName.Text;
string Dwelling = (string)cboDwellingType.SelectedItem;
string DisasterType = (string)cboDisasterType.SelectedItem;
string WorkerName = txtWorkerName.Text;
string DamageDescGeneral = txtDamageDescGeneral.Text;
bool Utilities = chkUtilities.Checked;
The above example also shows how to avoid selecting the Dwelling and
DisasterType values from the database again. It looks like you've already
loaded those datasets in your init code. With typed datasets, all you need
to do is set the combobox DisplayMember and ValueMember ti the desired
properties and so that SelectedItem returns the selected value.

> import System.Drawing.*;
Specify 'using' instead of 'import' for namespace references:
using System.Drawing;
Hope this helps.
Dan Guzman
SQL Server MVP
"pmetz" <p1metzler@.yahoo.com> wrote in message
news:1144446095.869601.105850@.z34g2000cwc.googlegroups.com...
> Thanks to anyone who helps!
> I'm building a data entry windows form that requires the data to
> be sent to a SQL Server table (the table's name is "Local") when the
> form's "SAVE" button is clicked. I've built the form using the windows
> form designer, and now I'm attempting to use SQL statements to
> insert/update data into the table. There are six fields that will use
> SQL statements to send data to the Local table, and none of the fields
> are permitted to be null values. Two of the fields are comboboxes that
> are populated through the use of their own respective datasets
> (DisasterType and Dwelling); the datasets were built using the forms
> designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
> WorkerName, and DamageDescGeneral) that require the user to manually
> enter data. Finally, there is a checkbox (Utilities) that should be
> checked if the parameter is "yes/true."
> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table. Or, can the
> issue be solved using the forms designer? There are also two other
> issues.
>
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
>
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
> statement, but I keep getting errors. Here are my compile errors under
> the current configuration:
> Type 'boolean' is not assignable to 'Object'
> Type 'int' is not assignable to 'Object'
>
> I've included my code. I KNOW it's wrong, and I'm hoping
> someone can
> assist me. Thanks again!!!
>
> import System.Drawing.*;
> import System.Collections.*;
> import System.ComponentModel.*;
> import System.Windows.Forms.*;
> import System.Data.*;
> import System.Data.SqlClient.*;
> import System.*;
>
> /**
> * Summary description for Local.
> */
> public class frmLocal extends System.Windows.Forms.Form
> {
>
> //windows forms designer variables
> private System.Windows.Forms.Label lblTitle;
> private System.Windows.Forms.TextBox txtFamilyName;
> private System.Windows.Forms.Label lblFamilyName;
> private System.Windows.Forms.TextBox txtWorkerName;
> private System.Windows.Forms.Label lblWorkerName;
> private System.Windows.Forms.Label lblDisasterType;
> private System.Windows.Forms.ComboBox cboDisasterType;
> private System.Windows.Forms.Label lblDwellingType;
> private System.Windows.Forms.ComboBox cboDwellingType;
> private System.Windows.Forms.Label lblUtilities;
> private System.Windows.Forms.Label lblDegreeOfDamage;
> private System.Windows.Forms.Label lblDamageDescGeneral;
> private System.Windows.Forms.TextBox txtDamageDescGeneral;
> private System.Windows.Forms.Button cmdCloseWindow;
> private System.Windows.Forms.Button cmdClearEntry;
> private System.Windows.Forms.Button cmdSave;
> private System.Data.OleDb.OleDbConnection oleDbConnection1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
> private ArcMaster.dsDisasterType dsDisasterType1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
> private ArcMaster.dsDwelling dsDwelling1;
> private System.Windows.Forms.CheckBox chkUtilities;
>
> /**
> * Required designer variable.
> */
> private System.ComponentModel.Container components = null;
>
> public frmLocal()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
>
> //
> // TODO: Add any constructor code after InitializeComponent
> call
> //
>
> }
>
> /**
> * Clean up any resources being used.
> */
> protected void Dispose(boolean disposing)
> {
> if (disposing)
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> super.Dispose(disposing);
>
> }
>
> #region Windows Form Designer generated code
> private void frmLocal_Load (Object sender, System.EventArgs e)
> {
> //populate disaster type combobox with data from Disaster_Type
> table
> dsDisasterType1.Clear();
> oleDbDataAdapter3.Fill(dsDisasterType1);
> oleDbConnection1.Close();
>
> //populate dwelling type combobox with data from Dwelling table
>
> dsDwelling1.Clear();
> oleDbDataAdapter4.Fill(dsDwelling1);
> oleDbConnection1.Close();
>
> }
>
> private void cmdSave_Click (Object sender, System.EventArgs e)
> {
> //SQL variables
> Object sql;
> SqlConnection cn;
> DataSet dsLocal = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> SqlCommand myCommand;
> SqlDataReader reader;
> //initialize the variables that handle data to be bound to
> Local table;
> Object FamilyName = txtFamilyName.get_Text();
> Object Dwelling = cboDwellingType.get_SelectedItem();
> Object DisasterType = cboDisasterType.get_SelectedItem();
> Object WorkerName = txtWorkerName.get_Text();
> Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
> Object Utilities = chkUtilities.get_CheckState();
>
> //Set the connection string of the SqlConnection object to
> connect to the ARC database
> cn = new SqlConnection("Server=METZLER"+
> "Integrated security=SSPI;" +
> "database=ARC");
>
> myCommand= new SqlCommand(sql, cn);
>
> cn.Open();
>
> //convert Disaster_Type to Disaster_ID
> sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
> ((Disaster_Name = " + DisasterType + " ))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> DisasterType = reader.GetValue(0);
> }
> reader.Close();
>
> //convert Dwelling_Desc to Dwelling_Type
> sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
> = " + Dwelling + "))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> Dwelling = reader.GetValue(0);
> }
> reader.Close();
>
> //Initialize the SqlCommandBuilder object to automatically
> generate and
> //initialize the UpdateCommand, the InsertCommand, and the
> DeleteCommand
> //properties of the SqlDataAdapter.
> cmdBuilder = new SqlCommandBuilder(da);
>
> da.Fill(dsLocal, "Local");
>
> //convert Utilites data from text datatype to bit datatype
> If (Utilities = true);
> {
> Utilities = 1;
> }
> If (Utilities = false);
> {
> Utilities = 0;
> }
>
> //SQL statement to insert data into Local table
> sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
> Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
> "VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
> + "',
> '" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
> Utilities;
>
> da.Update(dsLocal, "Local");
>
> //oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
> //oleDbConnection1.Close();
> //dsLocal1.Clear();
> //oleDbDataAdapter4.Update(dsLocal1);
>
> //oleDbDataAdapter4.Fill(dsLocal1);
> //oleDbConnection1.Close();
> //oleDbConnection1.Close();
>
> //Close the database connection.
> cn.Close();
>
> MessageBox.Show("Local Detailed Damage Assessment has been
> updated.");
> }
>
> private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
> {
> Close();
>