How do you use OleDbLiteral
I am trying to understand how you use OleDbLiteral in code.
I can't find a single code example in the .net reference or through google.
I've looked at three books. One didn't mention it, one has it in the index
but not the text, and one says in the text that it is outside the scope of
the book!
Any Microsoft people (or anyone else) out there who can explain the usage of
this crucial enumeration? How do you get from the enumeration symbols to the
values that they represent?
Tom Tag: Taste that correction update Tag: 68227
David Sceppa...if you are listening...Update question
First, the book is awesome. Started reading it Monday, finished it last
night. I've done some writing for (formerly) Wrox, so I knows it is tough
to do.
Now, the gory stuff.
We have a strange case I am wondering how to handle with cascading updates.
We have a parent table, call it names (members), and a child table, call it
addr (addresses). Names can have 0 to many types of addresses (Home,
Office, Special, Temporary). The names table has a field for each address:
names.haddr for home address, names.taddr for temp address, names.faddr for
office address, etc.
These fields store a control number that links to the address table. This
control number is the PK for the address table.
Our select returns the names record and any address records in separate
resultsets. @indexname and @id are input parameter to the sproc (sent from a
search screen on the client):
--names
SELECT <these columns>
FROM dbo.names n
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id;
--temp address
SELECT TOP 10
atemp.control, atemp.addr1, atemp.addr2 ,atemp.city, atemp.zip,
atemp.phone, atemp.fax , atemp.pobzip, atemp.fc, atemp.st
FROM dbo.names n LEFT JOIN dbo.addr atemp on n.taddr = atemp.control
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id
-- home address
SELECT TOP 10
ah.addr1, ah.addr2 , ah.city , ah.zip, ah.phone, ah.fax , ah.pobzip,
ah.control, ah.fc, ah.st
FROM dbo.names n LEFT JOIN dbo.addr ah on n.haddr = ah.control
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id;
etc for the remaining possible address
The client creates a DataRelation between the names address fields and each
address DataTable control field. There are 5 DataRelations in all, meaning
there are 5 address DataTables and one names Datatable.
So, on the client, names is the parent and each addr DataTable is a child (5
DataRelations). This works perfect, DataBinding is happy.
Here is the issue. Control is the PK for the addr table in sql server.
Control links to the individual names.haddr. names.taddr, etc fields in the
names table.
This field gets populated from a 'next available control number table in sql
server on an address insert. Kinda like autoincrement accept we supply the
value ourselves.
After doing the insert into the addr table, we need to update the names
record (haddr, taddr, etc) fields. So, on the update the addr is
kinda\sorta the parent and names is kinda\sort the child, if we want
cascading to work. Problem is, the constraint is set up the other way on
the client (in the schema) so navigation works on the client.
Anyway to make this work? Tag: Taste that correction update Tag: 68219
Send Connection Instructions to SQL Server
I assume that I can send the statement 'SET ANSI WARNINGS OFF' to SQL with
the sqlcommand object's method ExecuteNonQuery(), but can it (and other
behavioral statements) be sent alternatively?
Reason I ask is I'm executing stored procedures and simply didn't want to
pass behavioral statements this way. Is there any property like
_cmdSqlServer.Behavior = ...
thanks,
Eric Tag: Taste that correction update Tag: 68214
Databinding Question
Hi,
In my app I am trying to bind a dataset to a combo box and I am having some
trouble. In the combo box it shows this "System.Data.DataRowView" for each
record. It is showing the correct number of records. I am calling a stored
procedure in Access with a parameter. It seems to run fine. Any ideas? Tag: Taste that correction update Tag: 68211
newbie: forms databinding question
I have a c# windows application binding some controls to a source table: I
can browse the table records by retrieving the CurrencyManager from the
form's BindingContext for my dataset and table and using the Position
property; but I'd like the form to show on demand A SPECIFIC RECORD (if
existing): i.e. I'd like to find a specific record as with the
DataTable.Select method, and set it as the CURRENT record for my form if
found. How can I do this? I don't think I can use Select as it rets a set of
rows but not their index in the table... and I'd need an index to set
CurrencyManager Position.
Thx to all guys! Tag: Taste that correction update Tag: 68209
ItemChanged Event in C#
Hi,
I am having problems getting the ItemChanged event to work (it is just not
firing). I followed the steps from KB 312045 INFO: Firing the Windows Forms
CurrencyManager Events in Visual C# .NET, but again, the ItemChanged event
is not firing in that example either.
I can get all the other events related to the currency manager to fire...
I would really like to understand what is going on.
Using Framework 1.1 with a local SQL Server Northwind database (for the KB
article).
Thanks,
James Tag: Taste that correction update Tag: 68208
Using XML and ADO.Net for Database creation with the Compact Framework
Hey all
Appologies for cross posting, but this post covers a few areas in one.
I'm fairly new to XML and my ideas are over reaching my grasp (as well
as possibly not fully understanding certain concepts). My basic
questions are:
1.
I want to create a database in SQLCE. I understand that a schema is used
for validation purposes (I think) but if a schema was created describing
a database table, could it be loaded with ADO.net and used to create
the table in SQLCE (all this, of course, occurring on a PDA)? At this
point, there would be no requirement for data.
Or would it be better to use a CREATE TABLE script and simply run it as
a query?
2.
The table would be of a generic design so that, for example, a total of
20 fields would exist but depending on user requirement not all of them
would be used. Would this kind of design then limit the effectiveness
of using XML files for data input/output?
Any input would be apprieciated.
Paul Tag: Taste that correction update Tag: 68202
How to use DISTINCT in dataTable.select method
Hi All,
can body give solution to this
________________________________________________
Dim oDataRow As DataRow
oTable = oDataset.Tables(0)
Dim objResults() As DataRow
objResults = oTable.Select("DISTINCT objType")
For Each oDataRow In objResults
debug.writeline(((oDataRow("objType")))
Next
________________________________________________
Thanks
BaluSmiles
Thanks Tag: Taste that correction update Tag: 68201
maintaining DB Concurrency in Ado.net
Hi,
How I can prevent concurrent updation of a records in .net.
I am using Dataset which is disconnected from the data source. In this
situation if
two users opens the same record, modifies the data and updates one after
another,
my data won't be in the correct state. How I can prevent this. Is there any
locking technics.
Need Advice
Babz Tag: Taste that correction update Tag: 68197
DataTable.RowChanged - Newest row doesn't exist
Hi
If I add a eventhandle to DataTable.RowChanged, I get a event when a new row
is added. But the row isn't in DataTable.Rows??? Tag: Taste that correction update Tag: 68193
Dataset Properties Descrepencies From Table Design
VS 2003 VB net sql msde...
I used Server Explorer, Design Table, to define two tables primary keys
identically (i.e. autoincrement, same seed & increment, both clustered,
etc.)
In Component designer, I create a DataAdapter for each table above and then
generate the dataset adding the tables above to the dataset. However, when
I look at dataset properties (right click on DataSet), one of the two
primary keys is how I defined it in Design Table above, but the other one is
not.
This problem exists with many other tables, and I can see no pattern or
reason.
Is Dataset Properties reporting the wrong information? What is the problem
here? I would think this info would be pulled directly from the Design
Table information.
Please advise
Bob Day Tag: Taste that correction update Tag: 68180
DataSource Indexes Vs. DataAdapter Select Statements
Using vs 2003, vb.net, sql msde
Using Server Explorer, Design Table design a table with only a Primary
Index.
Example with 4 columns (but no index created for them, except primary key).
Table PINS
Primary Key, autoincrement
IX_PINS, string
IX_Role_Number, String
IX_Outbound_Calling_Status, string
Using Component Designer, I will Create a DataAdatapter with a 'covering'
select statement. I can selectively drop % into the statement below to
'ignore' any one of the three @variables.
Example of where section:
WHERE (PINS.fld_IX_PIN LIKE @PIN) AND (PINS.fld_IX_Role_Number LIKE
@ROLE_NUMBER) AND (PINS.fid_IX_Outbound_Calling_Status_Number LIKE
@OUTBOUND_CALLING_STATUS_NUMBER)
Here is my question. The above works fine with no indexes defined in Design
Table (i.e. the underlying datasource), but I suspect I am taking a
performance hit. When I design the table, how should the indexes be
designed to maximize the performance of the DataAdapter?
Scenario one: Design 3 different indexes, each one being on one indexed
column. This is easy to do, but I am not sure it helps the DataAdapter if
it uses multiple columns. But it seems the only possible option, because it
would allow me to define PIN as Unique Index, while the other two as not
unique.
Scenario two: Design 1 index with all index columns, starting with the
narrower columns first. This would amount to a 'covering' index (i.e
something like Outbound_Calling_StatusNumber+Role_Number+PIN). Logically,
this would be best for the DataAdapter (both covering), but doesn't seem
possible since I could not define PIN alone as a Unique index with the other
two not being unique.
Scenario three: Design as many indexes as possible, one each to match a
possible combinations of the 'covering' data adapter. Each index would have
multiple columns. For example, one index might be PIN, another PIN + Role,
another PIN+Role+Outbound Calling Status Number. This doesn't seem
practical since it would require predicting all possible combinations of the
use of the DataAdapater in advance.
Please advise.
Bob Day Tag: Taste that correction update Tag: 68178
Sceppa Core Reference errata on pp 469/473 and please help with concurrency error
I'm having trouble with concurrency errors (0 records affected)
but the Core Reference section relating to this subject seems to
have an error in the code sample. Maybe I'm wrong about this;
could someone please correct me if so!
On p. 473, the timestamp column is being assigned a value
e.Row("TSCol") = ....
and the penultimate paragraph on the page reads:
"After retrieving the new timestamp value and assigning it to the
appropriate column in the DataRow object..."
** But when I try to assign this column, I get an error that my
(SQLServer2000) timestamp column is READONLY. **
My update command uses the Primary Key and the timestamp columns
to check for concurrent changes to the row. My update command goes
like this:
Update T
Set ...
where PK = @PK and TS = @TS
And in the OnRowUpdated eventhandler, I assign the UpdateCommand
timestamp parameter as follows:
UpdateCommand.Parameters("@TS").Value = cmdGetTS.ExecuteScalar
However, when I make a second change to a record that I have just
updated (the scenario described at top of p.469 in Core
Reference), I get a concurrency error (0 records affected) even
though I am indeed using the most recent timestamp and have
correctly assigned the TS and PK parameters of my update query
prior to invoking the DA.Update() method.
Page 469 reads: "If you don't have the new value for the timestamp
column in your DataRow object, the subsequent update attempt will
fail."
I don't understand why the *row* must contain the new timestamp
value if one is using the primary key and the timestamp to test
for concurrency problems, and the update command takes the form
"Update Table ...where PK=@PK and TS=@TS". Wouldn't you want to
assign the UpdateCommand.Parameters("@TS") instead of the
e.row("TS")?
Clarification appreciated!
Timo Tag: Taste that correction update Tag: 68177
newbie needs a sample
I am a programmer that is fluent in Visual Basic 6.0, and I am trying to
learn VB .NET. I would like to create an application that imports comma
separated data in a textfile into a database in Access or SQL Server. I am
familiar with ADO, but not with ADO .NET. Would someone be kind enough to
send me a sample project to get me started?
Daniel Tag: Taste that correction update Tag: 68169
Transfering a dataset
I am reading a dataset from a CSV file and creating XML. From there I put it
into a dataset.
What I want to do now is to take that datatable and store it into a database
(through an ODBC connection).
I thought there was an easy way to assign the dataset to the new connection
and/or dataadapter and tell it to store/save/etc. the datatable to the
database.
Can someone point me in the right direction? Or am I just off my rocker
again?
Thank you for any insight you can provide,
Ken Tag: Taste that correction update Tag: 68168
Quick Question
Hi,
I am wondering what is faster. I am useing OLEDB to connect to an Access
database and was wondering which is faster. Calling a query saved in Access
or using SQL in the program? And is it a big difference in performance.
Thanks in Advance Tag: Taste that correction update Tag: 68154
Creating logins
Can I create a Windows Group login from .Net code, and if so, how?!
Cheers in advance
Sparky Tag: Taste that correction update Tag: 68144
Quey builder
Hi,
I like to create a query builder to allow the user to create his statistics
and see them using Cristal report
Any idea about how to do this, is there any controls that can help me.
Thanks. Tag: Taste that correction update Tag: 68141
Datagrid getting info from two tables in one database
Hi,
I'm quite new to developing ASP.NET applications with databases (ADO).
I think I've got a very easy question, but I haven't been able to sort
it out yet.
In SQL Server I've got two tables in my database: "issues" and
"users". The idea of the application (I use C#) is that users can add
issues. So the "issues" table has a secondairy key "user_id" connected
to the "id" column of the "user" table.
In a datagrid I want to show the "issue" table, but (!) instead of
showing the user_id number on this grid (from the "issue" table), I'd
like to show the matching name taken from the "users" table". Second,
when this datagrid is edited the user column should show a dropdown
list with all the user names, instead of the standard textfiels with
the "user_id". I've found something for this using the ASPX code;
<<<CODE>>>
<EditItemTemplate>
<asp:DropDownList id="Dropdownlist1" runat="server&q