|
Hi Garrett. You may want to have a subquery take care of the [Count] and [Ages] fields, with a custom function to derive [Ages]. Start with a custom function to return an age given a birthdate. Here is a good function that comes from a Microsoft KB article:
Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant
If IsNull(varBirthDate) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End FunctionThen create a function that takes a parent id (l_Mem_ID in your case) and returns a string of ages for each child. This can be done by executing a query on the fly and looping through the resulting recordset:
Function GetAgesOfChildren(parentID As Integer) As String
Dim sResult As String
Dim sSql As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
'--select statement uses the Age() function
sSql = "Select Age(DOB) From tbl_Children Where l_Mem_ID = " _
& parentID & " Order By Age(DOB)"
'--open the recordset
rst.Open sSql, conn
sResult = ""
'--loop through the recordset to create a concatentated string
While Not (rst.EOF)
If sResult <> "" Then sResult = sResult & ", "
sResult = sResult & CStr(rst(0))
rst.MoveNext
Wend
GetAgesOfChildren = sResult
Set rst = Nothing
Set conn = Nothing
End FunctionIt won't be particularly effecient, but it will get the job done. Your subquery could then just link tbl_Customers to tbl_Children, use the Group By option with the Count() function for your [Count] field, then use the following as an expression for the third [Ages] field in the query grid:
Field-- Ages: GetAgesOfChildren([tbl_Customers].[l_Mem_id])
Table--
Total-- Expression Make the join between tbl_Customers and tbl_Children a left outer join and you'll get zero values for those customers without children records, rather than having those records drop off. Finally then, you can use this subquery in your main query with the tbl_LATCH table to get your final result set.
I hope this helps.
|
|
|
|
|
Hi,
I have a question: How can I set the datasource of a datagrid by using codes? I've been using the properties on the right side to set it.
Then I also have a problem... Why is it that whenever I use:
adodc1.recordset.addnew
ado.Recordset.Fields(0) = Trim(txtname)
ado.Recordset.Fields(1) = Trim(txtage)
ado.Recordset.Update
the data that I save in a database is automatically displayed on the datagrid. But if I use:
Set adocon = New ADODB.Connection
Set adorset = New ADODB.Recordset
adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "dbnames.mdb"
adorset.Open "Table2", adocon, adOpenKeyset, adLockOptimistic
adorset.AddNew
adorset.Fields(0) = Trim(txtname)
adorset.Fields(1) = Trim(txtage)
adorset.Update
the data that I save on a database don't appear automatically and it would only appear once I unload and reload my form or if I use
DTG.Columns(0) = gadors.Fields(0)
DTG.Columns(1) = gadors.Fields(1)
DTG.Columns(2) = gadors.Fields(2)
DTG.Columns(3) = gadors.Fields(3)
wherein the problem is that it overwrites the first record on a datagrid and once I unload and reload, that's the only time that the new record would be placed at the end.
I can't use the movenext and moveprevious properly as well whenever I use this type of setup.
Thanks....
|
|
|
|
|
We developed a CS program by C#,the server is SQL Server,i copyed the program to a system without SQL Server,the program can not be run,the error report is something like this:"Can't connect to Database".when i installed the "Connectivity Only" option of SQL Server,the program can be run in this system.
what i want to know is how to run our program without install anything of SQL Server, or just copy something usefull to the system from SQL Server.
Tuliplanet
|
|
|
|
|
Hi,
Seems like you are pointing towards the local machine in your code. Obviously if there is no SQL server on the machine it can't connect.
Itz advisable that you keep the connection string configurable and change the connection string in the configuration and make it point towards a machine where you have SQL Server.
remember .NET requires MDAC 2.5 and above to connect to any database...
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
NO,I Pointed towards to the right machine.
Tuliplanet
|
|
|
|
|
How Are you connecting, OBDC dsn? Is it setup? SQL SERVER client utility?
Show us your connection string( leave out sensitive values of course). Sql Server does not need to run on the computer the app is on.
|
|
|
|
|
Ok！The Connection string is："Server=XXX";uid=XXX;pwd=XXX;database=XXX".
anything wrong?
Tuliplanet
|
|
|
|
|
OK, look at the Server name. Do you have the proper ODBC/Client Utility settings that allow you to access that data server. Test your connections. If you want to connect to Sql server you will need something to access it. You can not just say go talk to this server you need to have connectivity thru software like ODBC/Sql server Client Utility.
|
|
|
|
|
Hello All!!!!
I have got a problem, I have a generalized class for inserting/updating/and deleting data, here are few lines of code which work well for adding new records in database
<br />
<br />
cmdBldr = new OleDbCommandBuilder(dataAdapter);<br />
DataRow nRow = dataTable.NewRow();<br />
nRow.ItemArray = itemArray;<br />
dataTable.Rows.Add(nRow);<br />
dataAdapter.Update(dataTable); <br />
As command builder is used to generate the sql statements so it works well with columns names with no spaces like [FirstName], but if we make it
[First Name] then it throws following exception:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll 
Can anybody give me the idea that how to reslove this, i dont want to remove spaces because they coulmn names are displayed at run time on form so that if i change the name in table, it get changed on form automatically.
Thanx in Advance 
sorry for my bad English.
|
|
|
|
|
You should not use spaces when Naming your Database objects (tables,columns, SP, etc..) rename your columns and replace space with _ (ex. FIRST_NAME)
|
|
|
|
|
Hello Friends,
I have a problem in SQL 2000. I have a table call Employee in that there r many details of employee such as his name his age and other details.
I want to insert one employee name D'souza mike for that i write a query
insert into employee values ('D'Souza Mike', 21)
Here name is D'Souza so that special character ' create problem while executeing query.
any one know how to insert special characters like ' _ % then please tell me
Thank You
Hemant Mane
|
|
|
|
|
insert into employee values ('D''Souza Mike', 21)
|
|
|
|
|
You can't directly insert any special character.Just decode your special character with any other name e.g. "'" you can give your single quote the name "SQT".Now insert this value in the database.Now when you retrieve the value then decode this "SQT" to "'".That's all..... your job has done.
|
|
|
|
|
if the employee's name itself includes the chars : "SQT" ,
it can't work well .
|
|
|
|
|
Either escape the ' with another ' ('') or use an SqlCommand object with parameters. You can set the value of the parameter to a string containing 'special characters' and it will insert without difficulty.
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
Anyone have any hands on with Rapid SQL, we are considering buying an enterprise license.
What are your thoughts on this tool good, bad etc;
Thanks
Tony
|
|
|
|
|
|
I have followign two tables, and want to update TB1 such that IDs col gets updated
PARAMETER tables
TB1
Dt desk IDs
----------- ---------- ----------
1 PT
1 AB
2 PT
2 AB
TB2
Dt desk book IDs
----------- ---------- ---------- -----------
1 PT PT1 100
1 PT PT2 200
1 PT PT3 300
1 AB AB1 400
2 PT PT1 500
2 PT PT2 600
2 PT PT3 700
2 AB AB1 800
Final RESULT
TB1
Dt desk IDs
----------- ---------- ----------
1 PT 100, 200, 300
1 AB 400
2 PT 500, 600, 700
2 AB 800
Any pointers
Thanks
Ruchi
|
|
|
|
|
Is this on sql server? If so, your best bet might be to write a cursor to build each of your id strings ('100,200,300') and then update. I've been trying to come up with a clever way to do it in one SQL statement, doing self joins of tb2 to itself...but I am not getting anywhere...
Typically, when I need to denormalize data like this, I do the denormalization in the application (typically c#...but that's details). SQL just does not lend itself to this kind of thing nearly as readily as ADO.NET / more procedural programming approaches do.
Hope this helps
Bill
|
|
|
|
|
Thanks very much for your response.
I myself am doing the same thing you suggested - Cursor & Updates. But it is kind of expensive operation, when temp table is huge (depending on param passed to SP)
I need this result set for C# application. Could you please give me pointers on how you do it in ADO.NET
Thanks
Ruchi
|
|
|
|
|
Ruchi Gupta wrote:
But it is kind of expensive operation, when temp table is huge
Agreed. Which is why I was trying to be clever...the update...from is often handy for complex updates, but I can't come up with a single query that will generate your desired result strings ('100,200,300','400', etc). Besides, that approach required that you know the max number of strings (in your example, 3) before hand.
You mention passing parameters for the first time above. Are you updating only a few records in TB1? That might make this easier. I was assuming you were updating the whole table.
Anyway in ADO, the idea is to get your data from TB2 in order, loop through the results building the new IDs string for each entry in TB1 and then do the update. C# doesn't suffer from the horrible performance penalties of SQL for looping.
Something like:
-Load TB1 into a DataTable using a dataadapter (if you set the PK, you can use a SqlCommandBuilder to automatically generate your update command)
-Load TB2, sorted by DT,Desk into a second datatable
-create a string variable to accumulate the id string;
-Cycle through every row of this second table.
-if the current row has the same DT and Desk as the previous row, just add the ID to the id string with the comma
-other wise, set the IDs column of the Row in the first DataTable with the matching DT and Desk to the accumulated ID string. Use DataTable.Find() to find the row. Clear the ID string.
-At the end, use the original dataadapter to update the database.
This way, you can do all the itteration in C#, which does it well, rather than in SQL which is terrible at it.
Hope this helps
Bill
|
|
|
|
|
this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"
for the source, how do I make it so it accesses the database that is in the same folder as the program? Because when I load the program on other computer it give me this exception error:
"Process id = 0x4f4(1268), Thead id = 0x5dc(1500)"
I think it gives me this because it can't find the database because it is hardcoded into the program as being in a different location. So I want to know how to make it look for the database in the program location. i tried Data Source=technomedia.mdb but it gives me an error.
Thanks
|
|
|
|
|
Does this work? What exception is thrown, if not?
oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Password=\"\";User ID=Admin;Data Source=" + Environment.CurrentDirectory + "\\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"
|
|
|
|
|
now it gets stuck on my dataadapter, give me this:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
on this line: oleDbDataAdapter3.Fill(dataSet1, "Region");
|
|
|
|
|
Please post the full stack trace. (You should always do this when you post a question; it'll speed things up a bit.)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
