Solved

Getting query result in a variable

Posted on 2008-06-12
12
264 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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