locked
Option Strict On - Get error at <> RRS feed

  • Question

  • Visual Basic 2012

    Hello All,

    If I have Option Strict ON (which I want on) I can’t use the <> symbols in the code below.

    Is there something else I can do? The code looks for an existing User in a database table.

    Ifcmd.ExecuteScalar() <> 0 Then

                    Ifcn.State = ConnectionState.OpenThen

                        cn.Close()

                        cn.Dispose()

                        cmd = Nothing

                    EndIf

                    Me.txtRecipient.Focus()

                    MessageBox.Show(g_sMsgUserAlreadyExists, "Duplicate Recipient", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

                    Exit Sub

                EndIf

    Thanks,

    ADawn


    ADawn

    Thursday, September 5, 2013 2:34 PM

Answers

  • Hello,

    ExecuteScalar returns an object, the value of the first column, first row. If the value in your case is an Integer than cast ExecuteScalar to an integer.

    If you are doing a INSERT or UPDATE then use ExecuteNonQuery which returns the count of rows affected by your SQL statement.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.



    • Edited by KareninstructorMVP Thursday, September 5, 2013 2:44 PM ..................
    • Proposed as answer by John Smith 3 Thursday, September 5, 2013 3:04 PM
    • Marked as answer by tropicwhisper Monday, September 16, 2013 3:16 PM
    Thursday, September 5, 2013 2:43 PM
  • So, you are stating that there are Good programmers and Smart programmers?

    So, you would have Option Strict set to (Off)?

    How would you handle 'Widening' and 'Narrowing' conversions? Isn't that important? Or, I'm I overthinking this stuff?

    ADawn


    ADawn

    Our team policy is Option Strict On period. Since starting with VB.NET back in VS2003 I may have turn Option Strict Off once in a code module with less than 10 lines of code (hard to remember if this actually happened). Moving from "good" or "smart" programmer let's call it "being professional" where a professional understands what they can do with what is available to get a task done optimally.  If you widen or narrow something you understand why the decision was made rather than just doing it. IMHO if you see code that does something that is questionable then it is your responsibility to research until you understand the reasoning behind how a particular task was done and if opposed to this change it to what it should be. On a similar note many developers see LINQ as the "shiny object" and will use it for that reason where there are many time conventional code should be used rather than LINQ.

    Now here is a good example of narrowing/widening. All of our primary keys in our enterprise database tables are defined as Decimal yet are Integers. So when working with these values I pass an Integer to a parameter in a command object as Integer and let the backend handle the conversion. Keys are allows a whole number, never containing anything to the right of the decimal point (actually there is no decimal point). Now when I interact with the key in a DataTable wrapper in a BindingSource to invoke the Find method it does not matter if I pass a decimal or Integer. On the other hand if for some extremely odd reason we had keys like 1.4 instead of 1 than we still work the same way as the key field I track using BindingSource.Current cast as a DataRowView to a DataRow is is valid when I pass that value back to a BindingSource Find method or to a SQL command parameter.  You can now come back and challenge me with something and I will come up with a solution and why I did what I did without guessing. The key example is something done without thought because I know it works and why it works. Also here is another thought, I know developers will struggle with working with time in a database and can not figure out how to come up with a valid solution, they will pound away with code and never consider working at the database table definition or if the database has a time type and of course .NET does not how does one handle it. We get back to pounding away at code while why bother if you understand types and ramifications of our actions we can create a specialized control to deal with this, in this case a DataGridView (so happens I just wrote an article on this here).

    So with that said I feel a developer needs to research and try to break their code to understand their boundaries with Option Strict On. Most likely once you understand your boundaries there will be less than one percent of your developer life to use Option Strict Off. Spending time on another forum the people turning Option Strict Off are dealing with lating binding of Office automation and that is it.

    EDIT

    Example of keys I spoke of above


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    • Edited by KareninstructorMVP Friday, September 6, 2013 6:30 PM .......................
    • Marked as answer by Carl Cai Thursday, September 12, 2013 7:35 AM
    Friday, September 6, 2013 6:19 PM

All replies

  • Hello,

    ExecuteScalar returns an object, the value of the first column, first row. If the value in your case is an Integer than cast ExecuteScalar to an integer.

    If you are doing a INSERT or UPDATE then use ExecuteNonQuery which returns the count of rows affected by your SQL statement.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.



    • Edited by KareninstructorMVP Thursday, September 5, 2013 2:44 PM ..................
    • Proposed as answer by John Smith 3 Thursday, September 5, 2013 3:04 PM
    • Marked as answer by tropicwhisper Monday, September 16, 2013 3:16 PM
    Thursday, September 5, 2013 2:43 PM
  • Kevininstructor,

    I'm using a SELECT Statement against the database table to search for an existing person. In the past I use ExecuteNonQuery checking for rows however I thought (head somewhere) it more effenient to use ExecuteScalar.

    How would I cast ExecuteScalar to an integer?

    ADawn


    ADawn

    Thursday, September 5, 2013 5:02 PM
  • If CInt(cmd.ExecuteScalar()) <> 0 Then

    --
    Andrew

    Thursday, September 5, 2013 5:47 PM
  • Hello,

    In the code below I am working against the table shown below the code. The SQL selects two fields even thou we instruct via ExecuteScalar to return the first column only which in the case is the primary key Identifier, we would use ExecuteReader to get both fields. Does this make sense?

    Any ways the calling code will return in the MessageBox "ID for Pin 8456 is '2'"

    Calling the code

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim Identifier As Int32 = GetTable1Identifier(8456)
        If Identifier > 0 Then
            MessageBox.Show("ID for Pin 8456 is '" & Identifier.ToString & "'")
        End If
    End Sub

    Code to query the database table

    Public Function GetTable1Identifier(ByVal Pin As Int32) As Int32
        Dim Result As Int32 = 0
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText = "SELECT Identifier, LastName, PIN FROM Table1 WHERE (((Table1.PIN)=@P1));"
                Dim IdentifierParameter As New OleDb.OleDbParameter With
                    {
                        .DbType = DbType.Int32,
                        .ParameterName = "P1",
                        .Value = Pin
                    }
                cmd.Parameters.Add(IdentifierParameter)
                cn.Open()
                Result = CInt(cmd.ExecuteScalar)
            End Using
        End Using
        Return Result
    End Function

    Table data


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, September 5, 2013 5:55 PM
  • Should I use:

    If CInt(cmd.ExecuteScalar()) <> 0 Then

    OR

    If Convert.ToInt64(cmd.ExecuteScalar()) <> 0 Then

    ADawn


    ADawn

    Friday, September 6, 2013 2:27 PM
  • Either will work.  The first line converts to Int32, the second line converts to int64.  If I remember correctly, an ID column is int64, so using the latter would be slightly better.

    The difference between genius and stupidity is that genius has its limits.

    Friday, September 6, 2013 2:33 PM
  • I would use CInt but ToInt64 should be fine aso.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 6, 2013 2:34 PM
  • Kevininstructor,

    I'm just trying to be a good/better programmer.

    Isn't CInt the old way vs the new .Net way of Convert.ToInt64?

    I'm developing in VB2012 .net.

    ADAwn


    ADawn

    Friday, September 6, 2013 2:55 PM
  • In the case of being a good programmer you make a decision, if I use convert to a Long (this is what Int64 is) and have to work with Integers with Option Strict On you would need to cast this variable using CInt anyways as per the image below. Of course if you have Option Strict Off well you get the picture.

    Just because something appears better does not mean it is better for the task at hand.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 6, 2013 4:02 PM
  • The is not much difference between CInt and Convert.ToInt64.  CInt is in the VisualBasic namespace and Convert.ToInt64 is in the System.Namespace.  You want to avoid the VisualBasic.Compatibility namespace.  This namespace is used when converting VB6 (or prior) to .Net.  See this old article http://msdn.microsoft.com/en-us/library/aa289509.aspx#vbtchmicrosoftvisualbasicnetinternalsanchor4 for more information particularly the Visual Basic Runtime section. The info is still valid with later versions of VS.

    The difference between genius and stupidity is that genius has its limits.

    Friday, September 6, 2013 4:05 PM
  • Kevininstructor,

    I'm just trying to be a good/better programmer.

    Isn't CInt the old way vs the new .Net way of Convert.ToInt64?

    I'm developing in VB2012 .net.

    ADAwn


    ADawn

    It's referred to as an Inline Type Conversion and from that MSDN reference:

    "As a rule, you should use the Visual Basic type conversion functions in preference to the .NET Framework methods such as ToString(), either on the Convert class or on an individual type structure or class. The Visual Basic functions are designed for optimal interaction with Visual Basic code, and they also make your source code shorter and easier to read. In addition, the .NET Framework conversion methods do not always produce the same results as the Visual Basic functions, for example when converting Boolean to Integer."


    Please call me Frank :)

    Friday, September 6, 2013 4:10 PM
  • Kevininstructor,

    I'm just trying to be a good/better programmer.

    Isn't CInt the old way vs the new .Net way of Convert.ToInt64?

    I'm developing in VB2012 .net.

    ADAwn


    ADawn

    It's referred to as an Inline Type Conversion and from that MSDN reference:

    "As a rule, you should use the Visual Basic type conversion functions in preference to the .NET Framework methods such as ToString(), either on the Convert class or on an individual type structure or class. The Visual Basic functions are designed for optimal interaction with Visual Basic code, and they also make your source code shorter and easier to read. In addition, the .NET Framework conversion methods do not always produce the same results as the Visual Basic functions, for example when converting Boolean to Integer."


    Please call me Frank :)


    Totally agree.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 6, 2013 4:13 PM
  • So, you are stating that there are Good programmers and Smart programmers?

    So, you would have Option Strict set to (Off)?

    How would you handle 'Widening' and 'Narrowing' conversions? Isn't that important? Or, I'm I overthinking this stuff?

    ADawn


    ADawn

    Friday, September 6, 2013 5:25 PM
  • So, you are stating that there are Good programmers and Smart programmers?

    So, you would have Option Strict set to (Off)?

    How would you handle 'Widening' and 'Narrowing' conversions? Isn't that important? Or, I'm I overthinking this stuff?

    ADawn


    ADawn

    Our team policy is Option Strict On period. Since starting with VB.NET back in VS2003 I may have turn Option Strict Off once in a code module with less than 10 lines of code (hard to remember if this actually happened). Moving from "good" or "smart" programmer let's call it "being professional" where a professional understands what they can do with what is available to get a task done optimally.  If you widen or narrow something you understand why the decision was made rather than just doing it. IMHO if you see code that does something that is questionable then it is your responsibility to research until you understand the reasoning behind how a particular task was done and if opposed to this change it to what it should be. On a similar note many developers see LINQ as the "shiny object" and will use it for that reason where there are many time conventional code should be used rather than LINQ.

    Now here is a good example of narrowing/widening. All of our primary keys in our enterprise database tables are defined as Decimal yet are Integers. So when working with these values I pass an Integer to a parameter in a command object as Integer and let the backend handle the conversion. Keys are allows a whole number, never containing anything to the right of the decimal point (actually there is no decimal point). Now when I interact with the key in a DataTable wrapper in a BindingSource to invoke the Find method it does not matter if I pass a decimal or Integer. On the other hand if for some extremely odd reason we had keys like 1.4 instead of 1 than we still work the same way as the key field I track using BindingSource.Current cast as a DataRowView to a DataRow is is valid when I pass that value back to a BindingSource Find method or to a SQL command parameter.  You can now come back and challenge me with something and I will come up with a solution and why I did what I did without guessing. The key example is something done without thought because I know it works and why it works. Also here is another thought, I know developers will struggle with working with time in a database and can not figure out how to come up with a valid solution, they will pound away with code and never consider working at the database table definition or if the database has a time type and of course .NET does not how does one handle it. We get back to pounding away at code while why bother if you understand types and ramifications of our actions we can create a specialized control to deal with this, in this case a DataGridView (so happens I just wrote an article on this here).

    So with that said I feel a developer needs to research and try to break their code to understand their boundaries with Option Strict On. Most likely once you understand your boundaries there will be less than one percent of your developer life to use Option Strict Off. Spending time on another forum the people turning Option Strict Off are dealing with lating binding of Office automation and that is it.

    EDIT

    Example of keys I spoke of above


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    • Edited by KareninstructorMVP Friday, September 6, 2013 6:30 PM .......................
    • Marked as answer by Carl Cai Thursday, September 12, 2013 7:35 AM
    Friday, September 6, 2013 6:19 PM
  • Kevininstructor

    That has to be the best explanation of any question that I've asked to date.

    And, it's answers like the one you just provided that can help people with lessor knowledge.

    Thank You!

    ADawn


    ADawn

    Friday, September 6, 2013 6:42 PM
  • Kevininstructor

    That has to be the best explanation of any question that I've asked to date.

    And, it's answers like the one you just provided that can help people with lessor knowledge.

    Thank You!

    ADawn


    ADawn

    Glad my response was helpful :-)

    With that said my goals are to strive for others here to become better at their profession and I am constantly in learning mode where I learn from the more intelligent people here than me so it never stops or being surprised when one of the other frequent posters comes up with a solution I never considered which is what makes these forums a great place to come to keep the learning process moving forward never static.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 6, 2013 6:58 PM