|
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
|
|
|
|
|
Laine
I would suggest something like the following (which I have not tested):
CREATE PROCEDURE Login (
@username varchar(50),
@password varchar(50),
@customerid int OUTPUT)
AS
BEGIN
--Grab the unique customer ID.
SELECT @customerid = customerid FROM tblcustomer
WHERE username = @username AND password = @password
--If the customer ID is valid then record login time.
IF @@customerid > 0
BEGIN
INSERT INTO tblLogin (customerid, logindate)
VALUES(@customerid, CURRENT_TIMESTAMP)
RETURN 1
END
--If we got here then invalid ID/password passed-in.
RETURN -1
END
You don't need to pass the login-date into the SP because SQL-Server knows what the current date is.
Andy
|
|
|
|
|
how do i call the returned value from the stored procedure?
i got error message saying that procedure "login" requires a @username when i tried the following:
Dim username As String = txtUserName.Text
Dim password As String = txtPassword.Text
Dim ocm As SqlClient.SqlCommand
ocm = New SqlClient.SqlCommand("Login", SqlConnection1)
ocm.CommandType = CommandType.StoredProcedure
Dim pusername As SqlClient.SqlParameter
pusername = New SqlClient.SqlParameter("@Username", SqlDbType.VarChar, 50)
pusername.Value = username
ocm.Parameters.Contains(pusername)
Dim ppassword As SqlClient.SqlParameter
ppassword = New SqlClient.SqlParameter("@Password", SqlDbType.VarChar, 50)
ppassword.Value = password
ocm.Parameters.Contains(ppassword)
SqlConnection1.Open()
Dim dr As SqlDataReader = ocm.ExecuteReader()
If dr.GetValue(-1) Then
errormsg.Text = "invalid userid and/or password."
Else
Dim pid As SqlClient.SqlParameter
pid = New SqlClient.SqlParameter("@customerid", SqlDbType.VarChar, 50)
ocm.Parameters.Add(pid)
Response.Redirect("home.aspx")
End If
SqlConnection1.Close()
or is there a better way to do this?
Laine
|
|
|
|
|
Laine
I would have expected something like the following (note that I don't normally work with VB.NET, and I haven't tested the following):
ocm = New SqlClient.SqlCommand("Login", SqlConnection1)
ocm.CommandType = CommandType.StoredProcedure
<br> <br>
'Input parameters @Username and @Password ...
pusername = ocm.Parameters.Add("@Username", SqlDbType.VarChar, 50)
pusername.Value = username
ppassword = ocm.Parameters.Add("@Password", SqlDbType.VarChar, 50)
ppassword.Value = password
<br> <br>
'Output parameter @CustomerID ...
pcustomerid = ocm.Parameters.Add("@customerid", SqlDbType.VarChar, 50)
pcustomerid.Direction = SQLClient.ParameterDirection.Output
<br> <br>
'Return value ...
preturnvalue = ocm.Parameters.Add("@ReturnValue", SqlDbType.Int)
preturnvalue.Direction = SQLClient.ParameterDirection.ReturnValue
<br> <br>
'Execute command (note that no data is returned)...
ocn.ExecuteNoQuery()
<br> <br>
'Check the outcome (I used ToString to avoid explicit casting) ...
if preturnvalue.Value.ToString() = "1" then
customerid = pcustomerid.Value.ToString()
end if
I was unsure that all of your parameters should be varchar(50). I would have expected customerid to be an integer.
I hope this helps.
Andy Harman
|
|
|
|
|
pcustomerid = ocm.Parameters.Add("@customerid", SqlDbType.VarChar, 50)
oopss.. that was an error(due to cut & paste).. i forgot to change that.. it's suppose to be Int.
ok Andy.. thanks a lot for ur help. i'll try this out asap..
Laine
|
|
|
|
|
Hi,
I have a SQL SERVER 2000,
I have a Database ACCESS 97 ( db.mdb)
I use Entreprise manager for link the DB ACCESS 97 by linked servers to SQL2000.
The only choice for PROVIDER NAME I have Microsoft Jet 4.0 OLE DB but for ACCESS 97 it is not good.
I don't have the possibility for chabge the DATABASE ACCESS 97 with other.
If anyone can help me or suggest a solution. Please contact me.
Best Regards
youssef
|
|
|
|
|
Does the OLE DB Driver for ODBC drivers work?
Dave
|
|
|
|
|
Hi, all:
I have WinForm app to edit MS Access DB. On the dataset I have serveral Master-detail tables( the Relation is edited in the schema editorat design time, not runtime). The Master table is databind to a datagrid and detail tables are databind to either datagrids or a set of textbox controls. When I first load ( fill) the data, everything works fine and textboxes display the correct data. When I change selections in the master table, the textboxes change accordingly. Now, when I REOPEN the MDB file, the the tables which databind to the datagrid works as before, but the table that bind to a set of Textboxes do not have any data displayed. I can NOT figure it out what is wrong. I appreciate any help/insight you have. Thanks very much. The following is the code I used to bind the data after I open and fill the dataset: dsMain1
***************
textBox1.DataBindings.Clear();
textBox1.DataBindings.Add(new Binding("Text", dsMain1, "MyMaster.MasterToDetail.name"));
textBox2.DataBindings.Clear();
textBox2.DataBindings.Add(new Binding("Text", dsMain1, "MyMaster.MasterToDetail.Title"));
textBox3.DataBindings.Clear();
textBox3.DataBindings.Add(new Binding("Text", dsMain1, "MyMaster.MasterToDetail.Company"));
***************
Dion
|
|
|
|
|
Hello guys,
I have a problem with my application.
I'd like to execute the store procedure with a datetime parameter.
It'll seem easy do it but in my case I have to convert a DBTIMESTAMP type from VC++ application.
So I need to know how pass the SQL datetime parameter to my SP having the DBTIMESTAMP value in my Server application.
Can anyone help me???
Thanks in advance for your support!!!;)
Andrea
|
|
|
|
|
Can only tell you how i do it with ODBC. There are Escape-Sequences, i.e. '{d 26-03-02}', the driver will convert it to a date your DB can understand.
dirk
|
|
|
|
|
Hi Dirk,
thanks in advance for your support
Fortunatly I resolved my problem!!
Simply instead of pass the datetime value to my SP,I pass the TCHAR string and inside the SP use the CONVERT function.
In this manner in the C++ side (server) I can pass simply a BSTR parameter.
Bye
Andrea
|
|
|
|
|
Hi all
I m trying to learn VC++ database prog. in this regard i need help if
someone can create a small sample app as follows:
> IDE = VC++ 6
> Using ADO DSN less connection (Provider='' user='' password='' data source='')
> db contains two tables (table_a, table_b)
>each table contain 3 fields of datatypes Numeric, Char, Date
>data must populate in Edit Boxes via SQL statement which stored behind a button
>other buttons Add Update Delete (Add/Update will not work automatically as in Access or VB)
>data validation of table_a thru table_b during Add/Editing
I will be very grateful. This will help alot to understand VC database programing.
|
|
|
|
|
|
Hello,
Is their a Critical section function on store procedure? Or what is the counter part of CriticalSection of C in store procedure script?
|
|
|
|
|
What are you trying to do?
You can lock tables, etc... yourself if you want, but that could slow your whole architecture down.
Cheers,
Simon
"I ask candidates to create an object model of a chicken.", Bruce Eckel on interviewing programmers.
animation mechanics in SVG (latest pic 1) (latest pic 2)
|
|
|
|
|
Yeah, you should by default allow SQL to handle locking. If you concern is that all processes should complete or fail look at
BEGIN TRANSACTION
ROLLBACK TRANSACTION
COMMIT TRANSACTION
|
|
|
|
|
Hi,
I need to migrate an old database under Visual Fox Pro 3.0 to SQL Server 2000. I was looking around to got the connectionString for the olddbConnection object but I can't find anything.
I've tried to use the this one :
("Provider=MSDASQL; Persist Security Info=False;Data Source=Test")
but I got the following error message :
The .Net Data OLE DB Provider(System.Data.OleDb) does not support the MSDASQL Provider, Microsoft OLE DB Provider for ODBC Drivers.
Does anyone know how to read data from Fox Pro ?
Thanx
Sybux
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
