|
Hi!
I will write an application that work with an Access DB.
My database have 5 table that have a relationship to each other.
tables in DB:
Table 1: Car: CarID, Manufactore, Colour, Hp, Displacement, Price, Model
Table 2: SaleContractCar: SaleContractID, CarID
Table 3: SaleContract: SaleContractID, CustomerID, Date
Table 4: Customer: CustomerID, CustomerAddressID, Tel, Age, SeconCar, Name
Table 5: CustomerAddress: CustomerAddressID, Street, StreetNr, Zip, City
relationship between the tables:
Table 1 (one to more) Table 2
Table 2 (more to one) Table 3
Table 3 (more to one) Table 4
Table 4 (more to one) Table 5
at this moment I don't know how to start.
should I first load all tables I one DataSet?
and create for each table a DataTable?
how I should start?
how I should write an update SQL statement for this DataSet?
where I can find some sample application that work with Relational Data?
thx!!
|
|
|
|
|
Hello,
The .NET Framework SDK 1.0 includes a collection of QuickStart Tutorials. These can be installed locally or viewed online.
The ADO.NET tutorials can be found at
http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/adoplusoverview.aspx
Notes:
- In the tutorial, you can switch between C#, VB.NET, and JScript for the example code.
- To access MS Access, you should use the System.Data.OleDb data provider.
HTH,
Bernard
|
|
|
|
|
hi all
i open an access database from asp .. for some reason i need to know the number of records in the recordset before processing it as follows :
set con=Server.CreateObject("Adodb.Connection")<br />
set rs=Server.CreateObject("Adodb.Recordset")<br />
con.ConnectionString=strCon<br />
con.CursorLocation=1 'server<br />
con.Open<br />
set rs.ActiveConnection=con<br />
rs.CursorType=adOpenKeySet<br />
rs.open "select * from items"<br />
rs.MoveLast<br />
n=rs.RecordCount<br />
Response.Write n
i get an error :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E24)
Rowset does not support fetching backward.
can you help with this ?
|
|
|
|
|
You could call SELECT COUNT(*) FROM items first and then your actual query.
--
karl
|
|
|
|
|
I've already decided to use this method.. I know it but I didn't want to make 2 queries while I can use a simple property call
thank you anyway
any other ideas ?
|
|
|
|
|
You might read up on the following:
o CursorType = adStatic (3)
o CursorLocation = adUseClient (3)
Also, when I was doing ASP, I used to use the GetRows() method to store the recordset into a multi-dimensional array. That allows you to close the recordset and connection straight away - and you can wander backwards and forwards through the array data.
Andy
|
|
|
|
|
Hi..
I changed the driver in the connection string from
Microsoft access
to:
Jet 4.0
and it worked !!
|
|
|
|
|
I have been looking into randomizing a result set from a SQL Server database. I have looked into using the RAND() function but there are problems with this approach because RAND() seeds every time. So a query like this, then:
SELECT ID, rand()
FROM FOO
ORDER BY 2
This outputs something similar to:
ID
-- ----
1 .754
2 .754
3 .754
Since RAND() seeds of the time and the time did not change we get the same result through the query.
There is one solution:
SELECT * FROM TABLE ORDER BY NewId()
Another thought is to use an external dll to use c++ rand()
There are other methods that generate tables of random numbers and use cursors.
My question is what method do you use?
Thanks in Advance,
John
|
|
|
|
|
Here's a sample from SQL Books Online (why they call it online, one will never know):
DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND(@counter) Random_Number
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO An alternative is to just get a resultset and pulling rows from that randomly from your application.
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page... - vjedlicka 3:33 25 Nov '02
|
|
|
|
|
I have the sql books online and saw that example but I am unsure how to get that to work in my application.
leppie wrote:
An alternative is to just get a resultset and pulling rows from that randomly from your application.
I know that will work but in my application most of the time I need the data read in the normal order. Only under special cases will I need the data to be randomized. I thought it will be a lot simpler and require less code modifications if I could get SQL server to randomize the list under these conditions. The data is a list of cases (mammograms) that doctors will read. I call a stored procedure to produce this result set. In the normal mode you want them to read first in first out, but when you are doing a study you want to randomize the data so each doctor sees the cases in a different order. This is because there may be a pattern in the data that infulences the doctors answers for future cases. With the data randomized we don't have to worry about things like this.
John
|
|
|
|
|
John
I had a similar problem a couple of years back. Under certain conditions the client wanted to have rows sorted in a random order.
I wrote the following JScript ASP code:
function array_randomise() {
var li_rnd ;
var lo_temp ;
for (var li_loop = 0 ; li_loop < this.length ; li_loop++) {
li_rnd = Math.round(Math.random() * li_array_len) ;
lo_temp = this[li_rnd] ;
this[li_rnd] = this[li_loop] ;
this[li_loop] = lo_temp ;
}
}
It walks through the list of records, and randomly swaps the current record with some other record (at a random position in the list).
Hope this helps.
Andy Harman
|
|
|
|
|
Now I remember what the problem is with that approach (WHILE loop) the output looks like this:
Random_Number
-------------
0.713591993212924
Random_Number
-------------
0.713610626184182
Random_Number
-------------
0.71362925915544
Random_Number
-------------
0.713647892126698
I guess some of the digits are random but it is not clear how to use the data to randomize a result set.
|
|
|
|
|
John M. Drescher wrote:
I guess some of the digits are random but it is not clear how to use the data to randomize a result set.
I dont really know SQL well enough to do this, like I said, I would just do it on application level. Hope someone can help you.
I rated this article 2 by mistake. It deserves more. I wanted to get to the second page... - vjedlicka 3:33 25 Nov '02
|
|
|
|
|
Thanks. I thought there had to be an easy way to do it in SQL Server.
John
|
|
|
|
|
I may be WAY off base here, it's been a while since I've used SQL Server, but as no one else seems to know, I'll take a guess.
Isn't the problem here that you're saying "ORDER BY 2"?
Are you sure that this sorts on Column 2 or does it just sort with each record being 2?
Wouldn't "ORDER BY rand()" work better?
Paul
We all will feed the worms and trees So don't be shy - Queens of the Stone Age, Mosquito Song
|
|
|
|
|
use this:-
select ID, NewID() as [guid]
from FOO
order by [guid]
#include <beer.h>
|
|
|
|
|
Hi all..
I want to connect to an access database on an ftp server from my vb6 application using ado or whatever else...
i tried adding the ftp to (my network places) then creating a system dsn and point to the database file but failed
any help please ?
|
|
|
|
|
I don't think that you will be able to connect to Access in this way. Access is a file-based database system. Your program would need to have direct access to the filesystem where the MDB file is located.
SQL-Server would be a better bet because it is designed as a client-server database system, and its more robust, and its faster, etc. Also, you will find it easier to do data maintenance and change the database design at a later date
However, if you absolutly need to work with Access then your options are limited by what you want to achieve. If your VB program just needs to read from the Access database then you should be able to periodically FTP the entire database onto your local network, then read it from there.
If your VB program needs to write, but your web site only needs to read then you should be able to have the master database on your local network, then periodically FTP it up to the central web server (although you will have to be careful of file locks imposed by the web pages reading the database).
If you want to be able to read-and-write from both the VB application and the web application then you might be able to create a couple of special "web-service" web-pages, then use an HTTP connection from the VB application to invoke those web-services. This would take a whole bunch of effort.
Hope this helps.
Andy
|
|
|
|
|
Thank you alot for your helpfull ideas
my hosting account supports mysql but i just know SQL server and access
please point me to some helpfull tutorials or sites about mysql
|
|
|
|
|
We have a web application written in asp.net/c# using SqlServer 2000. Our database administrator has informed us that in Managerment>Current Activity>Process Ifno, we have several process id's with status of "sleeping" and command of "AWAITING COMMAND" and application of ".Net SqlClient Data Provider" and wait type of "not waiting". The process id's stays for a while even if nobody is already using the web application.
We know that our application is closing the db connections after we used them (in finally block) and we are using the same connection string information everytime.
Does anybody know why we are getting several process id's? Is this harmful to the db resources? If so, suggestion on how to resolve this is well appreciated.
Thanks in advance.
|
|
|
|
|
I haven't got access to my development machine at the moment, but it sounds like normal connection-pooling to me. The system keeps a "pool" of database connections ready for your next database request. If you don't use the connection within a pre-specified period of time then they will eventually be released.
Andy
|
|
|
|
|
|
i would like to record each customer's login ID and the time they login.
this is how it works:
when customer logs in, the system will check the customer's username and password against those stored in the database. if login is successful, the system will record the customer's ID (hidden, autogenerated by database) and the time to the database. how do i write the sql statement for this?
i used the following code but it doesnt work.
Dim strConn As String = "Provider=SQLOLEDB.1;Integrated Security.."
Dim objConn As New OleDb.OleDbConnection(strConn)
Dim objReader As OleDb.OleDbDataReader
Dim strSQL As String = "SELECT customerid FROM tblCustomer"
strSQL += " WHERE Username = '" & txtUserName.Text & "'"
strSQL += " AND Password = '" & txtPassword.Text & "'"
Dim objComm As New OleDb.OleDbCommand(strSQL, objConn)
objConn.Open()
objReader = objComm.ExecuteReader()
try
If objReader.Read() = False Then ' If the reader contains nothing
ErrorMessage.Text = "please re-enter your credentials..."
Else ' if login is ok
Dim logonSQL As String = "INSERT INTO tblLogon(customerID, LoginDateTime) VALUES (strSQL, datetime.now())"
Dim objComm2 As New OleDb.OleDbCommand(logonSQL, objConn)
Dim objReader2 As OleDbDataReader = objComm2.ExecuteReader(CommandBehavior.CloseConnection)
objComm2.ExecuteNonQuery()
Response.Redirect("home.aspx")
objReader2.Close()
End If
Finally
End Try
objReader.Close() ' Close our Data Reader Object
objConn.Close() ' Close our connection object
the error message was that the dataReader connection is still open and the problem area was the objComm2.ExecuteNonQuery()
how should i go about adding the login time and the customerID (obtained from the execution of 1st SQL statement) to the database?
Laine
|
|
|
|
|
I couldn't see the point of objReader2 at all. Try closing the DataReader before attempting objComm2.ExecuteNonQuery() because the you can't execute other commands on that connection until you have closed the reader, and remove the objReader2 stuff.
The CloseConnection behaviour is used to close the database connection after you have explicitly closed the reader.
In this particular case you could use the ExecuteScalar method because you are only interested in a single value from the database. This means that you wouldn't have to use a DataReader at all.
The SQL-Select statement will currently have problems if the user-id or password contain quotes. You might want to consider using parameters instead.
A single stored procedure would be a better way. You could do the select and the insert within the same procedure.
Hope this is helpfull.
Regards.
Andy
|
|
|
|
|
hrmm.. ok. i still have problem getting those records into the database. 1st converting the strSQL to integer, and the date from string to date format.
so i tried creating a stored procedure since there's so many benfits for using it.. and also new things for me to learn
however, i am stuck as to how i should write it:
CREATE PROCEDURE Login
( @username varchar(50),
@password varchar(50),
@logindate datetime,
@customerid int OUTPUT
)
AS
SELECT @customerid FROM tblcustomer
WHERE username = @username AND password = @password
INSERT INTO tblLogin (customerid, logindate)
VALUES(@customerid, @logindate)
RETURN
is the above correct? i think there's something lacking.. but i dont know what...
and is there any site on SP for beginners like me?
Laine
|
|
|
|