Database

Re: Help: Save data back to MS Access DB

6-Mar-03 19:57

What was the error message that you got?

What SQL Insert/Update/Delete statements were you using?

Andy Harman

DionChen7-Mar-03 6:38

DionChen

7-Mar-03 6:38

After about a day, I finally figured out what is wrong. In my DB table, I have a column called "Value". It caused the crash.

Thanks anyway.

Dion

Text Changed in WinForm Datagrid cell

DionChen6-Mar-03 9:49

DionChen

6-Mar-03 9:49

Hi,

I am developing a simple WinForm app with datagrid on the form. I want to know when any cell text changed so that I can enable my Save button. What event should I handle? Any example will be even better. Thanks.

Dion

Is it possible to set a adBoolean Column to AdColNullable ?

BrunoDelille6-Mar-03 3:40

BrunoDelille

6-Mar-03 3:40

I'm creating a simple Access Database with one table and one boolean field via ADOX and Jet Oledb Provider.

The following code gives me an IDispatch Error 3105 when appending the Table. Note that with adInteger or adVarWChar the code is working. Any idea ? Thx in advance.

pThisAdoCatalogPtr.CreateInstance(__uuidof(Catalog));
pThisAdoTablePtr.CreateInstance(__uuidof(Table));
pThisAdoColumnPtr.CreateInstance(__uuidof(Column));

pThisAdoCatalogPtr->Create("PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=4;Jet OLEDB:System Database=c:\\MsOffice\\Access\\System.mdw;Data Source=mybase.mdb;User Id=admin;Jet OLEDB:Database Password=mypass;");

pThisAdoTablePtr->Name="MyTable";

pThisAdoColumnPtr->Name="BooleanColumn";
pThisAdoColumnPtr->Type=adBoolean;
pThisAdoColumnPtr->Attributes=adColNullable;

pThisAdoTablePtr->Columns->Append(_variant_t((IDispatch *)pThisAdoColumnPtr), adBoolean, 0);

pThisAdoCatalogPtr->Tables->Append(_variant_t((IDispatch *)pThisAdoTablePtr)); <--- Here is the exception thrown.

Bruno.

How to display full column length in SQL

IrishSonic5-Mar-03 23:54

IrishSonic

5-Mar-03 23:54

Hiya I have an MFC app that writes data to my SQL database. The columns lenghts are set to 30. But when I open my SQL database to view the contents, I can only see the first 15 chars of each column..

How do I get the SQL database to display the full columns lenghts when it is opened??

Thanks,
grahamoj.

Re: How to display full column length in SQL

Nick Parker6-Mar-03 3:09

Nick Parker

6-Mar-03 3:09

Are you passing spaces at the end of the values at all, is it 30 characters of data, could you be trimming the values?

-Nick Parker

Re: How to display full column length in SQL

Jon Hulatt6-Mar-03 3:38

Jon Hulatt

6-Mar-03 3:38

in query analyser, tools menu, options, results tab, maximum output characters per column.

jon

#include <beer.h>

Re: How to display full column length in SQL

IrishSonic6-Mar-03 11:13

IrishSonic

6-Mar-03 11:13

Hiya thanks, have checked that but it is already set to 256 chars.
What I do is write the info to the database and then using Enterprise Manager, go in to the table to view it. All of the string is in the column. I can see it when I expand the column.

What I want to be able to do is when I open the table in Enterprise Manager, I want the column already expanded to the full lenght of the string...

Any ideas,
grahamoj

Introduction of parameter problem!!

Andrea Ferraro5-Mar-03 23:34

Andrea Ferraro

5-Mar-03 23:34

Hy guys,
I have a question relative to passing parameters of Class Accessor:
Below an extract of my code:

string sqlcmd;
sqlcmd = "UPDATE FileLocation \
SET LastAccessTime = CONVERT(DateTime,???????,121), \
LastUpdateTime = CONVERT(DateTime,??????,121) \
WHERE ([Index] = 1)";

// if(FAILED(ThedboSPSelectAccessor.Open(DBSession, _T("{ ? = CALL dbo.SPdboTWOModifyTime;1 (?,?) }"))))
if(FAILED(ThedboSPSelectAccessor.Open(DBSession, sqlcmd.c_str() ) ) )

I tried to using the calling of store procedure and all it's OK,after I tried to use a sql command but I don't know like to pass the two parameters (indicated as ?????), I tried using ? or (?) or '?' but noone is correct... Confused | :confused:

Can someone help me???
Thanks in advance.

Andrea Smile | :)

How to use the same db connection with multiple datareaders?

Segal5-Mar-03 11:22

Segal

5-Mar-03 11:22

Is it possible to define one db connection only, then define multiple dataReader assoiciated with that connection in ADO.net? If yes, how to do it? Thanks!

Re: How to use the same db connection with multiple datareaders?

Gertjan Schuurmans5-Mar-03 11:43

Gertjan Schuurmans

5-Mar-03 11:43

In general, it's not wise to do so. In a single threaded application you might orchestrate these DataReaders to use only one connection (that means one at a time, but that's probably not what you want), but in any multi-threaded environment (like ASP.NET) you will get into all kinds of trouble.

Rely on the underlying data provider to perform connection-pooling, and open a connection for each operation. For SQL Server you can specify a connectionstring a la: "Server=SVR1;Database=Northwind;...;Pooling=true"

This will force the re-use of connections, and makes opening one a lot cheaper.

Gertjan Schuurmans
Amsterdam
The Netherlands

Re: How to use the same db connection with multiple datareaders?

Gertjan Schuurmans5-Mar-03 11:45

Gertjan Schuurmans

5-Mar-03 11:45

= Server=SVR1;Database=Northwind;...; Pooling=true"
Laugh | :laugh:

Gertjan Schuurmans
Amsterdam
The Netherlands

Re: How to use the same db connection with multiple datareaders?

andyharman5-Mar-03 11:53

Re: How to use the same db connection with multiple datareaders?

5-Mar-03 11:53

No you can't have multiple datareaders open at the same time for the same database connection.

You might want to think of the following alternatives:

Open, read, then close each of your datareaders in sequence.
Execute all of the queries using a single datareader (e.g. "select * from a ; select * from b ; select * from c"). The datareader is able to process multiple resultsets (for many database products).
Read each of your queries into a DataSet object then do whatever processing you need against the DataSet.
Open multiple database connections.

Your choice depends upon what processing you are trying to achieve.

Hope this helps.
Andy Harman

Anonymous5-Mar-03 14:02

Anonymous

5-Mar-03 14:02

Thanks for replying! Initially what I did before is kind of as follows:

static void main()
{
....
func1();
....
}

static void func1()
{
....
string connectionstring = "...";
sqlconnection myconnection = new sqlconnection(connectionstring);
string sqlstr1 = "...";
sqlcommand mycommand = new sqlcommand(sqlstr1, myconnection);
myconnection.open()
sqldatareader myreader = mycommand.executereader();
string myvalue = "";
while (myreader.read()
{
myvalue = myreader["..."].tostring();
processThisValue(myvalue);
waitforresult(myvalue, connectionstring);
}
myreader.close();
myconnection.close();
}

static processThisValue(string myvalue)
{
..........
}

static void waitforresult(string inputvalue, string connectionstring)
{
sqlconnection myconnection2 = new sqlconnection(connectionstring);
string sqlstr2 = "select xxx from xxx when xxx=" + inputvalue;
sqlcommand mycommand2 = new sqlcommand(sqlstr2, myconnection2);
while (true)
{
myconnection2.open();
sqldatareader mydatareader2 = mycommand2.executereader();
string myvalue2 = "";
while (mydatareader2.read())
{
myvalue2 = mydatareader2["..."].toString();
}
if (myvalue2 == "...")
break;
}
mydatareader2.close();
myconnection2.close();
}

because of the cost for db connection is expensive, so I'm thinking to use one connection for 2 readers, etc. From your point of view, how should I simplify the above process? Thanks!

Re: How to use the same db connection with multiple datareaders?

andyharman6-Mar-03 7:13

Creating a running totals column

6-Mar-03 7:13

I would recommend that you try the simplest (easiest to code and maintain) way first, then optimise if you find that its performance is not good enough.

If your process is used a lot then you should find that the SQL-Server connection pooling will reduce the expense of database connections.

If your process is not used a lot then it is not worth the bother of optimising the code.

Andy Harman

nicholas.gionfriddo5-Mar-03 7:32

5-Mar-03 7:32

Hi All,

I am using MS Access 97.

Basically I am trying to create a query with a running totals column that displays a running total of data for all rows in the query.

Here is what I have:

tblClients
(Fields: ClientID, ClientName)

tblData
(Fields: ClientName, YTDGross, YTDNetCom, YTDNetInt, CSCost, OtherCost)

qryClientData
(Fields: tblData.ClientName, tblData.YTDGross, tblData.YTDCom, tblData.YTDNetInt, tblData.CSCost, tblData.OtherCost, YTDNetRev: (tblData.YTDNetCom+tblData.YTDNetInt), TotalAlloCost: (tblData.CSCost+tblData.OtherCost), TotalNetRev: ([YTDNetRev]-[TotalAlloCost])

So, I want to add a RunningTotal column in the qryClientData that will provide me with a running total of the TotalNetRev for all rows in the query.

If you can help me out, I'd really appreciate it.

If you have any questions (if my explaination was not specific enough), please let me know.

Thanks for your time

Nick

Chris LaQuerre5-Mar-03 10:09

Chris LaQuerre

5-Mar-03 10:09

Have you tried using the SUM() Function?

Here is the syntax:

SELECT SUM(field) AS [expression]<br />
FROM table<br />
[WHERE ...]<br />
[GROUPBY ...]<br />
[HAVING ...]

Hope this helps

Chris LaQuerre
eBusiness Analyst

nicholas.gionfriddo5-Mar-03 10:27

5-Mar-03 10:27

Yeah, I've tried it...the problem with the sum function is it groups the clients individually...so all I get when I use the sum function is a copy of the TotalNetRev field...because all its doing is summing the TotalNetRev field for each client..and since there is only one TotalNetRev field for each client, there is nothing to sum it against...the sum function would work if I was grouping the clients by account type (hedge fund, mutual fund)...it would give me a total TotalNetRev for each group....but I need a running total that encompassess all the clients...

Thanks for your help Mr. LaQuerre

Nick

nicholas.gionfriddo5-Mar-03 10:49

5-Mar-03 10:49

Does my request not make any sense???

Is it not possible to do this with SQL???

Please let me know

Thanks,

Nick

nicholas.gionfriddo5-Mar-03 10:52

5-Mar-03 10:52

Does my request not make any sense???

Is it not possible to do this with SQL???

Please let me know

Thanks,

Nick

andyharman5-Mar-03 11:42