Question Casting typed values into SQL-recognizable strings

[VBobCat]

Hello people,

My application connects to an Access .accdb file and its data is read and written through SQL commands. So I wrote a function that takes data stored into my app's variables, the desired type, and returns the appropriate string in order to compose SQL expressions like "INSERT INTO ..." or "UPDATE ...".

But I ask you wether there is a simpler way to achieve this. Did I "discover the wheel" here?

Thank you very much!

    Function CSQL(ByVal given_value As Object, ByVal given_type As Type, Optional ByVal max_length As Integer = 0) As String
        Dim ValueSQL As String = "NULL"
        Select Case given_type
            Case GetType(Boolean)
                Try
                    ValueSQL = IIf(CBool(given_value), "TRUE", "FALSE")
                Catch ex As Exception
                End Try
            Case GetType(Short)
                Try
                    ValueSQL = CShort(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(Integer)
                Try
                    ValueSQL = CInt(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(Long)
                Try
                    ValueSQL = CLng(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(Decimal)
                Try
                    ValueSQL = CDec(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(Single)
                Try
                    ValueSQL = CSng(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(Double)
                Try
                    ValueSQL = CDbl(given_value).ToString.Replace(","c, "."c)
                Catch ex As Exception
                End Try
            Case GetType(String)
                Try
                    ValueSQL = CStr(given_value).Replace("'"c, "´"c)
                Catch ex As Exception
                End Try
                If max_length > 0 And ValueSQL.Length > max_length Then ValueSQL = ValueSQL.Substring(0, max_length)
                ValueSQL = "'" & ValueSQL & "'"
                If ValueSQL = "''" Then ValueSQL = "NULL"
            Case GetType(Date)
                Try
                    If IsDate(given_value) AndAlso CDate(given_value) > Date.MinValue Then ValueSQL = "#" & CDate(given_value).ToString("yyyy/MM/dd HH:mm:ss") & "#"
                Catch ex As Exception
                End Try
        End Select
        Return ValueSQL
    End Function

 

[Herman]

Normally one does what you are doing here through the query itself, because types in SQL and types in VB are different. If you query direct to Access, you can also use VBA functions, including CInt, CLng, etc. inside the query.

 

[JohnH]

Use parameters and the provider will handle the parameter values correctly. Using parameters has several other important benefits as well.

 

[jmcilhinney]

Use parameters and the provider will handle the parameter values correctly. Using parameters has several other important benefits as well.

Quite so. Follow the Blog link in my signature and check out my post on Parameters In ADO.NET to learn why and how. What you're trying to accomplish there is part of the why.

 

[VBobCat]

Quite so. Follow the Blog link in my signature and check out my post on Parameters In ADO.NET to learn why and how. What you're trying to accomplish there is part of the why.

Yes. My ideas about parameters were quite vague, and I just didn't know how to use them, and most important, why I should to. This is the key information I needed. Thank you (really) very much!

 

[VBobCat]

When studying parameters once ago, I searched strictly to MS-Access implementation, and I was puzzled then by notations like this (this is not my code, but a sample I've got somewherelse):

Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Delete * From Contacts Where FirstName = ? And LastName = ?"
Using conn As New OleDbConnection(ConnString)
    Using cmd As New OleDbCommand(SqlString, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
        cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Using

If my database is an Access file through an .OleDb.OleDbConnection, could I use the much clearer and more preferable named notation that you exemplify in you blog's post?

Thank you very much.

 

[JohnH]

through an .OleDb.OleDbConnection, could I use the much clearer and more preferable named notation

Yes, but the names is only relevant in .Net context. The provider doesn't use the names and the OleDbParameter objects must be added in order.
When adding parameterized queries in designer use ? as parameter placeholder.

 

[VBobCat]

Hello again. It seems I need your help once more.

First let me say that my objective was (and still is) to have all my INSERTs and UPDATEs made by functions that receive as parameter a typed Dictionary(Of String, Object) where the String Key is the name of each field, and the Object Value is the value to be inserted or updated in each field.

For insertion, I wrote this code:

Friend Function Inserir(dic_campos_valores As Dictionary(Of String, Object)) As Integer
        Dim fields_list, values_list As New List(Of String)
        Dim Comm As New OleDb.OleDbCommand
        For Each campo_existente In Campos
            If dic_campos_valores.ContainsKey(campo_existente.Key) Then
                fields_list.Add(campo_existente.Key)
                values_list.Add("@" & campo_existente.Key)
                Comm.Parameters.Add(New OleDb.OleDbParameter("@" & campo_existente.Key, dic_campos_valores(campo_existente.Key)))
            End If
        Next
        Dim Identity As DataTable = Nothing
        Using Conn As New OleDb.OleDbConnection With {.ConnectionString = EndCon}
            Comm.Connection = Conn
            Comm.CommandText = "INSERT INTO " & Nome & " (" & Join(fields_list.ToArray, ",") & ") VALUES (" & Join(values_list.ToArray, ",") & ");"
            Conn.Open()
            Comm.ExecuteNonQuery()
            Try
                Identity = LerConsulta("SELECT @@IDENTITY", Conn)
            Catch ex As Exception
            End Try
            Conn.Close()
        End Using
        If Identity IsNot Nothing AndAlso Identity.Rows.Count > 0 AndAlso Identity.Columns.Count > 0 AndAlso TypeOf Identity.Rows(0).Item(0) Is Integer Then Return CInt(Identity.Rows(0).Item(0))
        Return 0
    End Function

It worked well in my first tries, but now I have a table where some of the fields are optional. In old times of making up SQL strings without parameters, I would set them with the expression "NULL" inside the SQL string. That is the case of my field named NSE. I would do something like "INSERT INTO T_Idents (NUM,NSE) VALUES (644854984,NULL)". In this case, I tried to pass Nothing as Object, but then I've got this exception. It says that parameter @NSE has no default value. But I don't want my NSE field to have a default value. I want it to remain DBNull when it is not to be set to an actual number.

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217904
  Message=O parâmetro @NSE não tem valor padrão.
  Source=Microsoft Access Database Engine
  StackTrace:
       em System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       em System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       em System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       em System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       em System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       em System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       em SASP.Tabela.Inserir(Dictionary`2 dic_campos_valores) na D:\PROG\SASP\SASP\ModuleDados.vb:linha 515
       em SASP.FormPartePessoa.OK_Button_Click(Object sender, EventArgs e) na D:\PROG\SASP\SASP\FormPartePessoa.vb:linha 314
       ...
  InnerException:

So what now? Help!

Thank you very much!

 

[VBobCat]

I've done a bypass in line 5, so that entries with value of Nothing will not be included. That worked. Nevertheless I still feel a little clueless about parameters, which is certainly my fault, for lack of practice and study.

    Friend Function Inserir(dic_campos_valores As Dictionary(Of String, Object)) As Integer
        Dim fields_list, values_list As New List(Of String)
        Dim Comm As New OleDb.OleDbCommand
        For Each campo_existente In Campos
            If dic_campos_valores.ContainsKey(campo_existente.Key) AndAlso dic_campos_valores(campo_existente.Key) IsNot Nothing Then
                fields_list.Add(campo_existente.Key)
                values_list.Add("@" & campo_existente.Key)
                Comm.Parameters.Add(New OleDb.OleDbParameter With {.ParameterName = "@" & campo_existente.Key,
                                                                   .IsNullable = True,
                                                                   .Value = dic_campos_valores(campo_existente.Key)})
            End If
        Next
        Dim Identity As DataTable = Nothing
        Using Conn As New OleDb.OleDbConnection With {.ConnectionString = EndCon}
            Comm.Connection = Conn
            Comm.CommandText = "INSERT INTO " & Nome & " (" & Join(fields_list.ToArray, ",") & ") VALUES (" & Join(values_list.ToArray, ",") & ");"
            Conn.Open()
            Comm.ExecuteNonQuery()
            Try
                Identity = LerConsulta("SELECT @@IDENTITY", Conn)
            Catch ex As Exception
            End Try
            Conn.Close()
        End Using
        If Identity IsNot Nothing AndAlso Identity.Rows.Count > 0 AndAlso Identity.Columns.Count > 0 AndAlso TypeOf Identity.Rows(0).Item(0) Is Integer Then Return CInt(Identity.Rows(0).Item(0))
        Return 0
    End Function

 

[JohnH]

OleDbParameter.Value Property (System.Data.OleDb)

When you send a null parameter value to the server, you must specify DBNull, not null.

 

[VBobCat]

Thanks! With that information, I can now write the Update function, which will be able to erase previously filled fields, sending DBNull when the application stores Nothing as new value for that field.

By the way, is there an easy process to map the .OleDb.OleDbDataType types to and from ordinary .NET CLR types?

Thank you again!

 

[JohnH]

For a best guess based on type of value use the parameters.AddWithValue or the OleDbParameter(name, value as Object) constructor.
Not sure what you mean by other way, if you have a value you also have the CLR type.

 

[VBobCat]

For a best guess based on type of value use the parameters.AddWithValue or the OleDbParameter(name, value as Object) constructor.
Not sure what you mean by other way, if you have a value you also have the CLR type.

Ok, I will try .AddWithValue
About types, never mind. I read the documentation about the .OleDb.OleDbDataType, because there is an overload of parameters.Add that receives an .OleDb.OleDbDataType especification as argument. Then I noticed that the correspondence between these types and CLR types is not straight one-to-one, and then these thoughts came to me, because I was not comfortable about passing parameters without explicitly defining its types (I was afraid of errors raising upon ambiguous type conversion).
But obviously .NET can do this work for me better than I thought, and also certainly it doesn't do this as a "wild guess". These doubts come because I am not professional on this. I have to (and love to) write code for reasons of work, but my basis of knowledge is weak and full of flaws. And sadly I can't study as deep and wide as I should, because of my main work activities, which are not about programming.
Let me say, finally, that the help I receive in this forum is most valuable. Thank you again!