syntax to see if record exists in SQL!! (ASAP!)

[ST]

Hi,
I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to
write the syntax to check a sql table to see if the record already exists
based on firstname and lastname text fields (will match to firstname and
lastname in SQL table). I can't figure out the syntax!!! I would like the
Error msg to just display and exit the sub if the row exists. help!!!!
thanks!

[JV]

try a sql statement something like:

SELECT count(*) from myTable where lastname=@theLa stName and
firstname=@theF irstName

where the parameters with the @ signs are where you plug in the values you
are searching on. Then you will know not only if one exists, but how many.

You can also use the EXISTS clause if you like.

If you are using MS Sql Server, I can recommend the online help in the
Query Analyzer function. Just look at "Transact SQL" help, which is also
accessible from your Windows START menu
"ST" <ST@discussions .microsoft.com> wrote in message
news:84******** *************** ***********@mic rosoft.com...

Hi,
I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to
write the syntax to check a sql table to see if the record already exists
based on firstname and lastname text fields (will match to firstname and
lastname in SQL table). I can't figure out the syntax!!! I would like
the
Error msg to just display and exit the sub if the row exists. help!!!!
thanks!

[ST]

that sounds great! but what I need help with is developing the vb.net code
to run the check...ie, i'm a little confused when to use data adaptors (da)
dataset( ds) and datarow (dr)

i was trying something like this

'Check if subject already exists
Dim comm As String = "Select * from Subject s WHERE
s.subject_lname = '" & txtLN.Text & "' AND s.subject_fname = '" & txtFN.Text
& "'"

Dim da As New SqlDataAdapter( comm, connStr)
Dim ds As New DataSet

dachk.Fill(ds, "SUBJECT")
Dim dr As DataRow
For Each dr In ds.Tables("SUBJ ECT").Rows
If sql.rows.count > 0 Then ****THIS PART DOESN"T WORK, Can't
figure out the code to perform this operation!
lblchk.Visible = True
Exit Sub
Else
End If
Next

"JV" wrote:

try a sql statement something like:

SELECT count(*) from myTable where lastname=@theLa stName and
firstname=@theF irstName

where the parameters with the @ signs are where you plug in the values you
are searching on. Then you will know not only if one exists, but how many.

You can also use the EXISTS clause if you like.

If you are using MS Sql Server, I can recommend the online help in the
Query Analyzer function. Just look at "Transact SQL" help, which is also
accessible from your Windows START menu
"ST" <ST@discussions .microsoft.com> wrote in message
news:84******** *************** ***********@mic rosoft.com...

Hi,
I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to
write the syntax to check a sql table to see if the record already exists
based on firstname and lastname text fields (will match to firstname and
lastname in SQL table). I can't figure out the syntax!!! I would like
the
Error msg to just display and exit the sub if the row exists. help!!!!
thanks!

[Morten Wennevik]

Hi ST,

VB.Net code isn't my strongest side, but what is 'sql'?
Also, the if statement in your 'for each' won't be reached if there are no rows in the DataTable

Furthermore, DataAdapter.Fil l returns the number of rows affected, so

[C#]
if( da.Fill(ds, "SUBJECT") > 0 )
// record found

Now, if you don't need to use the records you might be better off using
SqlCommand.Exec uteNonQuery instead.

using(SqlComman d sc = new SqlCommand(quer yText, conn))
{
if(sc.ExecuteNo nQuery() > 0)
// record exists)
}

You may need to encapsulate the query with sc.Open() and sc.Close().
On Fri, 24 Jun 2005 20:29:01 +0200, ST <ST@discussions .microsoft.com> wrote:

that sounds great! but what I need help with is developing the vb.net code
to run the check...ie, i'm a little confused when to use data adaptors (da)
dataset( ds) and datarow (dr)

i was trying something like this

'Check if subject already exists
Dim comm As String = "Select * from Subject s WHERE
s.subject_lname = '" & txtLN.Text & "' AND s.subject_fname = '" & txtFN.Text
& "'"

Dim da As New SqlDataAdapter( comm, connStr)
Dim ds As New DataSet

dachk.Fill(ds, "SUBJECT")
Dim dr As DataRow
For Each dr In ds.Tables("SUBJ ECT").Rows
If sql.rows.count > 0 Then ****THIS PART DOESN"T WORK, Can't
figure out the code to perform this operation!
lblchk.Visible = True
Exit Sub
Else
End If
Next

"JV" wrote:

try a sql statement something like:

SELECT count(*) from myTable where lastname=@theLa stName and
firstname=@theF irstName

where the parameters with the @ signs are where you plug in the values you
are searching on. Then you will know not only if one exists, but how many.

You can also use the EXISTS clause if you like.

If you are using MS Sql Server, I can recommend the online help in the
Query Analyzer function. Just look at "Transact SQL" help, which is also
accessible from your Windows START menu
"ST" <ST@discussions .microsoft.com> wrote in message
news:84******** *************** ***********@mic rosoft.com...

> Hi,
> I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to
> write the syntax to check a sql table to see if the record already exists
> based on firstname and lastname text fields (will match to firstname and
> lastname in SQL table). I can't figure out the syntax!!! I would like
> the
> Error msg to just display and exit the sub if the row exists. help!!!!
> thanks!
>
>

--
Happy coding!
Morten Wennevik [C# MVP]

[Mike S.]

"ST" wrote:

For Each dr In ds.Tables("SUBJ ECT").Rows
If sql.rows.count > 0 Then ****THIS PART DOESN"T WORK, Can't
figure out the code to perform this operation!
lblchk.Visible = True
Exit Sub
Else
End If
Next

Where is the "sql.rows.count " object coming from? "dr" is a row object so if
you get past the "For Each dr In ds.Tables("SUBJ ECT").Rows" line then you
have a populated row object (dr), meaning you have some data. Not the ideal
way of doing it but it would work.

Since you're in a hurry you can try this:
For Each dr In ds.Tables("SUBJ ECT").Rows
lblchk.Visible = True
Exit Sub
Next

When you get some time, check out the ExecuteScalar function of the
System.Data.Sql Client.SqlComma nd.Command object. That will bring back a
single value for you rapidly.

Good luck!

[Mike S.]

Here's a function for you when you get some time to review.

Public Function GetSingleValue( ByVal SQL As String) As String
Dim conConnection As System.Data.Sql Client.SqlConne ction =
GetConnection() 'See function below
Dim cmdCommand As New SqlCommand(SQL, conConnection)
Dim objResults As Object
Dim sReturn As String = String.Empty

Try
cmdCommand.Conn ection.Open()
objResults = cmdCommand.Exec uteScalar 'NOTE: Do not use
CommandBehavior .CloseConnectio n here.
If TypeOf objResults Is System.DBNull Then
sReturn = String.Empty
Else
sReturn = CType(objResult s, String)
End If

Catch sqlex As SqlException
Throw New Exception(sqlex .Message)
Catch ex As Exception
If ex.InnerExcepti on Is Nothing Then
Throw New Exception(ex.Me ssage)
Else
Throw New Exception(ex.In nerException.Me ssage)
End If
Finally
cmdCommand.Disp ose()
conConnection.C lose()
conConnection.D ispose()
End Try

Return sReturn
End Function

Public Function GetConnection() As System.Data.Sql Client.SqlConne ction
Return New System.Data.Sql Client.SqlConne ction(YouConnec tionStringHere)
End Function

"ST" wrote:

that sounds great! but what I need help with is developing the vb.net code
to run the check...ie, i'm a little confused when to use data adaptors (da)
dataset( ds) and datarow (dr)

i was trying something like this

'Check if subject already exists
Dim comm As String = "Select * from Subject s WHERE
s.subject_lname = '" & txtLN.Text & "' AND s.subject_fname = '" & txtFN.Text
& "'"

Dim da As New SqlDataAdapter( comm, connStr)
Dim ds As New DataSet

dachk.Fill(ds, "SUBJECT")
Dim dr As DataRow
For Each dr In ds.Tables("SUBJ ECT").Rows
If sql.rows.count > 0 Then ****THIS PART DOESN"T WORK, Can't
figure out the code to perform this operation!
lblchk.Visible = True
Exit Sub
Else
End If
Next

"JV" wrote:

try a sql statement something like:

SELECT count(*) from myTable where lastname=@theLa stName and
firstname=@theF irstName

where the parameters with the @ signs are where you plug in the values you
are searching on. Then you will know not only if one exists, but how many.

You can also use the EXISTS clause if you like.

If you are using MS Sql Server, I can recommend the online help in the
Query Analyzer function. Just look at "Transact SQL" help, which is also
accessible from your Windows START menu
"ST" <ST@discussions .microsoft.com> wrote in message
news:84******** *************** ***********@mic rosoft.com...

Hi,
I'm sort of in a rush here...I'm sort of new to vb.net and I'm trying to
write the syntax to check a sql table to see if the record already exists
based on firstname and lastname text fields (will match to firstname and
lastname in SQL table). I can't figure out the syntax!!! I would like
the
Error msg to just display and exit the sub if the row exists. help!!!!
thanks!