Parameters using the DataSet object ?
I know how to use parameters with SQL stored in an OledbCommand object but
is it possible to use parameters when using the DataSet object since it
doesnt use the OledbCommand object? If so can give a small example or link.
for example if i want to filter a table stored in a dataset with 2
parameters entered through a form on the page...
thank you
omar Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59638
Can't Append dBASE File
I'm hoping somebody can help me with this and I hope this is the
correct newsgroup. I am writing a VB.NET program that accesses a
dBASE IV file. I can't get a subroutine to actually add a row to
the end of the dBASE file - I keep getting an error (using
Try...Catch...Finally) that says "Syntax error in INSERT INTO
statement". This I can't understand because I'm using
OleDbCommandBuilder to create the INSERT command. The following
is the exact code from my program:
Public Sub Append_Database()
'---- First, set up the connection and the dataset ----
Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=C:\LCTA;" & "Extended Properties=dBase IV"
Dim cn As New OleDbConnection(cs)
cn.Open()
'---- Second, read in the existing data from the server ----
Dim myQuery As String = "SELECT * FROM MEMBERS"
Dim da1 As New OleDbDataAdapter(myQuery, cn)
Dim mDataSet As New DataSet()
da1.Fill(mDataSet, "MEMBERS") 'Fill dataset with dBase table
Members.dbf
Dim cb As New OleDbCommandBuilder(da1) 'This builds the
INSERT, UPDATE, and DELETE commands
'---- Now define what will be a new row ----
Dim myRow As Data.DataRow
myRow = mDataSet.Tables("MEMBERS").NewRow
myRow("LASTNAME") = UCase(txtLastName.Text)
myRow("FIRST") = UCase(txtFirstName.Text)
myRow("MIDDLE") = UCase(txtMiddleInit.Text)
myRow("STREET1") = UCase(txtAddress1.Text)
myRow("STREET2") = UCase(txtAddress2.Text)
myRow("TOWN") = UCase(txtCity.Text)
myRow("STATE") = UCase(txtState.Text)
myRow("ZIP") = txtZip.Text
myRow("HOMETEL") = UCase(txtHomeTel.Text)
myRow("WORKTEL") = UCase(txtWorkTel.Text)
myRow("EXTENSION") = UCase(txtExt.Text)
myRow("EMAIL") = txtEmail.Text
myRow("WIFE_FIRST") = UCase(txtSpouseFirst.Text)
myRow("WIFE_MI") = UCase(txtSpouseMI.Text)
myRow("WIFE_LAST") = UCase(txtSpouseLast.Text)
myRow("JOINED") = CDate(txtJoined.Text)
myRow("LASTPAID") = CDate(txtLastContrib.Text)
myRow("AMOUNT") = Val(txtAmount.Text)
'---- Now add the new row to the dataset
mDataSet.Tables("MEMBERS").Rows.Add(myRow)
'---- Finally, update the database on the server from the
revised dataset
Try
da1.Update(mDataSet, "MEMBERS")
Catch e As OleDb.OleDbException
MsgBox(e.Message)
Finally
cn.Close()
End Try
End Sub
The exception occurs at the statement: da1.Update(mDataSet,
"MEMBERS")
Any ideas what's wrong?
Thanks,
Roger Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59621
Cannot create assemblies ...
Hi
Thank's for your answer
Yes, I have the Environment variable:
Variable = vbc
Value = C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\vbc.exe
I tried this for user variable, system variable and both but result was
always the same : 'vbc' is not recognized as .....
IIS is set to use the root directory in D:, but everything is working fine
except these assemblies.
What can be wrong ? How should I fix this ?
Raja Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59599
Data Access Application Block ?
Hi everybody
Is it possible to use data Access Application Block without having VS.NET ?
I mean, I only use notepad and Web Matrix. I do not have Visual Studio.
Yhanks in advance
Raja Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59598
transfering database from one pc to others using msde
hi,
how do I transfer database create using vs.net on one pc to other pc using
msde. I don't see any tool provide with msde to export and import database. Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59580
Why compact an Access Database?
Why should a person compact their access database? I have noticed that
doing so creates a new database that is compacted instead of compacting the
current database, why can't you just compact the database you are using. I
am referring to the ADO. Replication Object libruary and the jro.jetengine
within Visual Studio.net.
Thanks
Chuck Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59571
Connection Fails?
Hi all,
I am trying to track down a connection problem.
VB.NET
SQL2000
ADO.NET
When running the application I get an error say that "the connection string
is empty". I am declaring the variable that holds the connection string in
a Module : Friend ConnStr$ = ""
A dialog box pops up, by design, that allows one to enter login/pwd info and
connect to this SQL Server. This works fine. This box pops up after the
initial error message. Everything after the initial error message runs
great!
I tried debugging and stepping into and over the code but this error pops up
before the code even starts. None of this type of programming is new to me
yet I can't for the life of me understand, or find, where this error is
coming from. If I initialize the ConnStr$ = "<connection string stuff>",
then I do not see an error.
Any ideas where to look would be appreciated.
Thanks,
John. Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59569
DataSet type conflict
Hi All,
I'm loading a DataSet through an exposed method of a Web Service (just
ignore the 'ominous' feeling, it's not that complex). I created a 'typed'
DataSet to put the data into, but I get the following compile error:
Cannot implicitly convert type 'System.Data.DataSet' to
'MyApp.MyTypedDataSet'
Here's the code:
private MyApp.MyTypedDataSet MyTypedDataSet;
MyTypedDataSet = Globals.Server.GetMyDataSet(-1);
Obviously GetMyDataSet( ) method returns a System.Data.DataSet type, and I
have a new type that is derived from it ('typed' DataSets derive from
System.Data.DataSet).
I tried 'casting':
MyTypedDataSet = (System.Data.DataSet) Globals.Server.GetMyDataSet(-1);
which compiled OK, but failed the same way during execution.
My question -
1) Can I 'expose' the 'System.Data.DataSet' buried in MyTypedDataSet so my
'typed' DataSet can accept the 'untyped' (System.Data.DataSet) data from the
Web Service?
-IF NOT-
2) What if I create a compatible (temporary) 'untyped' DataSet (a
System.Data.DataSet), and use that to catch the data from the Web Service
(just to get the data). How would I move the data (single table) from the
'untyped' DataSet into my 'typed' DataSet?
<PLEASE NOTE: I want the 'typed' DataSet so I can easily move the data into
my grid using a 'MyGrid.DataSource = MyTypedDataSet' assignment.>
Thanks in Advance,
SteveH Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59568
Trap "connection pool" errors [crosspost from .asp]
"Sean Nolan" <snolan@harriton.com> wrote in message
news:Oa7doR7RDHA.3768@tk2msftngp13.phx.gbl...
> We have implemented unhandled error trapping at the application level and
> log these errors to our database. One error, however, the does not get
> trapped is when the connection pool has exceeded the max number of
> connections.
>
> Obviously, we need to find the place(s) in our code where connections are
> not closed correctly (espcially in loops), but I'm wondering if it's
> possible to trap this error and to find out which part of our code (i.e.
> stack trace) caused it.
It it possible. The general idea is to have a object that will be Garbage
Collected in the same pass as your connection. Whenever a connection is
opened, store the stack trace of the opening method. And put a finalizer on
that object, and write out a trace entry if the finalizer runs and the
connection is still open.
One way to do this is to have a "wrapper object" for your connection.
But then your app code has to create the wrapper instead of the connection.
I think this is a good thing, since you can implement all the DAAB methods
as instance methods of your wrapper object. But that's another story.
Assuming you are using SQLServer (or some other connection that has a
StateChanged event), there may be an easier way.
Without a wrapper object, to get an object which will be finalized at the
same time as the connection we can use a "spy" object.
If we have a "spy" object which handles the StateChaned event of the
SQLConnection, and we give the spy object a reference to the connection we
will have what we want. If 2 objects mutually refer to each other, then
they will always be GC'd at the same time. The spy refers to the
SQLConnection and since the spy handles an event on teh SQLConnection, the
SQLConnection's delegate list contains a reference to the spy object. Voila!
There follows sample program to do this.
David
Imports System.Data.SqlClient
Class ConnectionFactory
Private Class ConnectionSpy
Private con As SqlConnection
Dim st As StackTrace
Public Sub New(ByVal con As SqlConnection, ByVal st As StackTrace)
Me.st = st
'latch on to the connection
Me.con = con
AddHandler con.StateChange, AddressOf StateChange
End Sub
Public Sub StateChange(ByVal sender As Object, ByVal args As
System.Data.StateChangeEventArgs)
If args.CurrentState = ConnectionState.Closed Then
'detach the spy object and let it float away into space
GC.SuppressFinalize(Me)
RemoveHandler con.StateChange, AddressOf StateChange
con = Nothing
st = Nothing
End If
End Sub
Protected Overrides Sub Finalize()
'if we got here then the connection was not closed.
Trace.WriteLine("WARNING: Open SQLConnection is being Garbage
Collected")
Trace.WriteLine("The connection was initially opened " & st.ToString)
End Sub
End Class
Public Shared Function OpenConnection(ByVal connect As String) As
SqlConnection
Dim con As New SqlConnection(connect)
con.Open()
Dim st As New StackTrace(True)
Dim sl As New ConnectionSpy(con, st)
Return con
End Function
End Class
Module Module1
Sub Main()
'pipe trace output to the console
'in your app this would go to a trace file
System.Diagnostics.Trace.Listeners.Add(New
TextWriterTraceListener(System.Console.Out))
Dim connect As String = "..."
Dim c As SqlConnection = ConnectionFactory.OpenConnection(connect)
c = Nothing '!!the connection was not closed
c = ConnectionFactory.OpenConnection(connect)
c.Close() 'this time it was closed
c = Nothing
GC.Collect(GC.MaxGeneration)
GC.WaitForPendingFinalizers()
'output will show 1 warning
End Sub
End Module Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59547
Blob: INSERT a file in SQL Server
Hello,
I'm trying to INSERT all kind of files in a SQL Server 2000 Table.
These files are e-mail attachments (.PDF, .XLS, .DOC, ....).
Using the code below, returns an IConvertible error.
The DataType of the field is 'Text'. Should this be 'Image'?
What am I missing?
Michael
Dim sqlCONN As New SqlConnection(global.sdc)
Dim scmd As New SqlCommand("UPDATE tblEmailAttachments SET
attachment=@attachment WHERE emailID = " & emailID & " AND fileName = " &
fileName, sqlCONN)
Dim fs As New System.IO.FileStream(SourceFilePath, IO.FileMode.Open,
IO.FileAccess.Read)
Dim b(fs.Length() - 1) As Byte
fs.Read(b, 0, b.Length)
fs.Close()
Dim P As New SqlParameter("@attachment", SqlDbType.Text, b.Length,
ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, b)
scmd.Parameters.Add(P)
sqlCONN.Open()
scmd.ExecuteNonQuery()
sqlCONN.Close() Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59535
Querying ADO.NET Datasets
Hi,
I am populating data of a Microsoft Project 2000(MPP) file into a
dataset through oleDataAdpter using Microsoft Project 2000 OLEDB
Provider.
The above mentioned MPP OLEDB provider does not support queries with
aggregate functions like sum,avg etc. Now i have the data of the MPP
file in my dataset.
Is it possible to query the datatable in my dataset with aggregate
functions like sum,avg etc. ???
If yes how?? If No is there any round about way to perform above
mentioned aggreagte queries on datasets???
Amol... Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59531
AS400 DB2
I have problem with DB2/400 running on AS/400 R4V5!
The OLEDB name IBMDA400 it's doesn't work with server
explorer i connect to server and the data arrived (the
data arrived is not correct too) but when click on the
name of the table VS.net will show console and display
some text "MiniDump" then close and restart automatically
and it appear like anything used to happen. can anyone
descript what happening and how i resolve this prob, or
where i can try another better provider?
Thx u Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59530
Naming conventions!
Hello,
I have searched MSDN and cant find any naming conventions
for MS SQL Server in the .NET environment.
Have I missed it or does not Microsoft have any
conventions for this? (table, column, SPROC names and so
on)
Regards
Stefan Hellberg Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59525
Error in inserting in table in data binded form
i've used dataform wizard and i have binded every column of my table to the
form's controls.but when trying to add a new row to the table an error
message is recieved :
"column X does not allow nulls"
while the related control is binded and filled.why??
can anybody help me? Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59524
strongly typed dataset
Hi,
I have to add 40 records in 40 tables with my application (using strongly
typed dataset). Do I have to use 40 times the "Fill" method before adding
each record in the corresponding table ? If not, how can I do to give a new
value for the primary key to each record ?
thanx,
Ced Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59523
[Microsoft][ODBC SQL Server Driver]Incorrect syntax near '\'
I'm receiving this error when executing a SQL statement with the
connection string below. The exact same code works when
"server=MyServer" rather than "MyServer\MSDE1". It appears to be a
problem with the instance. Any ideas?
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near '\'.
driver={SQL
Server};server=MyServer\MSDE1;database=Main;uid=test;pwd=abc123;Network=
dbmssocn;Connect Timeout = 60;
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59518
primary key problem.
This is a multi-part message in MIME format.
------=_NextPart_000_00B2_01C347B4.2A87BCD0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm new to ADO.NET, I have nonetheless the very ambitious purpose to =
write an ADO.NET provider for the FileMaker "DataBase" (or so they say).
So far it goes not too bad, but I have one big problem about primary =
keys (RECORDID in my particular case).
I see that the IDbDataAdapter has 4 members (the update, delete, =
insert,select command).
I read that a DbAdapter coulad automatically create the corresponding =
update/insert/delete during its Update() method using a CommandBuilder, =
provided the select command is defined
what puzzle me, because I can do it, but I have no idea how to do it in =
a generic way is how the insert (from the new line in the dataset) can =
be inserted and return back their primary key (RECORDID in this case).
Is there a generic method providing such information or should my =
DbAdapter write some FIleMaker specific stuff ?=20
And what code will generate the CommandBuilder ? I never use it, I have =
no idea ....
thanks for reading up to there..
if you have any tip :-)
Lloyd
------=_NextPart_000_00B2_01C347B4.2A87BCD0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>I'm new to ADO.NET, I have nonetheless =
the very=20
ambitious purpose to write an ADO.NET provider for the FileMaker =
"DataBase" (or=20
so they say).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>So far it goes not too bad, but I have =
one big=20
problem about primary keys (RECORDID in my particular =
case).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I see that the IDbDataAdapter has 4 =
members (the=20
update, delete, insert,select command).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I read that a DbAdapter coulad =
automatically create=20
the corresponding update/insert/delete during its Update() method using =
a=20
CommandBuilder, provided the select command is defined</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><FONT color=3D#800000>what puzzle =
me</FONT>, because=20
I can do it, but I have no idea how to do it in a generic way is how the =
insert=20
(from the new line in the dataset) can be inserted and return back their =
primary=20
key (RECORDID in this case).</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Is there a generic method providing =
such=20
information or should my DbAdapter write some FIleMaker specific stuff ? =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>And what code will generate the =
CommandBuilder ? I=20
never use it, I have no idea ....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>thanks for reading up to =
there..</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>if you have any tip :-)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Lloyd</FONT></DIV></BODY></HTML>
------=_NextPart_000_00B2_01C347B4.2A87BCD0-- Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59512
Timeout Error when insert data into Database(MSDE)
Timeout Error when insert data into Database(MSDE)
when I insert data from DataRow to Database(MSDE2000)
with ADO.NET , sometime it can insert into the database
, but sometime it can not which has an error message "Err
no.5 Timeout Expired"
[ These events have the same environment and infromation.]
In the error case,
- The program will show the error dialogbox.
- I press OK. button to accept the error message.
- I press INSERT [ button ] again. It can be
inserted into the database.
Dim cn As OleDbConnection
Dim command As OleDbCommand = New
OleDbCommand()
cn = New OleDbConnection
(strConnectionString)
cn.Open()
command.Connection = cn
command.CommandText = strInsertCommand
command.CommandType =
CommandType.Text
rowsAffected =
command.ExecuteNonQuery() ' The error has been occured in
this line.
Note :-
strInsertCommand ="INSERT INTO testTable
(id,Person_id,USNumber,UAddress,UnitType,SNumber,Stype,User
Type,CName,Notes,DL_id,Priority,Stime,HB) VALUES ( 1994,
17, 0, 0, 0,N'43532', 2, 1,N'435435435',N'43543543',
1,NULL, 0, 1)"
I try to solve this problem by set the connectionstring
with Connect TimeOut= 60.
However It will be an error within 40 seconds.
Comment :
UPDATE and DELETE statement Its have no error.
Can anyone help me please?
Best Regards
Awi Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59511
Retrieving row info
Hi! I learned how to use Adapters to create a Connection and retrieve a
Dataset based on a query using the VB control tools on the Data toolbar.
Now, if I don't have any fields to display the info, how would I go about to
go into my dataset, and check to see if my query returned a value or not.
For example, I am building a login system, I run a stored procedure based on
the parameters login and password inside the DataAdapter and create a
Dataset.
Now where do I go from there. I want to check the results and compare it to
the fields entered by the users in the text boxes to see if they match.
Sorry for asking so much, I am in the process of learning and I bought David
Sceppa book, but I don't seem to find many examples using the graphical
DataAdapter.
Hope someone can help, I know it's really basic stuff but if I learn it well
from the begining I will be able to learn it well.
Thanks so much in advance,
Teo Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59474
executenonquery error
Hello Everyone,
I am at my wits end with this problem, and any help would be
appreciated. I have a web app which contains a keywords textbox that
the user enters terms into. I am trying to validate that the terms
entered are in a thesaurus I have stored in a SQLServer 2000 database.
What I have done is gather the terms entered in the textbox into an
array. I split the array and use a procedure to check each term
against the thesaurus database. My problem is that it works for the
first term I check (i.e. the first element in the array) but doesnt
for the second, third, etc. term. I've checked and the new term is
going into the input parameter each time, but beyond that...nothing.
Any ideas????
The procedure in SQLServer 2000:
CREATE PROCEDURE thes_check
@STHES varchar(50),
@RESULT varchar(50) OUTPUT
AS
set nocount on
SELECT @RESULT = (select distinct term FROM term
WHERE term = @STHES)
GO
The VB.NET code:
Dim i As String = txtSubject.Text
Dim a() As String
Dim j As Integer
Dim subject As String
i.ToLower()
i.Trim()
a = i.Split(",")
For j = 0 To a.GetUpperBound(0)
'Response.Write(a(1))
'create the connection for a datareader
Dim strconn As String = "blahblahblah"
Dim cnSqlServer As New
SqlClient.SqlConnection(strconn)
Dim cmdSqlServer As New SqlClient.SqlCommand()
Dim prmSQLPar As SqlClient.SqlParameter
Dim thesterm As String
With cmdSqlServer
.Connection = cnSqlServer
.CommandText = "thes_check"
.CommandType = CommandType.StoredProcedure
End With
prmSQLPar = cmdSqlServer.Parameters.Add("@STHES",
a(j))
prmSQLPar.Direction = ParameterDirection.Input
prmSQLPar.SqlDbType = SqlDbType.VarChar
prmSQLPar = cmdSqlServer.Parameters.Add("@RESULT",
Nothing)
prmSQLPar.Direction = ParameterDirection.Output
prmSQLPar.SqlDbType = SqlDbType.VarChar
prmSQLPar.Size = 250
cnSqlServer.Open()
cmdSqlServer.ExecuteNonQuery()
cmdSqlServer.Cancel()
cnSqlServer.Close()
Response.Write("Input: " &
CheckNull(cmdSqlServer.Parameters("@STHES").Value))
Response.Write("Output: " &
CheckNull(cmdSqlServer.Parameters("@RESULT").Value))
Next Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59469
Populate each column of a datatable from a different oledbprovider
Hello,
Is it possible in ADO.NET to populate each column of a datatable from
a different oleDBprovider. I mean, if I define a datatable with 5
columns.
First column should be populated from a sybase data source. Second
from a SQL server , third from a XML file.
Also based on the data populated in this way, I need to make 4th and
5th columns as calculated columns based on data in first three
columns.
The data for 4th column should be something like this:
Round(ColData_1,2) + (ColData_2-ColData_3)/2
Any help on this would be great..
Thanks
-Mahesha Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59468
Copy a datacolumn - Most code done, Missing one thing though....
Hello,
I am trying to copy a datacolumn from a good table, and append it to
an incoming table that needs to be modified. This incoming table has
data, and I would rather not copy each property one by one, Is thier a
way to get the propertyinfo stuff to work were I can read the property
of a datacolumn, and set it on a new datacolumn...Here is were I am at
in Code:
If Not (TBL.Columns.Contains(DC.ColumnName)) Then
Dim DCNEW As New DataColumn
Dim T As Type
T = DC.GetType
Dim Pi As System.Reflection.PropertyInfo
For Each Pi In
T.GetProperties(System.Reflection.BindingFlags.Public Or
System.Reflection.BindingFlags.Instance)
Dim result As Object
If Pi.CanWrite Then
Try
result = Pi.GetValue(DC,
System.Reflection.BindingFlags.Public Or
Reflection.BindingFlags.Instance Or Reflection.BindingFlags.GetProperty,
Nothing, Nothing, Nothing)
'********Now that I havge the result, how do I get it
back into the new datacolumn?
Catch
Debug.WriteLine(Pi.Name & " = " & "Can't get Value")
End Try
End If
Next
TBL.Columns.Add(DC)
End If
Thanks to anyone who can help me with this.
Cade Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59465
How to pass a 'null' value to INSERT
Hi,
I use a SqlDataAdapter to add a row to a table. I want to set one column
value to be 'null' but it did not show '<null>' on the data grid after I run
the app. It only shows an empty space on the datagrid cell instead of a
'<null>'. The following is my call to set the null value for the 'Date'.
Thanks for help,
CT
DataSet ds = new DataSet("Info");
adapter.Fill(ds, "Info");
DataTable tbl = ds.Tables["Info"];
DataRow row = tbl.NewRow();
row["Id"] = sID;
row["Date"] = null;
tbl.Rows.Add(row);
adapter.Update(ds, "Visitor_Info"); Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59457
Truncate function???
Does anybody know if there is a similiar function in MS
Access like the ORACLE "TRUNCATE" function?
eg:
trunc(RSS_TRACKING.COMPLETION_DATE) AS COMPLETION_DATE
result is: 6/29/03
The truncate function basically will aggregate dates
(which I have in my GROUP BY clause) based off of a
common starting point, like every Sunday, for instance.
I tried using ROUND, but it seems only for numbers, not
dates. I also tried using the CONVERT and CAST functions
in addition to the ROUND function, but still couldn't do
the date conversion.
Thanks,
Bill....... Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59454
Reflection with SqlConnection
I am using reflection to create a data access component. I have the
following code:
SqlConnection o_Temp = new SqlConnection();
System.Data.IDbConnection o_Connection;
o_Connection = o_Temp as IDbConnection;
This works correctly but it does not use reflection. Now I am looking to
use this code:
System.Reflection.Assembly o_Assembly =
System.Reflection.Assembly.LoadWithPartialName("System.Data");
object o_Temp2 =
o_Assembly.CreateInstance("System.Data.SqlClient.SqlConnection");
System.Data.IDbConnection o_Connection;
o_Connection = o_Temp2 as IDbConnection;
This does not work as o_Connection is null when it attempts to cast. Any
idea why this would be?
Thanks in advance! Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59453
Dataset Relations
I have a DataSet containing 2 tables, one a header table
and the other a detail table. They are related by 3
fields, MrNbr, DocNbr, and TimeStamp. How do I set up a
DataSet Relationship based on all 3 of these? I see and
understand the examples using just one field but cannot
determine from that how to incorporate three fields. Any
help is greatly appreciated.
Robert Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59442
Crystal reports help!
I have an existing crystal report, and I want to integrate
it into a new app that receives data as a dataset from a
webservice.
My question is how do I get the report into the app and
assign the datasource at runtime? I would like to do
everything in code so I can dynamically access multiple
reports down the line in the development cycle.
The report was originally designed for direct access to a
MSAccess database.
I am not very familiar with oledb yet.. I have tried
inserting the viewer control, and then adding the report,
but when I attempt to run the program with ado.net code
accessing the database, it tries to bring up the report
which of course does not work because of the use of
workgroup security files.
this is a huge headache.. can anybody help? Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59441
OleDbDataAdapter won't update database from dataset
Armin Zingler of microsoft.public.dotnet.languages.vb says
this issue should be more appropriately addressed by
microsoft.public.dotnet.framework.adonet.
I cannot get my OleDbDataAdapter to update my database
table from my local dataset table. The Knowledge Base
doesn't seem to help - see item 10 below.
I have a Microsoft Access 2000 database which indexes
computer magazine articles for personal reference.
I am developing a Visual Basic.NET program whose sole
purpose is to enter new records into the database. No
updates to existing entries, no deletions, and no display
of existing records will be performed by this program;
only the entry of new records.
1. I use the following code to attempt the update (CRLF is
pre-coded - see item 9 below):
Try
OleDbDataAdapterArticles.Update _
(DsInfoBase, "Articles")
' An unhandled exception of type
' System.Data.OleDb.OleDbException
' occurs in system.data.dll
Catch ex As Exception
MsgBox("Type = " & _
ex.GetType.ToString() & CRLF & _
"Message = " & ex.Message, _
MsgBoxStyle.OKOnly, _
"Try-Catch Mechanism")
End Try
It reports:
Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO statement.
2. However, the INSERT INTO statement was automatically
generated by the IDE when the OleDbDataAdapterArticles was
created. For your reference, that INSERT INTO statement is:
INSERT INTO Articles
(Author1, Author2, Author3, bkTitleID, catCode1, catCode2,
catCode3, catCode4, catCode5, catCode6, catCode7,
catCode8, [Date], Location, magTitleID, miscTitleID,
Month, Notes, [Number], PageEnd, PageStart, Title, Type,
Volume, Year)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?)
and the data adapter's corresponding SELECT statement is:
SELECT artTitleID, Author1, Author2, Author3, bkTitleID,
catCode1, catCode2, catCode3, catCode4, catCode5,
catCode6, catCode7, catCode8, [Date], Location,
magTitleID, miscTitleID, Month, Notes, [Number], PageEnd,
PageStart, Title, Type, Volume, Year
FROM Articles
3. Also for your reference, the schema for the Articles
table (the only table being updated) is:
artTitleID AutoNumber Primary Key
Title Text 128 Default
= "No Title ???"
Author1 Text 32 Default = " "
Author2 Text 32 Default = " "
Author3 Text 32 Default = " "
Type Text 4 Default = "misc"
bkTitleID LongInteger Default = 0
magTitleID LongInteger Default = 0
Foreign Key = Magazines.magTitleID
miscTitleID LongInteger Default = 0
Year LongInteger Default = 2003
Month LongInteger Default = 18
Date LongInteger Default = 0
PageStart Text 4 Default = " "
PageEnd Text 4 Default = " "
Volume Text 4 Default = " "
Number Text 4 Default = " "
Location Text 128 Default = "MDJ Library"
Notes Memo Default = " "
catCode1 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode2 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode3 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode4 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode5 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode6 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode7 Text 16 Default = " "
Foreign Key = Categories.catCode
catCode8 Text 16 Default = " "
Foreign Key = Categories.catCode
The schema for the referenced Magazines table is:
magTitleID LongInteger Primary Key
Title Text 64
PubName Text 32
PubCity Text 32
And the schema for the referenced Categories table is:
catCode Text 16 Primary Key
catTitle Text 255
supCatCode Text 8
4. Before attempting the above update, I use the following
code to display the data from the newly created row in the
local dataset's table (DsInfoBase.Articles):
Dim rowCount As Integer
Dim xc As Integer
xc = 1
rowCount = DsInfoBase.Articles.Rows.Count()
MsgBox("Row Count = " & rowCount.ToString _
& CRLF & "artTitleID = " & _
DsInfoBase.Articles.Rows _
(rowCount - xc).Item("artTitleID") & CRLF _
& "Title = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Title") & CRLF _
& "Author1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author1") & CRLF _
& "Author2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author2") & CRLF _
& "Author3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author3") & CRLF _
& "Type = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Type") & CRLF _
& "bkTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("bkTitleID") & CRLF _
& "magTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("magTitleID") & CRLF & _
"miscTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("miscTitleID") & CRLF _
& "Year = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Year") & CRLF _
& "Month = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Month") & CRLF _
& "Date = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Date") & CRLF _
& "PageStart = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageStart") & CRLF _
& "PageEnd = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageEnd") & CRLF _
& "Volume = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Volume") & CRLF _
& "Number = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Number") & CRLF _
& "Location = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Location") & CRLF _
& "Notes = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Notes") & CRLF _
& "catCode1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode1") & CRLF _
& "catCode2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode2") & CRLF _
& "catCode3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode3") & CRLF _
& "catCode4 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode4") & CRLF _
& "catCode5 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode5") & CRLF _
& "catCode6 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode6") & CRLF _
& "catCode7 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode7") & CRLF _
& "catCode8 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode8") & CRLF, _
MsgBoxStyle.OKOnly, "Temporary Check")
An inspection of the information displayed in the msgBox
shows that everything seems okay.
5. To double-check, I then use the following code:
Dim i As Integer
Dim HC As String
Dim HE As String
Dim ErrRec As String
Dim TE As DataTable
Dim RE As DataRow()
Dim CE As DataColumn
HC = "No Changes"
HE = "No Errors"
ErrRec = ""
If DsInfoBase.HasChanges Then
HC = "Changes"
If DsInfoBase.HasErrors Then
HE = "Errors"
For Each TE In DsInfoBase.Tables
If TE.HasErrors Then
RE = TE.GetErrors
For i = 0 To RE.Length
For Each CE In TE.Columns
ErrRec = ErrRec & CE.ColumnName & _
" " & RE(i).GetColumnError(CE) _
& CRLF
Next
RE(i).ClearErrors()
Next
End If
Next
End If
End If
ErrRec = HC & CRLF & HE & CRLF & ErrRec
MsgBox(ErrRec, MsgBoxStyle.OKOnly, _
"Record of Errors")
It reports: Changes
No Errors
from which I conclude that the data has been successfully
entered into the local dataset's table but the
OleDbDataAdapter is failing to update the database from
the dataset for some reason I can't figure out.
6. FYI, I am using Microsoft Access 2000 (9.0.3821 SR-1)
and Visual Basic.NET(Microsoft Development Environment
2002 Version 7.0.9466, Microsoft .NET Framework 1.0
Version 1.0.3705) running under Microsoft Windows 2000
Professional (5.0.2195, Service Pack 3, Build 2195) on a
Dell Optiplex GX400 (P4, 1.3 GHz, 512 MB RAM, 37.2 GB HD).
7. After designing the form layout, I went to Server
Explorer and dragged the Articles table to the form, thus
creating OleDbConnection1 and OleDbDataAdapterArticles.
From the DataAdapter, I generated the DsInfoBase dataset.
8. I then dragged the two reference tables to the form,
thus creating OleDbDataAdapterMagazines and
OleDbDataAdapterCategories. I added the corresponding
local tables to the DsInfoBase dataset. I created several
dataviews based on the two reference tables and bound them
to listboxes on the form. The form includes an "Add
Record" button to execute the addition of the new row to
the Articles table. The following code performs the
transfer of information from the form to the local
dataset's Articles table:
' Update the Local Articles Table
Dim drN As DataRow = _
DsInfoBase.Articles.NewRow()
drN("Title") = HTitle
drN("Author1") = HAuthor1
drN("Author2") = HAuthor2
drN("Author3") = HAuthor3
drN("Type") = HType
drN("bkTitleID") = HBkTitleID
drN("magTitleID") = HMagTitleID
drN("miscTitleID") = HMiscTitleID
drN("Year") = HYear
drN("Month") = HMonth
drN("Date") = HDate
drN("PageStart") = HPageStart
drN("PageEnd") = HPageEnd
drN("Volume") = HVolume
drN("Number") = HNumber
drN("Location") = HLocation
drN("Notes") = HNotes
drN("catCode1") = HCatCode1
drN("catCode2") = HCatCode2
drN("catCode3") = HCatCode3
drN("catCode4") = HCatCode4
drN("catCode5") = HCatCode5
drN("catCode6") = HCatCode6
drN("catCode7") = HCatCode7
drN("catCode8") = HCatCode8
DsInfoBase.Articles.Rows.Add(drN)
9. CRLF = Chr(13) & Chr(10)
This is a personal idiosyncrasy - I prefer this to using
the constant vbCrLf because it's sometimes convenient when
generating certain files for Linux servers.
10. The Knowledge Base doesn't seem to have much on this.
I used various search terms including keyword searches on
kbADONET and kbVBNET. The few articles I found (301248,
308055, 316323, and 326602) indicate that I'm using the
proper update statement format. The
microsoft.public.dotnet.languages.vb,
microsoft.public.dotnet.framework.adonet, and other
newsgroups don't seem to address this either (at least, I
haven't been able to find anything).
M. David Johnson
Director of Information Technology
Illinois Racing Board
david_johnson@irb.state.il.us
File c:\work\word work\20030709dbProblem.rtf
Posted to:
microsoft.public.dotnet.languages.vb
2003/07/09 14:05 CDT - Recorded at 12:08
Posted to:
microsoft.public.dotnet.framework.adonet
2003/07/10 09:10 CDT Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59431
Accessing db with Workgroup file
Help!
I need to pull data from an access database that uses a
workgroup security file (mycomp.mdw).
The database is also a previous version of microsoft
Access... I cannot convert it because it is in use by a
VB6.0 application for which I do not have the source code.
I would like to programmatically access the data in that
database for other functions without disturbing the
existing security.
I have all of the valid passwords and such, I just need
some assistance getting into it.
Please email me at nektoinphx@yahoo.com Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59426
Property Binding
I have the following:
a combo box, cboPrinterType
a DataSet, dsPrinterTypes
a second DataSet, dsPrinter
I want to have the combo box list the values in dsPrinterTypes, so I have
set the DisplayMember and Data Source - this works
Next I want to have the selected item in the combo box bound to dsPrinter -
this does not work.
I have tried the following:
cboPrinterType.DataBindings.Add("Text", dsPrinter.Tables(0), "printer_type")
cboPrinterType.DataBindings.Add("SelectedItem", dsPrinter.Tables(0),
"printer_type")
cboPrinterType.DataBindings.Add("SelectedValue", dsPrinter.Tables(0),
"printer_type")
None of which work. The displayed items & values listed in the combo are
text.
Any suggestions?
TIA,
Jeremy Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59424
ADO.net and datasets question?
Hello,
Currently I have a sql server 2000 stored proc which executes queries
to return historical data for
a number of financial bonds in the following format.
Date OutputCol1 OutputCol2 OutputCol3
---------------------------------------------
1/1/90 3.2938 90.2930 94.019
1/2/90 3.2938 90.2930 94.019
The underlying query in the stored proc which returns the data as
above is in the following format.
Select a.CurveDate as Date,
b.TblCol1 as OutputCol1,
c.TblCol2 + d.TblCol3 + log(e.TblCol4) as OutputCol2
Each of these TblCols are retrieved from a different table based on
the joins on the Date field across all the tables.
As of now all these tables are within a single SQL server 2000
database.
We need to move all the math operations which we are currently doing
in stored proc to the middle tier(Either VB or C# class library
application). and use database just for retrieving raw data from the
tables.
Also We need to expand this functionality to get raw data for columns
from different data sources like SQL server, Oracle
sybase or a xml data file and perform math operations in the middle
tier and then return the processed data to the front end.
We were thinking of doing it in ADO.net which lets us get each of the
raw data columns(without
math experessions) into different oledbadapters and them filling each
into a dataset. I need to know if it is possible
to execute join queries on different tables of a dataset. Also I want
to perform math operations like
dataset.tables(0).columns(1) + log(dataset.tables(1).columns(2)).
If not do let me know if you have any other suggestions.
Thanks
-Mahesha Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59414
ado answer to TableDefs
Can anyone tell me what the code is to replicate the Access TableDefs
collection?
For instance I can code the following to get table data:
Dim db as DAO.database
Dim tdf as DAO.TableDef
set db = currentDb()
For Each tdf In db.TableDefs
Debug.Print tdf.Name
Next tdf
What I want to do is to loop through an Access database and see if a
specific table exists and if not create the table, which brings me to my
next question. Can I use DDL to create the table?
Thanks,
Tom Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59413
Data Relations using GUID (uniqueidentifier)
I am trying to build a data relationship between two tables in a dataset. I
am making the join on fields that are both System.Guid types in .NET and
uniqueidentifiers in SQL Server 2000. The join works as expected when used
in an SQL query but I get an empty set when I use the join in my dataset.
The join was working correctly when we were using integers as the primary
key but after changing the data types to GUIDs I am not able to make the
join.
Has anyone else experienced this kind of problem.
Thanks,
Andrew Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59412
Getting a list of columns that accept null values
Hi,
I need to find a way to generate a list of columns that
accept null values for a given table.
The easiest for me would be to do this through a SELECT
statement, but I haven't been able to figure out how.
regards
Natrajk Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59410
Third party code generation tool for .net
Hello,
Just wondering if anyone out there has used or can recommend a good third
party product for generating the database access layer. I have looked at a
product called ORM.Net an Object-Relational Mapping and Code Generation Tool
for .NET, which auto-generates a data layer object model based on your
database schema. However this product generated only C# code, did not
support "ORDER By" and concurrency.
If anyone else has come across/used a good product, I would be interested to
hear about it.
Thanks,
Kay. Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59407
MSSQL7 Linked Sever and Foxpro tables
We need to combine MSSQL7 tables with Visual FoxPro 6
tables for reporting purposes using Crystal Reports
Version 6.
The process we follows to create a Linked server is as
follows:
1. We create a System DSN using Visual FoxPro Tables
(*.dbf) driver and pointing to the directory (Free Table
Directory) of the Visual FoxPro tables.
2. In SQL we are creating a Linked Server using the
Microsoft OLE DB Provider for ODBC and the DSN as the
FoxPro DSN that we have created in 1.
3. Now you should see the FoxPro tables under the
Tables section of the Linked Server.
After formatting the SQL server and re-Installed Windows
2000 Server SP3 and SQL Server 7.0 SP3, we could create
the Linked Server without any problems. After 3 days of
work, the linked server's tables were not visible anymore.
We tried to re-create the Linked Server as described
above, but without any success. Although Linked server is
visible, the FoxPro tables are then not visible.
It seems that the only way to re-establish the Linked
Server is to format the drive. Then it will work properly
for a short while.
How can we re-create the Linked server without formatting
SQL server again?
Can anyone tell us what are the prerequisites for Linked
Server?
Why do the FoxPro Linked Server tables disappear?
We are using MSDASQL.DLL version 2.53.6200.0.
The Visual FoxPro Tables are all version 6.
To create the DSN, we are using Visual FoxPro tables
(*.dbf) driver version 6.01.8629.01
Where can we look for errors, as there are no errors
reported in the SQL logs?
Thanks Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59404
Ordinal Versus Columnname
I need some references to some hard facts to why the Ordinal value is a
better choice than the column/field name when extracting information of a
row in ADO.NET.
Anybody have some good reference with numbers that I can show to someone.
Peace,
Keith Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59402
Is ADO.NET Microsoft's answer to JDO ?
I have some experience in ADO.NET and none in JDO. The articles on JDO
(Java Data Objects - I think) though, seem to suggest that it is a new and
the 'best yet' attempt at solving the relational-object mismatch. From what
I understand, as developer I would really only work with JDO's and link any
sort of backend datastore (Relational, object database, files, XML files and
database etc) to the JDO and truly code against and object model. The JDO's
handle caching, transactions (or makes it very easy for me to manage),
instantiating new instances while sorting out id's an various optimizations.
I got on this track through an article by Dirk Bartels on DevX 'JDO Brings
DB Programming into 21st Century Despite Controversy'
(http://www.devx.com/devx/editorial/16373)
It all sounds great.
In ADO.NET a still work with tables and relations like in a relational
model.
Strongly typed datasets, and similar commercial attempts such as Deklarit,
is actually just mass code generation on an inherantly relational model, and
in my view a weak solution for a very big problem.
So is ADO.NET Microsoft's answer to JDO ? If not, what is ? Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59397
Timeout Error when insert data into Database(MSDE)
Timeout Error when insert data into Database(MSDE)
when I insert data from DataRow to Database(MSDE2000)
with ADO.NET , sometime it can insert into the database
, but sometime it can not which has an error message "Err
no.5 Timeout Expired"
[ These events have the same environment and infromation.]
In the error case,
- The program will show the error dialogbox.
- I press OK. button to accept the error message.
- I press INSERT [ button ] again. It can be
inserted into the database.
Dim cn As OleDbConnection
Dim command As OleDbCommand = New
OleDbCommand()
cn = New OleDbConnection
(strConnectionString)
cn.Open()
command.Connection = cn
command.CommandText = strInsertCommand
command.CommandType =
CommandType.Text
rowsAffected =
command.ExecuteNonQuery() ' The error has been occured in
this line.
Note :-
strInsertCommand ="INSERT INTO testTable
(id,Person_id,USNumber,UAddress,UnitType,SNumber,Stype,User
Type,CName,Notes,DL_id,Priority,Stime,HB) VALUES ( 1994,
17, 0, 0, 0,N'43532', 2, 1,N'435435435',N'43543543',
1,NULL, 0, 1)"
I try to solve this problem by set the connectionstring
with Connect TimeOut= 60.
However It will be an error within 40 seconds.
Comment :
UPDATE and DELETE statement Its have no error. Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59396
updating a typed dataset (newbie question?)
I created a DataGrid and bound a DataView of a typed DataSet to it (i tried
with the DataSet as well but i'd rather use the DataView as i'm using it for
sorting). I have paging working, sorting and the edit template is cool. When
i hit Update i wonder what i have to do to update the database with the user
inputted values.
Now in a perfect world the datagrid, bound to my view would have a method
for updating the selected row of the dataset/view with the templated values.
All i had to do is call myDataAdapter.Update(myDataSet) to make the changes
in the database. I've been looking for this and i can't seem to find a way
to do it though.
I found a lot of examples using a string or stored procedure to update the
values through a new Command object, but i was wondering why i shouldn't use
the automaticly generated update, delete, insert of my typed dataset? To
find the row i want to update i'd do something like this
myTypedDataRow = myTable.Rows.FindById(selectedId);
Now my biggest problem is how do i find the selectedId of the row just
edited? I already set the Key on the datagrid, so it knows the key of my
datatable, yet all the fancy methods on my DataGridEventArgs (or what it's
called - i don't have VS/my project here) all just return the index in the
datagrid - so since the Id is not the same as the index in the datagrid i
can't use it.
Anyone know how to solve this or have a good tutorial on DataGrids? I
searched for a good one, but only found very simple examples that didn't
cover my problem :(
Thanks!
Per Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59395
dllregisterserver entry point is not found
While i am instaling a new driver, i am getting the error
message like dllregisterserver entry point is not found. I
don't know how to slove this issue. Can anyone help me in
this issue. Thanx in advance. Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59392
Untyped Dataset and Datarows???
Hi,
I am having difficulty updating an existing record in a database through an
untyped dataset. Can't seem to work out what i am doing wrong, and have had
no luck finding any good examples on the net that match what i want to do.
Below is my code segment, currently i am getting an error that states
"Column 'Code' contains non-unique values" and i don't understand why it's
giving it to me, because there is only one record with that code and its the
one i am trying to update.
Any help would be appreciated.
DataRow tblRow ;
DataColumn[] pki = new DataColumn[1] ;
try {
DtlsConnection.ConnectionString = ConnectionPath ;
DtlsConnection.Open() ;
DtlsCommand.CommandText = "Select * From tbl" ;
DtlsCommand.Connection = DtlsConnection ;
DtlsAdapter.SelectCommand = DtlsCommand ;
DtlsAdapter.Fill(DtlsDs, "tbl") ;
pki[0] = SecurityDtlsDs.Tables["tbl"].Columns["Code"] ;
pki[0].Unique = true ;
DtlsDs.Tables["tbl"].PrimaryKey = pki ;
tblRow = DtlsDs.Tables["tbl"].Rows.Find(pCode) ;
tblRow.BeginEdit() ;
tblRow["Code"] = txtCode.Text ;
tblRow["Name"] = txtName.Text ;
tblRow["Type"] = txtType.Text ;
tblRow.EndEdit() ;
DtlsAdapter.Update(DtlsDs, "tbl") ;
//DtlsAdapter.Fill(DtlsDs, "tbl") ;
}
catch (Exception e) {
MessageBox.Show(e.Message, "Finwin", MessageBoxButtons.OK,
MessageBoxIcon.Error) ;
}
//Closing the connection
DtlsConnection.Close() ;
Kind Regards
Darryn Ross Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59380
Timeout expired
I am continually experiencing this error message while developing and
test a
simple .net application.
The timeout period elapsed prior to obtaining a connection from the
pool. This may have occurred because all pooled connections were in
use and max pool size was reached.
This has only started happening since installing 1.1. I have checked
every connection to be sure it is being closed after executing my
queries. I have tried reading everything I can about connection
pooling. Nothing helps. I have tried monitoring using Performance
Counters but the .NET CLR Data performance counters seem to accumulate
and are not a correct reflection.
Can anyone help me please?
Thanks
Ros Lee Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59370
Client/Server Samples with Business Logic Layer
Sorry for the cross-post, but I didn't know whom to ask..
Are there any samples for a true C/S app (preferably VB, but C# is welcomed)
with a middle tier using datasets/datatables similar to the the Fitch/Maher
sample w/VB6? The only thing i can find on windowsforms.com & MSDN is
TaskVision, which is overly complicated for my needs(dont need to expose
data via web services).
I have adopted the "MS Building Blocks" data access class, but it's only
been used in ASP.Net samples, and would like to continue using it in the
BLL. Is this possible?
Thanks,
Morgan Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59363
Which alternative acheives best performance?
VB.NET/SQL Server
Using datareader to create objects and populate collections (no datasets
involved)
Part of my object model looks like this (with corresponding tables in the
database with PrimaryKey/ForeignKey relationships)
Organisation
contains Projects
contains Contacts
contains Allowances
contains Adjustments
contains Adjustment Costs
Which of the following will provide better performance in terms of
downloading data?
Read all Projects belonging to the Organisation, then loop through each
Project and read all Contracts belonging to the Project,then loop through
each Contract and read all allowances belonging to the Contract etc.
OR
Amend my stored procedures to include multiple joins (up to 6 levels deep)
so that I read all Projects belong to the Organisation, then read all
Contracts belonging to the Organisation, then read all Allowance belonging
to the Organisation etc.
The first method means I can create the correct heirarchy as data is read
but involves many more calls to server and corresponding open/closing of the
reader.
The second method involves less (but more complex) calls. In addition, I
would need to create a temporary hashtable of each collection in order to
find out where objects lower down the model belong
Stephen Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59360
sql server vs msde, how to tell
How do I tell if someone is running sql server or msde?
the reason I need to know this has to do with the connection strings.
When I try to connect to sql server I connect with the name of the
server easily with Data Source=mysqlserver. If I want to connect to
an msde instance I have to do Data Source=(local)\NETSDK. The problem
obvoiusly is that neither works with the other connection string. So
I'm trying to figure out how to tell which it is so I can insert the
(local)\ or not.
any help or advice on alternative solutions to my problem would be
greatly appreciated.
thanks Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59354
Navigating DataGrids: How to access information in Child Tables
I am using a Windows Forms DataGrid to display a DataSet with two tables
using a parent / child relationship. I set this up with the following code:
parentColumn = ds.Tables["Parent"].Columns["ID"];
childColumn = ds.Tables["Child"].Columns["ID"];
dr = new DataRelation("RelParentChild",parentColumn, childColumn);
ds.Relations.Add(dr);
grid.SetDataBinding( ds, "Parent" );
* I need to access information from the Child Table when the user
double-clicks it.
gridWIP.CurrentRowIndex stores the value of the Parent Table and does not
change while the Child Table is being displayed.
Furthermore, this.BindingContext[ dsWIP, "Child" ] appears to be useless:
None of the events fire and the Current & Position values never change.
I can probably access the information I need using the MouseDown event & the
HitTest, but I need to be able to determine which Table the DataGrid is
showing at the time, and I'm not sure how to do that either.
Any suggestions or links to futher reading would be appreciated.
Sincerely,
Greg Persson Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59351
Help: Create schema dynamically from a valid XML string?
Hi All
I think I am attempting a little chicken before the egg, but I am
trying:
XmlDataDocument parsedErrorDoc = new XmlDataDocument();
parsedErrorDoc.LoadXml(strXML); //where strXML is a valid xml string
string schema = parsedErrorDoc.DataSet.GetXmlSchema();
parsedErrorDoc.DataSet.ReadXmlSchema(schema);
So I suppose that the dataset doesn't have a schema to return,
correct?
Is there anyway to programtically generate a schema from a string of
valid xml, then apply said schema to the XmlDataDocument's dataset?
Or must I load the XmlDataDocument's dataset with a schema first, at
all times?
Thanks for any help!
Geoff Moller
geoff_moller@hotmail.com Tag: Re: ADO.NET / SQL CE / MSDE Tag: 59346