|
I should have been more specific. What I need is the first line of each record to be associated to the detail lines below it. What I am getting is a straight import with many blank fields and no way to related the details to the person. Here is an example of my desired result from an import.
Aaron, Peter E 222-22-2222 Feb 14 2006 99274 220.00 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 95903 226.04 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 95904 185.36 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 72050 190.00 722.0
Thanks for the reply.
Jack Fleet
|
|
|
|
|
You need to think of some way of distinguishing the patient records from the diagnosis records programatically. Then you could write a routine that would find a patient record, load it, identify any associated diagnosis records and load them as well before moving on to the next patient record.
Paul
|
|
|
|
|
Paul,
Thanks. I wrote a routine that adds a recordid to each line, incrementing only when the next line the inscarrier is not null. Once this was accomplished it was pretty easy a query to separate and rename the fields and another to put is all back together nice and neat.
Thanks again,
Jack Fleet
Jack Fleet
|
|
|
|
|
Hey all,
I have posted this queston some time ago and so far have had no luck getting any further, it is v ery important i get this finished for a project, so fingers crossed someone will have an idea.
I writing an c# windows application that uses odbc to connect to a my sql database. i am able to successfully connect to the database and copy the contents of a table into a datagrid. i am trying to then get the table to update when i modify this table. This code returns no errors but does not update the table, I believe the problem is with the .Update() call.
<br />
DataSET.AcceptChanges();
DataAdapter.TableMappings.Add("Test", "TableData");
DataAdapter.Update(DataSET, "Test");
DataAdapter.TableMappings.RemoveAt("Test");
I have been trying for almost a week but jsut cannot get the table to update. Any suggestions would be much appreciated.
Below is the full code to a small application i have created to test this procedure. it simply connects, downloads a table named 'Test' to a datagrid and has a update button which when pressed whould update the table on the mysql database.
Many Many Many Many Many Thanks for any help or solution or similar code that works correctly!
Alex
using System;<br />
using System.Collections.Generic;<br />
using System.ComponentModel;<br />
using System.Data;<br />
using System.Drawing;<br />
using System.Text;<br />
using System.Windows.Forms;<br />
using System.Data.Odbc;<br />
<br />
<br />
<br />
namespace CannotUpdateTables<br />
{<br />
<br />
<br />
public partial class Form1 : Form<br />
{<br />
<br />
private System.Data.Odbc.OdbcConnection OdbcCon;<br />
<br />
private System.Data.Odbc.OdbcDataAdapter DataAdapter;<br />
private DataSet DataSET;<br />
<br />
private string ConStr;<br />
<br />
<br />
public Form1()<br />
{<br />
InitializeComponent();<br />
}<br />
<br />
private void Form1_Load(object sender, EventArgs e)<br />
{<br />
<br />
<br />
ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + "ORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + "WD=" + txtPassword.Text + ";OPTION=3";<br />
<br />
OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);<br />
<br />
try<br />
{<br />
txtLog.AppendText("Openning connection...\r\n");<br />
if (OdbcCon.State == ConnectionState.Closed)<br />
{<br />
OdbcCon.Open();<br />
}<br />
txtLog.AppendText("Connection opened\r\n");<br />
<br />
<br />
<br />
DataAdapter = new System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Test", OdbcCon);<br />
<br />
<br />
DataSET = new DataSet("TableData");
<br />
<br />
DataAdapter.Fill(DataSET);
<br />
<br />
dataGrid.DataSource = DataSET.DefaultViewManager;<br />
<br />
<br />
}<br />
<br />
<br />
catch (System.Data.Odbc.OdbcException Ex)<br />
{<br />
txtLog.AppendText(Ex.Message + "\r\n");<br />
MessageBox.Show("Could not access the database and Display Table.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);<br />
}<br />
<br />
<br />
<br />
<br />
}<br />
<br />
private void btnUpdate_Click(object sender, EventArgs e)<br />
{<br />
try<br />
{<br />
<br />
<br />
DataSET.AcceptChanges();<br />
DataAdapter.TableMappings.Add("Test", "TableData");<br />
DataAdapter.Update(DataSET, "Test");<br />
DataAdapter.TableMappings.RemoveAt("Test");<br />
<br />
txtLog.AppendText("Table Test successfully Updated\r\n");<br />
}<br />
catch<br />
{<br />
txtLog.AppendText("Table Unable to Update\r\n");<br />
}<br />
}<br />
<br />
}<br />
}
If you read this far, again, many thanks!
-- modified at 13:20 Thursday 2nd August, 2007
|
|
|
|
|
--------------------------------------------------------------------------------
Hi
I have been having horrible problems tryin to get connecter.net mysql driver to work on different machine, as my laptop has broken down
I decided to try and use myodbc to discover it connected to my database but to only dissapoint me as it doesn't support stored procedures yet
i have now managed to get hold of bytefx.mysqlclient driver but this still doesn't work I get the error message
"client doesn't support authentication protocol requested by server;consider upgrading mysql client"
so i am guessing this doesn't support mysql 5.0 the most up to date server available fromy mysql
can anyone provide me with an drivers which will allow me to run asp.net to mysql, which will support stored procedures.
I don't understand why the connector won't work
I get the message
"unable to connect to any specified mysql host"
any help greatly appreciated
cheers
boyinde
|
|
|
|
|
I need to make a procedure that return free numbers from a secuential table. Could somebody help me?.
for ex: 1, 2, 3, 4, 5, 7, 8, 9, 10
Its mast be return 6. Thank
|
|
|
|
|
If you have another table that has all of the numbers you could do a left join. So if table1 is missing numbers and table2 has all the numbers you could do:
select t2.id from table2 t2
left join table1 t1 on t2.id = t1.id
where t2.id is null
This would return all the missing numbers.
Ben
|
|
|
|
|
Interesting Problem. Here is a solution to find the missing holes in your data. It will even find a leading hole if you know the expected start number. In this example the start number is set to ONE (t.[MyID] != 1 & the ISNULL result set to 0). This works with SQL Server 2000, there is more than likely an easier solution with SQL Server 2005.
Replace [MyId] with your id column name. Replace [MyTable] with your table name.
SELECT
ISNULL((SELECT MAX([MyID]) FROM [MyTable] WHERE [MyID] < t.[MyID]),0) + 1 AS FirstMissingNumber,
t.[MyID] - 1 AS LastMissingNumber
FROM
[MyTable] t
WHERE
(t.[MyID] - 1) NOT IN (SELECT [MyID] FROM [MyTable]) AND
t.[MyID] != 1
|
|
|
|
|
Thank you. You make magic with this code. I'm trying to understand it. Its works fantastic!!.
|
|
|
|
|
You are welcome.
Finding the 'last' missing number was pretty easy. The query just subtracts one from every ID and sees if it is already there and that it isn't the first assigned id.
SELECT
[MyID] - 1 AS MissingNumber
FROM
[MyTable]
WHERE
[MyID] - 1) NOT IN (SELECT [MyID] FROM [MyTable]) AND
[MyID] != 1
Finding the first missing number uses a correlated subquery. If you look at that query by itself it is pretty clear. I added the ISNULL because the first record will have no preceeding records and therefore, return NULL.
|
|
|
|
|
I agree with ArielR. Very nice. I'd have said it wasn't possible if I hadn't seen your solution.
Correct me if I'm wrong, but doesn't your solution only return the first and last numbers from a missing block...
1,2,3,9,10
It won't return all the missing numbers: 4 thru 8, will it? Just 4 and 8; right?
I also found it difficult to see how it was working, so I dumbed it down for my own understanding:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable);
Of course, this will pick up numbers that don't belong in the table, like 0 (zero), so I added a predicate similar to yours:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable)
AND T.ID > @startnum;
Of course this ONLY picks up numbers of the form (ID-1) where ID IS in the table, and ID-1 is not. It won't pick up others numbers from a block of missing numbers.
Now a concern:
I *THINK* that each element from the (T.ID - 1) set will be subject to a linear scan of the entire (SELECT ID FROM tblMyTable) result set (well, it should stop as soon as a hit occurs). This will be an O(N^2) operation. I also *THINK* that if we just sorted the exclusion set:
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable ORDER BY ID)
the DBMS should recognize the order, and do a binary search instead of a linear scan, reducing the overall select to O(N*ln(N)) (of course the ordering operation itself may be an O(N*ln(N)) operation). This may seem insignificant, but if you're searching a million record table, N^2 could be huge! In this case, N*ln(N) could be 50,000 times faster than N^2.
Then I began wondering if it could be reduced it to an O(N) operation just like a person would code it manually in a procedural language if they knew the two sets were ordered. Perhaps a LEFT JOIN like kubben suggested would do it:
SELECT Missing.ID FROM
(SELECT (ID - 1) AS ID
FROM tblMyTable
WHERE ID > @startnum
ORDER BY ID) AS Missing
LEFT JOIN
(SELECT ID
FROM tblMyTable
ORDER BY ID) AS Includes ON Missing.ID = Includes.ID
WHERE Includes.ID IS NULL;
Maybe someone who knows more about the guts of a DBMS (like Mike Dimmick) could set me straight here. I'd have liked to run some speed tests with huge tables, but just don't have the resources available to me.
David
|
|
|
|
|
You are correct in your speed assumptions. When used on an primary key, my code would result in a binary search of the index. Your method would be significantly faster on larger tables especially since NOT IN is a very slow operation.
You would still have to acquire the first ID in a missing hole. It would be easy enough to re-insert the correlated subquery (will slow it down a bit) by using your final query as the source for a new query.
The only way I can see for locating all missing IDs is to use a while loop, a cursor or building a table of integers as the first respondent mentioned. Normally, I would leave further analysis to a client application where iteration through the result set is a simplistic operation.
|
|
|
|
|
Hi Guys I have a small database program and I want to add an e-mailling functionality to it. Its quite a simple design program, I move through the records in the dataset by Previous and Next labelled Buttons...
I want to put another button on the same form that will send an e-mail to the record currently viewed on the the application. The e-mail will stay the same and only change the name and also send a little information from the database.
Is this chair taken
|
|
|
|
|
Ok what programming language. If it's .NET then use the System.Net.Mail.SmtpClient to send the mail out.
|
|
|
|
|
How can I make this below statement work for MSSQL 2005
UPDATE TABLE
SET FIELD2 = (SELECT COUNT(*) FROM TABLE)
WHERE FIELD1 = @Parameter
I'm not sure if it will work as is. I was sort of hoping there was an eval fuction, but I haven't found one.
|
|
|
|
|
This should work:
Declare @cnt int
Select @cnt = count(*) from table
UPDATE TABLE
SET FIELD2 = @cnt
WHERE FIELD1 = @Parameter
Ben
|
|
|
|
|
Thanks that looks exactly like what I need to do.
|
|
|
|
|
Ok Is there something I need to do to make it do these statements in a .NET application using a SqlCommand. Possible following each command set with the word GO
Declare @cnt int
GO
SELECT @cnt = COUNT(*) from TABLE
GO
or a \n
|
|
|
|
|
It would be better to put the code into a stored procedure and call the procedure from the sqlCommand object.
Ben
|
|
|
|
|
In general SQLcommands in .net don't like the hard core sql stuff. They just want a select, update, insert, delete or call of a stored procedure.
Ben
|
|
|
|
|
Ok I maybe calling the
DECLARE @cnt int
Select @cnt = count(*) from PlaceInReferralLine
from more than one place so I'm assuming the best thing to do would be put just this code in
the stored procedure.
I'll give that a try, because the orginal code and the stuff I modified kept coming up with a SYNTAX ERROR near @cnt. Of course they couldn't atleast tell me which one was causing the problem from the debugger. Hehe.
Ok. While I am used to getting and retrieving data from different databases I'm still what I consider to be an extreme novice as I only know how to do the basic select, insert, etc.. stuff
So now for a few more questions
1. How do I get it to return the count from the store procedure.
CREATE PROCEDURE dbo.StoredProcedure1
DECLARE @cnt int
Select @cnt = count(*) from TABLE
RETURN @cnt <-I'm assuming
or maybe more simply
CREATE PROCEDURE dbo.StoredProcedure1
RETURN Select count(*) from TABLE
All this is being done in Visual Web Developer from MS.
2. Then I assume to get the data from the stored procedure
INSERT INTO TABLE
(col1, col2)
VALUES ('value1', dpo.StoredProcedure1)
|
|
|
|
|
Ok, there are several ways of doing it. I would guess the easiest is to just return an output parameters. so your SP looks like:
CREATE PROCEDURE dbo.StoredProcedure1
@param1 int,
@cnt int output
as
Select @cnt = count(*) from TABLE where column = @param1
RETURN 0
<pre>
So then when you call the stored procedure you will have to add two sqlparameters to your sqlcommand. The @cnt needs to be marked as direction = output
After you call to executenonquery on the stored procedure you can then check the parameters and get the output value. Then use this value when doing the insert or update.
You can't actually call a stored procedure in an insert statement.
You can call a function, but there are other limitation with functions.
Hope that helps.
Ben
|
|
|
|
|
Ok. I'm beginning to wonder if I shouldn't just do the entire set of procedures in a single stored procedure because I can't take the chance that during the UPDATE or INSERT operations that the record count will change. So maybe I should do something similar to the following.
<br />
create procedure dpo.StoredProcedure1<br />
@cnt int = 0<br />
@AccountID int<br />
@NewAccountID int<br />
AS<br />
SELECT @cnt = Count(*) FROM PlaceInReferralLine<br />
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID<br />
<br />
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1<br />
<br />
UPDATE UserAccounts SET ReferredBy = @AccountID WHERE AccountID = @NewAccountID<br />
<br />
INSERT INTO PlaceInReferralLine (AccountID, PlaceInReferralLine) VALUES (@NewAccountId , @cnt)<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = PlaceInReferralLine - 1<br />
<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = @cnt WHERE PlaceInReferralLine = @AccountID<br />
RETURN 1<br />
RETURN 1 <- Intended for the entire procedure being successful. Which I guess for .NET to do this I would need to execute as a SqlCommand.ExecuteScalar() operation.
Sorry if there is any confusion with both a col and table name PlaceInReferralLine
|
|
|
|
|
Yes that would be a good idea. Since you are going to just have one stored procedure you should remove the @cnt input parameter and add
Declare @cnt int
after the AS
and before the Select @cnt
You do not have to do an executeScaler to get a returncode. You always get a return code when executing a stored procedure. You can still use executenonquery.
Ben
|
|
|
|
|
Ok now to just go and learn how to use the result sets so I can do my if statements.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
