getting the database collection doesnt always wrok

why doesn't this code always work?

<code>

Dim conn as serverconnection=New serverconnection(".\SQLEXPRESS")

Dim srv as new server(conn)

Dim db as database

for each db in srv.databases

cmbDBfilenames.items.add(db.Name.tostring)

Next

</code)

If the databases are located in the applicatio path , it doesn't pick them up

Hi,

The method you are using only checks for the server attached databases not the user instances. So if the database is in the application path, but SQL Server does not hold an actual reference to them (being attached to the databases) you won′t find them (SMO makes a call to the server to evaluate the available databases)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Getting SqlDbType not declared

How do I dim SqlDbType in my code?

Dim connAsNew Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)

Dim cmdAsNew Data.SqlClient.SqlCommand

With cmd

.Connection = conn

.CommandType = Data.CommandType.StoredProcedure

.CommandText ="UpdateTopics

cmd.Parameters.Add("@.classificationID", SqlDBType.Int)cmd.Parameters.Add("@.TitleID", SqlDBType.Int)

conn.Open()

ForEach itemAs ListItemIn CheckBoxList1.Items

If item.SelectedThen

cmd.Parameters("@.classificationID").Value = item.Valuecmd.Parameters("@.TitleID").Value = DropDownList1.SelectedValue

cmd.ExecuteNonQuery()

EndIf

Next

conn.Close()

EndWith

EndSub

EndClass

JJ297:

How do I dim SqlDbType in my code?

SqlDBType is not an instance class so you cannot instantiate it. It is a enumeration so it just contains a list of values.

To access the SqlDbType enumeration just add the namespace System.Data to your code behind class file

Getting rid of 'NaN' values in the Graph

Hi folks,

I run into problems where some metrics have value associated with dim attributes Markets where some don't have... The graph shows NaN, in the reporting services. Is there a way to get of NaN. something like filter out.

--Imran

Use the dataset filters and the following filter expressions:

CStr(Fields!YourField.Value) <> "NaN" AND

Fields!YourField.Value <> Nothing

Shyam

Getting RecordCount with TableDirect

Hi, I would like to know if there's any method to get the total number of records in a table without using query. I have the following codes.

Dim conn As SqlServerCe.SqlCeConnection
Dim cmd As SqlServerCe.SqlCeCommand
Dim rdr As SqlServerCe.SqlCeDataReader

conn = New SqlServerCe.SqlCeConnection("Data source=\My Documents\test.sdf")
conn.Open()

cmd = New SqlServerCe.SqlCeCommand("Staff_Table", conn)
cmd.CommandType = CommandType.TableDirect
cmd.IndexName = "Id_Ind"

rdr = cmd.ExecuteReader
rdr.Seek(SqlServerCe.DbSeekOptions.FirstEqual, int_seek_n)

If rdr.Read() Then
strResult = rdr.GetString(1)
End If

rdr.Close()
conn.Close()

I did quite a bit of searching and got no results. Thanks.No, there's no shortcut to getting the count of rows in a table. I recommend doing a SELECT COUNT(*) and use ExecuteScalar to get the best performance on this query.

-Darren Shaffer|||Thanks Darren. Looks like I have no other choice. :)

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try
(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>