Excel VBA データベースのデータを更新する際のシングルコーテーションについて

データベース（オラクル）からデータを取得、追加、更新、一連の処理について
すでに完成してはいるもののどうしてもシングルコーテーションの部分で詰まります。

検索フォーム、データ追加フォーム、データ更新フォーム　３つあります。
Excel上にコマンドボタンがあり、押すことでそれぞれのフォームが起動します。

データ更新フォームの流れとして、まずシートにオラクルから取得したデータが表示されています。（これはBookを立ち上げたとき表示されるようになっています）
データが表示されているセルを選択してコマンドボタン（データ更新）を押すと
フォームが起動し、フォーム上にあるテキストボックスに選択したセルのデータが入ります。（この処理がUserForm_Initialize、列１つに対し、１つのテキストボックス）
更新なのでどこかを訂正し、フォーム上の更新ボタンを押すと、オラクルに接続し、update文を行い、データを更新するといった内容です。

その際のシングルコーテーションについて
データ追加の時、このようなデータを追加したいとします。
A,B'cde,C,D,E（,はそれぞれのテキストボックスにという意味です）
シングルコーテーションを文字として扱う場合、SQL文のため、テキストボックスからデータを入力する際は
A,B''cde,C,D,Eとなります。2つつける必要があるからです。
これで、データベースには
A,B'cde,C,D,E　として追加され、シートに表示されるときもこのように表示されます。

これを更新する際
セルを選択して更新フォームを起動すると
各テキストボックスに
A,B'cde,C,D,Eと表示されます。しかし、データを更新する際、わざわざもう一度B''cdeとシングルコーテーションを入力する必要があります。
この無駄を省く方法が知りたいです。

updateはText6の内容以外を更新するようになっています

VBA
1Option Explicit
2Private Sub UserForm_Initialize()
3    TextBox1.Value = Cells(ActiveCell.Row, 1).Value
4    Text6.Value = Cells(ActiveCell.Row, 2).Value
5    Text7.Value = Cells(ActiveCell.Row, 3).Value
6    Text8.Value = Cells(ActiveCell.Row, 4).Value
7    Text9.Value = Cells(ActiveCell.Row, 5).Value
8    Text10.Value = Cells(ActiveCell.Row, 6).Value
9End Sub
10
11Private Sub CommandButton1_Click()  'データ更新
12    On Error GoTo ERROR
13    Dim con As New ADODB.Connection
14    Dim cmd As New ADODB.Command
15    Dim rs As New ADODB.Recordset
16    Dim param As ADODB.Parameter
17    Set cmd = New ADODB.Command
18    Dim ds As String            'データソース定義
19    Dim user As String          'ユーザ名
20    Dim pass As String          'パスワード
21    
22    ds = "Provider=OraOLEDB.oracle;"
23    ds = ds & "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.5)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)));"
24    ds = ds & "User ID=hoge;Password=hogehoge"
25    
26    con.ConnectionString = ds
27    con.Open
28
29　　'ここから追記したもの
30　　cmd.ActiveConnection = con
31    cmd.CommandTimeout = 0
32    cmd.CommandType = adCmdText
33    cmd.CommandText = "SELECT * FROM EDM WHERE DJNAME = ? AND SONGNAME = ? AND GENRE = ? AND LABEL = ? AND YEAR = ?"
34
35    Set param = cmd.CreateParameter("DJNAME", adVarChar, adParamInputOutput, 45)
36    cmd.Parameters.Append param
37    Set param = cmd.CreateParameter("SONGNAME", adVarChar, adParamInputOutput, 30)
38    cmd.Parameters.Append param
39    Set param = cmd.CreateParameter("GENRE", adVarChar, adParamInputOutput, 25)
40    cmd.Parameters.Append param
41    Set param = cmd.CreateParameter("LABEL", adVarChar, adParamInputOutput, 20)
42    cmd.Parameters.Append param
43    Set param = cmd.CreateParameter("YEAR", adVarNumeric, adParamInputOutput, 4)
44    cmd.Parameters.Append param
45
46　　'テキストボックスに取得されたもの
47　　cmd.Parameters("DJNAME").Value = Text6.Text
48    cmd.Parameters("SONGNAME").Value = Text7.Text
49    cmd.Parameters("GENRE").Value = Text8.Text
50    cmd.Parameters("LABEL").Value = Text9.Text
51    cmd.Parameters("YEAR").Value = Text10.Text
52
53    If TextBox1.Text = "" Or Text6.Text = "" Or Text7.Text = "" Or Text8.Text = "" Or Text9.Text = "" Or Text10.Text = "" Then
54        MsgBox "データをすべて入力してください"
55    Else
56        rs.CursorLocation = adUseClient
57        rs.CursorType = adOpenStatic
58        rs.Open cmd
59        rs.Open "Update EDM set DJNAME = '" & Text6.Text & "',SONGNAME = '" & Text7.Text & "',GENRE = '" & Text8.Text & "',LABEL = '" & Text9.Text & "', YEAR = '" & Text10.Text & "' where NO = '" & TextBox1.Text & "'", con
60        MsgBox "データを更新しました"
61    End If
62    
63    GoTo FINAL
64    
65ERROR:
66    MsgBox Erl() & "行目" & ERROR(Err())
67
68FINAL:
69    'ファイナル処理
70    On Error Resume Next
71    Set rs = Nothing
72    Set con = Nothing
73End Sub

行動規範の内容に同意します

回答1件

ベストアンサー

パラメータを使用すればエスケープは意識しなくて済みますし、SQLインジェクション対策にもなります。
クエリにパラメータを渡して結果を取得する（ADO）Command オブジェクト・Execute メソッド

Parameters コレクション (ADO)

ADODB.Commandのパラメータ埋め込みストリームを出力

ADO.NET　ORACLEでのSQL文内のパラメータの使い方

投稿2020/08/04 03:44

編集2020/08/04 08:07

sazi

総合スコア25173

EDM

2020/08/04 05:19 編集

リンクありがとうございます。拝見しましたが、私の理解不足でよく理解できません。これによって何がどうなるのかよくわからないのが正直な感想です Dim cmd As New ADODB.Command ... With cmd .ActiveConnection = con .CommandType = adCmdText .CommandText = "SQL文" .Prepared = True MsgBox "データ更新しました" End With こういう感じなのでしょうか？　Paramに関しては必要ないと思い入れていません。

sazi

2020/08/04 06:34 編集

parameterはバインド変数とも呼ばれます。最初のリンクだとサンプルが質問内容からみると分かりにくいですね。リンク追加しておきました。要は、パラメータは型を持っているから、文字列ならばその中身(今回の'など）は気にする必要が無いという事です。また、都度々々SQLの文字列として'で括る必要もありません。

EDM

2020/08/04 07:18 編集

最後のリンクを参考に直してみました。一度やってみたのをコードに書き直しました。このような感じでしょうか？ただ、動作はしませんでした。 cmd.CommandtextにUpdate文（これは今のSQL文でいいんですかね？）パラメータ作成。これはテーブルデータを作成した時のような感じと理解していいですか？ ("PJNAME", adVarChar, adParamInput, 20) この部分を ("行列名", varchar2, adParamInput, 40) みたいな感じでパラメータに値をセットのところで .value = の部分が '" & Text6～10.Text & "'が入る感じでしょうか？

sazi

2020/08/04 09:08 編集

> cmd.CommandtextにUpdate文（これは今のSQL文でいいんですかね？）パラメータに置き換えないと駄目です。 ?で指定した場合は、parameterに追加した順番に処理されます。名前で指定する方が間違えが無いので、さらにリンクを追加しておきます。 ↑と思ったけど、名前での指定はプロバイダ依存ですね。一応追加しておきました。パラメータのセット時のパラメータはoracleの型ではなくADOで定義されている型でないと駄目なはずです。

EDM

2020/08/04 08:12

cmd.CommandText = "SELECT * FROM EDM WHERE DJNAME = ? AND SONGNAME = ? AND GENRE = ? AND LABEL = ? AND YEAR = ?" としました。 cmd.Parameters("DJNAME").Value = "'" & Text6.Text & "'" これではだめだということですね？ cmd.Parameters("DJNAME").Value = "Text6.Text" このような感じでしょうかでも、これだとセレクトしただけなので、rs.Open　cmdのあとに rs.Open update文が入るという感じと思うのですがどうですか？

sazi

2020/08/04 08:21

> cmd.Parameters("DJNAME").Value = "Text6.Text" 値を入れて下さい。

EDM

2020/08/04 08:29

値を入れることは難しいと思いますがどうなんでしょうか質問で記載したように、どこでもいいからセルを選択した状態でデータ更新フォームを開くと、行の要素がそれぞれテキストボックスに入るわけなので、cmd.Parametersに値を入れると、その値だけしかパラメータしないと思うのですが、 insertとして　W&W, Comin' To Getchaを追加する際は W&W, Comin'' To Getcha こうなりますが表では DJNAME SONGNAME W&W, Comin' To Getcha となります。この行だけ、パラメータすれば問題ないと思いますが。ほかにも何行もあるわけです

sazi

2020/08/04 08:35 編集

cmd.Parameters("DJNAME").Value = Text6.Text として、コントロールの名前では無く値を入れて下さい。試せば分かる事の方か多いですけど、試せないんですか？

EDM

2020/08/04 08:58

すみません。このあと用事のため、試せません明日、試してみます。なるべく、本日いただいたリンク、アドバイスを元に自分の手でできるように頑張ります。もし、万が一動作不良が起きたらまたここにコメントします。ありがとうございました！

EDM

2020/08/05 01:31 編集

おはようございます。昨日はありがとうございました。コードを変更しました。実行してみると、UserForm_Initialize()の処理で選択した行をテキストボックスに取得、一部を訂正して、データ更新すると、「オブジェクトが開いている場合、操作は許可されません」のエラー。 rs.Openを２つselectとupdateがあるからダメなのでしょうか？ rs.Open cmdを行った後に、rs.Closeを組んでみたら、引用符付き文字列が正しく終了していませんと。となると、createparameterのところなのか、sql文なのか。値を入れる部分について cmd.Parameters("DJNAME").Value = Text6.Text　こうなのか UserForm_Initialize()のように cmd.Parameters("DJNAME").Value = Cells(ActiveCell.Row, 列).Valueなのか　これだと取得した値がパラメータに入ることになると考えましたが。それか、Text.Textに入っている値を一度変数に入れて、値の部分に変数を指定してあげるとか？

sazi

2020/08/05 02:03 編集

追記されたコード見ましたが、selectは何のために行っているんでしょう？パラメータセットするのはチェックの後でupdateの直前に行うようにしないと無駄が多いですね。 updateにwhere条件がありませんけど？

EDM

2020/08/05 02:12

cmd.commandTextにupdate文を入れる感じなのですか？パラメータに置き換えないといけないということなので、リンクの内容でselect文だったので、selectで取得して、置き換えているのかなと思ったのですが。チェックのあとというのは？paramにセットして、値を入れてupdateという流れですよね？ updateには条件あります。NOというのが

sazi

2020/08/05 02:36 編集

NO見落としてました。試すにしてもあまりに適当すぎますね。理解する所から始めるレベルですから、テスト用のテーブルを作ってそのテーブルに対するテスト用の処理を作成し、動作の確認ができてから応用するようにされてはどうですか。

EDM

2020/08/05 02:24

わかりました。一度テスト用を作成して理解してきます。

EDM

2020/08/05 08:11

報告です。パラメータを使い、無事エスケープ意識することなく、データの更新ができました。データ追加フォームのほうもパラメータを使い、意識することなくデータの追加ができました。やはり一度テストでやってみたことで理解することができました。ありがとうございました。

sazi

2020/08/05 11:44

お疲れ様です。