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]