|
Sorry, could not think of a better subject line for this. I need an int stored in my table that is sequential, but will fill in any missing numbers. By this I mean if I have 4 records, these will be numbered 1,2,3,4. If I delete row 3, the next row to be inserted should use row 3. I just need some advice on how to do this.
|
|
|
|
|
I never code this style but this is what I just think and express my concept to you.
toticow wrote:
if I have 4 records, these will be numbered 1,2,3,4. If I delete row 3, the next row to be inserted should use row 3.
First make sure that you have have the table structure and the first field of your table is set to number (data type) and order it by ascending. When you delete any record from your database, you have to requery your table and order the first field. Then you have to count the total amount of your record in the database, after that make a loop from the first row to the second, third... until the end of row (the amount of loop process is base on the total number of record in the database). And in the each loop process you have to change the value of the first field to match the amount of loop proces. This might help you to solve the problem, if you still have the problem I will try to do it and post the code for your.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi, firstly I agree with you about coding in this style. I always use auto numbers. This table in fact has one, but this other id is actualy for a memory location. We have a device with a limited amount of memory, that can only be written to by memory location. This table will always be small, less than 100 records, but deletions and additions will occur, which prevent me using the primary key. I am probably wrong, but I thought that this would be the best way to do it. I am in the process now of trying your suggestion. Thank you
|
|
|
|
|
You could create a second table which contains a list of deleted numbers. If the table is empty the the next number you use is MAX(numberColumn)+1.
In order to populate the deleted numbers table you can create a delete trigger on the original table and insert the relevant row. When you are inserting a new record just take a number from the deleted numbers table and use that - remember to remove it from the deleted numbers table. You could create an insert trigger to do this for you.
However, this will still leave gaps in your sequence if you delete a row and don't insert one afterwards.
Do you want to know more?
|
|
|
|
|
Hi, I use the following statment in VB.Net to add new record in my database:
INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'abc')
and delete the record from the database using the following statement:
DELETE FROM tblSellTo WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'
Both of these statement are working fine!!!
But my problem is occure when I want to update the record. Suppose that I have Product ID =1 and customer name is 'abc' and I want to update the information to Product ID =1 and customer name is change to 'wkl' and I use the following code:
INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'wkl') WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'
But it is not success, it display an error message that missing character ; at the end of sql statement. But when I try to change the code to:
INSERT INTO tblSellTo (ProdID, CusName) VALUES(1,'wkl') WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc';
It still the same, it is not working. Does anyone have any idea about solveing this problem? 
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Bingo, now it is working. I use the following statement:
UPDATE tblSellTo SET tblSellTo.ProdID =1, tblSellTo.CusName ='wkl' WHERE tblSellTo.ProdID =1 AND tblSellTo.CusName LIKE 'abc'
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
I have an access db with a boolean field called manager.
When my vb form loads it populates a combo box with user names from the same table.
As I select a user I need to have the appropriate radio button checked, either manager if true or employee is false, based on the value of manager in the DB
Any help would be appreciated
|
|
|
|
|
skicrud wrote:
When my vb form loads it populates a combo box with user names from the same table.
If it is possible, please post the structure of your table on this forum. I used to do the similar project like you too and my solution is add the other field beside the user name. The field could be boolean (Yes/No) or list of text to describe the function of each people. Remember to select the user name and function of the people in to recordset. Then display only the name in the combobox and the other field is hidden and just analayse only for your radio button.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Mon problème est de créer une transaction sous ACCESS 2003 en VB comprenant deux requêtes:
- une requête de suppression d'un enregistrement dans une table distante liée par un pont ODBC
- la suppression du même enregistrement dans la table locale liée au formulaire sur lequel est placé le bouton qui lance la routine suivante:
Un warning s'affiche à l'éxécution de la suppression de l'enregistrement courant:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
En répondant NON à ce warning : "voulez vous supprimer...", une erreur se produit(jusque là tout est normal),
Alors le wks.Rollback devrait restaurer la suppression effectuée (rst.Delete) or il n'en est rien
Ce qui désynchronise mes deux bases.
pourquoi le rollback ne s'effectue pas?
voici mon code:
'macro du formulaire lié à la table locale membres
Private Sub ButDel_Click()
'** Bouton Supprimer
Dim TransStarted As Boolean
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim rst As DAO.Recordset
'Creation du workspace
Set wks = DBEngine(0)
'début de la transaction
wks.BeginTrans
TransStarted = True
'base courante
Set db = wks(0)
'selection de l'enregistrement à supprimer dans la table fiches_annuaire(table liée ODBC)
sqltext = "select * from fiches_annuaire where code = " & Me.ID
Set rst = db.OpenRecordset(sqltext, dbOpenDynaset)
On Error GoTo ErrDel
rst.MoveFirst ' goto the first record that needs to be inserted
'suppression de l'enregistrement(table liée ODBC)
rst.Delete
'supression de l'enregistrement dans la table liée au formulaire
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "base locale mise à jour", vbOKCancel
'on termine la transaction
If TransStarted Then
wks.CommitTrans
rst.Close
wks.Close
Set wks = Nothing
End If
TransStarted = False
GoTo endsub
ErrDel:
'PopupError
message = "Une erreur est survenue. Pour assurer la synchronisation des deux bases, aucune suppression n'a été effectuée " & vbCrLf & _
"Veuillez recommencer ultérieurement"
glyph = vbExclamation
Title = "Transaction annulée"
'on annule la transaction
If TransStarted Then
wks.Rollback
rst.Close
wks.Close
Set wks = Nothing
MsgBox message & "rollback?", glyph, Title
Else
MsgBox "pas de rollback!", glyph, Title
End If
TransStarted = False
GoTo sql_error_exit:
'Resume Next
sql_error_exit:
Exit Sub
endsub:
On Error GoTo 0
End Sub
|
|
|
|
|
i have a table on my database wich may have a duplicated ID numbers on the column ID so i'm using distinct to eleminate this duplication, but some of the data type of the columns are text or ntext so i'v failed on using distinct but i want to make this job by any way, can anyone help ?
|
|
|
|
|
If it is just duplicates in the ID number that you want to eliminate (for this I am assuming you mean eliminate from a select result rather than actually delete the records) then you might want to try something like:
SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT ID, MAX(TiebreakerColumn) as TiebreakerColumn
FROM MyTable
GROUP BY ID) AS SubQuery
ON SubQuery.ID = MyTable.ID
AND SubQuery.TiebreakerColumn = MyTable.TiebreakerColumn
It really depends on the data you have, especially being able to specify a "tiebreaker column" for the case when you want to remove the duplicates. The tiebreaker column must be unique within the ID and it must be aggregatable (By that that you can use an aggregation function that returns an actual value from the table, like MIN() or MAX(), but not AVG() or SUM())
Does this help?
Do you want to know more?
|
|
|
|
|
thanx for ur answer,
actually i don't write sql much, but i'm stucked in this problem so i'll tell u what exatly is the problem,
i have 2 tables (Table1 and Table2)
these 2 tables have a column named "KeyWords" with the same data type in both tables,
all i want to do is to select from the 2 tables and use the WHERE Clause to return a specific results, so i'v simply wrote the following
<br />
SELECT * from Table1,Table2 WHERE Table1.KeyWords LIKE '%test%' AND Table2.KeyWords LIKE '%test%'<br />
this works fine if the 2 tables returns any rows, but if any of the tables returns 0 rows then both will return 0 rows, all i need to do is to select from the 2 tables and use the where clause.
can anyone help ?
|
|
|
|
|
Can somebody help me with this error :
"Cannot use empty object or column names. Use a single space if necessary."
I am getting this error in a Select statement when a Particular column name is put into a Replace function.
Thanks
|
|
|
|
|
Whats the SQL you are submitting?
Store your favourite bookmarks online: my-faves.co.uk
|
|
|
|
|
Try to check the information from here[^], but if it is possible you should post the statement on this forum.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hello,
I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.
The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?
Thank you very much,
Dave
|
|
|
|
|
According to SQL BOL:
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_8rc5.asp[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Anyone here using this?
I have what is essentially a frozen database, (the live version is in SQLS2K) and I want a single program to be able to access it. I make heavy use of lookup tables (e.g. a text 'word' to a numeric ID, then look the ID up in a list to yield a list of matches), but my target machine is low on memory, so I don't really want to run MSDE on it, and I want the machine to work standalone.
From what I've read, once I've created and populated the database, it should do what I want, but I'd welcome comments.
(It's not quite readonly, since I save lists of results occasionally, and need to update one other table as well, but I can save those externally *as well* and then update the master later.
Does this sound feasible? I have all the SQL concerned (except that the word lookup is generated on the fly depending on input, since the number of words varies...)
Steve S
Developer for hire
|
|
|
|
|
Hi All,
I dont know whether this is actually possible but thought I post the questions anyway. Is it possible to have arguments for stored procedures derived from SQL Server Management API and not pre-configures in a separate table. Thanking you in advance.
N
|
|
|
|
|
Hi everone,
I am having trouble in updating the source database. I am using a dialog based application with a datagrid. I managed to add a number (01065017) to the first coloumn succesfully but could not update the source after the addition of this number. Any Idea why.
The following is the code:
m_pConn.CreateInstance(__uuidof(Connection));
m_pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;"
"Data Source=ADOTestDB.MDB",
_bstr_t (""),
_bstr_t (""),
adModeUnknown );
m_pComm.CreateInstance(__uuidof(Command));
m_pComm->ActiveConnection = m_pConn;
m_pComm->CommandText = "Select * From Student";
m_pReco.CreateInstance(__uuidof(Recordset));
m_pReco->CursorLocation = adUseClient;
m_pReco->Open ( (IDispatch *) m_pComm, vtMissing, adOpenStatic,
adLockBatchOptimistic, adCmdUnknown);
m_DataCtrl.SetCaption("Students");
m_DataCtrl.SetRefDataSource(NULL);
m_DataCtrl.SetRefDataSource((LPUNKNOWN) m_pReco);
m_DataCtrl.Refresh();
m_pReco->AddNew();
FieldsPtr pFields = m_pReco->Fields;
FieldPtr pStudentNo = pFields->GetItem("StudentNo");
ASSERT(NULL != pStudentNo);
CString ali = (char*)(_bstr_t) pStudentNo->Value;
ali.Format("01065017");
_variant_t vColumn, vValue;
vColumn.SetString("StudentNo");
vValue.SetString(ali);
m_pReco->Update(vColumn, vValue);
m_pReco->Update();
Thanx in advance
|
|
|
|
|
I have what I think is a fairly simple procedure, but its currently not working. I'm hoping someone here is an ADO.NET guru who has tried the same thing, and can help me out.  Here is the situation:
Process:
===============
I am developing a small app to post new, incomming data to an existing database. The database is a live, production database, and there is the possability that duplicate information may come through in the new incomming data. I've set up a processing database, where I insert the new data in temporary tables, and select and insert the data from the live database into other temporary tables. I post the new data to the same tables as the temp live data, perform some business logic, and then perform an update on the live database.
Setup:
===============
I am using ADO.NET with a single DataSet to get the data from the live database into the temp processing tables. I use the same dataset to insert the new data into other temp tables.
All the DataTables have primary keys configured.
There are DataAdapters configured for importing the live data, with proper DataTableMappings, each of which have proper DataColumnMappings.
There are DataAdapters configured for importing the new data, with proper DataTableMappings and DataColumnMappings.
The databases are both Microsoft Access databases, although I have tried with Microsoft SQL Server, with the same results.
Issues:
===============
When I execute the application, it imports the live data, imports the new data, and performs some basic business rules. After the rules are done, I need to update the live database. The primary keys on the DataTables filter out any duplicate data, so when I import the new data, only new information is added. The business logic changes some of the existing live data, as well as some of the new data. When this is all said and done, and I try to update the live database, it doesn't update properly. I've never tried to use a single DataSet with two different data sources before. This particular situation requires that both data sources be updated, inserted into, and selected from a couple times.
Question:
===============
How does one update #1 data source with data from #2 data source using a single DataSet, and be able to do the opposite, update the #2 data soource with data from #1 data source?
Thanks for any help, and I hope I have provided enough information.
|
|
|
|
|
I have this case:
I am working on a screwed up application in which arraylist are using for implementing layer isolation.
i.e., It is using some function to convert each and every values it return from the SQLDataReader into an arraylist and then pass it to the UI Layer.
I want to know that whether this will help in anyway or will cause an overhead to the application(remember the appn. is screwed up!!)
|
|
|
|
|
obymathew wrote:
I want to know that whether this will help in anyway or will cause an overhead to the application
This is the wrong way of decoupling layers:
1. This will add both complexity and overhead - you'll need a lot of casting on the UI layer, as ArrayLists store only objects.
2. Probably your data is being put on specific positions (e.g, al[1] is the customer name, al[2] your customer address, etc. This makes the application hard to debug, and change impact analysis very hard to do.
What I suggest:
If you still want or need to keep a strong isolation between these layers (e.g., the data or the UI layer can be used with other implementations) you can:
Create a 3rd assembly, which will define the interfaces that'll be used by both the data layer and the UI layer. From the data layer, return those interfaces, or arrays of those interfaces. To allow incremental changes, and to not disrupt everything when changing a method, provide a method on each interface that converts that data to an arraylist.
Before doing any changes, plan ahead a bit: make a backup (actually, you should use source control), create some basic unit tests and only then start working.
Don't forget that you may need to maintain two versions while you're converting things if your users need changes/bug corrections.
Yes, even I am blogging now!
|
|
|
|
|
Hello again buddies,
I wonder how can I work with just time section of a datetime or smalldatetime object ignoring its date section. wouldn't it be better to provide a "time" data type?
What I am looking for is to query records that a datetime column of them is in a specified time with no importance on the date
---
"Art happens when you least expect it."
|
|
|
|
|
Use DATEPART(). It is a bit of a mess if you want to do something like get me the records between 11:30 and 14:30 but it is doable.
Do you want to know more?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
