dilshad_ch
asked on
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
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
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
ASKER
dear rstomar:
i used tostring but it gives an error
"A first chance exception of type 'System.NullReferenceExcep tion' occurred in maincode.exe"
i used tostring but it gives an error
"A first chance exception of type 'System.NullReferenceExcep
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
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
ASKER
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.
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.
Are you sure that you have the right connectionstring ?
ASKER
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.ToStrin g())
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
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.ToStrin
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
looks like there is space between single quote and double quotes ...check and remove the spaces if any from :
' " & list1.SelectedItem & " '"
' " & list1.SelectedItem & " '"
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much
isn't this what I said
acctitle.Text = sel2.ExecuteScalar().ToStr