.NET .ADO でのSQL文でEXCELファイルのUPDATEで時刻の列に文字があると時刻が正しく入力されません。

質問

文字が含まれない列の時刻は正しく更新されるのですが、文字が含まれる列は通常は右寄せのはずが左寄せになってしまい、表示形式は「h:mm」なのですがセルを選択すると上のバー？に出るはずの秒が出ません・・・書式設定は一応、時刻になっているのですが・・・たぶん文字列になってしまっているような感じです。

EXCELファイルの形式は下記のようになっています。書式設定は、A列はすべて日付、BとC列はすべて時刻になっています。問題の「休」のセルも時刻のままです。
........A.........B........C.....
1......日付......出勤.....退勤
2....2010/1/1....7:54....18:24
3....2010/1/2....休........
4....2010/1/3....7:48....18:28
5....2010/1/4....7:56....18:33

日付と退勤の列はうまく更新できるのですが、B列の出勤列に「休」と書かれている行があるとB列の更新がうまくいきません。

ソースは、下記のようになっています。
con.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & _
"C:\DATE\タイムカード.xls;" & _
"Extended Properties=""Excel 12.0;HDR=YES;"""
Dim cmd As New OleDbCommand()
cmd.Connection = con
con.Open()
cmd.CommandText = "UPDATE [Sheet1$] SET 日付 = @hiduke, 出勤 = @jikoku, 退勤 = @nijikoku WHERE 日付 = 40203"
cmd.Parameters.Add("@hiduke", OleDbType.Variant).SourceColumn = "日付"
cmd.Parameters("@hiduke").Value = Format(DateValue("2010/1/21"), "yyyy/MM/dd")
cmd.Parameters.Add("@jikoku", OleDbType.Variant).SourceColumn = "出勤"
cmd.Parameters("@jikoku").Value = Format(TimeValue("2:40"), "H:mm")
cmd.Parameters.Add("@nijikoku", OleDbType.VarChar).SourceColumn = "退勤"
cmd.Parameters("@nijikoku").Value = Format(TimeValue("15:2"), "H:mm")
cmd.ExecuteNonQuery()

.NET自体初めてなのでおかしなところもあると思いますが、色々試しても結局どうにもならず現在は上記のような状態です。
上記の出勤と退勤のOleDbTypeが違いますが色々試しましたがすべてダメでした・・・
試したのは下記になります
Variant
VarChar
VarChar, 255
Char
DBTime
どのようにすればうまくいくでしょうか?

回答No.12

NOBNNN · ベストアンサー

#1です。
#10にも書いてありますが

＞＞説明不足で申し訳ないのですが、エクセルファイルの時刻を入力する列の書式設定は文字列でなく時刻＞＞にしたいのです。この列の書式設定をすべて文字列にしてしまうなら、特に変更なく私のソースでも問＞＞題なく更新できてしまいます・・・

SQLサーバーやＡＣＣＥＳＳのフィールドも当然、文字型 NvarChar(10) とか　あります。

それを　時刻型という型はありませんので日付時刻型（DateTime）にしなければなりません。

時刻の場合　1900/01/01 13:52:00.000 というような形になります。

これを無視して　「休」　などの文字を日付時刻型 に書き込むと　エラーになります。

だから　すべて文字列で扱うしかないのです。

#10 で説明した　出勤(時）と出勤(分）入力規則のリストで入力させて　VB.NET での
Excelシート入力時におけるＳＱＬで　合体させてあげれば大丈夫です。

(IIF(ISNUMERIC(TB.出勤_時),TB.出勤_時 & &':' & TB.出勤_分,TB.出勤_時)) as 出勤

というよな構文です　テストしてませんのでデバッグしてください

もし、文字型がどうしてもいやだというならExcelで「休」は別のセルに分けて記入
できるようにしてください。
ＤＢの出力項目も「休」(Nvarchar(4))と出勤（DtaeTime型）　としてください。

お礼

>すべて文字列で扱うしかないのです。
なるほど・・・やはりそうなのですね。
どの方法でやるか、やり方を検討してみます。

初心者の私にはNOBNNN様のソースが非常に勉強になりました。
ありがとうございました。

回答No.1

NOBNNN

サンプルプログラムを作成しました。
まず SQL SERVER 2005 もしくは2008 のManagement Studio で

Kintai というなまえのＤＢをつくってください。

　次にテーブルを追加するため。　以下のＳＱＬを　クエリ実行画面にコピー＆ペースト
　して実行してください。

USE [KINTAI]
GO
/****** オブジェクト: Table [dbo].[出勤名簿] スクリプト日付: 01/22/2010 02:07:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[出勤名簿](
[レコード番号] [int] IDENTITY(1,1) NOT NULL,
[社員番号] [nvarchar](10) NOT NULL,
[日付] [datetime] NOT NULL,
[出勤] [nvarchar](15) NULL,
[退勤] [nvarchar](15) NULL,
CONSTRAINT [PK_出勤名簿] PRIMARY KEY CLUSTERED
(
[レコード番号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

================

つづく