This has to be a BUG
After spending a few hours experimenting the typed dataset serialization
(see previous post "How to include public properties in a typed dataset in
serialization"), this is what I found which does look like a bug (I'm using
the normal serialization with XmlSerializer):
. The values for the public property are in fact written into the Xml.
They are presented as the attributes of the Element for the Dataset. They
have the namespace of "msdata".
. If the public property is of Int16, it will be ignored and not written
into the Xml.
. For the public properties which are written into the Xml, the
deserialization does not recover them. This means that the instance
returned from the deserialization doesn't have these property values set.
So it seems to me that something is only half done here. If the behaviour
is by design, then I don't understand why these public properties were
written into the Xml in the first place. And why properties with certain
data type are not written into the Xml at the same time.
Michael Tag: Query Builder Tool Tag: 121378
Data->Generate Dataset|Preview Data
I am writing an ADO.NET provider for a specialized (non-SQL) data store, and
wanted to support the options in the Data menu, specifically "Generate
Dataset" and "Preview Data". The documentation on these types of features
is near to non-existant, including when you download the VSIP package (I
thought that would help but it doesn't).
I have the following three problems:
1. I cannot get my DataAdapter implementation to enable these menu options.
2. If I add another adapter, which does enable these options, the Generate
Dataset option gives me a dialog box titled "Microsoft Development
Environment" with the following text in it:
"Retrieving the schema for MyDataAdapter1 failed.
Object reference not set to an instance of an object."
3. Also, with another adapter to enable these options, if you select my
adapter in the Preview Data dialog, and attempt to "Fill Dataset", you get
the following error message in another "Microsoft Development Environment"
dialog box:
"The data adapter "MyDataAdapter1" is not completely configured. To resolve
this problem, configure the adapter by using the Data Adapter Configuration
Wizard or by setting the SelectCommand property of the adapter."
My question is this. Is there any place that defines what these option
expect, or what calls they make to a DataAdapter implementation? Any place
where this is documented in a useful fashion. The documentation for all of
this is really sparse!!!
Help! ;) Tag: Query Builder Tool Tag: 121375
Error converting data type numeric to decimal?
I am running a stored procedure and getting the error 'Error converting
data type numeric to decimal'. I just can't work out why. My stored
procedure values are defined like so:
@addOn1 decimal(7,2),
@addOn2 decimal(7,2),
@addOn3 decimal(7,2),
@addOn4 decimal(7,2),
@addOn5 decimal(7,2),
@addOn6 decimal(7,2),
@addOn7 decimal(7,2),
@addOn8 decimal(7,2)
These map to the precision/scale values in my database.
I am calling the stored procedure from dotnet like so:
sqlCmd.Parameters.Add("addOn1", objPremium.addOn1);
sqlCmd.Parameters.Add("addOn2", objPremium.addOn2);
sqlCmd.Parameters.Add("addOn3", objPremium.addOn3);
sqlCmd.Parameters.Add("addOn4", objPremium.addOn4);
sqlCmd.Parameters.Add("addOn5", objPremium.addOn5);
sqlCmd.Parameters.Add("addOn6", objPremium.addOn6);
sqlCmd.Parameters.Add("addOn7", objPremium.addOn7);
sqlCmd.Parameters.Add("addOn8", objPremium.addOn8);
try
{
sqlCmd.ExecuteNonQuery();
blnReturnVal = true;
}
catch(Exception e)
{
Debug.Trace.WriteLine(e);
blnReturnVal = false;
}
And getting the exception there.
the objPremium.addOn1 - objPremium.addOn8 values are all decimals. I am
instantiating them like so and not setting them to anything else (for
debug purposes):
public decimal addOn1 = -1;
public decimal addOn2 = -1;
public decimal addOn3 = -1;
public decimal addOn4 = -1;
public decimal addOn5 = -1;
public decimal addOn6 = -1;
public decimal addOn7 = -1;
public decimal addOn8 = -1;
I'm sure it's something obvious, but can anybody spot what I'm doing
wrong?
Thanks in advance! Tag: Query Builder Tool Tag: 121370
MS Access problems
Hi there,
I'm currently rewriting an old VB 6 (stand alone) application in VB .Net
2003 in which users can create MS Access databases. In the new version I
want to add security options like encrypting the database, adding users and
groups etc.
I managed to create MS access databases and tables using ADOX, but I can't
add users and groups to it. I use the following code:
Dim myCat as New ADOX.Catalog
Dim myCon as OleDb.OleDbConnection
Dim myConStr as String
Dim myNewUser as ADOX.User
Dim myNewGroup as ADOX.Group
Try
myConStr = "Provider=Microsoft.Jet.OLEDB.4.0; Jet oledb:system
database=system.mdw;" & _
"Data Source=C:\Test.mdb"
'Creating the database
myCat.Create(myConStr)
myCon = New OleDb.OleDbConnection(myConStr)
myCon.Open()
myCat.ActiveConnection = myCon <<-- This seems to be the problem
after debugging the code
'Creating a new group
myCat.Groups.Append("User group")
'Creating a new user
myNewUser = New ADOX.User
myNewUser.Name = "Jan Janssen"
myCat.Users.Append(myNewUser)
'Add new user to the new group
myNewUser.Groups.Append("User Group")
myCon.Close()
myCat = Nothing
myCon = Nothing
Catch ex as Exception
MessageBox.Show(ex.Message)
End Try
When executing this code the following error occurs: "Interface not
supported"
Can anyone help me out or is there an alternative approach without using SQL
servers?
thnx Tag: Query Builder Tool Tag: 121369
Convert is not a member of String
Hi All,
I have the following Code:
Public Sub SetAccessLevelNull()
Me(Me.tableGeneralPasswords.AccessLevelColumn) = System.Convert.DBNull
End Sub
Im getting a ErroR on this Saying "Convert is Not a Member of String"
Could somebody kindly point me in the right direct?
Many Thanks
NSC Tag: Query Builder Tool Tag: 121366
Convert is not a member of String
Hi All,
I have the following Code:
Public Sub SetAccessLevelNull()
Me(Me.tableGeneralPasswords.AccessLevelColumn) = System.Convert.DBNull
End Sub
Im getting a ErroR on this Saying "Convert is Not a Member of String"
Could somebody kindly point me in the right direct?
Many Thanks
NSC Tag: Query Builder Tool Tag: 121365
Cannot add integer or long column to microsoft access table using ado.net
Hi, I have a problem.
I can't add integer or long columns to ms access table using ado.net.
Could you just take a look at it. I think sql syntax is correct.
The weird thing is that in another program it is working and it is the
same code.
If you comment out these two lines of code:
"IDParent INTEGER, "+
"Position INTEGER, "+
then everything is ok, the table is created.
The problem is I can't add LONG or INTEGER columns.
I don't know what's going on, maybe it's some kind of a bug?
Sample usage:
Test.exe c:\\test.mdb
using System;
using System.Data;
using System.Data.OleDb;
namespace Test
{
public class Test
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
Test t = new Test();
if (args.Length ==1)
{
t.Execute(args[0]);
}
}
private string connectionString = "";
private OleDbConnection connection;
private bool CreateTableWorkTopics()
{
string sqlText = "CREATE TABLE WorkTopics "+
"(IDWorkTopic COUNTER, "+
"IDParent INTEGER, "+
"Position INTEGER, "+
"Title CHAR(255), "+
"CONSTRAINT WorkTopics_PK PRIMARY KEY (IDWorkTopic))";
ExecuteNonQuery(sqlText);
return true;
}
private void ExecuteNonQuery(string sqlText)
{
OleDbCommand cmd = new OleDbCommand(sqlText, connection);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
public void Execute(string path)
{
try
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=\""+path+"\";"+
"User ID=Admin;"+
"Password=";
// create mdb file
if (!System.IO.File.Exists(path))
{
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
catalog.Create(connectionString);
catalog = null;
}
connection = new OleDbConnection(connectionString);
connection.Open();
CreateTableWorkTopics();
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadLine();
}
}
}
} Tag: Query Builder Tool Tag: 121362
DataSet write XML
Hi,
I have observed that DataSet.WriteXML() method does not store "Allow
Zero Length", "Indexed", and "Required" properties of the fields of a table
in the table schema.
dataset.WriteXml(stream, XmlWriteMode.WriteSchema)
How do I instruct WriteXML() method to include these information along with
field name, type and size?
<xs:element name="field1">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:maxLength value="64" />
</xs:restriction>
</xs:simpleType>
</xs:element>
Also, minOccurs="0" translates into which property in the MS Access
database?
Regards
Sanjib
NB: Please include "microsoft.public.dotnet.languages.vb,
microsoft.public.dotnet.xml" in your reply. Tag: Query Builder Tool Tag: 121357
Kevin Yu [MSFT]- How to include public properties in a typed dataset in serialization?
The public properties are not related to the typed dataset but defined in
the typed dataset class. Although the typed dataset class is marked
<serializable()>, these public properties are ignored when the class is
serialized. This does not seem to be right.
Kevin's reply indicated that dataset serialization is based on the xsd,
means that it ignors the public properties which are defined in the partial
class. This appears to contradict the basic serialization rule from .NET -
public property of a serializable class gets serialized.
It does sound like a BUG.
So what do i need to do in order to serialize these public properties? For
Kevin Yu, please provide a code sample as your pointed direction is just too
vague for me to follow.
Thanks,
Michael Tag: Query Builder Tool Tag: 121350
Binding textbox to dataview.table.column
Help me out!!!!!!!!!
i am creating a windows application.My datahandling class is returning
a dataview.i m assigning this returned dataview to a dataview on my
winform.then by using a databinding.add method of textbox i binnd one
of the column of dataview to this textbox.but when i refill this
dataview by another method in my class it does not show record after
navigating.
plz help me out
thanks in advanse Tag: Query Builder Tool Tag: 121347
Serialization Problem: custom properties of a derived DataSet class not serialized
I created a strongly typed dataset using the designer and added a couple of
public properties via the Partial Class. Everything is fine and the
strongly typed data set functions as expected including the public
properties until the serialization/deserialization process. When all the
custom public properties are total ignored and lost. Is this behaviour by
design or simply a bug?
Thanks,
Michael Tag: Query Builder Tool Tag: 121325
Can the application.config file handle provider registration?
Hi there,
I'm looking into provider-independent data access with ADO.NET 2.0. The
available providers should be registered in the machine.config file, but
what If I don't have access to that file? Can I register my provider in the
application's .config file or some other place?
Best Regards,
-Benton Tag: Query Builder Tool Tag: 121324
Invalid Operation Exception when adding Collection to Binding Source
Hello all,
Firstly, this is a .NET 2.0 issue. (C#)
I have a DataGridView and a dataset that has been generated by the
wizard from a strongly typed collection. In my form_load I am
attempting to add an instance of this collection to the BindingSource
eg.
gridBindingSource.Add(myStronglyTypedCollectionInstance);
I am getting an Invalid Operation Exception (Objects added to a
BindingSource's list must all be of the same type).
The collection in question is a strongly typed collection of abstract
objects. That is, I have an abstract class which is generalised by two
sub classes. I have built a collection to hold the abstracts.
Note this happens even though the collection is empty.
Is this a limitation of the BindingSource?
Any help greatly appreciated.
Cheers
Simon Tag: Query Builder Tool Tag: 121323
Adapter.Update does not change database
I am trying to enter a new row into a test database and have been
unsuccessful. The code compiles fine and runs with out runtime errors,
however after closing the form and checking the database it is the same.
Since this is my first time using a database any additional suggestions that
I should check on that are not obvious in my code snippet will be useful!
Sincere thanks,
Rock
private void button1_Click(object sender, EventArgs e)
{
// Create new entry
myDatabaseDataSet.UserNamesRow user =
myDatabaseDataSet.UserNames.NewUserNamesRow();
user.First = firstTextBox.Text;
user.Last = lastTextBox.Text;
myDatabaseDataSet.UserNames.Rows.Add(user);
// Save new entry
this.Validate();
this.userNamesBindingSource.EndEdit();
if(user.RowState != DataRowState.Added)
MessageBox.Show("No Changes Seen in DataSEt");
int result =
this.userNamesTableAdapter.Update(this.myDatabaseDataSet.UserNames);
//Display # of updated rows
MessageBox.Show(result.ToString());
} Tag: Query Builder Tool Tag: 121322
AddingNew BindingSource
Hi All,
Need help with the following:
I have a DataGridView bound to an SQL Server table. I would like to
use the ocrresponding Windows Forms BindingSource's AddingNew event to
default a column to a specified value. For example, when the user clicks on
an "Add New Customer" button or the "New" button on the BindingNavigator
control, I'd like to use the AddingNew event of the BindingSource to default
certain values like CustomerID, Quantity, etc... to a value that I assign
programatically (value may come from another object or table). The problem
is that I have read that I cannot use the AddingNew event of the
BindingSource when the source is bound to a table.
In the AddingNew event of the BindingSource, I am using typed Datasets
and would like to create a new "Customer" row, modify some properties (ID,
Quanity, AddingNewEventArgs. That's where the error occurs. Any help?
Regards,
Giovanni Tag: Query Builder Tool Tag: 121320
Disconnected DataSets and DataViews
For scalability we wish to use disconnected DataSets and hold them in
the application cache.
This means that multiple concurrent users will be reading the data (its
read only)
The DataSet is fairly complicated and contains many tables and many
relationships
What we would like to do is create a DataView that flattens the in
memory DataSet so that we can bind this single view to a control
We need to hold the separate tables in memory because there are some
tables that have parent child relationships where the parent has many
thousands of children (and grand children)
think Products and Orders and we want to hold Products and Orders in
Memory, the reason is that each Product row is large enough not to
warrant holding it multiple times
OK, the problem is that a DataView can only have one table and the row
filter can only be for that table,
what we want to do is an equivalent of a sql select (with a join) but
from disconnected a DataSet
e.g.
Select Product.ID, Product.Code, Product.Image, Order.FirstName,
Order.Price
from tbl_Product PRoduct
join tbl_Order Order
on Order.ProductID = Product.ID
Any help would be appreciated Tag: Query Builder Tool Tag: 121312
Dataview rowfilter
Hello Everyone!
I'm currently having trouble with a rowfilter where the name of the
column containt bracket.
Here the name of the column [Measures].[PointX]
when i sort the dataview it's ok no trouble
DataView dv = new DataView(dt);
dv.Sort = "[[Measures].[PointX]] ASC"; //it's ok working
But if I add the line it crash
dv.RowFilter = "[[Measures].[PointX]] = 0"; with the exception ..
An unhandled exception of type 'System.Data.SyntaxErrorException' occurred
in system.data.dll
Additional information: Cannot interpret token ']' at position 21.
Anyone know a workaround?
Thanks you
Best Regards,
Patrice Lamarche Tag: Query Builder Tool Tag: 121309
SqlCommandBuilder.DeriveParameters Permissions
hello,
i'm trying to populate the ParametersCollection of a SqlCommand throug the
static method DeriveParameters of the SqlCommandBuilder class
everything works fine when using the sa login of the database - but when i
try to use a login which only have permissions to "Execute" the stored
procedure the method only returns the ReturnValue of the stored procedure as
Parameter. i've also tried to grant the permission "View Definition" but
with no success
any ideas or advices?
regards, mike s. Tag: Query Builder Tool Tag: 121305
Access
Hi,
Was wondering if anybody could help.
I'm looking to add a lots of records to an access database, but it looks
like I'm going to have to insert them one at a time!!!
Looks like I'm going to have to do this for each new record..
Insert into Database(<fields>) VALUES(<values>)
Was wondering if there was anything similar to the MySQL Implementation
where I can add several new rows in one SQL command.
Many thanks
Wayne Tag: Query Builder Tool Tag: 121302
Check Constraints AND ADO.NET
Hi all,
I have a table where some columns have a constraint (datalength(Field1) > 0,
Field2 > 0)
When I use the Update method of the SqlDataAdapter to update the table, I
receive an error (a generic error)!
Why this error isn't generated in the RowUpdating event
Thank's in advance
Polo Tag: Query Builder Tool Tag: 121301
Come ADO experts!!!
I asked this question before:
I have a Strongly typed DataSet one of the datatables within it has data in
it. I have a stored procedure that expects a parameter that returns data
that also has got to go into the same datatable. Is it possible to bind one
of the columns that is already populated to a paramter of a Dataadapters
select command and call the .Fill to execute the stored procedure which will
fill the remaining empty columns?
--
Regards
Ian Tag: Query Builder Tool Tag: 121300
DbConnectionStringBuilder
I'm working on a general use query tool and in that project I'm currently
working on building connection strings for oledb providers after I enumerate
the installed oledb providers on a system (side question: this is a tiresome
registry read - if you know a better way please let me know) I'd like to
present an interface for the user to enter the parameters for the driver.
In the msdn docs there a bit that reads "For providers that support specific
key/value pairs, the connection string builder provides strongly typed
properties corresponding to the known pairs"
What I'm hopeing that means is that there is some way to get the known
parameters for a particular provider at runtime, but i'm not seeing it. In
some fashion visual studio is able to determine the parameters for a given
driver..... how does visual studio do that? For example i have a syabse
driver that supports all kinds of parameters that most other oledb providers
dont. How does visual studio able to detmine what the parameters for a
particular provider are?
(Sahil: yeah I know about and have thought about temp .udl idea and I may
have to go that route, but I would really rather not)
/gabe Tag: Query Builder Tool Tag: 121298
Connecting to an oracle 9.2 Database with Visual Studio 2003
Hi,
I have an Oracle database with about 30 tables all relations in order, data
is present in tables that require them.
I'm writing a windows service using vb .NET that polls a set of directories
for new input files (which are flat files in txt). So far so good. The data
contained in those files get parsed into an import entity that can be of the
type 'sample' or 'standard'. Yesterday i noticed that my design was done
rather poorly and decided to form a Business layer and a DAL layer. I created
my business objects (still not all business is already in it).
And now I'm trying to access my databank. I tried accessing it using OLE DB
for Oracle as well as the Oracle connection;
The problem atm is the following. When dragging either OLE DB Connection or
OracleConnection to my DAL component. When I try to create a DataAdapter I
get an errormessage displaying ORA-00162. I haven't got a clue where this
error is comming from.
Yesterday I was trying to connect to OLE DB and then an exception was thrown
(which I read on this forums had something to do with permissions and
read&execute rights on my ORA_HOME directory)
Many thanks in advance Tag: Query Builder Tool Tag: 121293
ADO.NET 2.0 Automatically insert records into child table
Hello !
I've info in 3 tables of SQL database:
1. Users (user_id, login, private_flag, ...)
2. Private (user_id, name, e_mail, ...)
3. Business (user_id, account_number, bank_name, ...)
If Users.private_flag equals to 1 then attributes of user are in "Private"
table,
if it's 0 - in Business one.
I created a Windows Form where the main table is Users.
And both Private and Business tables are attached to it with a DataRelation-s.
When "+" button is pressed in BindingNavigator for Users table I need to
insert new record into Private or Business automatically when user selects a
value for Users.private_flag.
I tried to use various events of BindingSource, DataTable and etc. and
insert new record to one of child tables but all was helpless.
New record always not inserted.
I also noticed that new record not appears in Users table until anotrher
record of Users is selected with BindingNavigator.
Does anybody know how to realize such a logic ?
Or how to allow user to enter Private/Business data after pressing the "+"
on Users ?
Or where can I read more regarding this ?
Thanks. Tag: Query Builder Tool Tag: 121292
Mail.SmtpMail.Send won't send to one of my addresses
I have an ASP.NET page that sends a Mail.MailMessage to several email
addresses (all mine). However, one of the addresses, the one ending in
@verizon.net, does not seem to be recieving the message even though the
other addresses are. I know the spelling is correct because I have tried
sending messages from my other accounts to this address and have recieved
them successfully. I also do not know of any junkmail filter that would
delete the emails sent using ASP.NET (I have also looked in the junkmail box
for this address, and it is empty). The code used to send the emails is as
follows:
mailmsg.To = "myaddress1@mydomain1.com;"
Mail.SmtpMail.Send(mailmsg)
mailmsg.To = "myverizonaddress@verizon.net;"
Mail.SmtpMail.Send(mailmsg)
mailmsg.To = "myaddress2@mydomain2.com;"
Mail.SmtpMail.Send(mailmsg)
When this code is run (the mailmsg object is created beforehand) it is
successfully sent to myaddress1@mydomain1.com and myaddress2@mydomain2.com
but is not sent to the verizon address. I do not recieve any errors of any
kind that I have noticed.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/ Tag: Query Builder Tool Tag: 121291
Insert a byte() or hexadecimal data to mySQL..
Hi everybody,
How do you insert to a column of a table with a blob or binary datatype if
there is one, from a byte() datatype? What is wrong? How can I fixed this? I
need help. Thanks.
Note: The data is in byte() or hexadecimal to be inserted into a column in
mySQL table.
Code:
string sqlText = "Insert table1(id, dataByte) values('" + id + "'," +
byteData + ")";
OdbcCommand cmd = new OdbcCommand(sqlText, odConn);
//opening connection here
int iRes = cmd.ExecuteNonQuery();
Result:
iRes = -1
I used Text datatype by converting bytes() to hexadecimal, it returns -1,
also. The hexadecimal is too long for statement to be executed. How can I
solved this? Is Anyone who can help me? Thanks.
denpsia
--
MCP Year 2005, Philippines Tag: Query Builder Tool Tag: 121286
ADO.NET 2.0 Validation
Hi all,
Real issue for me here: I'd like to know where it would be best to put
validation and which are the events best suited to deal with such
record-related operations. There are many options, and I seem to be a bit
lost. Many people like the Partial Dataset class as opposed to putting
client-side/control-based validation, while others, its the opposite.
Do TableAdapters fit into the picture anywhere in terms of validation?
How about BindingSource? Should I use that to respond to "Adding New row",
"Deleting", "Modifying", etc... or should I use the DataNavigator? All this
is kind of confusing. Thus, the best place to put validation code and the
best place to capture record navigation (with the possibility to add some
logic). I'd like to use the ErrorProvider, etc...
What is the best practice/modern OOP way of doing things?
Regards,
Giovanni Tag: Query Builder Tool Tag: 121284
Database Application Development Framework
I've written a framework for developing SqlServer-based database applications that folks may find
interesting/useful. It's currently available in beta at www.arcabama.com/omlsoftware under
DBFramework. There's documentation available for it (both as part of the package and as a separate
CHM download).
And...my thanks to Don Box who, a couple of years ago, straightened me out on some hairy .NET
database application problems. I couldn't have written these libraries without that advice.
- Mark
p.s. as the download site just went live, please report any problems using it to me at
mark@arcabama.com. Tag: Query Builder Tool Tag: 121282
Read integer value from Excel file failed
Hi all,
I have following code to read Excel content into a DataSet
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);
try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}
One of the column is phone number, and some cells of this column are marked
as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of the
cell, then values from these cells in DataSet are blank
Is there any trick how to write connection string or how to read Excel file?
Thanks!
--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy Tag: Query Builder Tool Tag: 121280
Oracle Updates with DataAdapter failing
Well, it appears that the designer generated bogus code. When I
drag/dropped the table onto my form the SQL statements seemed to be
generated reasonably well, but all of the Parameters.Add() calls included
the colon character in front of the parameter name. I noticed explicit
documentation that the colon was not to be used in this situation.
I fixed this manually, but my DataAdapter.Update() call still updates 0 rows
even though I have just modified a row in the DataTable. No error, just
doesn't update anything.
I'm going to write my own update statement and blow off these time saving
designer tools, but I would be curious if anyone knows why this fails??
PS: I see no way to determine exactly what the resulting SQL statement is
from the Data Adapter (hm, SQL Logging seems like a pretty nice feature to
add to VS.NET there, MS folks!!! My former company built this sanity-saver
into our client/server development tools way back in the early 90's). Tag: Query Builder Tool Tag: 121276
Dynamic Hyperlink?
In my app, I want to create a link from a field in a database, which
will always be a file path. I have bound the hyperlink to the db field.
I have set in code, the navigate.url to be the value I retrieve from
the dataset. Currently, I can see the path in the link and when I click
on it, IE will display the text 'start downloading from....' with the
path I expect to see. However, nothing is happening.
Here is the code I have with two different ways I have tried to set the
navigate.url property. Am I missing something? Is there a better way to
do this?
Thanks
hlinkImplement.DataBind()
' hlinkImplement.NavigateUrl =
RTrim(DsUpdate1.Tables(0).Rows(0)("doc_implement"))
hlinkImplement.NavigateUrl = "file://" &
RTrim(DsUpdate1.Tables(0).Rows(0)("doc_implement")) Tag: Query Builder Tool Tag: 121266
How to close Connections
There's an application
which runs on .NET Framework 1.1 and uses SqlClient class to connect to
database.
It is a server application, but sometime the application is closed without
letting int finish it's job.
I've seen that when this happens there could be open connections to the
database.-
Is there a way, in which I can check if there are unused connections and
destroy them the next time I open the application?. Tag: Query Builder Tool Tag: 121265
Slow Form load/navigation with databinding
All,
I have a table named "CONTACTS" with 104 records in it, a table named
"ZIP_CODES" with 42,471 records in it, and several other tables. I load
these records into a dataset, create the relationships, and then bind the
controls on the form to the dataset. The ZIP Codes are loaded into a
combobox. All the controls load quickly. However, It takes forever to load
the form, to move from record to record, or update a record.
If I remove the binding from the "ZIP_CODES" table, the problem is gone. The
form loads quickly, navigation is quick and deleting/updating is quick. This
defeats the purpose, though, as the record is no longer bound. Is there a
way that I can still have the ZIP codes databound to insure referential
integrity as well as speed up the form/dataset?
TIA,
Vince Dalton Tag: Query Builder Tool Tag: 121256
Connect using SQL Authentication in a non trusted environment
Hello all,
I need to connect to a SQL 2000 from a .net app using SQL Authentication.
My connection string looks like this:
Dim conn As New SQLConnection
conn.ConnectionString = "user id = validuser; password = validpassword;
Trusted_Connection=False; Intial Catalog=pubs; Data Source =
10.24.4.126,1433;Network Library=DBMSSOCN"
This connection string works when I use a internal IP to connect to a
internal SQL server but not when I need to connect to the outside world.
SQL Server is set for mixed mode.
Any thoughts. Tag: Query Builder Tool Tag: 121204
How do I find this piece of xml in a dataset?
Hey all, this may be a noob question but...
I am loading an xml file into a dataset but there is a piece of data in
it that I can not find in the dataset. In the xml there is an element
that looks somthing like this.
<somegroupofpeople>
<persontype>Male
<person>
<fname>John</fname>
<lname>Doe</lname>
</person>
<person>
<fname>Joe</fname>
<lname>Dirt</lname>
</person>
</persontype>
<persontype>Female
<person>
<fname>Jane</fname>
<lname>Doe</lname>
</person>
<person>
<fname>Joe</fname>
<lname>Dirt in drag</lname>
</person>
</persontype>
</somegroupofpeople>
<somegroupofpeople>...
This is just an example. The real data has elements that differ. What I
can't seem to find or navigate to is what would be values "Male" or
"Female" in persontype.
My first impression is that this kind of layout is kind of goofy in that
the sex should be an element or attribute of <person>. However I am no
xml guru and any tool that I run it through tells me that it is well
formed so what do I know.
If I needed to do something for persontype in each group of people
dependent on its value, how do I find this piece of the xml in a
dataset?
I hope this makes sense.
Thanks all. Tag: Query Builder Tool Tag: 121198
Request Advice: Big Tables, Expr. Complex Error
It was suggested to me the reason why I'm getting a "Expression is to
Complex" error message in my local ADO.Net application is because the
generated update query has too many AND's in the WHERE clause and the the MS
Access Jet provider is limited for this many fields (columns) ( max is 100
fields for DA).
The data source/set is a MS-Access Main table with 110 fields (columns).
The table grew from a little table to a big table overnight because of spec
changes. I know how to normilize the table in Access but not in ADO.Net
environment plus, that seems like a lot of work since the application is
almost done.
What are my options to remove the Expression is to Complex error message ?
Since I'm using MS-Access on the server, is it time to step-up to MS-SQL
Server? Does SQL Serverl use the Jet provider? Do you think the complex
error will go away with SQL Server? Comments?
Steve Tag: Query Builder Tool Tag: 121197
SQL statement under the covers
As we all know, ADO.Net allows you to use a command object to execute a
query. We all also know that the command object also exposes a parameter
collection that you can use if you are building a parameterized SQL
statement.
I am assuming that internally, ADO.Net takes the parameterized query and the
parameter collection to build the query statement that is sent to the
backend database engine (SQL server, Jet engine etc). So for example,
assuming that I have a parameterized query and that I add a parameter to the
command parameter collection as show below:
SELECT * FROM Customers WHERE CustomerName = @CustName
myCommand.Parameters.Add("@CustName", "Pupu Caca");
I am assuming that ADO.Net will take the information entered above and
produce an SQL statement that is sent to the database engine such as the one
below:
SELECT * FROM Customers WHERE CustomerName = 'Pupu Caca'
If this is true, is there a way to get this (already parsed) SQL statement?
Thanks. Tag: Query Builder Tool Tag: 121196
SqlDataReader and Text columns
Hi all,
I'm using the SqlDataReader to read result rows returned from a stored
procedure. One of the columns is a text column containing values longer
than 8000 characters. I cannot find a way to get SqlDataReader to read
the entire value - it always cuts it off at 8000 characters.
I've tried GetString, GetSQLString and GetChars but they all cut the
value off. Does anyone know how to read text values using
SqlDataReader?
Many thanks,
Paul Tag: Query Builder Tool Tag: 121193
Calling Oracle Stored Procs
A ref cursor data type can obviously be returned as an output parameter of a
stored procedure, but can an ASP.NET application call an oracle proc that
uses a ref cursor as an input parameter? If so, how? What object do you
pass as that parameter?
We are using System.Data.OracleClient, but if necessary, we might be able to
switch. Tag: Query Builder Tool Tag: 121192
Error "Cast from type 'DataRowView' to type 'String' is not valid"
I've seen this problem posted several times, but have not yet seen a
solution. I make a connection to a SQL database and use a DataAdapter object
to fill a DataSet object with several tables. A DataRelation object links the
tables by an ID column. I bind the parent table to a combo box and a child
table to a DataGrid control using a DataView object. I want the DataGrid
control to reflect the selection in the combo box. For the combo box,
DisplayMember is a text string while ValueMember is the ID field (integer).
In brief:
Private Sub Form1_Load(....)
' Connect to database, populate Tables in DataSet,
' setup DataViews, and define DataRelations.
Call Populate()
ComboBox1.DataSource = MyDataSet.Tables(0)
ComboBox1.DisplayMember = MyDataSet.Tables(0).Columns(1).ColumnName
ComboBox1.ValueMember = MyDataSet.Tables(0).Columns(0).ColumnName
Me.BindingContext(MyDataSet.Tables(0)).Position = 0
Dim strFilter As String
strFilter = "ID = " & ComboBox1.SelectedValue
MyDataView.RowFilter = strFilter
DataGrid1.DataSource = MyDataView
End Sub
Private Sub ComboBox1_SelectedValueChanged(....)
Dim strFilter As String
If (ComboBox1.SelectedIndex <> -1) Then
strFilter = "ID = " & ComboBox1.SelectedValue
MyDataView.RowFilter = strFilter
End If
End Sub
Strangely, the code works if I comment out the
ComboBox1_SelectedValueChanged sub. I can hard code in Form1_Load to select
the first (index=0), or any other item in the combo box, and the DataGrid
reflects the correct item. But of course I cannot select another item in the
combo box and have the DataGrid reflect this. The error is raised in
CombBox1_SelectedValueChanged on the statement:
strFilter = "ID = " & ComboBox1.SelectedValue
and the error is "Cast from type 'DataRowView' to type 'String' is not
valid. I've tried to use ToString and CStr to no avail. I think the key is
that I assign a DataSource to the combo box, plus the DisplayMember and
ValueMember properites, then attempt to use the SelectedValue property when
the user makes a selection. Any ideas?
Note. My code is based on "Using a Data Set with Tables in a Parent-Child
Relationship" from Chapter 10 (pg. 402-408) of "Programming Microsoft SQL
Server 2000 with Microsoft Visual Basic .NET" by Rick Dobson.
Richard Tag: Query Builder Tool Tag: 121190
SqlDataSource UpdateParameters supplies wrong number of params to storedproc
If I try and update an SqlDataSource inside a DetailsView control,
using a stored procedure, I get this error -
Procedure or function DataViews_UpdateDetails has too many arguments
specified
However, if I just simply remove the stored procedure call, and use
"inline" sql instead, and leave the UpdateParameters totally unchanged,
and the sql totally unchanged as well (just copy it from the stored
proc to the html section), it works fine, and there are no errors.
Any ideas very welcome, thanks, regards, dnw. Tag: Query Builder Tool Tag: 121189
asp.net 2 and stored procedures
Hello,
Not sure if there is a NEW way to do all this with asp.net 2,
here is an example of the way i used to do it and then I have questions to
follow:
store procedure example
CREATE PROCEDURE TEST_TEST
@TNUMBER NVARCHAR (10),
@ODRCOMMENT NVARCHAR(100) OUTPUT,
@RTASK NVARCHAR(1) OUTPUT
AS
SELECT @COMMENT = 'CANNOT COMPLETE ORDER'
SELECT @RTASK = 0
GO
default.asp page.....
SET Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = Application("Connection1_ConnectionString") ' pulls
from global.asa
Conn.Open
Set cm = Server.CreateObject("ADODB.Command")
cm.ActiveConnection = Conn
cm.CommandText = "TEST_TEST"
cm.CommandType = adCmdStoredProc
Set p = cm.Parameters
p.append cm.CreateParameter("@TNUMBER",adInteger,adParamInput,50)
p.append cm.CreateParameter("@COMMENT",adVarWChar,adParamOutput,100)
p.append cm.CreateParameter("@RTASK",adVarWChar,adParamOutput,1)
cm.Execute
If cm.Parameters("@RTASK").Value = 1 THEN
'DO SOMETHING ELSE
END IF
CM.CLOSE
CONN.CLOSE
SET CM = NOTHING
SET CONN = NOTHING
my question is:
1. How would this be done with Visual Studio 2005?
2. How would you connect to the web.config connectionStrings that has been
setup.
I seen this article of:
http://msdn.microsoft.com/data/dataaccess/whidbey/default.aspx?pull=/library/en-us/dnvs05/html/vsgenerics.asp
but I don't think that relates as well as many other article that I have
looked at.
Struggling to get into the future or programming....
Tdar Tag: Query Builder Tool Tag: 121188
Column names and typed dataset
I came across one thing/problem regarding typed datasets in the case where
the column name has two words or has a dash in between the words in the
column name like:
"E-mail" or "Expiry date"
In the select statment I use [E-mail] and [Expiry date] which works fine,
but the problem is that the
OleDbCommandBuilder(adapter)
does not build correct update statements and I get an error ("Syntax error
in UPDATE statement") when trying to update a row with "E-mail" or "Expiry
date"
Other colums with a single word column name works just fine.
Any hints would be very much appreciated.
Thanks..
Please see code sample below (not complete with all details, just to get an
idea):
string SqlString = "SELECT CustomerID, Firstname, [E-mail], [Expiry date]
FROM Customers";
// Create new dataset
CustomerDataset customerDataset = new CustomerDataset();
// Get customers
db.Execute(customerDataset, "Customers");
// Select row 1 (could be any other row)
CustomerDataset.CustomerRow row = (CustomerDataset.CustomerRow)
customerDataset.Customer.Rows[1];
// Change some fields
row.Firstname = "ZZ Top";
row._E_mail = zztop@zz.com;
// Then update data base with the changes
connection.ConnectionString = ConnectionString;
command.CommandText = SqlString;
// Auto generate update statements
OleDbCommandBuilder up = new OleDbCommandBuilder(adapter);
// UPDATE FAILS WITH "Syntax error in UPDATE statement"
adapter.Update(ds, tableName);
NOTE: everything works just fine if the column names are one word like
"Email", "Expirydate" etc. Tag: Query Builder Tool Tag: 121184
Adding a row with null columns to a typed dataset in ADO.NET 2.0
Hi,
In ADO.NET 2.0 if you have a table with foreign keys in it, when you
add a row using the strongly typed add row method you pass references
to the foreign rows rather than the foreign key value itself.
If any of these foreign key row references are null, I get an
exception.
The only way I have found to work around this is to use the following
for each column with a foreign key that can be null:
TypedDataSet.SomeTableRow myNewRow = ds.SomeTable.NewSomeTableRow ();
TypedDataSet.ForeignTableRow foreignRow = ds.ForeignTable.FindByID
(id);
//
// repeat for each foreign key column that allows nulls...
//
if (null != foreignRow)
{
myNewRow.ForeignKeyID = foreignRow.ID;
}
else
{
myNewRow["ForeignKeyID"] = DBNull.Value;
}
ds.SomeTable.AddSomeTableRow (myNewRow);
I must be missing something here - surely the generated code is smart
enough to detect null values for foreign key references and insert a
DBNull.Value in the appropriate foreign key columns (where null values
are allowed of course).
What am I missing here?
Thanks,
Todd Tag: Query Builder Tool Tag: 121183
Avoiding escalation to MSDTC with multiple TableAdapter inserts
I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit surprised
to see control of transactions being somewhat taken away from me and placed
into the System.Transactions namespace. I had a read and saw all the stuff
about distributed transactions, which seems quite cool for managing access
to multiple resources of different types. However, for plain old SQL
Transactions, it seems like if you're not careful, you might end up paying a
performance hit.
So I wrote a small app that inserts rows in a few tables in one function.
This all needs to be inside the one transaction. So I use the
TransactionScope, and everything seemed sweet while I was only posting the
first TableAdapter. Once I started posted the other two, I received
exceptions complaining that the MSDTC was not running. After reading the
generated code for the TableAdapters, it appears that what I have to do is
ensure that the first adapter's connection is open before it posts
(otherwise it will close it again), and that the other adapters all have
their connections assigned to be the first adapter's connection. This seems
to leave SQLServer 2005 happy enough to do it all on one transaction and not
involve the MSDTC.
Is this the "proper" way to get around your transaction getting promoted up
to the MSDTC? Also, can I/should I close the SqlConnection I manually opened
before/after completing the TransactionScope, or should I leave it?
Niall Tag: Query Builder Tool Tag: 121168
Data Grid and table binding
Hello newsgroup,
i am not sure if i am asking at the right place. If not, please forgive me.
I want to bind a table to a datagrid and make it editable.
I know how to data bind and so, but I got trouble with the dataadapter.
at runtime, the user selects a table from a list. with "Select * FROM table"
I can show the content
of the table in the grid. but how do I make it updatable? I would have to
define a update command from the table. is there a way to generate such a
command or is there an alternative?
thanks for the help!
Andy Tag: Query Builder Tool Tag: 121165
Query Builder in my app
Can the Query Builder that is used to create datasets in VS.NET be called
from an application? I hate building these things but always have a need for
one. Tag: Query Builder Tool Tag: 121164
Oracle Client Issue
I have created a web service that accesses an Oracle database. The server
that it runs on has both Oracle 8.15 and Oracle 9.2 installed on it. When I
run the web server I get the following error:
Server was unable to process request. --> System.Data.OracleClient requires
Oracle client software version 8.1.7 or greater.
I can run the software in a windows application on the server so I am
guessing that it has something to do with the environment variables for the
ASPNET login. Any idea how to correct it? Tag: Query Builder Tool Tag: 121163
DataAdapter doesn't insert record
This has got to be dumb question, but I cannot figure it out.
I am using VS2005, .NET 2, SQL Server Express 2005. I have no trouble
reading records, but the updates to not get persisted to the database.
Why won't this code insert a new record in the EventLog table? Any help
would be great!
CondoTrackerDatabaseDataSet.EventLogDataTable table =
this.EventLogTableAdapter.GetData();
//Rows = 1
MessageBox.Show(table.Rows.Count.ToString());
CondoTrackerDatabaseDataSet.EventLogRow logRow = table.NewEventLogRow();
logRow.EventDate = DateTime.Now;
logRow.RoomNumber = roomNumber;
logRow.EventTypeLabel = eventType.ToString();
logRow.LogText = logText;
//Add Row
table.AddEventLogRow(logRow);
//Rows = 2
MessageBox.Show(table.Rows.Count.ToString());
//Supposedly commits to database
this.EventLogTableAdapter.Update(table);
//Refill table
this.EventLogTableAdapter.ClearBeforeFill = true;
this.EventLogTableAdapter.Fill(table);
//Rows = 1
MessageBox.Show(table.Rows.Count.ToString()); Tag: Query Builder Tool Tag: 121159
How to write back to two table with GridView
I use GridView to edit the columns form two table.
The select command is select data from to table like:
Select A.StudentName, A.Weight, B.Height wher A.PID=B.PID
After I edit A.Weight and B.Height, How can I write the change back to A
table and B table? Tag: Query Builder Tool Tag: 121154
Is there out a Query Builder Tool that is possible to integrate into my
application ? Mainly for MS SQL Server.
Can I suggest to you our visual query building component? Using it your
users will be able to build queries with union and sub-queries visually. http://www.activequerybuilder.com/