Passing a variable into a select statement out of vb.2010

I am just trying to convert some code from a vb6 program to .net and am going further crazy. Please consider the 4 lines of code.
The first line is obviously commented out but it works. When I call the numeric field, the Select will work and the data returns.

NOW my problem: The second line passes in a string. I have resorted, for testing purposes, to hard coding a search term but ordinarily it comes out of a list box as a variable and then populates the following 2 fields. This does NOT work and I don't know why. I've tried many permeations to no avail. I would love to hear what is wrong with the string.

Background: I know it fails because I get a debug that says it returned a null value and stops executing. The first code completes and no error is returned.

Thank you in advance for your help.

 ' Dim mSearchString = "SELECT *  FROM vw_domains WHERE ((vw_domains.idnumber)=2);"

Dim mSearchString = "SELECT *  FROM vw_domains WHERE ((vw_domains.domain)='www.allstatefund.com');"

Dim DomainListdt As DataTable = gConn.GetDataTable(mSearchString)
            TextBox1.Text = DomainListdt.Rows(0).Item("Pages_Table_Name")
            TextBox2.Text = DomainListdt.Rows(0).Item("Key_Word_Table")
            DomainListdt.Dispose()
beechdriver100Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

beechdriver100Author Commented:
I need to add. The IDNUMBER field and the DOMAIN field are the same row.

Thanks
mbizupCommented:
Give this a try:

Dim mSearchString = "SELECT *  FROM vw_domains WHERE vw_domains.domain='www.allstatefund.com';"

mbizupCommented:
I'm misreading your question.  What is the variable that you are trying to pass in?

The syntax would be along these lines:

Dim mSearchString = "SELECT *  FROM vw_domains WHERE vw_domains.domain =" & chr(34) & strStringVariable & chr(34)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

beechdriver100Author Commented:
I am guessing that you are right but what does the chr(34) do?
beechdriver100Author Commented:
I would have thought that would get it. I am still  getting a "null reference" though:

Dim datString = "www.allstatefund.com"
Dim mSearchString = "SELECT *  FROM vw_domains WHERE vw_domains.domain =" & Chr(34) & datString & Chr(34)
Dim DomainListdt As DataTable = gConn.GetDataTable(mSearchString)
MsgBox(DomainListdt.Rows.Count())
beechdriver100Author Commented:
The search string evaluated to
"SELECT *  FROM vw_domains WHERE vw_domains.domain ="www.allstatefund.com";"
kmslogicCommented:
Dim mSearchString as String = "SELECT *  FROM vw_domains WHERE ((vw_domains.domain)='www.allstatefund.com');"
beechdriver100Author Commented:
kmslogic and mbizup have the perfect answer except it does not work on a string; numeric values still work fine.
I've also included the gConn.GetDataTable code in case you can spot something there.
Public Function GetDataTable(ByVal sqlCommand As String, Optional ByVal key As String = "") As DataTable
        v_errMsg = ""
        Try
            Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sqlCommand, v_conn)
            Dim table As New DataTable

            adapter.Fill(table)
            adapter.Dispose()
            If key <> "" Then DataTableForUpdate.Add(sqlCommand, key)
            Return table
        Catch ex As Exception
            v_errMsg = ex.Message
            Return Nothing
        End Try
    End Function
Jacques Bourgeois (James Burger)PresidentCommented:
A stupid one, but it has to be asked anyway.

Are you sure that the data in the database is "www.allstatefund.com". Isn't it possible that somebody might have typed "www.allstatefund.com " with a space at the end or something of this type? One error I often see in stuff like that is somebody that made a copy and paste of the address when inputting data, and grabbed some invisible character in the web page when copying, such as a carriage return or line feed. It does not show when you look at the data, but it is there anyway and the criteria ends up inconclusive.
kmslogicCommented:
I added "as string" to you dim statement which is how .NET likes it.

As far as the query working when you have a number in your WHERE clause--the format is correct--I'd be checking my database to make sure what you are searching for really is in the table you want.  In other words is www.allstatefund.com in your search table and exactly as you have it typed (same case, no http:// in front etc.)
kmslogicCommented:
Ugh EE thanks for not having a warning that others are posting while I type!  Anyhow what James said.
mbizupCommented:
< I am guessing that you are right but what does the chr(34) do? >

They are double quotes.  Text values need to be delimited with quotes.
beechdriver100Author Commented:
I cut and pasted the code with the domain hard coded into access and it found the record. I asked myself that question many times during this quest and out is a fair question. I' ll try the declaration and see what happens.
beechdriver100Author Commented:
Ok, oh boy and darn.
Someplace buried in the Microsoft wisdon there must be a reserved word called domain. I got extra frustrated and put in error code on everyline and it started to report E_FAIL; which has to be a foreign language for "you will never fix this".

I changed the field "domain" to "domain_name" and it works.

No wonder I cry at night....

Thank you to both of you. I appreciate the handholding and I DID learn some things which is most important.
Jacques Bourgeois (James Burger)PresidentCommented:
Have you tried saving your command as a parameterized query in Access and calling it as a stored procedure from you code. This is the recommended method for most databases. Having SQL in the code should be reserved for situations where you have no access to the database and/or do not have the right to create a query/view/stored procedure in it.

This then leave Access to all the job, with a query that has been proven to work or not in Access. All you have to do in you application is pass the parameter:

Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("<YourQueryName>", v_conn)
adapter.SelectCommand.CommandType = CommandType.StoredProcedure
adapter.SelectCommand.Parameters.AddWithValue("<ParameterName>", "www.allstatefund.com")
'The rest of the code is the same
Jacques Bourgeois (James Burger)PresidentCommented:
Tanks to you, I have added to my list of reserved names for Access fields (Page, Pages, Name, Date and now Domain)
mbizupCommented:
beechdriver100,

<I changed the field "domain" to "domain_name" and it works.>

Excellent!  And interesting!

That is of course the optimal solution (renaming your field to something that is not a reserved word).  However in cases where you are unable to do so, using sqare brackets also resolves that issue:

Dim mSearchString = "SELECT *  FROM vw_domains WHERE vw_domains.[domain] =" & chr(34) & strStringVariable & chr(34)

I never would have guessed that was a reserved word.

<I have added to my list of reserved names for Access fields>

Is this database definitely Access?  The SQL Syntax zone is a generic zone for resolving issues in your queries, so it is always a good idea to clearly state in your questions what database you are using since the syntax can vary.  It is also good to include the specific database zone in your question (such as Access,Oracle, SQL Server, etc) when posting here so that it is clear what you are working with.  






It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.