Database Discussion Boards

Re: Retrieving Dates from Access Database using ODBC

Rob Graham7-Mar-04 17:34

7-Mar-04 17:34

I domn't think your problem is with the type specifier, but with the data type of target you are trying to bind it to: CTime date.
the variable "date" should be an SQL_C_TYPE_TIMESTAMP stuct.

See this reference [^]for more info.

Dates in MS Access are a double: days and fractions of a day since 30 DEC 1899 12:00 AM Local time (an OLE Date) and pointer to a Ctime struct probably will not work. Try getting the date as a double, and doing the conversion yourself.

Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf

Re: Retrieving Dates from Access Database using ODBC

pavneet8-Mar-04 8:54

pavneet

8-Mar-04 8:54

:-DHey thanks for all the help, I worked out the way out...U were right but still there is better way...and a bit different than the way u suggested

DATE_STRUCT date;

sr = SQLBindCol(hstmt,1,SQL_C_TYPE_DATE,&date,sizeof(SQL_DATE_STRUCT),&ld);
if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
{
displayError(sr, "Error in binding 1");
}
The structure is exactly what the SQL query wants and works perfectly...
Thanks for everythng once again

DateTime not updating time

cje3-Mar-04 7:54

cje

3-Mar-04 7:54

I have a field in an Access table that is a Date/Time field but when I insert a new row into the dataset and update the database, the Date information is transfered but the Time information is not...it is set to zeros. Can anyone help??

<br />
this.dataSetLog.Log.AddLogRow(System.DateTime.Now, NewExp, ID, SN ); <br />
try<br />
{				                              <br />
   this.DataAdapterLog.Update( this.dataSetLog );		<br />
}<br />

thanks in advance
CJE

Connecting to MySQL with ODBC

sps-itsec463-Mar-04 2:56

sps-itsec46

3-Mar-04 2:56

Hi guys,

I think it's a simple question: I connect to a MySQL database with OdbcDataAdapter and I want to get the schema of the database directly from the database, so I use something like this:

public DataSet GetData()
{
    // Create DataSet
    DataSet ds = new DataSet(datasetName);
    OdbcDataAdapter adapter = new OdbcDataAdapter();
    adapter.SelectCommand = GetSelectAllCmd();  // Generates Query: "SELECT * FROM " + tableName
 
    // Get schema from database
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 
    // Rename table
    adapter.TableMappings.Add("Table", tableName);
 
    // Get data
    adapter.Fill(ds);
 
    // Do we have errors?
    if(ds.HasErrors)
        ds.RejectChanges();
    else
        ds.AcceptChanges();
 
    // Free resources
    adapter.Dispose();
 
    return ds;
}

Everything seems working fine, but the schema isn't transfered correctly. The MaxLength property of the DataColumn member has the value of the longest entry in the database. For example if there is a column called 'name' and the database has an entry like 'John' the MaxLength property is 4. But I specified the 'name' column to allow 100 signs while creating the table (... name VARCHAR(100) NOT NULL, ...).

Furthermore I'm not able to change the MaxLength property. The following has no effect:

ds.Tables[tableName].Columns["name"].MaxLength = 100;

So I'm not able to insert a name to the 'name' column that is longer than 4. I get the error "The value violates the MaxLength limit of this column". Many thanks in advance!!!

Regards, mYkel

connection to SQL Server from extranet

MyttO3-Mar-04 0:29

MyttO

3-Mar-04 0:29

I have a client application using ADO to access an SQL Server Instance (or an MDSE instance actually) on intranet.

The intranet is 'protected' by a Firewall with IP forwarding activated using ipchains. SQL Server documentation tells to masq UDP port 1434 and TCP port corresponding to the instance of SQL server.

DOESN'T WORK Frown | :(

Connection creation fails lamentably...

Any help will save my life.

-MyttO

Re: connection to SQL Server from extranet

Mike Dimmick3-Mar-04 2:10

Mike Dimmick

3-Mar-04 2:10

udp/1434 is used by SQL Server clients to discover the correct TCP port to use for a given instance (remember that SQL Server supports up to 15 named instances and 1 default instance on the same hardware). Each instance uses its own port; the default instance defaults to port 1433. Named instances default to the TCP stack's chosen next port number (i.e. bind is called with a 0 port number, if you're familiar with sockets programming).

Name resolution, to find the server, is done using the normal Windows mechanisms. On Windows 2000 or XP, this uses DNS and any HOSTS file first, then WINS if configured and any LMHOSTS file, then falls back on NetBIOS broadcasts. Windows NT 4.0 requires that the 'Enable DNS for Windows Resolution' checkbox is checked to resolve names using DNS. Ensure that the client computer can ping the server by name (this won't work if the firewall blocks ICMP echo, of course).

You can configure the TCP port used either using the Server Network Utility (available on the server's start menu) or by clicking Network Configuration on the General tab of the instance's properties in Enterprise Manager. If using the Server Network Utility, select the appropriate Firstly, ensure that the TCP/IP protocol is in the Enabled Protocols list. Select TCP/IP in Enabled Protocols and click Properties. You can set the default port for this instance in this box.

Next, check your MDAC version on the client. It should be at least version 2.6 if connecting to SQL Server 2000, with the MDAC service pack at least the same as the server. For SQL Server 2000 SP3(a), use at least MDAC 2.7 SP1. Obtain MDAC from MSDN's Data Access Developer Centre Downloads[^]. You can check your current version with the MDAC Component Checker[^].

You should also check that the client is configured to use TCP/IP. Run cliconfg on the client computers, and check that TCP/IP is in the Enabled Protocols list. If you're not going to allow NetBIOS-over-TCP/IP (NBT), you might as well disable the Named Pipes protocol on the client.

If you're using a non-default instance, and you've decided to block udp/1434, you may need to use the Alias tab to add an alias for your instance. This allows you to specify the IP address and port for a given name. You could consider doing this if your name resolution isn't working.

For more information, see KB article 287932: INF: TCP Ports Needed for Communication to SQL Server Through a Firewall[^].

Stability. What an interesting concept. -- Chris Maunder

Re: connection to SQL Server from extranet

Mike Dimmick3-Mar-04 2:27

Mike Dimmick

3-Mar-04 2:27

Just a quick addendum: if you're using a trusted connection, that is, Windows authentication, and your client computers are part of a Windows NT 4.0 domain (or Windows 2000 or Server 2003 in mixed mode), you'll need to ensure that the server can contact its own domain controller in order to authenticate the client (and that the server's DC trusts and can contact the client's DC, if server and client are members of different domains).

Windows 2000 and Server 2003 Active Directory don't have this problem, because the client obtains its ticket from its own domain controller itself, so the server is not required to contact the domain controller.

Stability. What an interesting concept. -- Chris Maunder

Many thanks

MyttO4-Mar-04 23:12

MyttO

4-Mar-04 23:12

Thanks a thousand times Big Grin | :-D

Now it works fine!

-MyttO

Connection from Crystal Report to DataSet

Pablopablo2-Mar-04 16:12

Pablopablo

2-Mar-04 16:12

Hi...

I have a Crystal report witch is connected to at local DataSet(dataTabel)...
Every thing seems to work well...but when I compile and the program starts...it
aks for login/pass to the database and I don't use login/pass at all...??

What do I do wrong??

I choose to connect through the project ADO.NET Dataset...--> myDataTable in the "Report Expert" wizard in Visual Studio...

I REALLY hope you can help me in some way...course I have spend too much time
with this prob already... Confused | :confused:

PabloPablo

Re: Connection from Crystal Report to DataSet

Anonymous9-Mar-04 5:16

Anonymous

9-Mar-04 5:16

Hi,
I have the same problem. I use a dataset that is passed to my ClassViewer(the form that would show the report).
but it continues to ask me data server name, database name, id and pwd.

Bye

Re: Connection from Crystal Report to DataSet

Pablopablo9-Mar-04 10:00

Pablopablo

9-Mar-04 10:00

Actually I'am pretty sure...that it is not possible to do it..

Use Microsoft Access and JET 4.0 database provider instead...
It works...and is much faster...as it make queries directly on the data in the database(your Access file)....

Best regards

/PabloPablo Cool | :cool:

Re: Connection from Crystal Report to DataSet

DustInTheWind10-Mar-04 5:54

DustInTheWind

10-Mar-04 5:54

Hello, I read your last message (with that solution) but I think it has an inconvenience (except I haven't caught idea): if you choose to do it through a provider (in this case: JET), it asks for path of the file as such, so what about if your file changes location?, or perhaps, if provider changes (suppouse you move to SQL Server 2k or maybe another one); it means if you have 200 reports, you'll have to change 200 times Unsure | :~

just because of that linking through report itself.
In other words, I suppouse that SOMEHOW Microsoft has thought about that issue. (And at same time, to have reports built-in with your app).

Re: Connection from Crystal Report to DataSet

DustInTheWind10-Mar-04 7:29

DustInTheWind

10-Mar-04 7:29

Hello, hope this could be the answer:

I have 2 forms, on second there's a viewer with its modifier set to public (of course, this is not advisable) but in order to see this, is much better to see with few lines than to create a better mechanism but having to go to many places. With no more words, here it goes:

private void button1_Click(object sender, System.EventArgs e)
{
oleDbDataAdapter1.Fill (dataSet11);

Form2 f2 = new Form2 ();
CrystalReport1 cr = new CrystalReport1();
cr.SetDataSource (dataSet11);
f2.crystalReportViewer1.ReportSource = cr;
f2.ShowDialog ();
f2 = null; // I don't know if it necessary, because GC.
}

Columns failing to save data on insert

Mike Osbahr2-Mar-04 12:25

Mike Osbahr

2-Mar-04 12:25

On SQL 7 I wrote a stored procedure which included:

Insert tblA
A.item1, A.item2,A.item3,
...
A.item10,A.item11,A.item12,
B.item1,B.item2,B.item3
From tblA A, tblB B
Where A.IDcol = @colA
And B.IDcol = @colB

Everything worked as expected.

I generated a script, imported it into another server (which happened to be SQL 2000), and it would not write the last three items when inserting. The fact that the items were coming from another table appears to just be a coincidence. I replaced the final three items with constants and there was still no joy. Finally I wrote an Update to modify the final three items after the insert was completed. The insert itself generated no errors, it just wouldn't write the data.

If anyone has seen this kind of problem before, I would sure appreciate knowing the cause and how to avoid it in the future.

Thanx...

>>>-----> MikeO

Export a DataSet to a NEW Excel File

Jeff Monheiser2-Mar-04 3:42

Jeff Monheiser

2-Mar-04 3:42

I'm writing a data acquisition program that will be acquiring data and storing it within multiple tables of a DataSet. I'd like to export this data set directly to a new excel file if possible. I'd like each table of the data set to be a new worksheet within excel. My customer doesn't know databases so they are requiring that I use excel. Any help or advice would be great.

Jeff Confused | :confused:

Re: Export a DataSet to a NEW Excel File

John Kuhn2-Mar-04 12:43

John Kuhn

2-Mar-04 12:43

How simple (or how complex) should the resulting workbook be? Also, perhaps obviously, there are a number of ways someone can transform SQL data into Excel -- using the DTS Wizard, exporting to Excel using a remote view in Access, or transforming a DataSet into Excel using the Excel object model. Which method is simpler for your users? In the case that you want to generate Excel directly from C# or VB.NET, you walk through the rows and columns in your dataset and create corresponding rows and columns in Excel. The method I often use is to store metadata about the format of the spreadsheet in other tables, then use a transformation process to go from one to the other.

What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.

DataSet object : not disposed

mxcad2-Mar-04 3:36

mxcad

2-Mar-04 3:36

Hello all!
DataSet object is never disposed! I derived my own class from DatSet, added destructors to it then, and the problem is I NEVER get destructor called!
Is it right? I never get any of created objects of that class to be collected by GC.

Is it right? Or I miss something?

Thanks in advance.

Regards,
alexx.

Re: DataSet object : not disposed

Mike Dimmick2-Mar-04 6:30

Mike Dimmick

2-Mar-04 6:30

Yes, you didn't wait for a garbage collection to happen that required your finalizer to be called.

The GC presently runs finalizers in a queue using a dedicated finalizer thread. An object is added to this queue (called the freachable queue by the CLR people and some of the documentation) when the garbage collector collects the object (the memory actually survives the garbage collection; the memory itself will only be freed on the next collection of that generation after the finalizer runs). If memory pressure isn't high enough, the GC won't reclaim the memory and won't run the finalizer.

Finalizers in .NET languages do not work in the same way as C++ destructors. If you need to dispose of resources as soon as the object goes out of scope, implement IDisposable and use a using block (in C#) or call Dispose directly.

Stability. What an interesting concept. -- Chris Maunder

DataSet object : not disposed

The Bug2-Mar-04 3:36

The Bug

2-Mar-04 3:36

those xmlbuilder generated objects..

Anonymous1-Mar-04 23:32

Anonymous

1-Mar-04 23:32

Has anyone ever had luck type-casting them from standard classes before? ie. a "UserDataTable" from DataTable?

Even with matching constraints I get errors. Shouldn't it be possible?

Re: those xmlbuilder generated objects..

Mike Dimmick3-Mar-04 2:17

Mike Dimmick

3-Mar-04 2:17

If you've got a DataTable that's been returned from some general procedure which doesn't know about your derived classes, you can't cast it to a derived class - because it isn't one. The object's actual type is carried with it.

You should be able to write the data to an XML format using DataSet.WriteXml and read it back using a typed dataset's ReadXml method.

Stability. What an interesting concept. -- Chris Maunder

use modem dialup to retrieve data from SQL 2000 Server...

DengJW1-Mar-04 17:23

DengJW

1-Mar-04 17:23

I am programming a program to use modem dialup to retrieve data from SQL 2000 Server...

What would be a good solution for the connection betweeb a client PC and the server running SQL Server 2000?

JW

DJ

Problem while accessing SQL server 2k remotely?

DengJW1-Mar-04 15:49

DengJW

1-Mar-04 15:49

I have an SQL Server 2k (personal edition) running on Windows 2000 Pro for testing.

I tried to connect from other PC running SQL server to this databse. However,
from SQL Query Analyzer on the local PC, I could not find the database once I clicked on the ... which is right to the SQL Sever: text box.

The connection between the PC and the Database machine is modem dialup. From either side, I can ping to each other without any problem.

Moreover, once I connected my PC to the intranet, I can access/login to any SQl server database which is within the intranet environment.

ANy help are greatly appreciated.

JW

DJ

Re: Problem while accessing SQL server 2k remotely?

Mike Dimmick3-Mar-04 2:31

Mike Dimmick

3-Mar-04 2:31

The box that appears when you click the '...' button is filled using broadcasts to the local subnet, on UDP port 1434, or by using NetBIOS broadcasts. It's unlikely that this will work over a dial-up connection, because routers do not forward broadcast messages.

You'll need to specify the server explicitly. This may not work using names for the same reason as above, so you may need to use an IP address.

Stability. What an interesting concept. -- Chris Maunder

How to read encrypted data from SQL Server...

OHSAH1-Mar-04 10:12

OHSAH

1-Mar-04 10:12

Hi all,

I'm encrypting some private information into a SQL but now I got
an issue where my users want to be able to perform searches on
these encrypted columns. I could limite them to exact searches but
for sure they will complain about that so I will have to provide
search where I can use the LIKE operator.

The encryption happens before I store the data into the database.

Any ideas?

Thanks in advance.

Alberto

OHSAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.