Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY?
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:

It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

DataTransfer Error

I have written following lines of code, but when TransferData() method is called, it generates error "Property LoginSecure cannot be changed or read after a connection string has been set."

Can anybody help me? Thanks in advance....

Code:

//1. Create Transfer Object

Transfer transObj = new Transfer();

//transObj=new Transfer(server.Databases[fromdatabasename]);

transObj.DestinationLoginSecure = false;

transObj.DestinationLogin = "sa";

transObj.DestinationPassword = "test";

//Set Destination Server

//Build a "serverConnection" with the information of the "sqlConnection"

destSrvCon =

new ServerConnection(sqlCon);

//The serverConnection is used in the ctor of the Server.

destServer = new Server(destSrvCon);

transObj.DestinationServer = destServer.Name.ToString();

//2. Define Transfer Object's Properties

transObj.Database = server.Databases["Aworks"];

transObj.DestinationDatabase = "AWorksCopy";

transObj.Options.WithDependencies =true;

transObj.Options.ContinueScriptingOnError = true;

transObj.CopySchema = true;

transObj.CopyData = true;

transObj.CopyAllTables = true;

//4. Finally Transfer Data

transObj.TransferData();

//transObj.ScriptTransfer();

I got this error as well, and have tried everything to avoid it. The only explanation I have left is that I'm using server 2000. Is this what you're using Ritesh?
|||I got this error as well. I changed the file permissions on the ReplData folder and it went away.|||

i get this same error.

did you guys resolve the problem?

ReplData folder? only folder i can think of is the folder where the database files are stored....

Datasets in custom code

Hi guys,

is it possible to access my report's datasets from within custom code or pass them as parameters to a custom assembly? I want the dataset itself, not just fields, so i can programmatically look through it for data.

Thanks!

sluggy

This is not directly supported.

There are however some alternative approaches:

* add a list reportitem to the report and inside the list make a call to your custom code/custom assembly function and pass in the fields as parameter values. This will call your function for every row of data of the dataset bound to the list.

* or a more complex but also more powerful option is to use the new CustomReportItem feature in RS 2005 and implement a custom processing control which can examine the processed DataValues inside the CustomReportItem. A starting point with a sample is available here: http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx

-- Robert

|||Thanks Robert, i will check that solution out, although it is probably overkill for the particular scenario i was dealing with

Datasets access from custom code

Hi to all
Is possible to access to the report datasets or even report items from the
custom code embedded in the report?, If yes, how?,could you send some sample
code?
ThanksNo, you would need to pass in the values you are interested in as parameters
to your function.
For example:
=Code.MyFunction(Fields!CityCode.Value) or
=Code.MyFunction(ReportItems!textbox1.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> Is possible to access to the report datasets or even report items from the
> custom code embedded in the report?, If yes, how?,could you send some
sample
> code?
> Thanks
>
>|||Ok... I think this is very restrictive, I need to condition the visibility
of some components in base to some information contained in the dataset,
like if exist some value for one column and if it is the same for others
columns (if exist some) and I was trying to create a general function for
all my components , it seems that I'll have to create other datasets that
makes this kind of comparision with SQL or some SP, If you have a best idea
please let me know...
What about to pass Datasets as parameters? is possible?
Thanks
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> No, you would need to pass in the values you are interested in as
parameters
> to your function.
> For example:
> =Code.MyFunction(Fields!CityCode.Value) or
> =Code.MyFunction(ReportItems!textbox1.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > Hi to all
> > Is possible to access to the report datasets or even report items from
the
> > custom code embedded in the report?, If yes, how?,could you send some
> sample
> > code?
> > Thanks
> >
> >
> >
> >
>|||No, you cannot pass the entire data set as a parameter.
You could hand in the entire fields collection, however, if that would help
you any.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:u1qA$AcdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> Ok... I think this is very restrictive, I need to condition the visibility
> of some components in base to some information contained in the dataset,
> like if exist some value for one column and if it is the same for others
> columns (if exist some) and I was trying to create a general function for
> all my components , it seems that I'll have to create other datasets that
> makes this kind of comparision with SQL or some SP, If you have a best
idea
> please let me know...
> What about to pass Datasets as parameters? is possible?
> Thanks
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> > No, you would need to pass in the values you are interested in as
> parameters
> > to your function.
> > For example:
> >
> > =Code.MyFunction(Fields!CityCode.Value) or
> > =Code.MyFunction(ReportItems!textbox1.Value)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > > Hi to all
> > > Is possible to access to the report datasets or even report items from
> the
> > > custom code embedded in the report?, If yes, how?,could you send some
> > sample
> > > code?
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
>

DataSet table as DataFlow Destination Component

Is it possible to use a datatable as destination component in dataflow ?

Sample code Fr MS:
Create the source component.
IDTSComponentMetaData90 source = dataFlow.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper srcDesignTime = source.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();

destination.ComponentClassID = WHAT ? for dataset Table ?

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

thanks in advance
PatrickI saw that the datareader can be used as Destination.
So, my "ask for" is closing.
Thanks
Patricksql

DataSet rows being deleted, but after the update , the sql database is not updated. The de

Stepping thru the code with the debugger shows the dataset rows being deleted.

After executing the code, and getting to the page presentation. Then I stop the debug and start the

page creation process again ( Page_Load ). The database still has the original deleted dataset rows.

Adding rows works, then updating works fine, but deleting rows, does not seem to work.

The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.

cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId);

rowCountDb = cDependChildDs._ClientDependentChild.Count;

for (row = 0; row < rowCountDb; row++)
{

dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0];
dr_dependentChild.Delete();


//cDependChildDs._ClientDependentChild.Rows.RemoveAt(0);

//cDependChildDs._ClientDependentChild.Rows.Remove(0);
/* update the Client Process Table Adapter*/
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);
// cDependChildTA.Update(cDependChildDs._ClientDependentChild);

}

/* zero rows in the DataSet at this point */

/* update the Child Table Adapter */
cDependChildTA.Update(cDependChildDs._ClientDependentChild);

Hi,

You should use AcceptChanges method after using delete in order to update the data. The following link may be helpful to you.

http://msdn2.microsoft.com/en-us/library/ms233823(VS.80).aspx

Thanks.

DataSet and Transactions...

hi all,

i'm developing a WebApplication with VisualStudio2005 (with C# code).
i've defined a DataSet with some TableAdatpter. For each TableAdapter, i've definet some "customized" commands, performing specialized operations over the table on DB. (db is SqlServer2000).
For each TableAdapter, i've set as "public" the connection, so that i can use it within my code for beginning a new transaction. Now, how can i set that transaction, to the specialized commands I defined for each TableAdapter?

Thanks all.
Andrea.

Hi Andrea,

I think you can begin transactions by doing db.BeginTransaction and then transferring the same db to through the different commands so that the whole set of operations take place as one Transaction(hope this is ur requirement!) and once you have filled the adapters u can commit the transaction.

sql

datareader wont read first line of DB

hi there, i have some code which cycles through my table in my DB and fills some textboxes. For some reason if there are 5 lines it will only get 4, yet strangly i used the code somewhere else for the same kind of thing and it reads all the lines, and anyone help?

int a = 0;int b = 0;

//This is the sql statement.

string sqlMultiL ="SELECT * FROM tbl_stock_part_multi_location WHERE stock_ID =" +Convert.ToInt32(Request.QueryString["qsStockID"]);// + ") AND EXISTS (SELECT * FROM tbl_stock_part_multi_location WHERE stock_ID =" + Convert.ToInt32(Request.QueryString["qsStockID"]) + ")";

//This creates a sql command which executes the sql statement.

SqlCommand sqlCmdMultiL =new SqlCommand(sqlMultiL, myConnMultiL);

myConnMultiL.Open();

SqlDataReader drMultiL = sqlCmdMultiL.ExecuteReader();

//This reads the first result from the sqlReader

while (drMultiL.Read())

{

try

{

((TextBox)Panel2.FindControl("txtDesc_L" + a.ToString())).Text =Convert.ToString(drMultiL["description"]);

for (b = 1; b <= 5; b++)

{

((TextBox)Panel2.FindControl("txtQty" + b.ToString() +"_L" + a.ToString())).Text =Convert.ToString(drMultiL["qty" + b.ToString()]);

}// end of for b loop

}

catch (Exception exMultiL)

{

lblError.Text = lblError +Convert.ToString(exMultiL);

}

//increase the counter

a++;

// you can add a break if you only want 5 rows returned:

if (a >= 5)break;

}

also i dont know if anyone could answer this is if the value in my DB is not = to one in the DDL how can i handle that so it dosnt throw an error and cause my code to stop?

Thanks in advance

Jez

Change to

for (b = 0; b <= 5; b++) //You need to start from the 0

{

a++; //Here you missed

((TextBox)Panel2.FindControl("txtQty" + a.ToString() +"_L" + a.ToString())).Text =Convert.ToString(drMultiL["qty" + a.ToString()]); //Need to change to a too

}// end of for b loop

|||

hi gave that a go and still no luck :( i have tried changing it to 0 before by my controls start from 1 etc. As for changing the controls all to a.ToString() i think thats wrong because it would just do txtQty1_L1, txtQty2_L2 etc instead of going from 1 - 5 and then to the next L

DataReader Output skipping first line

I am using the following code to query a DB and output an Excel Spreadsheet. For some reason, the first line is always missing.

Any help would be greatly appreciated.

============================================

1reader = cmd.ExecuteReader()2If Not reader.Read()Then3 Return False4Else5 For i = 0To reader.FieldCount - 16 strLine += reader.GetName(i).ToString & Chr(9)7Next8 objStreamWriter.WriteLine(strLine)9 strLine =""10With reader11While .Read12For x = 0To .FieldCount - 113 strLine = strLine & reader.GetValue(x) & Chr(9)14Next15 objStreamWriter.WriteLine(strLine)16 strLine =""17End While18 End With19End If

line 2 of you code is wasting the first row of data by performing a reader.Read that you ignore.

change line 2 to:

If Not reader.HasRows()Then

|||

Perfect!

Thank you!!!!

Datareader not referencing connection object

Please see following code :

SqlConnection conn=new SqlConnection(@."something...;");
SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);

conn.Open();
SqlDataReader rd;
conn=null;

try
{
rd = comm.ExecuteReader();
rd.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

This Code works fine. I have set conn=null, still datareader is able to read the data. Why?

Thank you.

You set conn to null, you are setting thereference to null. Since your SqlCommand has it's own reference to the connection, the connection is still around. You should use a Finally block for the .Close() to ensure it is called.|||

Thank you for answering.

Yes it is bad code that I have set it null.

Please look following code:

rd=comm.ExecuteReader(<<behaviour close connection>>);
do someting.. donot close rd
comm.CommandText="another query"
rd=comm.ExecuteReader(<<behaviour close connection>>);
rd.close()

Is there First SQL Connection is still open?
Thank you.

|||

The only provider that will even allow such a thing is SQL Server 2005, otherwise you will get an error when you attempt to execute the second ExecuteReader.

Regardless of what it actually does, I wouldn't recommend assuming anything. It's ambiguous on what it SHOULD do, so don't rely on what it really does, and don't code that.

|||

Hi Motley,
Yes, we do not code like this way. But this is already done by other developers, and we are reviewing it.

Please have a look to following code (Assume try/catch is properly placed) :

privatevoid Form1_Load(object sender, System.EventArgs e)
{
SqlDataReader dr1=GetDataReader("Select 1"); //Line1
SqlDataReader dr2=GetDataReader("Select 2"); //Line2
dr1=GetDataReader("Select 3"); //Line3
dr1.Close(); //Line4
dr2.Close(); //Line5
}

private SqlDataReader GetDataReader(string qry)
{
SqlConnection conn=new SqlConnection(@."Server=localhost;uid=user;pwd=password;database=northwind;;");
SqlCommand comm=new SqlCommand(qry,conn);
conn.Open();
SqlDataReader rd;
rd = comm.ExecuteReader(CommandBehavior.CloseConnection);
comm=null;
conn=null;
return rd;
}

This works withSQL2000 also. Each time new instance of sqlDatareader is created and reference is returned.
At Line3, we are assigning new datareader to dr1. Will it close the dr1 previously opened (for "Select 1") automatically?
If 2 datareaders are not allowed open at the same time, how it is working?
At Line4 we are closing the dr1, but I think there are 2 different connections opened for dr1 and this will lose the last one. Right?

Thank you,

|||In this example, you are using a new connection for each data reader, and so you can get an additional datareader and assign it to dr1. However, in this case, dr1 will be closed (and in this example, the underlying connection) whenever the data reader object is garbage collected, which is a terrible idea.|||What doug said is correct.

Datareader insted of dataset Sored Procedure

i m writing a stored procudrue to update my data that is onthertable.and i pass the parameter in my vb code,when i pass the data thatis insert only first record of data but second record insert the eroorwill come is data reader is colsed. now insted of data reade i have touse data set how can i use that and update my data is ontehrtable.?below i written my vb.net2005 code.

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
' con.Open()
' Dim ggrnid As String
' Dim acceptqty As String
' Dim itemid As String

' Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox)
' ggrnid = grnid.Text

' Dim sWhere As String = grnid.Text
' If (Not String.IsNullOrEmpty(sWhere)) Then
' For Each s As String In sWhere '
' 'Dim iRowIndex As Integer = Convert.ToInt32(s)
' Dim sqldtr As SqlDataReader
' sqlcmd = New SqlCommand
' sqlcmd.Connection = con
' sqlcmd.CommandType = CommandType.Text
' sqlcmd.CommandText = "select acceptqty,itemid fromgrndetail where grnid='" & Trim(ggrnid) & "'"

' datacommand = CommandType.StoredProcedure
' 'datacommand("aaceptqtygrn", con)

' Dim cmd As New SqlCommand("aaceptqtygrn", con)

' sqldtr = sqlcmd.ExecuteReader()

' 'dataset = datacommand.
' 'sqldtr = sqlcmd.ExecuteScalar

' If sqldtr.HasRows = True Then
' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While
' 'sqldtr.Close()
' 'cmd.ExecuteNonQuery()
' 'Next sqldtr.HasRows
' End If
' Next s
' sqldtr.Close()
' con.Close()
' End If
' End If
Catch ex As Exception

MsgBox(ex.Message)

End Try

prajapatiamit2003:

' While sqldtr.Read()
' acceptqty = sqldtr.Item("acceptqty")
' itemid = sqldtr.Item("itemid")
' cmd.CommandType = CommandType.StoredProcedure
' cmd.Parameters.AddWithValue("@.acceptqty", acceptqty)
' cmd.Parameters.AddWithValue("@.itemid", itemid)
' sqldtr.Close()
' cmd.ExecuteNonQuery()

' End While

It looks like you are closing your data reader before exiting you while loop. And you probably do this because your stored proc wont execute while you have the data reader open.

You can either ececute the stored procedure ona different connection, or you can store the results of the reader in some kind of list/array/collection close the reader and then iterate through the collection to execute the sproc.

Because the datareader maintains an open connection to the database you cannot use that connection while the reader is open.

|||But how can i store in array of my data!

Datareader does not return result

the following is the code,...When i run this code I get "Invalid operation exception", Trying to read when no data is present, but the query returns result in query analyser...can anyone suggest what the problem is ...

Connection is open and the query is simple select statement

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

Thanks

Niranch

niranch,

can you show me exact syntax of your query string within the context of how you are using it in the c# code? You may have some quotes being escaped incorrectly or something such that when run from the app code, it does not return any results and therefore you are getting your error. so, make sure that your query string is written correctly, particularly how you have used single or double quotes...--jp

|||

hi jp,

thank you for your suggestion...I just figured out the problem...

It is just that, when I run the same set of statements in my code behind file, I get the result, but when I run it in a seperated .CS file(DAL), I get the error mentioned in my post...I am trying to return datareader object to the Business tier...

I dont know why...

Thanks

Niranch

|||ahh so you are having trouble with the data moving through tiers. can you show some code of how your are interacting between the tiers?|||

hi this is my code...the same set to commands ,connections works fine in codebehind file

codebehid file

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database)

class where i have database access

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader);

|||

connectionString = BuildConnectionString().ToString();

string query = "select * from users";

myConnection.ConnectionString = connectionString;

SqlCommand myCommand =newSqlCommand(query,myConnection);

myConnection.Open();

SqlDataReader myReader = myCommand.ExecuteReader();

return(myReader); <-- at this point is your reader null? if you put a throw statement here like if(myReader == null) throw new Exception("Null Reader") will you get an exception? is the reader already null here, or are you getting results here and just not when you set

myReader = obj.getdbvalues("Server", sName.ToString()); (obj is the instance of the class - the class where i have access to Database) you get the null reader here? that make sense? at what point are you not getting any results? -- jp

|||

I get the problem, Exactly after executing the command

DAL Page

myreader = mycommand.executereader(), i get a null reader. "Invalid read attempt when there is no data" is the error...only in this page, i have my connection open...

i dont know if it is ok to send a datareader object as return object to another page............

|||

thankyou, i got the problem solved,,,

I transfered the contents from reader to datatable and passed as return objec to presentation tier...It worked fine.............

Datareader can not open connection to my database for login "myusername"

This is my page_loads event code and iam getting the Exception pasted below the code.

---------------------------------------------

Protected

Sub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)IfNot Page.IsPostBackThenDim myconnectionAsNew SqlConnection("Data Source=localhost\SQLEXPRESS;initial catalog = safetydata.mdf;Integrated Security=True;User Instance=True")Dim strSQLAsString ="SELECT Incident_Id,Emp_No From Report_Incident"Dim mycommandAsNew SqlCommand(strSQL, myconnection)

myconnection.Open()

Dim readerAs SqlDataReader = mycommand.ExecuteReader()

Dim chartAsNew PieChart

chart.DataSource = reader

chart.DataXValueField =

"Incident_id"

chart.DataYValueField =

"Emp_No"

chart.DataBind()

chart.DataLabels.Visible =

True

ChartControl1.Charts.Add(chart)

ChartControl1.RedrawChart()

myconnection.Open()

EndIfEndSub

--------------------------------------

EXCEPTION IS BELOW

Cannot open database "mydatabase.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open database "safetydata.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.

Source Error:

Line 18: Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"Line 19: Dim mycommand As New SqlCommand(strSQL, myconnection)Line 20: myconnection.Open()Line 21: Dim reader As SqlDataReader = mycommand.ExecuteReader()Line 22:

Source File:C:\Incident Reporting System--Trial Version\WebChart.aspx Line:20

Stack Trace:

[SqlException (0x80131904): Cannot open database "safetydata.mdf" requested by the login. The login failed.Login failed for user 'myusername'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89 System.Data.SqlClient.SqlConnection.Open() +160 ASP.webchart_aspx.Page_Load(Object sender, EventArgs e) in C:\Incident Reporting System--Trial Version\WebChart.aspx:20 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.

Hi Nick,

Based on the connection string, we can see that you're currently using the the Windows integrated authentication to connection to the .mdf file. And the exception message reveals your windows identity is not a valid user to this database.

In this case, you can try to check if you have added your identity to the database login list. Also, please check if you have granted enough permission to this username.

HTH.

Datagrids

Having populated my datagrid with the following code,

DA = New SqlCeDataAdapter(SqlceCmd)
MyData = New DataTable
DA.Fill(MyData)
MyData.Columns(0).ColumnName = "Task"
MyData.Columns(1).ColumnName = "Reported"

IncidentDtl.DataSource = MyData
IncidentDtl.Refresh()

I now want to change the size of the columns. Could someone please let me know how to manipulate the appearance of my grid as my grid IncidentDtl doesn't seem to have a Columns property only the Table does.

I would take this thread to one of the Visual Studio or Visual Basic (or even C#) forums. Many of these SQL Server folks don't have much experience with the inner workings of the DataGridView control.

The DataGridView has any number of properties like the AutoSizeColumnsMode that can automatically set the size of the columns based on the first row width, the displayed rows maximum width etc.

|||

What about:

Code Snippet

DataGridView dgw = new DataGridView();

dgw.Columns["SomeColumn"].Width = 100;

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Ah, that would be nice, but the Columns collection.Width property is read-only.

|||http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridviewcolumn.width.aspx

DataGridViewColumn.Width Property

Gets or sets the current width of the column.

Namespace: System.Windows.Forms
Assembly: System.Windows.Forms (in system.windows.forms.dll)

Jens K. Suessmeyer

http://www.sqlserver2005.de

Datagrids

Having populated my datagrid with the following code,

DA = New SqlCeDataAdapter(SqlceCmd)
MyData = New DataTable
DA.Fill(MyData)
MyData.Columns(0).ColumnName = "Task"
MyData.Columns(1).ColumnName = "Reported"

IncidentDtl.DataSource = MyData
IncidentDtl.Refresh()

I now want to change the size of the columns. Could someone please let me know how to manipulate the appearance of my grid as my grid IncidentDtl doesn't seem to have a Columns property only the Table does.

I would take this thread to one of the Visual Studio or Visual Basic (or even C#) forums. Many of these SQL Server folks don't have much experience with the inner workings of the DataGridView control.

The DataGridView has any number of properties like the AutoSizeColumnsMode that can automatically set the size of the columns based on the first row width, the displayed rows maximum width etc.

|||

What about:

Code Snippet

DataGridView dgw = new DataGridView();

dgw.Columns["SomeColumn"].Width = 100;

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Ah, that would be nice, but the Columns collection.Width property is read-only.

|||http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridviewcolumn.width.aspx

DataGridViewColumn.Width Property

Gets or sets the current width of the column.

Namespace: System.Windows.Forms
Assembly: System.Windows.Forms (in system.windows.forms.dll)

Jens K. Suessmeyer

http://www.sqlserver2005.de

Data-Driven Subscription not shown

I am upgradinding to sql 2005 and the Data-Driven subscription is not working. the data-Driven option It is now showing in report manager and in code I get the error " rsOperationisNotSupported".... I have the same security permitions I had before in sql 2000. Does anyone knows if there is another difference that would prevent the subscription from working ?

thank you

drey

What sku of SQL 2005 are you using? Data driven subscriptions are only supported on developer and enterprise editions.|||

thank you,

I am using Standard edition, my company does not have enterprise. I guess that is a deal breaker for upgrading...

|||

Data driven subscriptions was also an Enterprise / Developer Edition in RS 2000. It was not available for the Standard Edition.

-- Robert