parameterized queries on a public function..

[jlego]

kind of directed towards adatapost...

in most of my applications, i have a public function called DBUpdate()

Public Sub DB_Update(ByVal strQuery As String)
        Dim ADOCmd As New ADODB.Command

        Try

            If ADOConnect.State = ConnectionState.Open Then Call CloseConnection()


            ADOConnect.Open(dsn, user, pass)
            ADOCmd.ActiveConnection = ADOConnect
            ADOCmd.CommandText = strQuery
            ADOCmd.Execute()

        Catch ex As Exception

            ' sends error message to me directly with information i need to debug and correct

        Finally

              'clean up

        End Try

    End Sub

i have slimmed this down to make it very basic, however
call i do in is whenever i need to update

call db_update("UPDATE TABLE SET COLUMN = 'whatever' WHERE ID = whatever")

for example

how could i implement parameterized queries into a function like this?

thanks

[jlego]

also, before the information is passed to this function

i do clean the input fields..

example, if it is a textbox i dont allow entries like / \ or ' "

after focus is left from the textbox it is cleaned from invalid characters (to prevent copy and paste)

this is also just another example of how i try to protect from SQL injection.

[kvprajapati]

I think this is VB6 question. isn't it?

[jlego]

no its in .net

[kvprajapati]

Have look at this code.

Public Class DB
    Private mCmd As ADODB.Command
    Private CnStr As String = "...connection...string"

    Public Sub New(ByVal Sql As String)
        Dim Cn As New ADODB.Connection
        Cn.ConnectionString = CnStr
        mCmd = New ADODB.Command

        mCmd.CommandText = Sql
        Cn.Open()
        mCmd.ActiveConnection = Cn

    End Sub

    Public Sub AddP(ByVal name As String, ByVal type As ADODB.DataTypeEnum, ByVal size As Integer, ByVal value As Object)
        mCmd.Parameters.Append(mCmd.CreateParameter(name, type, , size, value))
    End Sub

    Public Function Execute() As Integer
        Dim recAff As Integer
        mCmd.Execute(recAff)
        mCmd.ActiveConnection.Close()
        Return recAff
    End Function

You instantiate the DB class by passing Sql.

Dim x As New DB("insert into emp (eno,ename,edate) values (?,?,?)")
    x.AddP("?", ADODB.DataTypeEnum.adInteger, 0, 10)
    x.AddP("?", ADODB.DataTypeEnum.adVarChar, 30, "AA")
    x.AddP("?", ADODB.DataTypeEnum.adDate, 8, "1-1-2002")
    x.Execute()

[jlego]

thanks