how to control data types

i have sp that insert fileName and fileType

how can i forbidden in the sp other types then word doc and images?

Code Snippet

if not exists(select Number

from dbo.Freezrelease

where FileName1 like @.FileName1 and

FileType like @.FileType and

FileSize = @.FileSize)

/*{*/ begin

if @.FileSize<500

/*{*/ begin

insert into dbo.Freezrelease(FileName1,FileType,FileSize)

values(@.FileName1,@.FileType,@.FileSize)

select @.@.identity

/*}*/ end

else

select -1

/*}*/end

else

select 0

So you are just inserting the file names, not the bits? If so, just add a line:

if @.fileType not in ('doc','jpg','...etc')

begin

raiserror ('Filetype must be doc, jpg, or etc')

return -100

end

And the procedure will stop there after raising an error

|||

You could add a CHECK constraint to the table that limits the values in the FileName field to only values ending with '.doc', '.docx'*, or '.jpg'. (And of course, any other image file extensions required...)

Using this method, it would not be necessary to 'control' every stored procedure or application query. Programmers and users will not be able to thwart your determination that a 'proper' FileName be supplied. The CHECK constraint would 'police' the inserts/updates for you.

* .docx for Word 2007

Code Snippet

CREATE TABLE #MyTable
( RowID int IDENTITY,
FileName varchar(50)
CHECK ( FileName LIKE '%.doc%' OR FileName LIKE '%.jpg' )
)
GO

-- These succeed
INSERT INTO #MyTable VALUES ( 'MyFile.doc' )
INSERT INTO #MyTable VALUES ( 'MyPic.jpg' )
INSERT INTO #MyTable VALUES ( 'MyFile.docx' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile.txt' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile' )

SELECT *
FROM #MyTable

RowID FileName
-- --
1 MyFile.doc
2 MyPic.jpg
3 MyFile.docx

DROP TABLE #MyTable

|||

What Arnie says is true too, though I would prefer having a fileType column to hold the extension also, for all of the basic normalization reasons:

1. You can add information about types of files easily and join to it without the substring

2. Listing files by type will not require a substring

3. Searching for files by type can be indexed

Really it is all about the need to avoid dealing with parts of a column value.

How to control CLR memory threshhold?

Please help!

I'm running a lengthy (about 5 minutes) transformation process on SQL Server 2005, which consumes about 300-400 MB. The code is a C# SP. The same code can be run as a part of WinForm application and works fine. If executed as a stored procedure e.g. from Management Studio it breaks after consuming certain amount of memory above roughly 200MB with

.NET Framework execution was aborted by escalation policy because of out of memory.

The SP works fine on smaller amount of data. The assembly has UNSAFE permission and DB's TRUSTWORTHY attribute is OFF/false. Timeout is set to 10 minutes.

If it's memory limit, how can it be increased? Consumption is well below server limitations. The server is Windows 2003 with 3.5 GB of RAM on Intel Xeon.

Thanks, Andrei Kuzmenkov.

Hi Andrei,

I wrote a blog entry touching on this topic here: http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

You can increase the amount of memory available to SQLCLR via the -g flag on Sql Server start-up, as described in BOL: http://msdn2.microsoft.com/en-us/library/ms190737.aspx. Be aware of the trade-offs however, as increasing memory_to_reserver decreases the amount of memory available to the main SQL Buffer pool.

Steven

|||

Thank you Steven for the prompt response.

That'd helped immediately.

Andrei Kouzmenkov.

How to consolidate the count?

Reg code Cntry code Area code Count
--- ---- --- --
AF AO CAB 15
AF AO LAD 20
AF BF OUA 23
AF BI BJM 11
AF BW GBE 72
AF CD FIH 30
AF CD MNB 8

I need the result like this

Reg code cntry code Total

AF AO 35
AF BF 23
AF BI 11
AF Bw 72
AF CD 38

consolidated total of area code 15+20 = 35, 30 + 8 = 38

table structures(two tables I am using)

create temp table country (ccode char(5),
acode char(4), rcode char(4));

create temp table report(count char(5),acode char(5));

how to write the sql?This is a simple sum and group by:

select c.rcode, c.ccode, sum(t.count)
from country c, temp t
where c.acode = t.acode
group by c.rcode, c.ccode;

BTW, why is column count declared as char(5)?

How to connect with SQL database?

I am new learner of ASP.NET and coding in C#.I am not able to connect the SQL database.I have written the code for connection of database but to write the connection string web.config file is neccessary which is not apearing in my application. Please help me .Here is code I have written.

string sCon =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

SqlConnection oCon =new SqlConnection(sCon);

try

{

oCon.Open();

string sQuery ="Select Offering1,Offering2,Offering3 from Offering";SqlCommand oCmd =new SqlCommand(sQuery, oCon);

oCmd.ExecuteNonQuery();

DataSet oDs =newDataSet();

SqlDataAdapter oDa =new SqlDataAdapter(oCmd);

oDa.Fill(oDs);

DataList1.DataSource = oDs;

DataList1.DataBind();

}

catch (Exception ex)

{

ex.ToString();

}

What kind of error are you getting?

Can you post a copy of the connectionString settings from your web.config?

|||

Hii Jimmy,

Actually I do not see the web.config file in my solution explorer. So that I am unable to write the connection string. Can you please tell me why it is so ?

|||

Looks like you may have started with the empty web site template. When you try to start/debug the application, does it not automatically add a web.config for you?

Well, you can manually add one. Right click on your web site and select ADD NEW ITEM then select the WEB CONFIGURATION FILE

|||

Hey thanks,

As there are errors because I have not added the connection string into the web.cofig file it is not able to debug the file.Right now I have added web.config manually.

What the connection string format to add to web.config?

<addname="ConnectionString"connectionString="Server=(local)\SQLEXPRESS;Integrated Security=True;Database=Knowledgebase;Persist Security Info=True"/>

is it right?

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

|||

Bluestar123:

Now the error coming is "The type or namespace name 'SqlConnection' could not be found" when I try to debug my default.aspx.

That would because you did not import the namespace

Add this to the top of your code behind for your default.aspx

using System.Data.SqlClient;

|||

The connection string can look something like this

"Data Source= xxx ;Initial Catalog=xxx; uid=xxx ;pwd=xxx"

where datasource is your database server; could be your machine name or I.P. address, Initial Catalog is the database name, UID is the login name, PWD is the login name's password

If your SQL Server is set up for windows authentication then you can do replace the UID and PWS with "Integrated Security=True"

Have a look at this link for connection string examples

http://connectionstrings.com/?carrier=sqlserver2005

|||

Hi Jimmy,

Now I have added using System.Data.SqlClient to my default.aspx.cs and that error has gone thanks for the same.

I am still not able to fetch the data to my datalist from database, is still there any problem with my connection string? in my web.cofig I have used windows authentication.

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

Please help ..........

|||

Bluestar123:

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Knowledgebase;Integrated Security=True"/>

for the connection string, the data source should be [your machine name]\SQLEXPRESS assuming SQLEXPRESS is the server instance name

update your code to this and give it a go

string sCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection oCon =new SqlConnection(sCon);
try
{
string sQuery ="Select Offering1,Offering2,Offering3 from Offering";
 SqlCommand oCmd =new SqlCommand(sQuery, oCon);
 DataSet oDs =new DataSet();
 SqlDataAdapter oDa =new SqlDataAdapter(oCmd);
 oDa.Fill(oDs);
 DataList1.DataSource = oDs;
 DataList1.DataBind();
}
catch (Exception ex)
{
 ex.ToString();
}

Add some break points and step through the code, if there any exceptions, take not of the exception message.

|||

I have added break points at Select command and line below that ,control goes there when I debug the default.aspx and then it display the page without fetching thedata from database.So i have added the break point to the line

DataSet oDs=new DataSet();

at this line control donot go and directly display the page without showing the data from the database.

|||

So when you put a break point at the DataSet oDs=new DataSet(); and when you step over it F10, it does not proceed to the next line? Then that means an exception was raised and it should be caught in your catch. So put a break point in your catch statement, so you can check the exception being thrown if there is one at all and give us the error message

|||oops! Column name I had given was wrong .......Its OfferingName. I made that change but now when I put brk point at

DataList1.DataSource = oDs;

after pressing F10 control goes to next line i. e.

DataList1.DataBind();

but data from database is not getting displayed on the page though the page has been displayed......

oh no whats the problem ?am I doing any mistake again?

Follwing output I got in Output window

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_d4ehzmko.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\knowledgebase\23a636d8\418c589d\App_Web_oq4mi2vf.dll', Symbols loaded.

The program '[3368] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Well the obviously question is have you tried executing the SQL statement against your database to make sure it does return data?

after you have stepped over this line of code oDa.Fill(oDs); check it the dataset has returned a datatable with datarows.

|||

No I didnt,how can I check it ?I am using Microsoft SQL server management Studio. When go to line

oDa.Fill(oDs);

it did not return the table contents......

I tried to execute the query by using Execute SQL but not able to execute ,How to do?

Did you get fed up of my questions Jimmy??

|||

Bluestar123:

Did you get fed up of my questions Jimmy??

Not at all, just trying my best to understand your problem which is at times a little hard.

Lets try execute you select statement first in SQL Server Management Studio.

Log onto your database, and select the NEW QUERY option.

Now paste your SQL statement into this window , "Select Offering1,Offering2,Offering3 from Offering" and click execute

Does it return any results?

sql

How to connect using C#

Hi

I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
already got some VB code that works but I'm unable to translate it to
working C# code. Can anyone help me out?

Working VB code:

Dim Conn
Conn = CreateObject("ADODB.Connection")
Const ConnectionString = "DSN=dsn_name;database=database_name"
Conn.Open(ConnectionString, "user_name", "password")

Not working C# code:

string ConnectionString = "Initial Catalog=database_name;" +
"Data Source=dsn_name;" +
"User ID=user_name;" +
"Password=password;" +
"Integrated Security=SSPI;";

SqlConnection Connection = new SqlConnection(ConnectionString);
Connection.Open();
Connection.Close();Use exactly the same connection string form C# as for VB. If it works
in one, it'll work in the other.

Specifically, if you are using SSPI, you don't need to specify uid and
pw, and vice-versa.|||AHM (ingen@.spam.tak) writes:
> I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
> already got some VB code that works but I'm unable to translate it to
> working C# code. Can anyone help me out?

If you help us to help you. Don't just post:

> Not working C# code:
> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";
> SqlConnection Connection = new SqlConnection(ConnectionString);
> Connection.Open();
> Connection.Close();

Tell us in what way it is not working. Do you get an error message?
Do you get unexpected results? Does the light go out?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";

Note the change to 'Data Source' in the example below as well as the removal
of the unnecessary User ID and Password parameters:

string ConnectionString = "Initial Catalog=database_name;" +
"Data Source=database_server_name;" +
"Integrated Security=SSPI;";

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AHM" <ingen@.spam.tak> wrote in message
news:MPG.1cb65eb0b33ffb61989705@.news.inet.tele.dk. ..
> Hi
> I'm trying to connect to a MS SQL Server using ASP.NET / C#. I've
> already got some VB code that works but I'm unable to translate it to
> working C# code. Can anyone help me out?
> Working VB code:
> Dim Conn
> Conn = CreateObject("ADODB.Connection")
> Const ConnectionString = "DSN=dsn_name;database=database_name"
> Conn.Open(ConnectionString, "user_name", "password")
> Not working C# code:
> string ConnectionString = "Initial Catalog=database_name;" +
> "Data Source=dsn_name;" +
> "User ID=user_name;" +
> "Password=password;" +
> "Integrated Security=SSPI;";
> SqlConnection Connection = new SqlConnection(ConnectionString);
> Connection.Open();
> Connection.Close();

How to connect to SQL server 2005 from C# after default installation?

Hi everyone,

I just installed Visual Studio 2005 and SQL Server 2005. However, I`m not able to connect to the database.

When running this C# code, I get a SqlException.

using System; using System.Data; using System.Data.SqlClient; class Program {

static void Main(string[] args) {

SqlConnection connection = new SqlConnection(); connection.ConnectionString = "Data Source=(local)\\sqlexpress;Initial Catalog=X;Integrated Security=True;"; connection.Open(); connection.Close();

}

}

I get the following error message:

Cannot open database X requested by the login. The login failed.
Login failed for user Y.

Can anyone tell me how to connect to sql server 2005 after default installation? Any help would be appreciated! :-)

Jan

Most likely your user ID does not exist in a list of the logins for the database. You need to grant an access for your Windows account to the database. Check next links with some info

http://support.microsoft.com/kb/325003/en-us#XSLTH4213121122120121120120

http://support.microsoft.com/kb/240872/en-us

|||Thx, my program runs!

I executed the following command (in VS Command Prompt) to add my windows account to the list of database accounts.

C:\>osql -E -S (local)\sqlexpress
1> EXEC sp_grantlogin 'machine_name\user_name'
2> go
1> exit

How to connect to sql server 2000 (asp.net)

hi everybody, i got this error in my code.

Server Error in '/' Application.

---

SQL Server does not exist or access denied.

Source Error:

Line 12:       Dim SQLConn As SqlConnection = New SqlConnection()

Line 13:                SQLConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")

Line 14:                SQLConn.Open()

Line 15:       

Line 16:       cmdLath = New SqlCommand ( _

my web.config

<configuration>

<appSettings>

<addkey="ConnectionString" value="DataSource=xxx;UID=xxx;pwd=xxx" />

<addkey="ConnectionString" value="Server=xxx.xxx.x.x;database=xxx;UID=xxx;pwd=xxx;Trusted_Connection=True"/>

</appSettings>

<system.web>

<sessionState cookieless="false" timeout="20"/>

<customErrorsmode="Off"/>

</system.web>

</configuration>

what seems to be a problem, please help me??Is your connection string correct (server address, username, password, etc.)? Does the user your authenticating as have the correct permissions on the database?

Ryan|||i ran into this problem a few weeks ago as well, like the previous poster stated, you have to make sure they have an account in the sql server itself, not just a windows account. So the problem isn't with your code (assuming no spelling errors or the such) but with the sql server authenticating the user name and password. Hope that helps.|||Besides the suggestions given above, make sure you have SQL Server Authentication configured to be Mixed mode. Checkthis for the configuration.|||To Strongtypes,

Yes, I write the correct server, database, username and password. basically i use sa account with password but it didn't work. I used sql server authentication mixed mode.|||To Darkempire and Alvin,

Thanks for the reply, You know I've done a lot of codes in my asp.net but unfortunately it appears errors in my webpage. This is my setup, my sql server 2000 is installed on other machine with the domain controller running windows server 2003. I have this local pc with my asp.net with web server(iis). I think that my web server does not locate my sql server database or my connection string is wrong but if I'm going to used my dreamweavermx to fetch data to my sql server database it will show up perfectly but when i code an asp.net and run in my localhost, bang errorrs.. please help me|||

Let's do a connection testing on teh .NET client machine: new a '.udl' file e.g. test.udl, then double click to edit it->switch to Provider tab and choose 'SQL Native Client' (which is the default provider when you add connectionstring element in your web.config)->click next to switch to Connection tab->entering connection information including login and pasword (the same as you connect to the SQL Server in DreamWeaver)->click Test Connection. If this succeeds, then the SQL Native Client provider is OK; otherwise there's something wrong with the SQL Native Client provider, so you need to change another provider.

To learn more about connection strings using different providers, you can visit:

www.connectionstrings.com

sql

how to connect to a remote sql DB

Hi all,

myDB is located atX:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB.mdf
and my code is located at : C:\Inetpub\wwwroot\applicationfolder
how do I code my server.mapPath?

Btw, can I get VS 2005 to detect the server path when using server solution.Thanks

sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("X:\Program Files\Microsoft SQL Server\MSSQL\Data\myDB.mdf"))
dbconn.Open()
sql = "SELECT * FROM myTable"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
myDB.DataSource = dbread
myDB.DataBind()
dbread.Close()
dbconn.Close()
end sub

One big problem you are pointing to Access database which lets you map the path and SQL Server which controls the path so if your database is Access you can map the path, if it is SQL Server you cannot map the path because the database is not the runtime the relational engine is the runtime. Hope this helps.

how to connect to a local sql server on your laptop?

i have problems connecting to the local server on my laptop. i ahve used the following code but it gives me an error saying login flaied for user "sa" or "sekhar".

i tried using the username and password i use to log onto my computer. but it doesnt work.
can anyone tell me how to do it?

the code is typed is

connection = new sqlconnection("server=localhost;uid=sekhar;pwd=sekhar;database=chandu")
..................
.............................
........................
connection.open()..............i have all the next statements correct.
my username is sekhar and the password is sekhar too but it gives me an error.
somebody help me

Thank you

sekhartry using server=(local)

and whether you need the password or not depends on how the SQL Server is set up. does the users view show a user called 'sekhar'? no? when you right click the server node and go to properties->security, does it show windows auth only, or Windows and SQL Server auth?

how to connect sql server 2005 cube using visual studio 2005

May i know how to connect sql server 2005 cube using visual studio 2005 ?
May i have some examples and source code to solve this problem ?

Thanks a lot.Please direct these types of questions to the SQL Server Analysis Server forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=83

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

Hi

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

Hi

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

Hi

please advice us regarding the code. is it the usual option available.

-

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

--

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

Hi

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

Hi

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

Hi

please advice us regarding the code. is it the usual option available.

-

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

--

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)

How to Compare 2007-9-11 and Month(GetDate()) ?

I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)

I write code

select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)

it will like

select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)

→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30

but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code

pleae help... thank you very much

Please do not open multiple threads for same question. Refer:http://forums.asp.net/t/1176161.aspx.

How to combine 2 records into 1 unique record

Hi all,

We have an app that uses SQL 2000. I am trying to track when a code field
(selcode) is changed on an order which then causes a status field (status)
to change. I tried a trigger but the app may use 2 different update
statements to change these fields depending on what the user does. When the
trigger fires (on update to selcode), the status field has already been
changed. So my trigger to record the changes from inserted and deleted do
not get the true 'before' value of the status field.

The app does use a log table that tracks these changes. The problem I am
having is that 2 records are created, one for the change to selcode and
another for the change to status.

I am looking for help with a script to combine the existence of these 2 log
records into 1 unique record or occurance that I can track.

example:
ordlog: table that logs order changes
ordernr: order number
varname: name of field being changed
old_value: contents of field before change
new_value: contents of field after change
syscreated: date/time of log entry

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'

So I need a way to combine these 2 log entries into a unique occurance. The
ordernr and syscreated could be used to link records. syscreated always
appears to be the same for the 2 log entries down to the second. Selcode
can change from NULL to a number of different values or back to NULL.Status
is either 'A' for approved or 'O' for open. An order can have many log
entries during its life. The selcode may be changed several times for the
same order.

Ideally, I would like a result that links 2 log entries and shows the status
changed from 'A' to 'O' when selcode changed.

Thanks for your time.rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'selcode' and ordernr = '10580'
>
>
SELECT ordernr, varname, old_value, new_value, syscreated
FROM ordlog
where varname = 'status' and ordernr = '10580' and old_value = 'A' and
new_value = 'O'
>
>
So I need a way to combine these 2 log entries into a unique occurance.
The ordernr and syscreated could be used to link records. syscreated
always appears to be the same for the 2 log entries down to the second.
Selcode can change from NULL to a number of different values or back to
NULL.Status is either 'A' for approved or 'O' for open. An order can
have many log entries during its life. The selcode may be changed
several times for the same order.
>
Ideally, I would like a result that links 2 log entries and shows the
status changed from 'A' to 'O' when selcode changed.

Could this do:

SELECT a.ordernr, a.syscreated,
oldselcode = a.old_value, newselcode = a.new_value,
oldstatus = b.old_value, newstatus = b.new_value
FROM ordlog a
JOIN ordlog b ON a.ordernr = b.ordernr
AND datediff(seconds, a.syscreated, b.syscreated) <= 1
WHERE a.varname = 'selcode'
AND b.varname = 'status'
AND coalesce(a.old_value, '') <coalesce(a.new_value, '')
AND a.old_value = 'A'
AND b.new_value = 'B'

Note: this is an untested query.

If the does not return the expected results, I suggest that you post:

o CREATE TABLE statments for the involved table(s).
o INSERT statements with sample data.
o The desired output given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to code sql comments within sql statement

I want to write a query like this:

select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...

The result should looks like this:

--
select count(*) /* - emp_name */
FROM emp_name

query result:

COUNT(*)-EMP_NAME
-
3

-
But, now I can only got

COUNT(*)
-
3

What's wrong with my original code?Actually, I am getting

- as the result, I think I just need to find out how to turn headings on.
3

Thanks|||

Well... you are comment out whatever is written. Comment out means that it will be ignored, it will not show up in the results, etc.

It looks like you want to create column headers for aggregrated columns. Fortunately, you can use an ALIAS for that task.

For example:

Code Snippet

SELECT
count(*) AS 'count(*) --Emp_Name'
FROM MyTable

Of course, whatever is typed as the ALIAS has to be know in advance, it will not dynamically put in the column or table name.

|||
Thanks, Arnie:

I got it now.

How to code DateTime-Literal in SQL Server

Hi,

I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype? For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!

Any help very appreciated!

Thanks in advance and best regards

Reiner

PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).

I am not familiar with the german localized database, but I have some guesses...

You are doing the right thing, dates in SQL are inclosed by single '. Are your clients localized with the same culture as the database? Is so you should be able to

strsql = "INSERT INTO TABLE VALUES('" & datetime.tostring() & "')"
and the tostring method with format it properly.

Also try it with - instead of . in the date.

What I would do is open SQL Query Analyzer and write out the SQL insert query with a date and keep modifying the date format until it accepts it.

If none of this is a help then post the exception message.
|||

Look up date and time formats in books online. There are some ISO date time formates that are the best practice. Generally speaking, the formats are:

YYYYMMDD or YYYY-MM-DD

then time:

HH:MM:SS.SSS

There are variations, but the most important bits have to do with the year month date arrangement that is not ambiguous in any country

|||

There are only two unambiguous formats (one for date and another for datetime). Both are ISO formats.

YYYYMMDD - ISO unseparated date format

YYYY-MM-DDThh:mm:ss.nnn[ Z ] - ISO 8601 timestamp format (note there is no space after and before Z. I have to do it to prevent a stupid icon from showing up and there seems to be no way to prevent formatting.)

The ISO 8601 format is supported only from SQL Server 2000 onwards. Any other format interpretation depends on the language / session settings.

How to Code Conditional "Jump to Report"

I have a system summary report where I want to allow them to drill-thru to
the detail system report only if the system's percent available is less than
100%. I tried to use the Fx on the "jump to report" to enter an IIF
statement something like
=IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
various other things but it didnt work. How can I do this ? I want to
essentially turn OFF the action if the percent is 100 and allow them to
drill-thru to the detail if it is less than 100 percent. The FX is there
next to where you select the report to jump to so I should be able to code an
expression from what I read. Thanks!!I have done something similar with Jump to URL:
= iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP" ,Globals!ReportServerUrl &
"?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
Fields!db_uniqueloadid.Value)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>I have a system summary report where I want to allow them to drill-thru to
> the detail system report only if the system's percent available is less
> than
> 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> statement something like
> =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> various other things but it didnt work. How can I do this ? I want to
> essentially turn OFF the action if the percent is 100 and allow them to
> drill-thru to the detail if it is less than 100 percent. The FX is there
> next to where you select the report to jump to so I should be able to code
> an
> expression from what I read. Thanks!!|||I cant use "jump to url" ... is there a way to do what I am asking? If so ..
I am not sure how to code the empty string for the report. It gives me an
error about the report cant be loaded or something
"Bruce L-C [MVP]" wrote:
> I have done something similar with Jump to URL:
> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP" ,Globals!ReportServerUrl &
> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
> Fields!db_uniqueloadid.Value)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
> >I have a system summary report where I want to allow them to drill-thru to
> > the detail system report only if the system's percent available is less
> > than
> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> > statement something like
> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> > various other things but it didnt work. How can I do this ? I want to
> > essentially turn OFF the action if the percent is 100 and allow them to
> > drill-thru to the detail if it is less than 100 percent. The FX is there
> > next to where you select the report to jump to so I should be able to code
> > an
> > expression from what I read. Thanks!!
>
>|||Why can't you use jump to url? I don't understand, anyplace you can use jump
to report you can use jump to URL. Jump to report is a bit handier but both
will work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
>I cant use "jump to url" ... is there a way to do what I am asking? If so
>..
> I am not sure how to code the empty string for the report. It gives me an
> error about the report cant be loaded or something
> "Bruce L-C [MVP]" wrote:
>> I have done something similar with Jump to URL:
>> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
>> ,Globals!ReportServerUrl &
>> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
>> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
>> Fields!db_uniqueloadid.Value)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>> >I have a system summary report where I want to allow them to drill-thru
>> >to
>> > the detail system report only if the system's percent available is less
>> > than
>> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
>> > statement something like
>> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "")
>> > and
>> > various other things but it didnt work. How can I do this ? I want
>> > to
>> > essentially turn OFF the action if the percent is 100 and allow them to
>> > drill-thru to the detail if it is less than 100 percent. The FX is
>> > there
>> > next to where you select the report to jump to so I should be able to
>> > code
>> > an
>> > expression from what I read. Thanks!!
>>|||how would I code that passing parameters? not sure of the syntax. In
addition ... where would the server name come from? We are having issues
with exposing the url to the end users since it contains a parameter that
would allow them to get somewhere they shouldnt (if they knew how ). Someone
from MS is working on that for my company right now. I saw on the portal
that "jump to report" exposes the parameters the same as "jump to url"
probably. So .. that brings me back to syntax ... how do I code the
parameter "values" ... I know the parameter you code in the url as
¶meter_name= <= but how to code the field that goes in there from
the report you are drilling from?
"Bruce L-C [MVP]" wrote:
> Why can't you use jump to url? I don't understand, anyplace you can use jump
> to report you can use jump to URL. Jump to report is a bit handier but both
> will work.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
> >I cant use "jump to url" ... is there a way to do what I am asking? If so
> >..
> > I am not sure how to code the empty string for the report. It gives me an
> > error about the report cant be loaded or something
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I have done something similar with Jump to URL:
> >>
> >> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
> >> ,Globals!ReportServerUrl &
> >> "?/Inventory/Composite+Info&CompositeID=" & Fields!db_uniqueloadid.Value
> >> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
> >> Fields!db_uniqueloadid.Value)
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> >> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
> >> >I have a system summary report where I want to allow them to drill-thru
> >> >to
> >> > the detail system report only if the system's percent available is less
> >> > than
> >> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> >> > statement something like
> >> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "")
> >> > and
> >> > various other things but it didnt work. How can I do this ? I want
> >> > to
> >> > essentially turn OFF the action if the percent is 100 and allow them to
> >> > drill-thru to the detail if it is less than 100 percent. The FX is
> >> > there
> >> > next to where you select the report to jump to so I should be able to
> >> > code
> >> > an
> >> > expression from what I read. Thanks!!
> >>
> >>
> >>
>
>|||True, from a security viewpoint there is no difference. You can turn off the
parameter area so they can't change it from Report Manager but if someone
wants to figure out and change the URL that can be done. You have to use web
services if you want to totally hide everything from the user.
As far as how to create this. Look at my example, it covers everything.
Including the use of Globals!ReportServerUrl so no server name is
hardcoded. I suggest trying jump to url without being fancy and then add in
your iif statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:51B133E4-55DA-4F15-9ABF-BF688E5CBA0F@.microsoft.com...
> how would I code that passing parameters? not sure of the syntax. In
> addition ... where would the server name come from? We are having issues
> with exposing the url to the end users since it contains a parameter that
> would allow them to get somewhere they shouldnt (if they knew how ).
> Someone
> from MS is working on that for my company right now. I saw on the portal
> that "jump to report" exposes the parameters the same as "jump to url"
> probably. So .. that brings me back to syntax ... how do I code the
> parameter "values" ... I know the parameter you code in the url as
> ¶meter_name= <= but how to code the field that goes in there
> from
> the report you are drilling from?
> "Bruce L-C [MVP]" wrote:
>> Why can't you use jump to url? I don't understand, anyplace you can use
>> jump
>> to report you can use jump to URL. Jump to report is a bit handier but
>> both
>> will work.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> news:BA135695-39B6-48C6-89FF-0F9965E69006@.microsoft.com...
>> >I cant use "jump to url" ... is there a way to do what I am asking? If
>> >so
>> >..
>> > I am not sure how to code the empty string for the report. It gives me
>> > an
>> > error about the report cant be loaded or something
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I have done something similar with Jump to URL:
>> >>
>> >> = iif( mid(Fields!db_uniqueloadid.Value,1,2)="CP"
>> >> ,Globals!ReportServerUrl &
>> >> "?/Inventory/Composite+Info&CompositeID=" &
>> >> Fields!db_uniqueloadid.Value
>> >> ,Globals!ReportServerUrl & "?/Inventory/Load+ID&UniqueLoadID=" &
>> >> Fields!db_uniqueloadid.Value)
>> >>
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
>> >> news:EC316C43-E68F-4523-BC3B-A131BF4F8AED@.microsoft.com...
>> >> >I have a system summary report where I want to allow them to
>> >> >drill-thru
>> >> >to
>> >> > the detail system report only if the system's percent available is
>> >> > less
>> >> > than
>> >> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
>> >> > statement something like
>> >> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name",
>> >> > "")
>> >> > and
>> >> > various other things but it didnt work. How can I do this ? I
>> >> > want
>> >> > to
>> >> > essentially turn OFF the action if the percent is 100 and allow them
>> >> > to
>> >> > drill-thru to the detail if it is less than 100 percent. The FX is
>> >> > there
>> >> > next to where you select the report to jump to so I should be able
>> >> > to
>> >> > code
>> >> > an
>> >> > expression from what I read. Thanks!!
>> >>
>> >>
>> >>
>>|||I have the same problem, Did you ever figure out how to shut off the action?
thanks.
"MJ Taft" wrote:
> I have a system summary report where I want to allow them to drill-thru to
> the detail system report only if the system's percent available is less than
> 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> statement something like
> =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> various other things but it didnt work. How can I do this ? I want to
> essentially turn OFF the action if the percent is 100 and allow them to
> drill-thru to the detail if it is less than 100 percent. The FX is there
> next to where you select the report to jump to so I should be able to code an
> expression from what I read. Thanks!!|||I knew the minute I sent this that I'd figure it out.
I had to use the keywork NOTHING.
= IIF(Fields!Failed_rule_Rule_Cd.Value = "",Nothing,"Page.aspx")
"Paula" wrote:
> I have the same problem, Did you ever figure out how to shut off the action?
> thanks.
> "MJ Taft" wrote:
> > I have a system summary report where I want to allow them to drill-thru to
> > the detail system report only if the system's percent available is less than
> > 100%. I tried to use the Fx on the "jump to report" to enter an IIF
> > statement something like
> > =IIF(ReportItems!Percent_Avail.Value < 100, "Detail_Report_Name", "") and
> > various other things but it didnt work. How can I do this ? I want to
> > essentially turn OFF the action if the percent is 100 and allow them to
> > drill-thru to the detail if it is less than 100 percent. The FX is there
> > next to where you select the report to jump to so I should be able to code an
> > expression from what I read. Thanks!!

How to code ASP.NET page with return value Store Procedure?

Does anyone know how to call a SQL store procedure that return a value to the page?

I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.

Are there a similar article somewhere?

Thank you all!

check out the docs about using ExecuteScalar() Method.|||

Using ExecuteScalar() requires a separate procedure call. I would like to use the same procedure call that to insert a record into a table and also return an ID back. My store procedure look like this:

sqlString = "EXEC spInsertRow 'parm1','parm2','parm3', @.ReturnID OUTPUT"

Thanks

|||

In the end of your sproc:

SELECT @.ReturnID = @.@.IDENTITY

|||

I suggested ExecuteScalar assuming you are doing some insert and want to return the ID.

If that is not the case you can use the OUTPUT parameters..

Dim res as integer

myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.returnId"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.bigint
mycommand.Parameters.Add(myParam)

res = mycommand.Parameters("@.result").Value

|||

Thank you. It works

|||

Can you share your general procedure. I'm having the same issue and have tested many ways. Still not working.

See posthttp://forums.asp.net/1178243/ShowPost.aspx

Thanks

How to code an aspx page to run a stored procedure with a parameter

My stored proceddure "My Programs" includes a parameter @.meid.
How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?
I tried the following codes, but the error message indicated it can not find
the Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter?

<%
meid = EmpId
cmd.CommandText ="MyPrograms meid"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader(meid)
While reader3.Read()
sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")
EndWhile
%>

TIA,
Jeffrey

Check if this helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74220.aspx

|||

Thanks.

My co-worker told me I am supposed not need to know the parameter name, "@.meid".
Then what does "Failed to convert parameter value from a String to a Int32" mean?
Why the ExecuteReader() needs to convert EmpID to Int32? It's laready an integer.

TIA,
Jeffrey

<%
cmd.CommandText ="MyPrograms"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@.meid", SqlDbType.Int))
cmd.Parameters("@.meid").Value =EmpID
cmd.Connection = sqlConnection2
sqlConnection2.Open()
reader3 = cmd.ExecuteReader() Failed to convert parameter value from a String to a Int32.]

While reader3.Read()

sb.Append(reader3(i).ToString() +".....<BR> <BR /> ")

EndWhile

|||

I think I have solved the problem. The reason for the rror: "Failed to convert parameter value from a String to a Int32"
is Value = "EmpID". It should be EmpID.

Thanks gain.

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.