|
Good day.
I am a beginner in VB.NET. I have a problem that I used (TheTable.Rows.Count + 1) to generate the no. for the customer ID. If I deleted some of the records and then add a new records, the customer ID will not correct. This is because I am using TheTable.Rows.Count + 1, what I can do to solve this problem?
How do check the last customerID in the Customer table in SQL server? can this solve my problem? I am using ADO.net.
The code i am writing is :
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
SqlDataAdapter1.Fill(Me.DataSet11, "Customer")
Dim TheTable As DataTable = Me.DataSet11.Tables(0)
Dim aRow As DataRow = TheTable.NewRow()
txtCustID.Text = TheTable.Rows.Count + 1
ClearForm()
txtCustName.Focus()
end sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
Dim TheTable As DataTable = Me.DataSet11.Tables(0)
Dim aRow As DataRow = TheTable.NewRow()
aRow("CustID") = TheTable.Rows.Count + 1
aRow("CustName") = txtCustName.Text
aRow("CustAdd") = txtAdd.Text
aRow("Postcode") = txtPostcode.Text
aRow("City") = txtCity.Text
TheTable.Rows.Add(aRow)
SqlDataAdapter1.Update(Me.DataSet11, "Customer")
txtCustID.Text = aRow("CustID")
MessageBox.Show("Customer Was Successfully Added ", "VIP", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtCustID.Text = TheTable.Rows.Count + 1
ClearForm()
txtCustName.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
If anyone know how to solve this problem, please post.
Thanks in advance!
viv
|
|
|
|
|
Write a query to get Max(custId).
Select max(custID) from tblCustomer
What this will do is get the maximum value in the cust id column from the table and return it to you, just add 1 to it and show thas as your next custID.
Tarakeshwar
CCIE Q(Routing and Switching), MCSE Security
|
|
|
|
|
Wrong!!!
You cannot rely on the identity to always 'grow by one'
If you want to know the latest Identiit value of a table use:
IDENT_CURRENT('table_name')
If you want the Identity of the record you just inserted use:
SCOPE_IDENTITY( )
Wout Louwers
|
|
|
|
|
I guess, MAX thing will surely work in anycase.
And also, I heard, SQL Server looses the current identity value when server is restarted, I think it is in 6.0/or 6.5 version. After restarting, you need to run dbcc consistency check on the database to restore the identity values.
Bhaskara
|
|
|
|
|
I have sum DataSets and DataAdapters on my MainForm.
I configure and fill the datasets through the DataAdapters on the MainForm.
I want to use this configurated DataAdapters and DataSets in all my other form, without reConfiguration or refilling.
please guide to me.
thank you.
Hadi : hd_ali@yahoo.com
|
|
|
|
|
Write a common class. In that define your DataSet and the Adapter.
You could write a function called executeQuery in this class. So from the main form you can call this function and fill the dataset. You can call this function by creating a object of that class in any of your forms having the same namespace.
Tarakeshwar
CCIE Q(Routing and Switching), MCSE Security
|
|
|
|
|
Or
If you want these datasets to be available only for these two forms, write a constructor in the second form, which will accept dataset as a parameter. When calling the second form, send the dataset from the MainForm to Second form.
Bhaskara
|
|
|
|
|
I m using VC++ and Access as database
ti insert a Date it take following format...
db->ExecuteSQL ("INSERT INTO Tab values ('20-JUNE-1991') ");
But to delete a date it take #mon/dd/yyyy# format
db->ExecuteSQL ("DELETE from Tab WHERE [Tab].[date] = #7/21/1991# ");
i not under stand y the fornat differnce in delete there is a hash # ..
Now i want to fetch records between two dates
i m using following query
but it yields only one record .. but i have many record in database
if(!rs->IsOpen())
{
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * from Tab WHERE [Tab].[date] BETWEEN #6/25/1992# AND #6/21/1994# ");
int n =rs->GetRecordCount ();
if(n==0)
{
rs->Close ();
db->Close();
AfxMessageBox (" NO record found");
}
else
{
db->Close();
CString ss;
ss.Format ("%d" , n);
AfxMessageBox (" found no of records... " +ss);
}
}
plz rely me thanx
how can i get all records between two given dates .
|
|
|
|
|
The reason you don't need # for the insert is because the date is in DD-MMMM-YYYY format.
When you insert a date, it is preferable to use ISO format, which is YYYY-MM-DD or YYYY/MM/DD (I know, ISO is supposed to be YYYYMMDD, but it doesn't work with Access without the / or - ).
Here's an example on inserting date:
INSERT INTO tblTest(dtmDate) VALUES (#2004-06-17)
When you query data using daterange, also use the ISO format otherwise the DD part may be assumed as MM or vice versa.
Here's an example on specifying date range condition:
SELECT * FROM tblTest WHERE tblTest.dtmDate BETWEEN #2001-01-01# AND #2002-01-15#
Hope it helps. 
|
|
|
|
|
I tried it but .. again it is fetching only ONE record.
is there any problme of datatype. BCOZ in ACCESS
datatype is Date/Time .. is Time plays a pert in it
when u using only date ..
problm is still there ..
|
|
|
|
|
If you only have Date in your Date/Time column you don't need to specify the Time part.
I have tested the date range condition and it is supposed to return all date in between the specified range.
You might want to check whether the Day and Month of the date in the database is stored correctly, i.e. 01/06/2004 is 1 June 2004 not 6 January 2004.
Do some test with the data with date in between the range, e.g. with SELECT * WHERE dtmDate = #2004-06-01#
Hope it helps.
|
|
|
|
|
u wrote:
You might want to check whether the Day and Month of the date in the database is stored correctly, i.e. 01/06/2004 is 1 June 2004 not 6 January 2004.
My reply:
I think and i checked it is 6th January 2004....
Well if u have a query that chk the records between range of dates n return all ,,plz send to me at
zahid_ash@hotmail.com
my querey is
SELECT * from Tab WHERE [Tab].[date] BETWEEN #25/01/1980# AND #1/10/1999#
but it return only one record..
Provided that Database is Access.
thanx
|
|
|
|
|
I think will it work ??
I m chking
SELECT *
FROM Tab
WHERE (((Tab.date) Between #1/25/1991# And #1/10/1992#));
I m confused with its # sign in Access ..ufffffff
|
|
|
|
|
But it is still showing me 1 record in VC++ front end.
but in Access it givs all records ..
i cant got it
|
|
|
|
|
I want to write a function in VC++ which will create a table(in a new mdb file) in MsAccess and also fill the table with values in the same function. As for creating the database i have to use msadox.dll and for populating database i have to use msado15.dll. But if i import both these dlls i get the following error.
< error C2011: 'DataTypeEnum' : 'enum' type redefinition >
This is probably due to DataTypeEnum being defined in both msadox.tlh and msado15.tlh.
Is there any way in which i can use both the dlls
or any other way i can create and populate the table in same function.
Note :(I got to create database in Access 2000)
With Regards,
Kaustubh
|
|
|
|
|
Couple of ways....simplest is to use namespaces
#import "msado15.dll" rename_namespace "ADONamespace"
#import "msadox.dll" rename_namespace "ADOXNamespace"
or
#import "msadox" exclude ("DataTypeEnum")
Remembering to do it only for one of them!
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hi,
I am using sql server 7.0 in that i am having a table of 3 feild i.e.
empName, Logindate, HoursWorked
select * from tablename
Result:
empName LoginDate HoursWorked
------- ------------ ------------
Amit 06/03/04 13:44:45
Amit 06/04/04 14:44:45
now i want the sum of the field "HoursWorked"
Can any body help?
Actually im taking 'LoginTime' and 'LogoutTime' Datatype
'DateTime' from "login" table and subtraction of this two field is strored in field 'TimeDiffrence' datatype varchar
Query i m using is
"convert(varchar(8),(logoutTime-loginTime),14)Timediffrence"
now i want the sum of the field "Timediffrence"
|
|
|
|
|
try:
Select empName, Sum(Cast(HoursWorked as int))
From tablename
Group by empName
Or
Select empName, HoursWorked = Sum(DateDiff(ss, LoginTime, LogoutTime)) / 3600
From login
Group by empName
Wout Louwers
|
|
|
|
|
Thanks let me check it out
|
|
|
|
|
Hi
As almost a continuation of my last question, I'm now trying to select the latest date entry record for ALL products out of a table - the latest date varies for the products. I'm using a SELECT DISTINCT productID, but when I try to use MAX(date) it of course is choosing only those with literally the latest date....
Hope you can help again! I am learning.. 
Patrick Collins
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
Try:
<br />
Select ProductID, Max(Date)<br />
Group by ProductID<br />
Wout Louwers
|
|
|
|
|
Thank you Wout,
How embarrassingly easy! 
Just to check, if I select other fields for the product from that record, it has effectively limited them to the record with the Max Date for that product?
Thanks in advance
Patrick Collins
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
No, the GROUP BY clause will only permit you to select the fields in the GROUP BY clause itself and any field in an aggregate function.
To do what you want you would have to create some sort of subquery
SELECT * FROM my_table
INNER JOIN
(SELECT product_id, MAX(date_field)
FROM my_table GROUP BY product_id)
AS inner_table
ON my_table.product_id = inner_table.product_id
This will allow you to return all the fields from your table.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
Thanks again Colin,
Got there - It would have helped if any of the tables I was working with were normalized!
Patrick
Everything & Nothing
http://www.lostsheep.com
|
|
|
|
|
I'm currently using Enterprise Manager to connect to some SQL 2000 Servers to manage them. The problem is the Enterprise cannot connect with some servers but can with other servers, the error is: "SQL Server does not existe or access denied ConnectionOpen(Connect())". To verify the connection, I use Query Analyzer and it can connect all servers. (All connection library was enable for client and servers).
Please help me.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
