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!!!
Here is the code, Plz dont mind my variable names as u said b4.But anyway, this works perfectly for me.
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 objectis used to call the ExecuteScalar() and returns may be a
'decimal value which is converted to integer and assigned to a variable.
Dim key As Integer = Convert.ToInt32(comd.ExecuteScalar())
'thisis 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)
' Dim rowAffected As Integer = 0
' cmd.Connection = cn
' rowAffected =
If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
= Windows.Forms.DialogResult.Yes Then
Catch ex As SqlException
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;")
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.
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.
There is Something that you are not Doing Right. You must always remember that SQl is not shipped with your Application.
SQL server must be Installed Separately and it has nothing to do with what you have in your Application. In your Application if you decide to use SQl , then you will use appropriate libraries and use ado.net. When you are done compiling your application , you will install the setup project on the client machine that has .net installed and your application is supposed to work, there is nothing extra that your application needs than SQl Server, so no licenses.
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."