EOF Issue

I am trying to write data to a recordset and it doesn't write to the last record. Here is my code:

Set rs = New ADODB.Recordset
rs.Open sql, conn, 1, 2
With rs
j = 0
Do While Not rs.EOF
rs(0).Value = j
j = j + 1
.Update
.MoveNext
Loop
End With
Set rs = Nothing

I think the problem is my eof setting but I don't know how to adjust that in code since I am not using an adodc. If that is my problem then can I get the code snippet, please?

Otherwise, what is my problem?

Thanks!Have you referred to any VB forums, as I can see most of the SQL database related questions. Try it and see.sql

Environment Variables

Hi everyone...

I'm trying to create a database with the parent directory being a environment variable,

something like this:

Code Snippet

CREATE DATABASE mydatabase
ON
PRIMARY(NAME = myDataBase,
FILENAME = '%PARENTDIRECTORY%\mydatabase.mdf',

the problem is I don't know to get an environment variable in transact SQL...

I know that in C# we can get it with %PARENTDIRECTORY%...

Thanx in advance

The following batch may help you...

Code Snippet

Create Table #Result

(

Data varchar(8000)

);

Insert Into #Result

Exec master..xp_cmdshell 'echo %TEMP%';

Declare @.ParentDirectory as nvarchar(256);

Select Top 1 @.ParentDirectory = Data From #result;

Select @.ParentDirectory = @.ParentDirectory + '\mydatabase.mdf'

Exec ('CREATE DATABASE mydatabase

ON

PRIMARY(NAME = myDataBase,

FILENAME = ''' + @.ParentDirectory + ''')')

Drop table #result

|||Thanx Manivannan the code above does the job, but doesn't the xp_cmdshell work only for XP users?

|||

sqlclr's udf can imprement below,

This assembly's permission need to 'EXTERNAL_ACCESS'.

Usage:

Code Snippet

select dbo.GetEnvironmentVariable('temp');

C# Source file:

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString GetEnvironmentVariable(string variable)
{
string value = Environment.GetEnvironmentVariable(variable);
return new SqlString(value);
}
};

Enumerating Stored Procedure dependencies using SQL-DMO

I have the following (VB.NET) code:
For some reason, I can't get this code to return anything but a ResultSet
with 0 rows. Any ideas?
Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
Dim objSP As SQLDMO.StoredProcedure2 = db.StoredProcedures.Item(Me.Text)
Dim objQueryResults As SQLDMO.QueryResults = _
objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
Dim sb As New System.Text.StringBuilder(4096)
Dim writer As New System.IO.StringWriter(sb)
For i As Integer = 1 To objQueryResults.ResultSets
objQueryResults.CurrentResultSet = i
For j As Integer = 1 To objQueryResults.Rows
For k As Integer = 1 To objQueryResults.Columns
writer.Write(objQueryResults.ColumnName(k) & ": ")
writer.WriteLine(objQueryResults.GetColumnString(j, k))
Next
Next
Next
writer.Flush()
writer.Close()
Return sb.ToString()
End Function
Developer ExtraordinaireHi
Don't forget, in SQL 7.0 and 2000, dependency information is not guaranteed
to be correct due the Deferred Name resolution.
Have you looked in sysdepends if there is information there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<developerExtraordinaire@.spamMeAndDie.com> wrote in message
news:ewd2zkeGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I have the following (VB.NET) code:
> For some reason, I can't get this code to return anything but a ResultSet
> with 0 rows. Any ideas?
> Public Function GetDependencies(ByVal db As SQLDMO.Database2) As String
> Dim objSP As SQLDMO.StoredProcedure2 =
db.StoredProcedures.Item(Me.Text)
> Dim objQueryResults As SQLDMO.QueryResults = _
> objSP.EnumDependencies(SQLDMO.SQLDMO_DEPENDENCY_TYPE.SQLDMODep_Valid)
> Dim sb As New System.Text.StringBuilder(4096)
> Dim writer As New System.IO.StringWriter(sb)
> For i As Integer = 1 To objQueryResults.ResultSets
> objQueryResults.CurrentResultSet = i
> For j As Integer = 1 To objQueryResults.Rows
> For k As Integer = 1 To objQueryResults.Columns
> writer.Write(objQueryResults.ColumnName(k) & ": ")
> writer.WriteLine(objQueryResults.GetColumnString(j, k))
> Next
> Next
> Next
> writer.Flush()
> writer.Close()
> Return sb.ToString()
> End Function
>
> Developer Extraordinaire
>

EnterpriseLibrary 2006 DATA ACCESS LAYER

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}

Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST

PLEASE CHECK THE CODE AND REDEFINE THE CODE

using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@.CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;--ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}

Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:\KOTI_PRJS\Enterprise\Customer\Class1.cs 34 13 Customer

Is this related to SSIS? If not, let me know and I'll be happy to move it to a more appropriate forum. If it is, please describe where the problem is occurring in SSIS.