StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Sample for parameterized SQL operations, as suggested by SkinnyWhiteNinja</p> <p>I have a table with 4 colunms, CollCode and CollSeq are the key, TermType and TermText are the modifiable data The code explains how to insert, update or delete a row with parameters instaed if textvalues in the SQL. The code is valid only for ACCESS, SQL SERVER or MYSQL require different code for the template and have different DbTypes</p> <p>in the first part of the program:</p> <pre><code> ' Insert Dim DbConn As New OleDbConnection(SqlProv) Dim SQLTwInsert As String = "INSERT INTO SearchTerms (CollCode, CollSeq, TermType, TermText) VALUES (?, ?, ?, ?)" Dim DRTwInsert As OleDbDataReader = Nothing Dim DCCTwInsert As OleDbCommand Dim TwInsP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4) Dim TwInsP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4) Dim TwInsP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4) Dim TwInsP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255) DCCTwInsert = New OleDbCommand(SQLTwInsert, DbConn) DCCTwInsert.Parameters.Add(TwInsP1) DCCTwInsert.Parameters.Add(TwInsP2) DCCTwInsert.Parameters.Add(TwInsP3) DCCTwInsert.Parameters.Add(TwInsP4) ' Delete Dim SQLTwDelete As String = "DELETE FROM SearchTerms WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?" Dim DRTwDelete As OleDbDataReader = Nothing Dim DCCTwDelete As OleDbCommand Dim TwDelP1 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4) Dim TwDelP2 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4) Dim TwDelP3 As New OleDbParameter("@TermType", OleDbType.VarChar, 4) Dim TwDelP4 As New OleDbParameter("@TermText", OleDbType.VarChar, 255) DCCTwDelete = New OleDbCommand(SQLTwDelete, DbConn) DCCTwDelete.Parameters.Add(TwDelP1) DCCTwDelete.Parameters.Add(TwDelP2) DCCTwDelete.Parameters.Add(TwDelP3) DCCTwDelete.Parameters.Add(TwDelP4) ' Update Dim SQLTwUpdate As String = "UPDATE SearchTerms SET TermType = ?, TermText = ? WHERE CollCode = ? AND CollSeq = ? AND TermType = ? AND TermText = ?" Dim DRTwUpdate As OleDbDataReader = Nothing Dim DCCTwUpdate As OleDbCommand Dim TwUpdP1 As New OleDbParameter("@TermType", OleDbType.VarChar, 4) Dim TwUpdP2 As New OleDbParameter("@TermText", OleDbType.VarChar, 255) Dim TwUpdP3 As New OleDbParameter("@CollCode", OleDbType.VarChar, 4) Dim TwUpdP4 As New OleDbParameter("@CollSeq", OleDbType.Integer, 4) Dim TwUpdP5 As New OleDbParameter("@oldTermType", OleDbType.VarChar, 4) Dim TwUpdP6 As New OleDbParameter("@oldTermText", OleDbType.VarChar, 255) DCCTwUpdate = New OleDbCommand(SQLTwUpdate, DbConn) DCCTwUpdate.Parameters.Add(TwUpdP1) DCCTwUpdate.Parameters.Add(TwUpdP2) DCCTwUpdate.Parameters.Add(TwUpdP3) DCCTwUpdate.Parameters.Add(TwUpdP4) DCCTwUpdate.Parameters.Add(TwUpdP5) DCCTwUpdate.Parameters.Add(TwUpdP6) </code></pre> <p>in the processing part of the program:</p> <pre><code> ' Update TwUpdP1.Value = new value TermType TwUpdP2.Value = new value TermText TwUpdP3.Value = key value CollCode TwUpdP4.Value = key value CollSeq TwUpdP5.Value = old value TermType to avoid updating a row that 1 millisecond earlier was modified by someone else TwUpdP6.Value = old value TermText Try DRTwUpdate = DCCTwUpdate.ExecuteReader() Catch ex As Exception your type of report exception Finally If Not (DRTwUpdate Is Nothing) Then DRTwUpdate.Dispose() DRTwUpdate.Close() End If End Try ' Insert TwInsP1.Value = new key value CollCode TwInsP2.Value = new key value CollSeq TwInsP3.Value = value TermType TwInsP4.Value = value TermText Try DRTwInsert = DCCTwInsert.ExecuteReader() Catch ex As Exception your type of report exception Finally If Not (DRTwInsert Is Nothing) Then DRTwInsert.Dispose() DRTwInsert.Close() End If End Try ' Delete TwDelP1.Value = key value CollCode TwDelP2.Value = key value CollSeq TwDelP3.Value = old value TermType to avoid deleting a row that 1 millisecond earlier was modified by someone else TwDelP4.Value = old value TermText Try DRTwDelete = DCCTwDelete.ExecuteReader() Catch ex As Exception your type of report exception Finally If Not (DRTwDelete Is Nothing) Then DRTwDelete.Dispose() DRTwDelete.Close() End If End Try </code></pre> <p>Try it, it really avoids many problems, though a bit clumbsy to write it all.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload