|
 Hello gents, I have this interesting question. I have one field that contains a record such as this "SD070220N0300E00004" that I need to turn in to several sub fields. The first being ST and being the first 2 charecters in the field, then another that contains the second two char and so on.
I have agregated several fields to one, but never one into many!
Your expertise would be greatly appreciated and to the most helpful goes good Karma!
David
You are only as good as your last project.
|
|
|
|
|
Well, since the gents seem to be busy, do you mind if I answer?
How you actually do what you want depends on the language you are using. If it is VB.NET you could use the Substring method of the String class. If its a different language, you need to look up info on working with strings.
|
|
|
|
|
Hi there,
I'm not sure why following code does not work for one class function but does for another in my application. It doesn't allow me to insert into a table that contains more than one field. Below, the field and value arrays are char array pointers (char *fields[200], values[200]) that contain the field names and their corresponding values. num_fields is the number of fields in the table.
HRESULT hr;
m_Rec.CreateInstance( __uuidof(ADODB::Recordset));
hr = m_Rec->("SELECT * FROM Employees", _variant_t(IDispatch *)m_Conn, true), ADODB::adOpenForwardOnly, ADODB::adLockOptimistic, ADODB::adCmdText);
if (SUCCEEDED(hr))
{
COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, num_fields);
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, num_fields);
long lArrayIndex[1];
for (i = 0; i < num_fields; i++)
{
lArrayIndex[0] = i;
vaFieldList.PutElement(lArrayIndex, &(_variant_t(fields[i])));
vaValueArray.PutElement(lArrayIndex, &(_variant_t(values[i])));
}
m_Rec->AddNew(vaFieldList, vaValueArray);
m_Rec->Close();
}
//clear the array for the next record
for (i = 0; i < num_fields; i++)
{
*(fields + i) = 0;
*(values + i) = 0;
}
Thanks!
|
|
|
|
|
Nevermind. I figured it out. It was actually my database data types that were mismatching. Stupid me.
|
|
|
|
|
Is it possible to compare two datarows taken from two separate datasets as a whole, or do you have to compare the value of each column in the row.
Please advise
|
|
|
|
|
u can do ,if the table schemes r the same.
|
|
|
|
|
Just Greeky Creek wrote:
u can do ,if the table schemes r the same.
How is this done?
|
|
|
|
|
Dim greeky As Boolean = True<br />
Dim xDs1 As New DataSet<br />
Dim xDs2 As New DataSet<br />
For i As Short = 0 To xDs2.Tables(1).Rows.Count - 1<br />
greeky = greeky And xDs1.Tables(0).Rows.Contains(xDs2.Tables(1).Rows(i))<br />
Next<br />
If greeky Then<br />
MessageBox.Show("xDs1.Tables(0) contains all rows of xDs2.Tables(1)")<br />
End If
|
|
|
|
|
As I read in all my records from a Recordset, I want to delete them.
Will this code work:
<br />
try<br />
{<br />
CString mySelect;<br />
mySelect.Format("SELECT MDC1_DATA_TEXT FROM MDC1 WHERE (MDC1_I_O = 'O')");<br />
pCommand.CreateInstance(__uuidof(Command));<br />
pCommand->ActiveConnection = m_pConnection;<br />
pCommand->CommandText = _bstr_t(mySelect);<br />
pRecordset.CreateInstance(__uuidof(Recordset));<br />
pRecordset->CursorLocation = adUseClient;<br />
pRecordset->Open((IDispatch*) pCommand, <br />
vtMissing,<br />
adOpenStatic,<br />
adLockBatchOptimistic,<br />
adCmdUnknown);<br />
_variant_t MDC1_DATA_TEXT;<br />
_variant_t MDC1_REV_DATE;<br />
int recCount = pRecordset->GetRecordCount();<br />
while (!pRecordset->adoEOF)<br />
{<br />
MDC1_DATA_TEXT = pRecordset->GetCollect("MDC1_DATA_TEXT");<br />
int strLength = strlen((char*)_bstr_t(MDC1_DATA_TEXT));<br />
if (MDC1_DATA_TEXT.vt != VT_NULL)<br />
{<br />
m_sConnectSocket.Send((char*)_bstr_t(MDC1_DATA_TEXT), strLength);<br />
pRecordset->Delete(adAffectCurrent);<br />
pRecordset->UpdateBatch(adAffectCurrent);<br />
<br />
<br />
}<br />
pRecordset->MoveNext();<br />
}<br />
}<br />
catch (_com_error &e)<br />
{<br />
_bstr_t bstrError(e.ErrorMessage());<br />
CString strError = (char*)bstrError;<br />
AfxMessageBox(strError);<br />
}<br />
catch (...)<br />
{<br />
AfxMessageBox("Unknown Error!");<br />
}<br />
<br />
<br />
So after I read in each records that has a 'O', I want to delete it with these commands:
pRecordset->Delete(adAffectCurrent);
pRecordset->UpdateBatch(adAffectCurrent);
Then read the next record that has an 'O', then delete it....so on and so forth.
Will the Delete and UpdateBatch work for me?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
If this is really all you want to do, I suggest using a DELETE statement:
DELETE FROM MDC1
WHERE MDC_I_O = 'O' If you want more conditions you can easily add them to the WHERE clause.
If you can't do this - you need to cross-reference with another data source, perhaps - you should be able to call UpdateBatch after performing all the Deletes.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
my problem is that I may have more than one record with an 'O'. The database is based on a date time stamp in ascending order. This is why I wanted to delete each record as it's read in to my record set.
Let me ask this, as I run thru my code, I'm getting one record at a time, right? Or does the recordset pointer get all records at one time, that match my select statement?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Tom Wright wrote:
Let me ask this, as I run thru my code, I'm getting one record at a time, right? Or does the recordset pointer get all records at one time, that match my select statement?
No, you are using a client side recordset, which marshalls all matching records in its return.
if you want a 'firehose' cursor that marshals the records one at a time as you 'movenext' you should use adUseServer for the connection and rs, and adForwardOnly for the recordset type.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
In VB.NET
I have added a DataGrid control to a windows form and bound it to the dataset. However, I would like to change the order of the columns. Is there a way to do that?
I have tried using:
TableStyles | GridColumnStyles
and then setting the HeaderText and MappingName
But that doesn't seem to have any affect on the DataGrid.
Thanks.
|
|
|
|
|
Please First Check MAppingName property of DataGridTableStyle either u have already set or not. U have to set that to TableName
|
|
|
|
|
Thank you, exactly what I needed.
|
|
|
|
|
Thanks for at least looking. I know there is a SQL Guru out there just waiting to help unlearned and ignorant programmers
I have two tables which are as indicated:
Table1
IDNO : RefID
01 : 100
02 : 200
03 : 300
Table2
IDNO : RefID : Code : SmallDateTimeStamp
01 : 100 : a : 1/1/2005 1:01:00
02 : 100 : b : 1/1/2005 1:02:00
03 : 100 : c : 1/2/2005 2:03:00
04 : 100 : d : 1/2/2005 3:00:00
05 : 100 : f : 1/3/2005 1:00:00
06 : 200 : a : 1/1/2005 4:55:00
07 : 200 : c : 1/1/2005 4:59:00
08 : 200 : f : 1/1/2005 5:02:00
09 : 300 : a : 1/2/2005 1:00:00
10 : 300 : c : 1/2/2005 2:00:00
11 : 300 : d : 1/2/2005 3:00:00
12 : 300 : f : 1/2/2005 4:00:00
My problem is that I need to select the IDNOs from Table1 where the same RefID in Table2 has a last record of “f” and a second to last record of “c”
EDIT: New task, I now assume all record in table1 have a last record of 'f' so all I need to get now is where table2 has a 2nd to last record of 'c'
Currently I'm getting all the rows from table1 and looping through them in code to do another query on table2. While this solution works it is slow and I believe there is another way to do this better and faster.
Thanks for any help you can offer.
-Richard
|
|
|
|
|
Use correlated subqueries:
SELECT IDNO
FROM Table1
WHERE
(SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId) = 'f' AND
(SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId AND
Code < (SELECT MAX(Code) FROM Table2 WHERE RefId = Table1.refId)) = 'c'
|
|
|
|
|
Close but I didn't give you enough data I think. The codes will not be in order like I had them (e.g. a,b,c,d,...) they will be all random. My task is to make sure the last record in table2 is 'f' and the 2nd to last is 'c'.
I also added a post that contains a sql script to make table1 & table2 and fill them with test data.
Again I do apreciate you trying to help me with this
-Richard
|
|
|
|
|
CREATE TABLE [Table2] (
[IDNO] [int] NULL ,
[RefID] [int] NULL ,
[Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dt] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Table1] (
[IDNO] [int] NULL ,
[RefID] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO Table1 (IDNO, RefID) VALUES (1, 100)
INSERT INTO Table1 (IDNO, RefID) VALUES (2, 200)
INSERT INTO Table1 (IDNO, RefID) VALUES (3, 300)
INSERT INTO Table1 (IDNO, RefID) VALUES (4, 400)
INSERT INTO Table1 (IDNO, RefID) VALUES (5, 500)
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (1, 100,'a', '1/1/2005 1:01:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (2, 100,'b', '1/1/2005 1:02:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (3, 100,'c', '1/2/2005 2:03:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (4, 100,'d', '1/2/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (5, 100,'f', '1/3/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (6, 200,'a', '1/1/2005 4:55:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (7, 200,'c', '1/1/2005 4:59:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (8, 200,'f', '1/1/2005 5:02:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (9, 300,'a', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (10,300,'c', '1/1/2005 2:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (11,300,'d', '1/1/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (12,300,'f', '1/1/2005 4:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (13,400,'a', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (14,400,'c', '1/1/2005 2:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (15,400,'e', '1/1/2005 3:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (16,400,'ff', '1/1/2005 4:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (17,500,'c', '1/1/2005 1:00:00 AM')
INSERT INTO Table2 (IDNO, RefID, Code, dt) VALUES (18,500,'f', '1/1/2005 2:00:00 AM')
|
|
|
|
|
The task changed. I don't care if the last record is 'f' anymore. I now only care if the second to last record is 'c'.
Why the change? Because as I started working on this problem I came to the conclusion that all the items in table1 will always have a last record of 'f'.
--Here is what I have so far.
--If you test against the test data it almost works.
--My only issue now is that I'm seeing 500 which is wrong
--because there are only 2 records not 3.
--See I'm trying 
select * from table1
where refid in
(
select top 1 refid from
( select top 3 * from table2 where refid = table1.refid order by dt desc
) as tt where tt.code = 'c' order by dt
)
Thanks go out to Michael Potter who reminded me about correlated subqueries.
-Richard
|
|
|
|
|
I think you are mis-stating your criteria. 'c' is the 2nd to a last record using RefId 500. It is not the 2nd to last in RefId 100, 300, 400. Your code dumps all RefIds. Here is some code that reports if 'c' is the 2nd to last record (by the DateTime order that you didn't specify in the first message).
SELECT *
FROM Table1
WHERE
(SELECT COUNT(*)
FROM Table2
WHERE RefId = Table1.refId AND
dt > (SELECT dt
FROM Table2
WHERE RefId = Table1.refId AND
Code = 'c')) = 1
If you are truely looking for the 3rd to last then just change the '1' to '2'. If you want to make sure that 'c' can not be the first than add that criteria also.
|
|
|
|
|
Absolutely brilliant!!! I knew there had to be a better way to do it. Thank you very much for your assistance. I’ll attempt to implement this solution tomorrow. Hopefully my page will load much quicker now.
Again thank you. And I’m sorry if my initial remarks where not exactly what I was attempting to get.  It’s almost like I became a manager or something for a minute... ahhhh... I’m better now
-Richard
|
|
|
|
|
i'm not an SQL guru but, you can create a View with this two tables and join the RefID of Table1 with the RefID of table2 this will give you (with the correct WHERE clause) what you want
hope this help you.
GanDad
|
|
|
|
|
i have created sp for insert and edit data in the table called
EPG_tblResourceMaster.
when i run my sp....
exec dbo.USP_AddEditResourceMaster '001','r002','veh','m001','s002','1000.0' it execute sucess fully.
but when i run the select * from dbo.EPG_tblResourceMaster before run the sp...and after again try to run my sp it gives error like....
Server: Msg 50000, Level 16, State 1, Procedure USP_AddEditResourceMaster, Line 76
Rowcount 988982068 inserting into Region
how can i solve this prob......
Thanks
Amal
|
|
|
|
|
Without seeing the code for the sp, I wouldn't have a clue.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
