I am wanting to insert a record into a table in SQL then retrieve its key at the same time, the key is a int that is automatically generated, I read something about the '@@Identity' function, is this what I need? How could I then bind this to a label in the ASP.net page?
View 12 Replies I'm having a question about retrieving data. I'm using VS 2008 and I have to create a new consultation record for a patient and I've already created a database with 3 tables, which are doctor, patient and consultation.
I have 10 records saved in consultation table, and I've made a aspx form which contains a DetailsView that ask for new consultation details to be added. But I have to be able to get the doctor and patient information from the database and put in the DetailsView, then fill in the rest of the consultation details. The information entered have to be written to the database in the consultation table. But I don't know how to get doctor and patient information from database and apply them into the DetailsView so that doctor who wants to add new consultation details can select patient and doctor information from the database.
I was thinking maybe I need to do a query with insert statement and apply into the DetailView,this is the query i have:
INSERT INTO [Consultation] ([ConsultationID], [GPname], [PatientFirstName], [PatientLastName], [Height], [Weight], [IllnessDescription], [Diagnosis], [Date]) VALUES (@ConsultationID, @GPname, @PatientFirstName, @PatientLastName, @Height, @Weight, @IllnessDescription,
@Diagnosis, @Date)
as you can see, I need to get the GPname, PatientFirstName and PatientLastName from the database tables and the rest will be filled in by the doctor.
I have used in line queries for inserting and retrieving data from database. How should i use stored proceduress
Insertion code
string strSQL1 = "select * from cust_details";
DataSet ds = new DataSet();
SqlConnection m_conn;
SqlDataAdapter m_dataAdapter;
m_conn = new SqlConnection(conn);
[Code] ....
Retrieval code
try {
SqlConnection conn3 = new SqlConnection(conn);
String q1;
//string ddl = DropDownList1.SelectedItem.ToString();
q1 = "select * from Product where ID ='" + DropDownList1.SelectedItem.ToString() + "'";
SqlCommand cmd = new SqlCommand(q1, conn3);
[Code] .....
[Code]....
[Code]....
[Code]....
[Code]....
[Code]....
I am using ASP.NET 2.0, C#, SQL Server 2005. I have a gridview which has 10 records. I want to only insert few records(for example: 3 records) to the database. How to insert only part of the whole data to the database.
View 3 Repliesi got the following error when i tried to inser a recrord from my MVC web application , although the insert will work fine when i tried to inser the record manually from the databse explorer, and i checked all the FK relationship they sound valid:-
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Articles_Users". The conflict occurred in database "84A669DA0E532BEFAFBE7BE38367071F_IONALNETWORKWEBAPPLICATIONPROFESSIONALNETWORKWEBAPPLICATIONAPP_DATAPROFESSIONALNETWORKDB.MDF", table
"dbo.Users", column 'User_ID'.The statement has been terminated.
i am inserting a record into oracle database using vb.net,there is a password field by using md5 i am generating it and taking tht value as string(ex: "16D78A6B10D631C8C86397C35A3CCD57") now my requirement is i should insert it into database(oracle) & in database that field is "RAW" how to do this?and the error msg is
Type of value has a mismatch with column typeCouldn't store in Col_Pass Column. Expected type is Byte[].
Students can subscribe for any project via a webform. Table 'project ' contains all the proposed project and table 'student' contain the name of the student and the project-id.Now, suppose there is a limit of 4 students for project 'A' and there are already 3 subscribed students for that project. One more student can choose that project. The code-behind checks whether the limit is not reached (by counting the amount students for that project in table 'student') before inserting that student in the table 'student'.
My problem is that when two students fills the webform for the same project and click on the 'save-button' exactly at the same time, the code has no time to check the limit and both students are inserted into table 'student'. Is there a way to lock the table or something in order to preventing this?
[Code]....
How to show confirmation message after the process of saving or inserting a record to Database?
View 3 RepliesIn the stord procedure below. I am retreiving all the records for that 1 deal. What I want is to retrieve the most recent record hence the one with the most recent in the AddedDate field.
How can I accomplish this?
(
@DealID int)
AS
SET NOCOUNT ON
SELECT deals_Comments.CommentID, deals_Comments.AddedDate, deals_Comments.UserID, deals_Comments.AddedByEmail, deals_Comments.AddedByIP, deals_Comments.DealID, deals_Comments.Body, deals_Comments.SBody,
ROW_NUMBER() OVER (ORDER BY deals_Comments.AddedDate DESC) AS RowNum
FROM deals_Comments
WHERE deals_Comments.DealID = @DealID
I am creating an album using the following stored procedure, which returns the primary key for the newly created record:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[fpa_sp_albums_update_insert]
@album_id int,
@album_name nvarchar (50),
@album_descr nvarchar(250),
@album_img_cover_id_FK int,
@album_creation_date date
AS
If @album_id > 0
UPDATE [fpa_albums]
SET
album_name=@album_name,
album_descr= @album_descr,
album_img_cover_id_FK = @album_img_cover_id_FK,
album_creation_date = @album_creation_date
Where ((album_id = @album_id))
Else
INSERT INTO [fpa_albums] (
album_name,
album_descr,
album_img_cover_id_FK,
album_creation_date)
VALUES (
@album_name,
@album_descr,
@album_img_cover_id_FK,
@album_creation_date)
Return SCOPE_IDENTITY();
I execute the above stored procedure using the SQLHELPER. The VB.NET code for the page is as follows:
[Code]....
However, the createdAlbumID always shows -1 instead of the id for the newly created album.
i saved files into database. when i'm displaying i wrote code like this
Dim mySqlDataReader As System.Data.SqlClient.SqlDataReader
Dim mySqlCommand As New System.Data.SqlClient.SqlCommand("pl_Resumes", mySqlConnection)
mySqlCommand.CommandType = CommandType.StoredProcedure
[code]...
for ex. my amount is $ 1,234,343.00
and i want to insert the numbers ONLY in my database (1234343) how to that in c# .net???
i am working in the vb.net and inserting the date into DATETIME field sql db. During duging am checking it shows correct date but when insert into db it show "1/1/1900 00:00:00" . I dont know whats the problem. Code:
Dim ObjDob As DateTime = CDate(Me.ddlDates.SelectedValue & "/" & Me.ddlMonths.SelectedValue & "/" & Me.ddlYears.SelectedValue).ToString("dd/MM/yyyy")
I am retrieving connection string based on value stored in cookie.NowI am making crystal report. I cannot hard code server credential. I have to get these credential from connection string from web.config. so that i can pass these credentials to my crystal report .
ConnectionInfo reportConnectionInfo = new ConnectionInfo();
reportConnectionInfo.ServerName = connection string server name from web.config
reportConnectionInfo.DatabaseName = connection string database name from web.config
reportConnectionInfo.UserID = ...;
reportConnectionInfo.Password = ...;
I have a web portal designed in ASP.NET in which we ask customers to enter the data. On the click of submit button all i have done is, just read the data and called a stored procedure which inserts that into a table. There seems to be a problem in SQL Server 2005 while inserting the data from multiple computers at the same time. We have tested in our lab with three computers, result is that we get the data inserted successfully in only one machine and on the other two machines we get error on page. I have used transactions in the stored procedure and also tried setting the isolation levels to READ_UNCOMMITTED, SERIALIZABLE and SNAPSHOT. Nothing seems to work properly.
View 4 RepliesI have requirement that User can Upload the Excel Sheet Data to sql server Database at once.
View 16 RepliesI am new to NHibernate and have just started right now.I have very simple table contain Id(Int primary key and auto incremented), Name(varchar(100)), Description(varchar(100))
Here is my XML
<class name="DevelopmentStep" table="DevelopmentSteps" lazy="true">
<id name="Id" type="Int32" column="Id">
</id> [code].....
I want the users to login and have the app only display their data on the main page. The login control works perfectly...it displays the data...but it displays ALL of the users records instead of just the user that is logged in. I'm still learning how to use SQL...
View 5 RepliesBefore a subscription to a newsletter and the record will be inserted, i'd like to check if the record already exist ina sql-server database. This what i've got as dusfar
adres = TxtEmail.Text
DBConn.Open()
If adres <> "" Then
Try
DBCmd = New SqlCommand("SELECT COUNT(*) FROM TBL_Subscribers WHERE email = @adres)", DBConn)
''Add
DBCmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = adres
DBCmd.ExecuteScalar()
DBAdap = New SqlDataAdapter("SELECT * FROM TBL_Subscribers ORDER BY ActivateDate", DBConn)
DBAdap.Fill(DS)
Catch exp As Exception
Response.Write(exp)
End Try
End if
how to insert record in sql server database by using linq?
still i m using MVC 3.0 send me full detail example for insert record using Linq
What I want to do is to store new article and for each article choose a category..
I have 3 tables for example :
1. Article table : ArticleID - int , Article - nvarchar(max) .......
2. Categories table : CategoryID - int , CategoryName - nvarchar(150)
3. Article_Categories : ArticleCategoryID - int , ArticleID - int , CategoryID - int (And I set up relation in this table...)
I can store data into tables 1 and 2 that is working fine.. But I stuck with Table 3.
So far my code looks like this :
protected void Button1_Click(object sender, EventArgs e) //Add new Category (working fine)
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string insertSql = "INSERT INTO Categories(catname) VALUES(@catname)";
[code]....
I have a id in label1 ...
on button click i want to delete the data of id from database ...
I'm a new user of ASP.NET MVC and I'm trying to replicate the ListView control from WebForms in an AJAX-y manner. Take the following as an example:
[Code]....
The "ItemTemplate", "EditItemTemplate", and "InsertItemTemplate" replacements are implemented as Partial Views, so the view for this page looks something like this:
[Code]....
The edit/update flow is very simple. The user clicks the "edit" button, an AJAX request is made to get the "EditItemTemplate", and the contents of that row are replaced with the "EditItemTemplate". When the user clicks "Update" in the edit row, the form is submitted via AJAX and the resulting content replaces the "EditItemTemplate".
The part I'm not sure how to deal with is the InsertItemTemplate. When the user submits the insert form, I need two partial views to be returned: one to replace the InsertItemTemplate (clear the form or show any validation errors), and one to insert into the table as a new record (ItemTemplate). I'm not sure what to do about this...the only options I've come up with are:
Make 2 Ajax requests -- one to insert the item and get the ItemTemplate for the new item and another to get a clean InsertItemTemplate Make 1 Ajax request for JSON -- render the ItemTemplate and InsertItemTemplates into strings and then put them into a JSON object that can be analyzed client-side (ex: { itemtemplate: "<tr>...</tr>", insertitemtemplate: "<tr>...</tr>" }) Make 1 Ajax request for the ItemTemplate and then manually clear the InsertItemTemplate form client-side (removing any form entries and validation elements)
None of these options really seem that great to me...especially #1 (because of a second AJAX request) and #3 (because it keeps my solution from being easily reused on other "ListView replacements" due to the need to manually clear the form). #2 is somewhat acceptable but still kind of hacky because rendering partial views to strings and then sending them in JSON isn't easy to do and ends up being a bit of a hack.
copying some data from an existing remote database(SQL2005) to an existing local database.First - I have very little experience in this area. I'm using Visual Web Developer 2008 (VB). Currently if I want to update some data in the local database I do something like this:
Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CCCdbConnectionString").ConnectionString)
Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("UPDATE [Orders] Set [OrderStatus]
[code]...