• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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()
0
beechdriver100
Asked:
beechdriver100
  • 7
  • 4
  • 3
  • +1
2 Solutions
 
beechdriver100Author Commented:
I need to add. The IDNUMBER field and the DOMAIN field are the same row.

Thanks
0
 
mbizupCommented:
Give this a try:

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

0
 
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)

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
beechdriver100Author Commented:
I am guessing that you are right but what does the chr(34) do?
0
 
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())
0
 
beechdriver100Author Commented:
The search string evaluated to
"SELECT *  FROM vw_domains WHERE vw_domains.domain ="www.allstatefund.com";"
0
 
kmslogicCommented:
Dim mSearchString as String = "SELECT *  FROM vw_domains WHERE ((vw_domains.domain)='www.allstatefund.com');"
0
 
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
0
 
Jacques Bourgeois (James Burger)Commented:
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.
0
 
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.)
0
 
kmslogicCommented:
Ugh EE thanks for not having a warning that others are posting while I type!  Anyhow what James said.
0
 
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.
0
 
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.
0
 
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.
0
 
Jacques Bourgeois (James Burger)Commented:
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
0
 
Jacques Bourgeois (James Burger)Commented:
Tanks to you, I have added to my list of reserved names for Access fields (Page, Pages, Name, Date and now Domain)
0
 
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.  






0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now