Solved

Getting query result in a variable

Posted on 2008-06-12
12
228 Views
Last Modified: 2010-04-23
Dear All,
please see the code.

Private Sub list1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles list1.Validated
        Dim v_titlecode As String = "test"
        Dim a As Object
        sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = ' " & list1.SelectedItem & " '"
        Conn.Open()
        Dim sel2 As New SqlCommand(sel_stat2, Conn)
        acctitle.Text = sel2.ExecuteScalar()
        Conn.Close()
        'MsgBox("list validated")
    End Sub

Now my requirement is that if user selects "ASSETS" from list then title_code which is "A" in table should be writen to acctitle.text field. but it shows nothing there.
i think my question is clear and well understandable.
Dilshad
0
Comment
Question by:dilshad_ch
  • 5
  • 4
  • 3
12 Comments
 
LVL 5

Expert Comment

by:rstomar
ID: 21766838
Since ExecuteScalar returns Object. You may have to convert it to string...try
acctitle.Text = sel2.ExecuteScalar().ToString()
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21766849
Do you get any error ?
Private Sub list1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles list1.Validated

Try

        Dim v_titlecode As String = "test"

        Dim a As Object

        sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = ' " & list1.SelectedItem & " '"

        Conn.Open()

        Dim sel2 As New SqlCommand(sel_stat2, Conn)

        acctitle.Text = sel2.ExecuteScalar()

        Conn.Close()

        'MsgBox("list validated")

catch ex as Exception

   messagebox.show ex.tostring()

end try

    End Sub

Open in new window

0
 

Author Comment

by:dilshad_ch
ID: 21766917
dear rstomar:
i used tostring but it gives an error
"A first chance exception of type 'System.NullReferenceException' occurred in maincode.exe"
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21766941
Are you sure that your query returns something ? After creating the query, can you show the value of sel_stat2 ?
sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = ' " & list1.SelectedItem & " '"

It's also good practice to check on a null-value before assigning it to a variable
dim _retVal as object = cmd.ExecuteScalar()

If (_retVal Is Nothing) Then

    'Null

Else

    acctitle.Text  = _retVal.ToString()

End If

Open in new window

0
 

Author Comment

by:dilshad_ch
ID: 21767109
Dhaest:
thank you for sparing time.
now i found that query returns no value. i showed sel2_stat value in msgbox it shows me as
SELECT title_code FROM ch_account_titles WHERE title_desc ='ASSETS'
whereas when i run this query in sqlserver EM it returns A
now tell me where is proble.

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21767173
Are you sure that you have the right connectionstring ?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dilshad_ch
ID: 21767221
Dhaest:
thanks again.
i think connectionstring is right. cos when i insert some records with the same string it works fine.
the complete code is

Imports System
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient





Public Class maincode
    Private strConn As String = "Data Source=DILSHAD;Initial Catalog=chacc;Integrated Security=True"
    Private Conn As New SqlConnection(strConn)
    Private Sub list1_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles list1.Validated
        Try
            Dim sel_stat2 As String
            Dim v_titlecode As String = "test"
            sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = ' " & list1.SelectedItem & " '"
            MsgBox(sel_stat2)
            Conn.Open()
            Dim sel2 As New SqlCommand(sel_stat2, Conn)
            'acctitle.Text = sel2.ExecuteScalar()
            Dim _retVal As Object = sel2.ExecuteScalar()
            If (_retVal Is Nothing) Then
                MsgBox("nothing there")
            Else
                acctitle.Text = _retVal.ToString()
            End If
            Conn.Close()
        catch ex as Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Private Sub insert_data_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles insert_data.Click
        Dim insert_stati As String


        'FROM(ch_main_chart)
        insert_stati = "insert into ch_main_chart values (3,2,4)"
        Conn.Open()
        'Dim sel As New SqlCommand(sel_stat, Conn)

        Dim cmd As New SqlCommand(insert_stati, Conn)
        'sel.ExecuteNonQuery()

        cmd.ExecuteNonQuery()
        Conn.Close()

    End Sub
0
 
LVL 5

Expert Comment

by:rstomar
ID: 21767284
looks like there is space between single quote and double quotes ...check and remove the spaces if any from :
' " & list1.SelectedItem & " '"
0
 

Author Comment

by:dilshad_ch
ID: 21767285
sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = 'ASSETS' "

further if i use the above statement i.e. hard code the title_desc values in code then it returns A in acctitle.Text successfully.

0
 
LVL 53

Accepted Solution

by:
Dhaest earned 100 total points
ID: 21767301
Can you try this query in your code:
sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = '" & list1.SelectedItem & "'"
0
 

Author Closing Comment

by:dilshad_ch
ID: 31466454
Thank you very much
0
 
LVL 5

Expert Comment

by:rstomar
ID: 21767436
isn't this what I said
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now