|
I have designed a MS Access 2000 Database which has three tables CUSTOMERS, BOOKINGS & HOLIDAYS to control data entry I have designed three vb.NET Forms. A user is able to SELECT, INSERT, UPDATE and DELETE records on each table.
CUSTOMERS & HOLIDAYS are working fine – no worries. The problem is with the INNER JOIN Table BOOKINGS. The tables are linked by the attribute Coding, the Pk in HOLIDAYS and the Fk in BOOKINGS.
HOLIDAYS table attributes are:
Coding (NUMBER). Pk
Out (DATE)
Return (DATE)
Airport (TEXT)
Country (TEXT)
Destination (TEXT)
Hotel (TEXT)
Rating (NUMBER)
Night (NUMBER)
Price (CURRENCY FIXED)
BOOKINGS table attributes are:
Ref (NUMBER)
CustID (NUMBER)
Coding (NUMBER)
Date (DATE)
Adult (NUMBER)
Child (NUMBER)
Deposit (CURRENCY FIXED)
Balance (CURRENCY)
Cost (CURRENCY)
The Error/Information message, which is just a list, is:
Parameter[0]’Airport’has no default value.
Following in the same syntax is: Country, Destination, Hotel, Night, Out, Price, Rating, and Return
The error message then changes slightly to 'Original_attribute name'.
Parameter[9]'Original_Coding’has no default value.
Then the following are listed as above: Original_Country, Original_Destination, Original_Hotel, Original_Night, Original_Out, Original_Price, Original_Rating, and lastly Original_Return. All the messages return - has no default value???
Note the error message appears on the BOOKINGS UPDATE, although all the attributes mentioned are in the HOLIDAYS table, also the errors are not returned in the order of the attributes in the HOLIDAYS table (see above).
Below is the BOOKINGS UPDATE Statement I have used:
command = "UPDATE BOOKINGS SET Ref = " & txtRef.Text _<br />
& ",CustID = " & txtCustID.Text _<br />
& ", [Coding] = " & txtCoding.Text _<br />
& ", [Date] = '" & txtDate.Text _<br />
& "', Adult = " & txtAdult.Text _<br />
& ", [Child] = " & txtChild.Text _<br />
& ", Deposit = " & txtDeposit.Text _<br />
& ", Balance = " & txtBalance.Text _<br />
& ", Cost = " & txtCost.Text _<br />
& " WHERE Ref = " & txtRef.Text & "" _
It looks to me like an Array problem or the way I have set up the attributes, but what is it and how do I fix it?
I have attached a copy of the actual error message, the Database and the relevant Form for anybody to look at
Any help would be appreciated, thank you in advance.
Regards jetski4419 
jetski4419
|
|
|
|
|
Are you sure you are running the right SQL statment? Your last line ends with an underscore, indicating that the command continues, is that right? If so what is the remainder of the command? Can you show how you are running the command in the database? (There may be something there that is amiss)
Also, you are placing the contents of the user interface controls directly in to the SQL Statement. This is extremely bad practice as it is the source of security flaws in your application. You should read SQL Injection Attacks and Tips on How to Prevent Them[^]
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thank you for your reply.
Sorry about the confusion, there is no underscore at the end of the UPDATE Statement, I must have typed it in when I was sorting the message out prior to sending it.
The full code for the update stament is shown below:
Private Sub btnBookingUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBookingUPDATE.Click<br />
<br />
If MessageBox.Show("Do you wish to update the record", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.Yes Then<br />
End If<br />
<br />
Try<br />
'Open Connection<br />
OleDbConnection1.Open()<br />
Dim command As String<br />
<br />
'set up an SQL update<br />
command = "UPDATE BOOKINGS SET Ref = " & txtRef.Text _<br />
& ",CustID = " & txtCustID.Text _<br />
& ", [Coding] = " & txtCoding.Text _<br />
& ", [Date] = '" & txtDate.Text _<br />
& "', Adult = " & cboAdult.Text _<br />
& ", [Child] = " & cboChild.Text _<br />
& ", Deposit = " & txtDeposit.Text _<br />
& ", Balance = " & txtBalance.Text _<br />
& ", Cost = " & txtCost.Text _<br />
& " WHERE Ref = " & txtRef.Text & ""<br />
<br />
OleDbDataAdapter1.UpdateCommand.CommandText = command<br />
lblSQL.Text = command<br />
<br />
'do the update<br />
OleDbDataAdapter1.UpdateCommand.ExecuteNonQuery()<br />
Catch exceptionObject As Exception<br />
MessageBox.Show(exceptionObject.Message)<br />
Finally<br />
OleDbConnection1.Close()<br />
<br />
End Try<br />
End Sub
I see you point about placing the contents of the user interface controls directly into the SQL Statement, I'll do that in future and thank you for the link.
Regards
jetski4419
|
|
|
|
|
I am using .net 2003. I created a webform that displays data in a datagrid. I want to refresh this page every 5 minutes. This webform is for internal use only.
I added a timer control to my webform. I set the interval to 300000 (5 Mins). After reading the help topic on timers I realize I have to create a event handler to refresh the data (call page_load()?) when the timer has elapsed. However I have never done this so I am not sure how to procede.
Thanks for all the help
NewbieDave
|
|
|
|
|
|
hello my Article updated by myself if wants to benefit search for author Ahmad or search for xml guest book
Ahmed Erarslan
MCAD,MCDBA,MCP
MCSD.NET
|
|
|
|
|
I have one table having two primary keys here if i created one unique index and now i want to create another nonunique index taking another columns. now my questions are :
1) can i take the columns which i already included in unique index, to build nonunique index?
2) If i take these columns then what that cost on performance of query wheather time required to run query is more or less or no effect?
T.I.A
Shashank
|
|
|
|
|
under2811 wrote:
I have one table having two primary keys
You cannot have two primary keys. Unless you mean that you have a primary key with two columns.
You can have as many unique and non-unique indexes as you wish using what ever combination of columns you wish (including using the same column in two, or more, indexes). As I recall, there is a maximum of 16 columns per index.
under2811 wrote:
If i take these columns then what that cost on performance of query wheather time required to run query is more or less or no effect?
The only impact on performance by adding extra indexes is when you do an INSERT, UPDATE and DELETE commands because these will result in the indexes changing. Obviously SELECT statements get faster because they can use the indexes to get at the data they need more efficiently.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello friends!!
Here I am giving you some details of table and indexes
CREATE TABLE TEST_EMP (
SSN VARCHAR(9) NOT NULL,
CCODE VARCHAR(4) NOT NULL,
GRADE VARCHAR(2) NULL,
CONSTRAINT GRADE_SSN_CCODE_PK
PRIMARY KEY CLUSTERED (SSN, CCODE),
EMPID VARCHAR(5) NOT NULL,
EMPNAME VARCHAR(10))
--------------------------------------------
NOW I WANT TO ADD TWO COLUMNS MORE WHICH FREQUENTLY USED IN JOIN CONDITION for select & insert i.e.
AUTONUMBER int
MODIFIEDDATE DATETIME
NOW I WANT TO CREATE ANOTHER Index (IX_TEST_EMP)
FOR COLUMNS
SSN
CCODE
AUTONUMBER
MODIFIEDDATE
so including that two columns(SSN and CCODE) which already have index (GRADE_SSN_CCODE_PK),
will that cause more cost on query or that doesnot matter?
and if I exclude these two columns(SSN and CCODE) then wheather my select and insert clause
will cause same cost as when i include these two columns(SSN,CCODE)?
T.I.A
Shashank
|
|
|
|
|
I want to create strong passwords. I read an article on the Microsoft website a while ago where they said that strong passwords consisted of upper case and lower case letters, numbers, and symbols. And the symbols they mentioned are ` ~ ! @ # $ % ^ & * ( ) _ + - = { } | [ ] \ : " ; ' < > ? , . /
But some of these symbols can be used in SQL statements. Which of these symbols are not advised to be used as part of your password?
Thanks
|
|
|
|
|
|
Hi,
I need to alter column size of particular table with constraints, table which has primary key and referred to another table.
I have used like this "alter table my_table_name alter column my_column_name varchar(50)"
It shows me error as
Server: Msg 5074, Level 16, State 8, Line 7
The object 'PK_DiagnosisSummary' is dependent on column 'my_column_name'.
Server: Msg 5074, Level 16, State 1, Line 7
The index 'my_column_name' is dependent on column 'Code'.
Server: Msg 5074, Level 16, State 1, Line 7
The object 'FK__Diagnosis__DiagC__51DA19CB' is dependent on column 'my_column_name'.
Server: Msg 4922, Level 16, State 1, Line 7
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.
Let me know how to alter field size of this table with constraints and indexed.
Be simple and Be sample.
|
|
|
|
|
|
Hi,
I am struggling in using dataset or traditional dataReader in ADO.Net.
It seems to me that if I use the dataset (direct bind it to UI), then the dataset will replace the object model layer, so it seems only good for simple application development without much object model logic.
If I insert a object layer
above the dataset, then it seems no difference from the traditional
approach by using DataReader with SQL query. Then what is the goodness
of dataset ?
And if I use dataset, the cached data by read/write in the dataset will be lost if the application hang, so the cached data by read/write should be restricted within the scope of one transaction.
Are all my perceptions on using dataset above right ? Any comments?
Thanks
|
|
|
|
|
I've create a software that uses microsoft access 2003, better it manage with ado.net a .mdb database.
To distribuite that application have to have got the final user Microsoft Access to get working the applicationo or it is not need?
|
|
|
|
|
No
the clients only need JET driver on their machines (can be obtained freely from microsoft).
|
|
|
|
|
Hi
I'm having trouble installing SQL CE. It is installed, and i can see the sql query analyzer icon, once i run that. The normal SQL CE screen comes up, but there's no tools options at the bottom. Have I installed in wrong or is it something else.
Thanks
Rossi46 -> The Doctor 
|
|
|
|
|
Now that i have my database connected and my acces page doing what it needs to do...I am ready to introduce the world to my database...But how do i get the User Interface to be up all the time without building it over and over...i just want to pull it up type the entry in and update my database...but for now i can only do it on the computer i made it on...You see i am ready to put it on the computer for everyday use.......
|
|
|
|
|
Hey there,
I have two datasets with exactly the same tables and exactly the same columns. I want to merge the second dataset into the first dataset, but i want to maintain the original row version of the first dataset.
ds1.Merge(ds2, true, MissingSchemaAction.Ignore) only changes the Original Row version of ds1's rows and ds1.Merge(ds2, false, MissingSchemaAction.Ignore) changes both Original AND current value... is there no way to change only the Current version of a row??
I cannot ds2.Merge(ds1) because i have a modified dataset class which inherits datasets but also maintain a connection to a database. ds1 and ds2 are both connected to diffrent databases and changing the connection after merge doesn't work ;-(
|
|
|
|
|
thinking one step beyond solves the problem, which means I've already got a solution...
for this interested, you need two merges...
ds2.merge(ds1, true, MissingSchemaAction.Ignore)
ds1.merge(ds2, false, MissingSchemaAction.Ignore)
|
|
|
|
|
I have written my own datatable merge function. It merges 2 datables, adding ew clumns to the source if those dont exists, and also append the new rows to the source. Hope my code is of help to you?
Michiel Erasmus
MaxCredible B.V.
Nieuwezijds Voorburgwal 44
1012 SB Amsterdam
Postbus 11079
1001 GB Amsterdam
Postbus 11079
1001 GB Amsterdam
T: +31(0)20-3449070
F: +31(0)20-3449071
I: http://www.maxcredible.com
<br />
#region MergeDataTables(), merge 2 datatables.<br />
<br />
public DataTable MergeDataTables(DataTable dtInput, DataTable dtOutput)<br />
{<br />
return MergeDataTables(dtInput, dtOutput, false);<br />
}<br />
<br />
public DataTable MergeDataTables(DataTable dtInput, DataTable dtOutput, bool addNewRowsToMerged)<br />
{<br />
if(dtInput == null)<br />
{<br />
return dtOutput;<br />
}<br />
<br />
try<br />
{<br />
if(dtOutput == null)<br />
{<br />
dtOutput = dtInput.Clone();
<br />
if(dtOutput == null)
{<br />
return dtOutput;<br />
}<br />
}<br />
<br />
try<br />
{<br />
foreach(DataColumn dc in dtInput.Columns)<br />
{<br />
if(dtOutput.Columns[dc.ColumnName] == null)<br />
{<br />
dtOutput.Columns.Add(dc.ColumnName, dc.DataType);<br />
}<br />
}<br />
}<br />
catch<br />
{<br />
string strTijdelijk;<br />
strTijdelijk = "dummy";<br />
}<br />
<br />
foreach(DataRow drInput in dtInput.Rows)<br />
{<br />
DataRow drOutput;<br />
<br />
if(dtOutput.Rows.Count == 0 || addNewRowsToMerged)<br />
{<br />
drOutput = dtOutput.NewRow();
}<br />
else<br />
{<br />
drOutput = dtOutput.Rows[0];<br />
}<br />
<br />
drOutput.BeginEdit();<br />
<br />
foreach(DataColumn dcInput in dtInput.Columns)<br />
{<br />
try
{<br />
drOutput[dcInput.ColumnName] = drInput[dcInput.ColumnName];
}<br />
catch<br />
{<br />
string strTijdelijk;<br />
strTijdelijk = "dummy";<br />
}<br />
}<br />
<br />
drOutput.EndEdit();<br />
if(dtOutput.Rows.Count == 0 || addNewRowsToMerged)<br />
{<br />
dtOutput.Rows.Add(drOutput);<br />
}<br />
dtOutput.AcceptChanges();<br />
}<br />
dtOutput.AcceptChanges();<br />
}<br />
catch(Exception ex)<br />
{<br />
throw new Exception(ex.Message + "; Stacktrace=" + ex.StackTrace, ex);<br />
}<br />
<br />
return dtOutput;<br />
}<br />
#endregion<br />
met vriendelijke groet,
Michiel Erasmus
|
|
|
|
|
Ziet er op zich wel OK uit, maar denk dat het niet een oplossing was geweest voor mijn probleem. Ik heb een selectie van data die ik uit de database trek, op een laptop zet en zonder database connectie wijzig. Daana wil ik een sync doen van de data terug naar de db. Ik heb dan twee identieke datasets (qua structuur dan) alleen is de 'sql' dataset gevuld met oude data, en de 'nieuwe' dataset gevuld met de up-to-date data. Ik moet in de 'sql' dataset dan wel de gewijzigde rijden bijhouden (nieuwe, gewijzigde en verwijderde) om aan een adapter door te geven wat hij in mijn db moet wijzigen... moeilijk verhaal allemaal (waarschijnlijk omdat ik het onhandig uitleg) maar vooral door de AcceptChanges kan ik de wijzigingen niet meer terugvinden en ben ik dus alsnog de row states kwijt.. echter, de oplossing was al gevonden middels een dubbele merge..
|
|
|
|
|
When I set a password to my access db.
myCon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;Password=password");
myCon->Open();
I get a exception error.
"The workgroup information file is missing or opened exclusively by another user"
..but when I remove it, it open ok. What other param's I need to set in order to open a password protected access db?
|
|
|
|
|
if its the database password and not tied to specific users then try the following connection string
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & sFullPath & ";Mode=ReadWrite;Persist Security Info=False; Jet OLEDB:Database Password=" & sPassword
Where sFullPath is the full path and filename of the db and sPassword is the db password.
Jon
|
|
|
|
|
Guys/Gals,
I've not been able to find a "neat", reliable way to fill a DataTable with the results of a DirectorySearcher. I tried multiple variations of the available DataAdapters that I could find, but none seemed to be "adaptable"... (I made a funny!) 
Anyway, I'm trying to stay away from a direct LDAP call to AD, and use the DirectorySearcher/SearchResults. I wouldn't think that it would be too difficult. I'm sure there's some way that the Framework will let me do this.
Your assistance is greatly appreciated...
-Ted
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
