?
Solved

what is wrong with this code -- sql query thru command object

Posted on 2006-04-14
7
Medium Priority
?
194 Views
Last Modified: 2010-04-23
this returns the error "Referenced object has a value of nothing" on the line rdr1 = cm1.executereader

I'm guessing it has to do with how I do parameters?


        Try
            c1 = New SqlConnection
            c1.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("Connection")
            c1.Open()

            cm1 = New SqlCommand
            cm1.CommandType = CommandType.Text
            cm1.Connection = c1
            cm1.CommandText = "SELECT desc [d] FROM table1_sql WHERE item = ? ORDER BY fn"
            cm1.Parameters.Add("@Desc", SqlDbType.Char, 15, txtItem.Text)
            rdr1 = cm1.ExecuteReader
            While rdr1.Read
                lstFtr.Items.Add(CType(rdr1("d"), String))
            End While
            rdr1.Close()

            c1.Close()

        Catch ex As Exception

        End Try
0
Comment
Question by:g_johnson
  • 4
  • 3
7 Comments
 
LVL 4

Author Comment

by:g_johnson
ID: 16454863
actually, the more i investigate the more i think it's a syntax error.

obviously i am doing something wrong when adding parameters, but i don't know what it is
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16454892
Hi g_johnson,

Is "desc" your field name? I think the problem lies there because "desc" is a reserved word (of the ORBER BY clause). Try putting it in square brakets like you did for the alias.

Cheers!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16454900
g_johnson,

and what happen if you don't use a parameter?  
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Author Comment

by:g_johnson
ID: 16455236
if i don't use a parameter, i'm fine
0
 
LVL 4

Author Comment

by:g_johnson
ID: 16455258
this is the actual line of code:
cm1.CommandText = "SELECT description_1 [d] FROM table1_sql WHERE item = ? ORDER BY fn"
and ex.message returns
"Line 1: Incorrect syntax near '?'."
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 16455267
Does fn is a valid fieldname?

Try this:
cm1.CommandText = "SELECT desc [d] FROM table1_sql WHERE item = @Desc ORDER BY fn"
0
 
LVL 4

Author Comment

by:g_johnson
ID: 16455304
got it -- just before I read your answer.  Now I understand!   :-)

Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question