|
souravghosh18 wrote: Dim tub_batch As OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2)
Try adding the length of the varcher2 variable as
OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2, 100)
100 will be the lenght of varchar you have defined in database
|
|
|
|
|
In some fields the parameter length mismatch is happening
Niladri Biswas
|
|
|
|
|
Hi,
I just added full text searching to a table, and the text in the ProductLongDescription column is:
On the go. For all purpose Industrial and Outdoor purposes. Half Hose. Available in Grey or Navy Blue.
When I use the following statement then the record above is returned:
SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'go');
When I use the following statement then nothing is returned, but you can see that "for" is in the sentence as well:
SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'for');
What am I doing wrong? Please can someone help me?
Thanks
Brendan
|
|
|
|
|
Hi Brendan
"a","the","an", "for".. etc are noise words. If you view the Messages can see a information
Informational: The full-text search condition contained noise word(s).
if u execute the same query searching for Outdoor then the query runs properly.
Thanks & Regards
Satish Pai B
|
|
|
|
|
I'm using vs2008 , I try to view multi line at crystalreport textobject as next
<br />
Dim s As String<br />
s = s & "this is first line " & Chr(13) + Chr(10)<br />
s = s & "this is second line "<br />
Dim rpt As New CrystalReport1<br />
<br />
Dim txt As TextObject<br />
<br />
txt = rpt.Section2.ReportObjects("Text1")<br />
txt.Text = s<br />
<br />
but it isn't working ,
it print the text at single line ,, despite of when I export the report to doc file, I fount that it's printed at two lines ..
what can I do
Thanks
jooooo
|
|
|
|
|
try this one.
s = s & "this is first line " & vbCrLf
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
declare @BatchHeader table
(bhid int, status int)
insert into @BatchHeader values (1, 10)
insert into @BatchHeader values (2, 10)
insert into @BatchHeader values (3, 10)
insert into @BatchHeader values (4, 10)
insert into @BatchHeader values (5, 15)
insert into @BatchHeader values (6, 15)
insert into @BatchHeader values (7, 15)
select * from @BatchHeader
declare @BatchHeaderReference table
(parentid int, child int)
insert into @BatchHeaderReference values (1,2)
insert into @BatchHeaderReference values (1,3)
insert into @BatchHeaderReference values (1,4)
insert into @BatchHeaderReference values (1,5)
insert into @BatchHeaderReference values (1,6)
insert into @BatchHeaderReference values (1,7)
select * from @BatchHeaderReference
@BatchHeaderReference.ParentId and Child are the FKs to @BatchHeader.bhid
The output is this:
@BatchHeader
bhid Status
1 10
2 10
3 10
4 10
5 15
6 15
7 15
@BatchHeaderReference
Parentid Child
1 2
1 3
1 4
1 5
1 6
1 7
I want to write a query to fetch unique @BatchHeader rows only if the parent and child have status = 10. So in this case the query shoudnt return any @BatchHeader rows (bcos bhid 5,6,7 have status 15). But if I update the status for bhid=5,6,7 to 10 then the query should return bhid=1.
I tried something like this but it returns bhid=1
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status
Please help.
Thanks,
Joe
|
|
|
|
|
I had to go look at this properly IE in TSQL. @BatchHeaderReference joins ALL records to the header record 1, therefore header records 2-7 do not take part in the statement. no link to the @BatchHeaderReference table!
Joe_P wrote: oin @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
What database are you using, I have never seen readpast
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using SQL Server as the database. I have copied and pasted the query as-is but for simplicity, I changed the tables to table variables, so you see the (readpast) in the query that fetches rows from a table variable.
I have to achieve this in a single SQL, so cant use any complex logic of additional table variables.
Thanks,
Joe
|
|
|
|
|
You missed the point, the problem is in your join. All reference records join to hdr record 1 and are therefore valid.
parentid int (1) = bhid int (1) therefore status will always be 10
@BatchHeaderReference
Parentid Child
1 2
1 3
1 4
1 5
1 6
1 7
Is correct, status 15 never comes into it and the 2-7 are NOT FROM THE HDR table. I think your data structure is screwed, if your status was on the reference table then the filtering would work
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understand, but I cant have a status on the BatchHeaderReference table. This table is just to figureout the parent/child relationship and the status of these should be retained on the BatchHeader table only.
So only if all the header and child rows from HDR table are in status 10 then the query should return the id.
Thanks,
Joe
|
|
|
|
|
Joe_P wrote: Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status
One begins to see the light....
Change the joins to INNER JOIN and add the filter (=10) on BH2 join rather than the where clause
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply Mycroft Holmes, but no luck.
Tried this
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id and bh1.Status = 10
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10
And
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10
Where
bh1.Status = 10
|
|
|
|
|
Try this
Select *
From @BatchHeaderReference bhr
INNER Join @BatchHeader bh1 on bhr.Child = bh1.bhid
WHERE bhr.child IN (SELECT bhid FROM @BatchHeader WHERE status = 10)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The way I read this problem all of the children must have the status of 10. If the parent has any children without the status ten then it doesn't come back.
This makes it a bear because you have to do something to match all children with all children who have a status of 10 and only return the parent if the children all come back as status 10.
So I'd use a counting mechanism.
select distinct x.parentid
from (
select
bhr.* ,
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status,
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt,
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount
from @BatchHeaderReference bhr
) x
where x.cnt=x.truecount
I'm more of an oracle guy but the above will work.
Going from the inside out, which is usually the best way to deal with these problems, you create a count of all children. Then a count of all 'true' children- those with a status of 10. Then you put all of that inside of a wrapper which we call X.
From there we treat X as another table.
If you need to also check to see if the parent has a status of 10 then you just change it to this.
select distinct x.parentid
from (
select
bhr.* ,
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status,
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt,
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount,
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.parentid) pstatus
from @BatchHeaderReference bhr
) x
where x.cnt=x.truecount
and x.pstatus=10
Here you get the parent status, calling it pstatus, and then just check it at the last stage.
edit: the reason MAX is used is to stop certain errors from creeping in if you have the same value show up but multiple times. If you expect different values for status against a given id then you'd have to take another approach because this would be incorrect.
_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.
|
|
|
|
|
If I have understood your problem correctly, then try the following query
SELECT BHID 'PARENTID' FROM @BatchHeader BHR1
WHERE BHR1.STATUS = 10
UNION
SELECT PARENTID FROM @BatchHeaderReference BHR
INNER JOIN @BatchHeader BH
ON BH.BHID = BHR.CHILD
AND BH.STATUS = 10
As per the current scenario, the output will be
PARENTID
--------
1
2
3
4
If you update 5 and 7 of @BatchHeader to 10, the output will be
PARENTID
--------
1
2
3
4
5
7
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi
I used to use the function SCOPE_IDENTITY() to get the inserted identity, now I need to get the inserted GUID any idea how to do so
Thanks
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
|
The minimum of research turns up the first in this list clickety[^] which leads me to believe that GUIDs are a really bad idea.
I have used unique identifiers where databases needed to be merged but there was always an identity field for local processing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all, am not too experienced with microsoft sql database. I want to use a foreign key e.g mod_data_id, auto generated in a table Data_modification, as a foreign key in another table called modification_copy, such that each time data is inserted in Data_modification table, the mod_data_id will be used to update the modification_copy Tanle.
Plz! help. The code is given below:
Data_Modification (mod_data_id,Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)
Modification_Copy (Data_Mod_ID,mod_data_id, Date_Collected, Data_Attribute, Correct_Data_Value)
Thanks in advance for ur help.
|
|
|
|
|
A couple of things:
What you are describing sounds like a trigger, triggers are EVIL as they make support difficult. I would suggest using a stored procedure to do the updating in both tables wrapped in a transaction @@IDENTITY (SCOPE_IDENTITY() is required if you use triggers) will give you the last entered identity field
nedusmile wrote: Data_Modification (mod_data_id,Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)
This is going to drive you (or the person who supports your DB) nuts. Get rid of the unserscores they are sooo 80s.
Data_Modification (ModDataID,PatientID, DateCollected, WrongDataEntryDate, DataField, Provider)
is much more readable IMHO.
Foreign keys define relationships and constraints, they will not cause data to be changed.
I recommend getting a beginners book on database design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try
LoadData()
' Configure and execute the command.
comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
" values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider)", con)
'comd.Connection = con
Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
comd.ExecuteNonQuery()
Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
con.Close()
End Try
Try
LoadData()
comd = New System.Data.SqlClient.SqlCommand("select distinct scope_identity()as Integer from Data_Modification", con)
Dim key As Integer = comd.ExecuteScalar
con.Close()
LoadData()
' Configure and execute the command.
comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
" values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)
'comd.Connection = con
Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key)
Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)
comd.ExecuteNonQuery()
If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
= Windows.Forms.DialogResult.Yes Then
repMorDetPnl.Hide()
repDatTabPg.Show()
Else
repMorDetPnl.Hide()
vwPatient.Show()
End If
Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
con.Close()
End Try
|
|
|
|
|
Seems the usual reply to this question is to use a stored procedure (I ALWAYS use a proc so I had no idea how to do it using parameterised queries) however I found this response [^] which may help you.
I would still recommend a stored proc but that is a personal/professional opinion.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks alot! mycroft, i resolved the issue.I didn't have to use stored procedure since to me there wasn't any performance issue.So i included the SCOPE_IDENTITY() method in my query command and used ExecuteScalar which only returns the last key generated. I then assigned it to a variable.this variable was then passed on as the foreign key to another table.
Anyway thanks for ur help!!!
|
|
|
|
|
Post the script, there is not a lot of examples of that style - most use procs.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try
LoadData() ' function containing connection string
' Configure and execute the command.
'the scope_identity() is inserted in the query that will insert the data
'this will retrieve the last auto-generated number from the first table.
comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
" values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider) select scope_identity()", con)
'Declare and Initialize parameter that is passed to the query strings
Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
'command object is used to call the ExecuteScalar() and returns may be a
'decimal value which is converted to integer and assigned to a variable.
LoadData()
Dim key As Integer = Convert.ToInt32(comd.ExecuteScalar())
con.Close()
LoadData()
' this is an insertion to the second table, the integer value (key)
'Obtained above is passed on to the second query as foreign key (data_mod_ID) to the second table
comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
" values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)
'comd.Connection = con
Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key.ToString)
Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)
' Load the DataTable.
'datadapt = New System.Data.SqlClient.SqlDataAdapter(comd)
'datadapt.Fill(dSet)
'dSet.Tables.Add(dTab)
' Dim rowAffected As Integer = 0
' cmd.Connection = cn
' rowAffected =
comd.ExecuteNonQuery()
If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
= Windows.Forms.DialogResult.Yes Then
repMorDetPnl.Hide()
repDatTabPg.Show()
Else
repMorDetPnl.Hide()
vwPatient.Show()
End If
Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
con.Close()
End Try
Public Sub LoadData()
dSet = New DataSet
dTab = New DataTable
con = New System.Data.SqlClient.SqlConnection("Data Source=192.168.3.101,1433;Initial Catalog=DataBaseName;User ID=Doctor;Password=gudone;")
con.Open()
End Sub
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
