|
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
|
|
|
|
|
Thanks alot mycroft holmes!!. but the following is my idea, i dont know y the ExecuteScalar method is returning nothing i guess.keeps throwing exception about type conversion.Can anyone help me!
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
|
|
|
|
|
Here is the code, Plz dont mind my variable names as u said b4.But anyway, this works perfectly for me.
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
|
|
|
|
|
nedusmile wrote: " values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider) select scope_identity()", con)
Interesting, I never knew you could append a select statement to the tail of a prameterised query. Comes form using stored procs 100% of the time !
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i have table for advertisement
i want trans advertise which completed 15 days automatically to another table archive
thank you
|
|
|
|
|
you can write a script and run it from a SQL job that checks your table and move rows that comply with your constraint...
I am assuming you are working with SQL Server
|
|
|
|
|
|
NNR_Noga wrote: i want acually code
We all do:
You need to use the DateDiff function :
DateDiff[^]
Create a Job:
Create a Job[^]
|
|
|
|
|
Read this [^] to understand why Bassams answer was so good.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
My company has now decided that we developers now have to bring in a persistence layer in our applications(mostly c#.net,wpf and ASP.net). Previously I wrote my own entity classes and had a common data class handle all my database transactions. This data class used Corelab's Unidirect to generate sql for a vast multitude of DBs and defined the way in which you are to communicate with the database. Now I have to implement Nhibernate. I've never used it before and apparently it makes our job as developers easier. I do not yet know whether this is the case and so far it has only complicated things. Can anybody tell me if Nhibernate is worth the effort as I don not yet know the product well enough to make a conclusion. The learning curve is messing with my productivity.
|
|
|
|
|
It is very hard to answer your question. NHibernate is a very large open source project, it will take everyone much time to make use of it in a good way. Like any framework, simply using it doesnt mean you are using it effeciently. yet once you learn it and there are many articles here in CP, it will bring great value and ease to your team.
Now that being said, there are other Microsoft framework that may be worth digging into like Entity Framework but I am not expert on EF so I cannot recommend it over NHibernate.
|
|
|
|
|
how to create edb in C# and read data from there.
plzzzzzzzzzzzzz anyone help as soon as possible.
|
|
|
|
|
I am assuming you mean Enterprise Database? The answer is the same as you open a sql or any other kind of database:
using EnterpriseDB.EDBClient;
string strConnectionString = ConfigurationSettings.AppSettings
["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
try {
conn.Open();
Response.Write("Connection opened successfully");
}
catch(EDBException exp) { exp.ToString(); }
finally {
conn.Close();
}
I have not compiled the code so expect errors
|
|
|
|
|
How can I write some text to the Event Viewer from a stored procedure?
|
|
|
|
|
|
Quoted from this[^] website;
xp_logevent {error_number, 'message'} [, 'severity']
I are troll
|
|
|
|
|
I'm fairly new to ADO .NET concepts.
I understand that the framework gives a Data.SqlClient namespace with its collection of classes. I installed SQL Server on my machine, but not the SQL Client. And connected/queried the database from C#, using these provider classes.
I deployed my application on other machines as well to connect/query the 'database on my machine'. I did not install the SQL Client that comes with SQL Server on any of the machines.
My question is - for a production envionment is there a necessity for SQL Client licensing for various users? Is mere installing SQL Server on a network server not sufficient when I'm using ADO .NET?
How does this licensing - sql client installation - server set up work?
I tried to read a number of sites but I could not get a straight answer to my question.
I greatly appreciate response.
Thanks
Ap
|
|
|
|
|
You don't need licenses on the client machines. The server needs to have a license for the appropriate version of SQL Server, unless it's SQL Express.
I are troll
|
|
|
|
|