show single result from query

slybitz

Member
Joined
Aug 16, 2005
Messages
9
Programming Experience
Beginner
Ok the below code seems ok to me but is not showing the right result. I have a text box called "txtTranNo" where the user will enter in a number. This number will then be used as a parameter for the sql statement that I have created and called "SQLStatement." When I run this query against the server through Query Analyzer it runs fine but I am hardcoding what would normally be entered into the text field for use in the query. I'm just trying to return a count # from the query but for some reason the only thing that returns is 0 for the recordcount and always 0 no matter what is inputted into the text box. Can anyone see what is going wrong with my code? Thanks!

VB.NET:
[size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] sConnStr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "server=SQLOLEDB.1;Integrated Security = SSPI;User ID=sa;Password=xxxx;Initial Catalog=mas500_app;Data Source=LONGS; Trusted_Connection=yes"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlConnection(sConnStr)

[/size][size=2][color=#008000]'Me.txtTranNo.Text = Right(0 + LTrim(RTrim(Me.txtTranNo.Text)), 10)

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] SQLStatement [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT COUNT(*) FROM LONGS.mas500_app.dbo.tpoPurchOrder WHERE TranNo = '" & [/size][size=2][color=#0000ff]Me[/color][/size][size=2].txtTranNo.Text & "'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] RecordCount [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] SelectCommand [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlClient.SqlCommand(SQLStatement, cn)

[/size][size=2][color=#0000ff]Try

[/color][/size][size=2]cn.Open()

RecordCount = [/size][size=2][color=#0000ff]CInt[/color][/size][size=2](SelectCommand.ExecuteScalar())

[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception

Message2.Text = "Failed to execute command"

[/size][size=2][color=#0000ff]Finally

[/color][/size][size=2]cn.Close()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try

[/color][/size][size=2]Message2.Text = "There is/are " & RecordCount & " matching PO(s)"[/size]
 
I thought that I'd already answered this but apparently not. You are putting single quotes around the field value in your SQL statement, which is just for text columns. Judging by the field name it is a numerical column, so removing the single quotes should probably fix the problem. You could also use a parameter instead of a literal value, which negates the need for single quotes on any values. Parameters also improve security.

Edit:
That first sentence is saying that I thought I'd already posted to this thread, not that you were asking a question again that I'd already answered. My bad, not yours. :)
 
Last edited:
Back
Top