Execute Scalar Problem+Sourabh Das

[sd1978]

Hi All,
Please see the code below:

                    Dim vencode As String
                    conn.Open()
                    Dim cmdvenstr As String = "Select vendor_code from tbl_vendor where vendor_name='" & cmbVendor.Text.Trim() & "'"
                    Dim cmdven As New SqlCommand(cmdvenstr, conn)
                    vencode = cmd.ExecuteScalar()
                    MessageBox.Show(vencode, "IG", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

on the messagebox everything is coming other than the value of vencode. where am I going wrong. In the database vendor_code datatype is nvarchar(50).

[stimbo]

Have you tried filling a datatable to see if there's definitely data being returned? The code looks fine to me. And I take it there are no errors, it's just showing a blank messagebox where the text should be. You also need to remember to close the connection before displaying the messagebox, although I don't know if that could cause an issue.


Just as an additional comment (Although this wont be the reason for the problem ) ExecuteScalar returns an object so you might be better declaring vencode as an Object and then using the .ToString when displaying the result in a messagebox.

[sd1978]

I did try by converting the vencode as object but still not working, neither its returning any values, but the same query is working fine in the sql server.

[stimbo]

It might have something to do with the combobox.Text. Try this instead:

Dim cmdvenstr As String = "Select vendor_code from tbl_vendor where vendor_name = @Vendor"
Dim cmdven As New SqlCommand(cmdvenstr, conn)
cmdven.Parameters.AddWithValue("@Vendor", Me.cmdvendor.selectedItem.Tostring.trim)

[sd1978]

Hi this code is giving an blue under line in the thrid line as "AddWithValue" is not a member SqlparameterCollection class

[sd1978]

Hey Stimbo..,
I was a stupid..
read my first thread carefully, you will understand what is the mistake..
even you didnt notice, if you are unable to figure out let me know.. its a very very very silly mistake..

[stimbo]

I don't why it's doing that, I've used the code loads of times. It should work. When I decalre a new SQL command, the parameters and add with value both appear when typing it.

Well, forget about the parameters for the time being, what happens if you replace the combobox.Text in your sql string with the .selecteditem text instead? Something like this, or whatever the correct format is.

"Select vendor_code from tbl_vendor where vendor_name='" & cmbVendor.SelectedItem.ToString. & "'"

[sd1978]

My dear sweet friend,

Dim vencode As String
                    conn.Open()
                    Dim cmdvenstr As String = "Select vendor_code from tbl_vendor where vendor_name='" & cmbVendor.Text.Trim() & "'"
                    Dim cmdven As New SqlCommand(cmdvenstr, conn)
                    vencode = cmd.ExecuteScalar()
                    MessageBox.Show(vencode, "IG", MessageBoxButtons.OK, MessageBoxIcon.Information

just see I have marked the difference in bold. we both are good programmers.. no great programmers...

[sd1978]

it should have been

cmdven.ExecuteScalar()

[stimbo]

Doh! Posted the last one before seeing your follow up.

Well, my code should work fine now - that's what's important!