Thread: Casting typed values into SQL-recognizable strings

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

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.

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

Originally Posted by JohnH

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.

Originally Posted by jmcilhinney

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!

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.

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.

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.

Code:

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!

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

OleDbParameter.Value Property (System.Data.OleDb)

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

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!

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.

Originally Posted by 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.

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!