store data in table based on id column of another table

Kavita Pharde

13y1.7k

i have two tables first table stores student information and another table stores document uploaded by student

i want to store document based on identity column of first table but i am not getting it can anyone help me

thanks

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
first modify your this sp as like below to return current inserted record id

ALTER proc [dbo].[pqr]
@name varchar,@city varchar
as
begin
declare @id int
insert into tp1(name,city) values(@name,@city)
select @@IDENTITY as Id
end

then in your code

protected void btnupload_Click(object sender, EventArgs e)
{
if (Page.IsValid) //save the image
{
int studentId = AddStudentInfo();
if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
{

HttpFileCollection fileCollection = Request.Files;
for (int i = 0; i < fileCollection.Count; i++)
{
string ext = Path.GetExtension(fileCollection[i].FileName);
if (ext == ".jpeg" || ext == ".png" || ext == ".tiff" || ext == ".pdf" || ext == ".jpg")
{
HttpPostedFile uploadfile = fileCollection[i];
string fileName = Path.GetFileName(uploadfile.FileName);
if (uploadfile.ContentLength < 1048576 && uploadfile.ContentLength > 0)
{
string name = "StdDoc" + (DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString()) + RandomNumber(20) + ext;
uploadfile.SaveAs(Server.MapPath("~/Docs/") + name + ext);
lblMessage.Text += fileName + " Saved Successfully
";
testng t = new testng();
t.Add(studentId, name);
}
}

}
}
}
}

private int AddStudentInfo()
{
string constring = @"Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUserId;Password=MyPassword";
int id;
using (SqlConnection conn = new SqlConnection(constring))
{
conn.Open();
SqlCommand cmd = new SqlCommand("pqr", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", txtBoxName.text);
cmd.Parameters.AddWithValue("@city", txtBoxCity.text);
id = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return id;
}

Regards,
Iftikar
0
Kavita Pharde13y ago
Thanks a lot Iftikar
0
Kavita Pharde13y ago
i am calling addinfo before calling add method
firstly i used these sp

ALTER proc [dbo].[abc]
@id int,
@fname varchar(100)
as
begin
insert into tp(fname) values(@fname)
end

ALTER proc [dbo].[pqr]
@id int,@name varchar,@city varchar
as
begin
insert into tp1(name,city) values(@name,@city)
end

after that i changed sp

ALTER proc [dbo].[pqr]
@id int,@name varchar,@city varchar,@fname varchar
as
begin
insert into tp1(name,city) values(@name,@city)
insert into tp(fname,fk_id) values(@fname,(select id from tp1))
end

0
Iftikar Hussain13y ago
are you calling addinfo before Add method? Also why the return type of both method is DataSet? Can you share your both sp?

Regards,
Iftikar
0
Kavita Pharde13y ago
protected void btnupload_Click(object sender, EventArgs e)
{
if (Page.IsValid) //save the image
{
if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
{

HttpFileCollection fileCollection = Request.Files;
for (int i = 0; i < fileCollection.Count; i++)
{
string ext = Path.GetExtension(fileCollection[i].FileName);
if( ext==".jpeg"|| ext==".png"||ext==".tiff"||ext==".pdf"||ext==".jpg")
{
HttpPostedFile uploadfile = fileCollection[i];
string fileName = Path.GetFileName(uploadfile.FileName);
if (uploadfile.ContentLength < 1048576 && uploadfile.ContentLength>0)
{
string name = "StdDoc" + (DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString()) + RandomNumber(20) + ext;
uploadfile.SaveAs(Server.MapPath("~/Docs/") + name+ext);
lblMessage.Text += fileName + " Saved Successfully
";
testng t = new testng();
t.Add(0, name);

}
}

}
}
}
}

public DataSet Add(int id,string filename)
{
return SQLDatabaseOperations.ExecuteDataset(WiCommonProperties.ConnectionString, CommandType.StoredProcedure, "abc",id, filename);
}

public DataSet addinfo(int id,string name,string city)
{
return SQLDatabaseOperations.ExecuteDataset(WiCommonProperties.ConnectionString, CommandType.StoredProcedure, "pqr",id,name,city);
}

0
Iftikar Hussain13y ago
still you can do, can you share me how you are passing multiple documents to the sp

Regards,
Iftikar
0
Kavita Pharde13y ago
thanks for reply but i want to store multiple documents at time so i cant use single sp
0
Iftikar Hussain13y ago
Hi,
Are doing both in a single SP? if yes then you can use

in your sp after inserting record into StudentInfo table

declare int

SELECT @stuid=IDENT_CURRENT('StudentInfo ')

then write insert for document upload

Let me know if you need any more information

Regards,
Iftikar
0