Solved

Getting query result in a variable

Posted on 2008-06-12
12
251 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.

825 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