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

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!

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

try a sql statement something like:

SELECT count(*) from myTable where lastname=@theLastName and
firstname=@theFirstName

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:84A12BD4-B41F-49ED-BF39-F49C1676CBE0@microsoft.com...[color=blue]
> 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!
>
>[/color]

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

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("SUBJECT").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:
[color=blue]
> try a sql statement something like:
>
> SELECT count(*) from myTable where lastname=@theLastName and
> firstname=@theFirstName
>
> 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:84A12BD4-B41F-49ED-BF39-F49C1676CBE0@microsoft.com...[color=green]
> > 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!
> >
> >[/color]
>
>
>[/color]

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

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.Fill 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.ExecuteNonQuery instead.

using(SqlCommand sc = new SqlCommand(queryText, conn))
{
if(sc.ExecuteNonQuery() > 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:
[color=blue]
> 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("SUBJECT").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:
>[color=green]
>> try a sql statement something like:
>>
>> SELECT count(*) from myTable where lastname=@theLastName and
>> firstname=@theFirstName
>>
>> 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:84A12BD4-B41F-49ED-BF39-F49C1676CBE0@microsoft.com...[color=darkred]
>> > 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!
>> >
>> >[/color]
>>
>>
>>[/color]
>[/color]



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

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

"ST" wrote:
[color=blue]
> For Each dr In ds.Tables("SUBJECT").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[/color]

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("SUBJECT").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("SUBJECT").Rows
lblchk.Visible = True
Exit Sub
Next

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

Good luck!

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

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.SqlClient.SqlConnection =
GetConnection() 'See function below
Dim cmdCommand As New SqlCommand(SQL, conConnection)
Dim objResults As Object
Dim sReturn As String = String.Empty

Try
cmdCommand.Connection.Open()
objResults = cmdCommand.ExecuteScalar 'NOTE: Do not use
CommandBehavior.CloseConnection here.
If TypeOf objResults Is System.DBNull Then
sReturn = String.Empty
Else
sReturn = CType(objResults, String)
End If

Catch sqlex As SqlException
Throw New Exception(sqlex.Message)
Catch ex As Exception
If ex.InnerException Is Nothing Then
Throw New Exception(ex.Message)
Else
Throw New Exception(ex.InnerException.Message)
End If
Finally
cmdCommand.Dispose()
conConnection.Close()
conConnection.Dispose()
End Try

Return sReturn
End Function

Public Function GetConnection() As System.Data.SqlClient.SqlConnection
Return New System.Data.SqlClient.SqlConnection(YouConnectionS tringHere)
End Function

"ST" wrote:
[color=blue]
> 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("SUBJECT").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:
>[color=green]
> > try a sql statement something like:
> >
> > SELECT count(*) from myTable where lastname=@theLastName and
> > firstname=@theFirstName
> >
> > 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:84A12BD4-B41F-49ED-BF39-F49C1676CBE0@microsoft.com...[color=darkred]
> > > 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!
> > >
> > >[/color]
> >
> >
> >[/color][/color]