no record found check

[skaswani]

hi, can u please tell me how to check NOT RECORD FOUND Check

i mean if the record set is empty then a msg box may be shown .

thanks


i m using vb.net (vs) with sql server 2005


===================================

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionstring As String = "Data Source = .\SQLEXPRESS; Initial Catalog = master; Integrated Security = true"
        Dim theDatabase As New SqlClient.SqlConnection(connectionstring)
        theDatabase.Open()
        Dim thecommand As New SqlClient.SqlCommand
        thecommand.CommandType = CommandType.StoredProcedure
        thecommand.CommandText = "Getname"

        thecommand.Connection = theDatabase

        thecommand.Parameters.AddWithValue("@PriKey", 25)
        Dim outparam As SqlClient.SqlParameter = thecommand.Parameters.Add("@RecName", SqlDbType.Char, 50)
        outparam.Direction = ParameterDirection.Output

        thecommand.ExecuteNonQuery()

        MsgBox(outparam.Value)



        TextBox1.Text = Trim(outparam.Value)



    End Sub
End Class

 

[Redmo]

You could try a simple loop, if outparam.value isn't empty then output value, if it is, then output a simple message..

thecommand.ExecuteNonQuery()

if outparam.value is not nothing then
        MsgBox(outparam.Value)



        TextBox1.Text = Trim(outparam.Value)

else
msgbox("There was no value")
end if
    End Sub

 

[skaswani]

error on

If outparam.Value Is Not Nothing Then





Error 1 'Is' operator does not accept operands of type 'Integer'. Operands must be reference or nullable types. C:\Users\Sajjad\Documents\Visual Studio 2008\Projects\WindowsApplication2\WindowsApplication2\Form1.vb 20 30 WindowsApplication2

 

[skaswani]

actully i need to check if the recordset is null / no records found or not,.. not the null value in a field

got it?

anyone can guideme
thanks

 

[CoachBarker]

Since you are using thecommand.ExecuteNonQuery() you can do:

If thecommand.ExecuteNonQuery() = 0 Then 
       ' no matching were records found 
      MessageBox.Show("No matching records found.", "Message")
End If

 

[cjard]

hi, can u please tell me how to check NOT RECORD FOUND Check

i mean if the record set is empty then a msg box may be shown .

Youre not using a recordset, youre calling a stored procedure and wanting it to dump a value into a parameter.

We cannot see the code in your stored procedure so we are going to struggle to advise you. You'd be advised to check, within the stored procedure, how many records have been selected, and throw an exception if none are selected. In Oracle, we have this behaviour by default; a SELECT ... INTO <variable name> must select exactly one variable.. 0 throws a NO_DATA_FOUND, and too many throws a TOO_MANY_ROWS

If youre not selecting values, but making them up ont he spot, you'll have to code your stored procedure to throw an exception


Note1: If youre expecting a single value back, you should be returning it from your stored procedure and using ExecuteScalar()
Note2: I cannot guarantee that CoachBarker's advice will work in the case of a SELECT. ExecuteNonQuery is normlly used for UPDATE, INSERT or DELETE operations that concretely affect rows. You may find that it is always returning 0 on a SELECT operation because the db engine may not know at the time of returning from the call, how many rows it has selected

 

[cjard]

actully i need to check if the recordset is null / no records found or not,..

Youre not using a recordset.. If youre asking us to help you code a stored procedure that can deal with there being no records in a database, then why would you show us client side code?

Recordset = old language, not used any more, stop using this term
DataSet/DataTable = new language, client side temp store of info for data interactions
database

= a table in a server side database

 

[CoachBarker]

No I was wrong with what I posted, did not realize it was a query,he should fill a data set then check the rows returned, if it 1 there is a recored, if it 0 there is no record.

Sorry for the misunderstanding.

 

[cjard]

Well, for a single value, using a dataset is overkill.. Returning the relevant value from the proc rather than dumping it into a variable, and then using ExecuteScalar would be fine.

Actually, he can use the tableadapter wizard to help write all this code.. SO I really should be saying to him to read DW2..