CODEWITHSUNDEEP
.netasp.netsqlvb.net.net-3.5
oshirowanen
oshirowanen

Reputation: 15965

Count records in database

I have the code below which does work, but I need to add further functionality to it. The functionality I want to add to it is the text I have commented in the code below.

Dim objSQLConnection As SqlConnection
Dim objSQLCommand As SqlCommand

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
    Dim intID As Integer = CType(Request.Form("ID"), Integer)
    Dim strHeading As String = CType(Request.Form("Heading"), String)
    Dim intState As Integer = CType(Request.Form("State"), Integer)
    Dim strUser As String = CType(Request.Form("User"), String)

    objSQLConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("connString"))

    ''#count if records with this user already exist in the database below

    If intState = 1 Then
        objSQLCommand = New SqlCommand("insert into table1 (id, heading, user) values (@intID, @strHeading, @strUser)", objSQLConnection)

        objSQLCommand.Parameters.Add("@intID", SqlDbType.Int, 4).Value = intID
        objSQLCommand.Parameters.Add("@strHeading", SqlDbType.VarChar, 255).Value = strHeading
        objSQLCommand.Parameters.Add("@strUser", SqlDbType.VarChar, 3).Value = strUser
    ElseIf intState = 0 Then
        objSQLCommand = New SqlCommand("delete from table1 where id = @intID and user = @strUser", objSQLConnection)

        objSQLCommand.Parameters.Add("@intID", SqlDbType.Int, 4).Value = intID
        objSQLCommand.Parameters.Add("@strUser", SqlDbType.VarChar, 3).Value = strUser
    End If

    objSQLCommand.Connection.Open()
    objSQLCommand.ExecuteNonQuery()
    objSQLCommand.Connection.Close()
End

Before the if statement, I want to find out if the database already has records with the username in the strUser variable. What is the best way to go about doing this?

Upvotes: 1

Views: 640

Answers (2)

MAW74656
MAW74656

Reputation: 3549

Simple enough. Just do ExecuteScalar on the database with a query like "Select field1 from table where username = @username" and pass the strUser into the query object. Then save the scalar result into a variable (result). Then you can do if result is <> "", do such and such.

Upvotes: 1

JonH
JonH

Reputation: 33183

I'm not going to do all of this for you but a simple way to check the count is store the result of a stored procedure (SQL Server) into a SqlDataReader and check if the count > 0 or if HasRows = True.

With (myObjectCommand)
    .Parameters.Add("@strUser", SqlDbType.Varchar, 3).Value = myUser
    myReader = .ExecuteReader
End With

if myReader.HasRows
    return true ?
end if

Upvotes: 1

Related Questions