|
Hello,
I am developing a library system. I have 2 tables, book and student. The relationship is 1 student can have many books. I am having updating the book table when the student returns the book, the studentID (foreign key) will be cleared from the book table. I think this is the problem l am having. My code is listed below with the error description.
When the student returns the book, their studentID number should be cleared from the studentID number in the book table.
try<br />
{<br />
cnnReturnBook.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdb;Persist Security Info=False";<br />
<br />
OleDbCommand cmdReturnBook = cnnReturnBook.CreateCommand();<br />
cmdReturnBook.CommandText = @"SELECT * FROM BOOK<br />
WHERE RefNumber = '" + txtBookRef.Text + "' ";<br />
<br />
OleDbDataAdapter daReturnBook = new OleDbDataAdapter(cmdReturnBook);<br />
OleDbCommandBuilder cbReturnBook = new OleDbCommandBuilder(daReturnBook);<br />
<br />
if ( daReturnBook.Fill(dtReturnBook) == 1 )
{<br />
txtDateOut.Text = "";<br />
txtDateDue.Text = "";<br />
txtStatus.Text = "Available";
txtIDNumber.Text = "";
txtName.Text = "";<br />
txtSurname.Text = "";<br />
<br />
dtReturnBook.Rows[0]["Status"] = txtStatus.Text;<br />
dtReturnBook.Rows[0]["DateOut"] = txtDateOut.Text;<br />
dtReturnBook.Rows[0]["Remarks"] = txtRemarks.Text;<br />
dtReturnBook.Rows[0]["StudentID"] = txtIDNumber.Text;<br />
dtReturnBook.Rows[0]["DateDue"] = txtDateDue.Text;<br />
<br />
daReturnBook.Update(dtReturnBook);
}<br />
}<br />
catch ( OleDbException ex )<br />
{<br />
MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK,MessageBoxIcon.Warning);<br />
}<br />
catch ( Exception ex )<br />
{<br />
MessageBox.Show(ex.ToString(),"Exeception has occurred",MessageBoxButtons.OK, MessageBoxIcon.Warning);<br />
}<br />
finally <br />
{<br />
cnnReturnBook.Dispose();<br />
}<br />
}
Error description
System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Student'.
At System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
At System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
Thanks in advance,
Steve
|
|
|
|
|
I have recently written a program in Visual Basic 6.0 that use ADO to talk to both a SQL Server database and also an MS Access 2000 database. They both use the same code to talk to the database.
When run on the development machine (XP Pro) the program can talk to the SQL Server database and MS Access database with ease.
When run on a virginal XP Pro machine the program can talk to the SQL Server database - SELECT, UPDATE, INSERT and DELETE all work fine. However, when trying to do the same on the MS Access database only SELECT and DELETE works? INSERT and UPDATE cause the program to freeze. No errors are thrown up, the program just locks up!
I have checked the folder / file permissions of the MS Access database to ensure I have the correct priviledges (I do) but cannot resolve the problem?!
I am a bit desperate here as the program needs to be delivered in the next week and this was the final stage of packaging!
Many thanks in advance!
- Pete -
|
|
|
|
|
Try a MDAC (2.8) upgrade on the virginal machine. Just search MDAC an microsoft.com.
|
|
|
|
|
dbms_utility.get_time
It seems like dbms_utility.get_time is not giving me the correct time...?
SET TIMING ON;
DECLARE
i NUMBER :=0;
time1 NUMBER;
time2 NUMBER;
BEGIN
time1:=dbms_utility.get_time;
LOOP
i:=i+1;
DBMS_OUTPUT.PUT_LINE(i);
EXIT WHEN i>1000;
END LOOP;
time2:=dbms_utility.get_time;
DBMS_OUTPUT.PUT_LINE(time1);
DBMS_OUTPUT.PUT_LINE(time2);
DBMS_OUTPUT.PUT_LINE('time elapsed in ms: ' || (time2-time1) /100*1000 );
END;
The output of the above script:
...
...
997
998
999
1000
1001
862757
862757
time elapsed in ms: 0
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.25
*** It seems like get_time is not returning the correct time... What's happenning? ***
THANKS in advance!
Norman Fung
|
|
|
|
|
dbms_utility.get_time will only give you resolutions of 1/100 second. try to use TIMESTAMP instead.
dirk
|
|
|
|
|
Can't figure out datasets to save my life. I like the idea of having a little relational database at my fingertips that can keep track of changes and update the datasource automatically. I can get my head around that part. What I can't figure out is how to get data in and out of a dataset. For instance I need to read in data from three separate tables in my database into a dataset. All the documentation I have read only deals with one table. can't figure out how to read in multiple tables without using multiple dataadapters/datacommands. Not to mention that I already have to deal with three sets of objects. One for OleDb one for Sql and One for ODBC. The application must be able to work with Many different providers. It's already driving me up the wall that I have to do everything three different ways everytime I do anything. It's making my code a little long winded. Now I have to implement these blasted datasets and I don't want to have 9 separate dataadapters 9 separate datacommands and 3 separate connection objects. Can't find any good documentation, I have many books and have been searching the internet for days. About ready to tear what's left of my hair out. Can anyone explain how to fill and update datasets containing multiple tables in a simple way that a stupid dummy like me can understand? Thanks.
|
|
|
|
|
Use stored procedures (if you can, not all DBMS support them).
CREATE PROCEDURE dbo.MyProc
@SomeKey int
AS
SELECT * FROM TableA WHERE Id = @SomeKey
SELECT * FROM TableB WHERE Id = @SomeKey
SELECT * FROM TableC WHERE Id = @SomeKey
GO Then on the .NET side use
SqlConnection conn = new SqlConnection(theConnectionString);
SqlCommand cmd = new SqlCommand("MyProc", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapeter da = new SqlDataAdapter(cmd);
da.Fill(theDataSet);
You might find this code generation tool useful: http://www.dotnetnuke.dk/Default.aspx?tabid=64[^] It is quite simple, but there is an offline version you can download, if you are willing to pay for it. Even if you don't use the code it generates, it gives a great example of how to build your data abstraction layer clearly and efficiently while dealing with multiple database systems. IMO.
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
> Use stored procedures (if you can, not all DBMS support them).
Yea, that's one of the major problems. The application absolutly must be able to use MySQL in addition to Access (we use jet for standalone versions) and SQL Server/MSDE. We market to many elementary/middle schools and we offer MySQL as a low cost alternative to MS SQL Server. Most elementary/middle schools can't afford SQL Server and use too many concurrent connections for MSDE to handle. I don't think MySQL supports stored procedures. I could be wrong hovever. I've never messed with stored procedures and a lot of the code examples I can find on this subject use them, so it makes it that much more confusing to me. In most of my code I just use datareaders and datacommands to interact with the database. I've been told that this is "doing it the hard way". But, it seems to work just fine for what I am using it for. Now I am doing something else that lends itself nicely to using datasets and I am having a heck of a time figuring it out.
I'ts making me feel really dumb. Although I did teach myself C# and VB.Net using only books and no help from anyone and the only programming experience I had prior to that was vb6 and a tiny bit of C++/MFC, so I figure I'm not totally brain dead. What I really need is for somone to sit down with me and look at my code and tell me what I am doing wrong and how to implement datasets into this class I am working on. Unfortunatly, the only other programmer I know is my boss and he knows nothing of .NET only C++/MFC.
Dave is frustrated!
|
|
|
|
|
David Bliss wrote:
In most of my code I just use datareaders and datacommands to interact with the database. I've been told that this is "doing it the hard way".
I think what these people are saying is just rubbish. DataReaders are just doing it differently. If you access the data for quick throw away results, or are going to store it internally in a format other than in a DataSet they are an excellent choice. Lots of people blindly load data into DataSets and then copy it to where they need it and end up with extra copies of data and really crappy inefficient data access. Like Scotty said in Star Trek V "How many times do I have to tell ye! The right tool for the right job!". From my experience DataReaders have been more appropriate than DataSets. (But I do a lot of data manipulation tasks and not a lot generally gets to the User Interface)
If you are using DataGrids and the link DataSets can work really well - Having said that, you can use a DataReader as the source in many cases and it works just as well (if you just need display only access). If you need random access to the records once they are client side then DataSets are quite useful.
Also, the DataAdapter just uses DataReaders and Commands to get the information in and out of the database anyway. So, you're still using them, you're just one layer of abstraction further away.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
That's what I always thought and that is why I've never tried messing with them until now. Datasets always seemed over redundant to me. Why have a copy of my tables, they are already in the database!?
I don't understand how they can make anything easier if 400 pages of your avarage ADO.NET book is dedicated to datasets (and their associated classes) and only about 10 pages or less on datareaders. Datareaders and Datacommands are very straight forward and easy to understand and use. Using datasets is just overcomplicating things. Also, I've never bound anything to anything and I don't see the need.
I thought that maybe insted of using datamembers inside my class, I could just use a dataset and have properties that access the dataset directly.
I'm giving up at this point. I'm just going to do it a different way. I've wasted almost three days on this now. I wanted to use datasets because they can keep track of changes and what has been deleted, then do the appropriate things with the data source when updating. This is all well and good, but it seems to me like there is just as much work involved with using the datasets as it is to just keep track of that stuff myself. Maybe if I was able to use stored procedures across the board It would be a more viable option.
Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters.
Thanks.
|
|
|
|
|
David Bliss wrote:
Also, I wanted to use datasets because, I guess you can store strings in the database and not have to worry about special characters like ' and ;. Is this true? Right now I'm having a hell of a time with storing strings containing special characters.
You should use parameterised queries. The syntax is slightly different depending on the database. SqlServer uses named parameters, while Access uses placeholders and you must supply the parameters in the same order that they appear in the query.
Here is an example using Sql Server
SqlCommand cmd = new SqlCommand("SELECT * FROM TableA WHERE ColumnA = @ParameterA AND ColumnB = @ParameterB");
cmd.Parameters.Add("@ParameterA", "David's Data");
cmd.Parameters.Add("@ParameterB", "String with ; semi-colon in it");And in Access I think it is [disclaimer - I don't use Access much so this may be wrong]
OleDbCommand cmd = new OleDbCommand("SELECT * FROM TableA WHERE ColumnA = ? AND ColumnB = ?");
cmd.Parameters.Add("David's Data");
cmd.Parameters.Add("String with ; semi-colon in it");
I hope this helps.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Hmmm, I've heard of this before.
OK, pain in the butt changing syntax between providers, but doable. Does this work with ODBC/MySQL? If so, we might have something here. I'll have to look into this more deeply.
Thanks.
|
|
|
|
|
David Bliss wrote:
pain in the butt changing syntax between providers
It just depends on what the back end database supports.
David Bliss wrote:
Does this work with ODBC/MySQL?
Should do.
If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks[^]
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
> Should do.
I'll have to check into it. Lord, I hope so. Keeping my fingers crossed.
> If you want another reason to implement parameterised queries then you should know that it helps prevent SQL Injection Attacks
Yea, I know about those. Although, In my case I doubt I would have to worry about that in an elementary school setting, but you never know I suppose. Also, This is not a web based product.
|
|
|
|
|
Another idea. I'm not sure if all databases support this (I think it is part of the ANSI SQL92 standard - but it may just be a Microsoft thing [sorry, that's not very definite])
You can supply semi-colon delimited SQL Commands.
SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC That might help, but I'm not sure about the update/insert/delete part of the data adapter.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Yea, I already thought of that. Problem is, I need to use data returned from the second select statement to create the third. So that doesn't work.
Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database? That just seems wacky.
Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple?
See, the whole dataset concept just dosen't make any sense to me.
|
|
|
|
|
David Bliss wrote:
Or, wait a minute "SELECT *" am i just supposed to get a copy of the entire damn database?
No, no... I'm just being lazy when I'm typing my reply. And remember your WHERE clause to filter the data.
It is always best to specify a column list in a query. If you do that and you add columns to the table it won't affect pre-existing code which might expect a certain number or order of columns.
David Bliss wrote:
Is that how you are supposed to use datasets? Copy the entire database in its entirety!? Holy crap, what if there are thousands of records and you only need to deal with a couple?
 I'm just thinking of one table in particular that I'm working on. Data is being generated at a rate of several million rows per year.
In SqlServer at least SELECT TOP xxx where xxx is the number of rows you want back is very useful.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
I thought not.
Yea, Yea I'm fairly familiar with SQL syntax and dealing with databases in general. Just not stored procedures, paramaterized querys or anything having to do with datasets or databinding.
|
|
|
|
|
Hye..how to write the code to check hardware status whether is function properly or not?
|
|
|
|
|
hi, when i execute a procedure in asp, the procedure only make 5 of 8 elements of a bucle, and if i execute this proc in query analizer or with a job the procedure make all perfectly.
Anyone could help me plz?
the procedure is:
CREATE PROCEDURE [guarda_navegacion]
AS
declare @cadena as nvarchar(500)
declare @cadena2 as nvarchar(500)
declare @cadena3 as nvarchar(500)
declare @resultado as nvarchar(1000)
declare @pagina as nvarchar(1000)
declare @npag as int
set @npag=1
declare Cnav cursor local for
select urlpag from navegacion order by urlpag asc
open Cnav
fetch next from Cnav into @pagina
while @@fetch_status=0
begin
if left(@pagina,4)='http'
begin
set @cadena='wget --restrict-file-name=nocontrol "'+@pagina+'" -O "d:\Inetpub\intranetdev\capturaimg\archivos\resultado'
set @cadena2=@cadena+cast(@npag as nvarchar(10))
set @resultado=@cadena2+'.txt"'
exec master..xp_cmdShell @resultado
print @resultado
set @npag=@npag+1
print cast(@npag as nvarchar(10))
end
fetch next from Cnav into @pagina
end
close Cnav
deallocate Cnav
select top 1 * from navegacion
GO
|
|
|
|
|
Have anybody use on your program? i`m having difficulties on it.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I know this means that my recordset does not convert properly with the SQL table.....but how do I go about finding out what field is not right?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Can anyone help me determine why I am getting the error "Object must implement IConvertible" when I execute the stored procedure? Below is the code. I have checked the stored procedure, database table column datatype and the sqldbtype and they are identical. So I am a little lost here. Any assistance is greatly appreciated.:
objConn.open
objCom = New SqlCommand("UPLOAD_A", objConn)
objCom.CommandType = CommandType.StoredProcedure
With objCom.Parameters
.Add("@col1", SqlDbType.Decimal, 9)
.Add("@col2", SqlDbType.Decimal, 5)
.Add("@col3", SqlDbType.VarChar, 6)
.Add("@Count", SqlDbType.Int)
End With
With objCom
.Parameters("@col1").Value = dt.Columns(0)
.Parameters("@col1").Precision = 11
.Parameters("@col1").Scale = 2
.Parameters("@col2").Value = dt.Columns(1)
.Parameters("@col2").Precision = 5
.Parameters("@col2").Scale = 4
.Parameters("@col3").Value = dt.Columns(2)
.Parameters("@Count").Direction = ParameterDirection.Output
End With
If objCom.ExecuteNonQuery() > 0 Then
'uploaded records
Dim iCount As Integer
iCount = objCom.Parameters("@Count").Value
Console.WriteLine(iCount)
Console.ReadLine()
Return True
Else
Return False
End If
|
|
|
|
|
cooldev wrote:
why I am getting the error "Object must implement IConvertible" when I execute the stored procedure?
So, do you get this when you ExecuteNonQuery(), or after that? What line does this error occur?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
This error is thrown on the ExecuteNonQuery() line.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
