Returning 0 instead of the actual value

Question

I use the same SQL query within SQLiteStudio 3.1.0 and it works with no issues using the same parameter that I pass in. When I use the same SQL and use the code below in my VB project it returns 0 instead of right value.

Private Function GetData() As Object
     Dim sql As String = "Select Data from Table where Option = ?"
     Return FunctionObject(sql, New List(Of Object) From {"This is a Test"})
End Sub

Public Function FunctionObject(ByVal sql As String, ByVal parameters As List(Of Object)) As Object
    Using sqlconnection As New SQLiteConnection(ConnectionString)
          sqlconnection.Open()
          Using Cmd As New SQLiteCommand(sql, sqlconnection)
                Dim par As SQLiteParameter
                For Each parm As Object In parameters
                    par = Cmd.CreateParameter()
                    par.Value = parm
                   Cmd.Parameters.Add(par)
                Next
          FunctionObject = Cmd.ExecuteScalar()
          End Using
   End Using
End Function

I pass in list of objects into the method of the parameters. The connection string works because it is making other sql queries work. I have an identical set of code that uses OleDb that is built the exact same way as this and it works with this same SQL statement. So is there something I am missing that can make the behavior different from the SQLite and the OleDb connection and executeScalar since one returns 0 and the other returns the correct string?

What is `sqlQuery`? You never finished the query for `sql`. And the parameters also look unnamed. — TyCobb, Jan 20 '17 at 15:14
Sorry I didn't have the right variable in the Cmd. The sql is looking for the column data for the name SomeVariable. It gets passed in the method — DotNet Programmer, Jan 20 '17 at 15:56
Your statement though is still incomplete. `Select Data from Table where Option = ` is like `It is imperative that we.` ;) Look at this question: http://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp — TyCobb, Jan 20 '17 at 16:01
Your right. I was missing the ?. That isn't the problem. Since it is there in my actual code, I just must have left it out, So I am not sure why it returns 0 since this same works for all other sql queries that call this method — DotNet Programmer, Jan 20 '17 at 16:06
So what is `Data`? `ExecuteScalar` selects the first column of the first row of the result set. What is the value of that column and row? — Chris Dunaway, Jan 20 '17 at 16:34

score 1 · Accepted Answer · answered Jan 26 '17 at 17:24

It ended up being a Database field being wrong. It was suppose to be a VarChar(200) but instead it was a Numeric(200). So if you are getting issues with a wrong data being returned then you should look at the table itself. Something I learned from SQLite is it returns a vbCr so you should add .ToString().Replace(vbCr, String.Empty) to the ExecuteScalar().

Returning 0 instead of the actual value

1 Answers1