Getting query result in a variable

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
dilshad_chAsked:
Who is Participating?
 
DhaestConnect With a Mentor Commented:
Can you try this query in your code:
sel_stat2 = "SELECT title_code FROM ch_account_titles WHERE title_desc = '" & list1.SelectedItem & "'"
0
 
rstomarCommented:
Since ExecuteScalar returns Object. You may have to convert it to string...try
acctitle.Text = sel2.ExecuteScalar().ToString()
0
 
DhaestCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dilshad_chAuthor Commented:
dear rstomar:
i used tostring but it gives an error
"A first chance exception of type 'System.NullReferenceException' occurred in maincode.exe"
0
 
DhaestCommented:
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
 
dilshad_chAuthor Commented:
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
 
DhaestCommented:
Are you sure that you have the right connectionstring ?
0
 
dilshad_chAuthor Commented:
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
 
rstomarCommented:
looks like there is space between single quote and double quotes ...check and remove the spaces if any from :
' " & list1.SelectedItem & " '"
0
 
dilshad_chAuthor Commented:
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
 
dilshad_chAuthor Commented:
Thank you very much
0
 
rstomarCommented:
isn't this what I said
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.