Get records from one table and insert into another one

I make that but I did not work, thanks

 

SqlCommand cmd2;cmd = new SqlCommand("SELECT CS_ID FROM Tbl_Conselheiros WHERE CS_TIPO IN (5,6,7)", conn);

dr = cmd.ExecuteReader();

 

while (dr.Read())

{

cmd2 =
new SqlCommand("SP_PRES_GRAVAR", conn);

cmd2.CommandType = CommandType.StoredProcedure;

SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);id.Value = Convert.ToInt32(dr[0]);

cmd2.Parameters.Add(id);

SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8);

data2.Value = FormataData();

cmd2.Parameters.Add(data2);

SqlParameter tipo2 = new SqlParameter("@ID_TIPO", SqlDbType.VarChar, 1);

tipo2.Value = ddlTipo.SelectedItem.Value;

cmd2.Parameters.Add(tipo2);

SqlParameter status = new SqlParameter("@ID_STATUS", SqlDbType.VarChar, 1);status.Value = "C";

cmd2.Parameters.Add(status);

SqlParameter idconsesubst = new SqlParameter("@ID_CONSE_SUBST", SqlDbType.Int, 4);

idconsesubst.Value = Convert.DBNull;

cmd2.Parameters.Add(idconsesubst);

SqlParameter justificativa = new SqlParameter("@JUSTIFICATIVA", SqlDbType.VarChar, 200);justificativa.Value = Convert.DBNull;

cmd2.Parameters.Add(justificativa);

 

cmd2.ExecuteNonQuery();

}


Best regards,

Ivan Andrade
0
Roberot
8/17/2007 4:19:25 PM
asp.net.web-forms 93655 articles. 6 followers. Follow

5 Replies
850 Views

Similar Articles

[PageSpeed] 58

Some Points need to be cheked..

Is the dataReader returning any rows ?

Also you can't use the same connection for a query which is associated with a datareader already.

 

 


Satya
------------------------------
Mark as answer if you think
this helped you Yes
0
satya_tanwar
8/17/2007 5:01:00 PM

If before cmd2.ExecuteNonQuery(); I close the connection:

dr.Close();

It works for the first record but after returning to the loop:

while (dr.Read())

it says that the connection is closed so that cannot continue...

How to do that?

 

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class REL_FREQ_CONSE : System.Web.UI.Page

{

# region Page_Load
protected void Page_Load(object sender, EventArgs e)

{

lblConfirma.Visible = false;

if (!IsPostBack)

{

txtData.Text = DataHoje();

// BindData();

}

}

# endregion

# region
Tratamento de datasstring DataHoje()

{

string dia = DateTime.Now.Day.ToString();

string mes = DateTime.Now.Month.ToString();

string ano = DateTime.Now.Year.ToString();

if (dia.Length == 1) { dia = "0" + DateTime.Now.Day.ToString(); }if (mes.Length == 1) { mes = "0" + DateTime.Now.Month.ToString(); }

 

return dia + "/" + mes + "/" + ano;

}

string FormataData()

{

string dia = txtData.Text.Substring(0,2);

string mes = txtData.Text.Substring(3,2);

string ano = txtData.Text.Substring(6,4);

if (dia.Length == 1) { dia = "0" + DateTime.Now.Day.ToString(); }

if (mes.Length == 1) { mes = "0" + DateTime.Now.Month.ToString(); }

return ano + mes + dia;

}

# endregion

# region
BindData - Popula DataGridvoid BindData()

{

if (txtData.Text != string.Empty)

{

SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CONEXAO"]);

SqlCommand cmd = new SqlCommand("SP_PRES_CONS_PRESENTES", conn);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tipo = new SqlParameter("@TIPO", SqlDbType.VarChar, 1);

tipo.Value = ddlTipo.SelectedItem.Value;

cmd.Parameters.Add(tipo);

SqlParameter data = new SqlParameter("@DATA", SqlDbType.DateTime, 8);data.Value = Convert.ToDateTime(txtData.Text);

cmd.Parameters.Add(data);

conn.Open();

SqlDataReader dr = cmd.ExecuteReader();if (dr.Read())

{

dg.DataSource = dr;

dg.DataBind();

}

else

{

dr.Close();

SqlCommand cmd2= new SqlCommand("SP_PRES_GRAVAR", conn);

cmd2.CommandType = CommandType.StoredProcedure;

SqlCommand cmdnovo = new SqlCommand("SELECT CS_ID FROM Tbl_Conselheiros WHERE CS_TIPO IN (5,6,7)", conn);SqlDataReader drnovo = cmdnovo.ExecuteReader();

 

while (drnovo.Read())

{

SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);

id.Value = Convert.ToInt32(drnovo[0]);

cmd2.Parameters.Add(id);

SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8);

data2.Value = FormataData();

cmd2.Parameters.Add(data2);

SqlParameter tipo2 = new SqlParameter("@ID_TIPO", SqlDbType.VarChar, 1);

tipo2.Value = ddlTipo.SelectedItem.Value;

cmd2.Parameters.Add(tipo2);

SqlParameter status = new SqlParameter("@ID_STATUS", SqlDbType.VarChar, 1);status.Value = "C";

cmd2.Parameters.Add(status);

SqlParameter idconsesubst = new SqlParameter("@ID_CONSE_SUBST", SqlDbType.Int, 4);

idconsesubst.Value = Convert.DBNull;

cmd2.Parameters.Add(idconsesubst);

SqlParameter justificativa = new SqlParameter("@JUSTIFICATIVA", SqlDbType.VarChar, 200);justificativa.Value = Convert.DBNull;

cmd2.Parameters.Add(justificativa);

 

}

drnovo.Close();

cmd2.ExecuteNonQuery();

dr = cmd.ExecuteReader();

if (dr.Read())

{

dg.DataSource = dr;

dg.DataBind();

}

}

 

conn.Close();

}

else { txtData.Focus(); }

}

# endregion

# region Eventos do DataGrid

# region Editarprotected void dg_EditCommand(object source, DataGridCommandEventArgs e)

{

dg.EditItemIndex = e.Item.ItemIndex;

dg.Columns[1].Visible =
true;

BindData();

}

# endregion

# region
Gravarprotected void dg_UpdateCommand(object source, DataGridCommandEventArgs e)

{

if (txtData.Text != string.Empty)

{

SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CONEXAO"]);

SqlCommand cmd = new SqlCommand("SP_PRES_GRAVAR", conn);

SqlCommand cmd2 = new SqlCommand("SP_PRES_LISTAR_CONSE_SUBST", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd2.CommandType = CommandType.StoredProcedure;

if (e.Item.ItemType != ListItemType.Header && e.Item.ItemType != ListItemType.Footer && e.Item.ItemType != ListItemType.Pager)

{

string sStatus = null;

DropDownList ddl3 = (DropDownList)e.Item.FindControl("ddlStatus");

DropDownList ddl4 = (DropDownList)e.Item.FindControl("ddlConselheiros");

TextBox txt = (TextBox)e.Item.FindControl("txtStatus");

Label lbl = (Label)e.Item.FindControl("lblId");

if (ddl3.SelectedItem.Value == "C") { sStatus = "C"; }

if (ddl3.SelectedItem.Value == "J") { sStatus = "J"; }

if (ddl3.SelectedItem.Value == "F") { sStatus = "F"; }

if (ddl3.SelectedItem.Value == "L") { sStatus = "L"; }

SqlParameter id = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);

id.Value = Convert.ToInt32(lbl.Text);

cmd.Parameters.Add(id);

SqlParameter data = new SqlParameter("@DATA", SqlDbType.VarChar, 8);

data.Value = FormataData();

cmd.Parameters.Add(data);

SqlParameter tipo = new SqlParameter("@ID_TIPO", SqlDbType.VarChar, 1);

tipo.Value = ddlTipo.SelectedItem.Value;

cmd.Parameters.Add(tipo);

SqlParameter status = new SqlParameter("@ID_STATUS", SqlDbType.VarChar, 1);

status.Value = sStatus;

cmd.Parameters.Add(status);

SqlParameter subst = new SqlParameter("@ID_CONSE_SUBST", SqlDbType.Int, 4);

bool substituto = true;

if (sStatus == "C") { substituto = false; }if (ddl4.SelectedItem.Value != string.Empty && substituto == true)

{

subst.Value =
Convert.ToInt32(ddl4.SelectedItem.Value);

}

else { subst.Value = Convert.DBNull; }

cmd.Parameters.Add(subst);

SqlParameter justif = new SqlParameter("@JUSTIFICATIVA", SqlDbType.VarChar, 200);

if (txt.Text != "") { justif.Value = txt.Text; }else { justif.Value = Convert.DBNull; }

cmd.Parameters.Add(justif);

// Parâmetros de verificação

// Evita regravar um registro já existente

SqlParameter id_conse = new SqlParameter("@ID_CONSE", SqlDbType.Int, 4);id_conse.Value = Convert.ToInt32(lbl.Text);

cmd2.Parameters.Add(id_conse);

SqlParameter data2 = new SqlParameter("@DATA", SqlDbType.VarChar, 8);

data2.Value = FormataData();

cmd2.Parameters.Add(data2);

conn.Open();

SqlDataReader dr = cmd2.ExecuteReader();if (!dr.Read())

{

cmd2.Connection.Close();

cmd.Connection.Open();

cmd.ExecuteNonQuery();

}

conn.Close();

cmd.Parameters.Clear();

cmd2.Parameters.Clear();

lblConfirma.Visible =
true;lblConfirma.Text = "Inclusão efetuada com sucesso!";

}

txtData.Text = DataHoje();

dg.EditItemIndex = -1;

dg.Columns[1].Visible =
false;

BindData();

}

}

# endregion

# region
Cancelarprotected void dg_CancelCommand(object source, DataGridCommandEventArgs e)

{

dg.EditItemIndex = -1;

dg.Columns[1].Visible =
false;

BindData();

}

#endregion

# region
Combo Statusprotected void ddlStatus_SelectedIndexChanged(object sender, EventArgs e)

{

DropDownList ddl = (DropDownList)(sender);

DropDownList ddl2 = (DropDownList)(ddl.Parent.Parent.FindControl("ddlStatus"));

DropDownList ddl4 = (DropDownList)(ddl.Parent.Parent.FindControl("ddlConselheiros"));

Panel ddl3 = (Panel)ddl.FindControl("panelStatus");

Panel pnl2 = (Panel)(ddl3.Parent.Parent.FindControl("panelStatus"));

if (ddl2.Text != "C")

{

pnl2.Visible =
true;if (ddl4.Items[0].ToString() != string.Empty)

{

ddl4.Items.Insert(0, new ListItem());

ddl4.Items[1].Selected = false;ddl4.Items[0].Selected = true;

}

}

else { pnl2.Visible = false; }

}

# endregion

#endregion Eventos do DataGrid

#region Botão Carregarprotected void botCarregar_Click(object sender, EventArgs e)

{

BindData();

}

#endregion

}


Best regards,

Ivan Andrade
0
Roberot
8/17/2007 5:50:01 PM

Hi,

I have not read your code but readin your problem i have understood that you are closing the connection after reading the first value in the loop,

you should not do this as datareader requires open connection if you close the connection sqldatareader can not be read so you have to keep the connection open until you have read the values from the datareader and close the connection outside the while loop.

this must solve your problem.


Known is handful and unknown is worldful.

Regards,
Ajay Matharu
http://ajaymatharu.com
0
matharuajay
8/17/2007 7:09:17 PM

Instead of passing one by one record to stored procedure, I would recommend pass the query as an argument to the stored procedure and execute it in the stored procedure..it will be easy and performance will also be gud


Pardeep Bogra (MCA, MCAD)
Systems Develper
0
PardeepBogra
8/17/2007 8:28:53 PM

simple solution is to create  one more connection and use it to execute the sql command.. inside the loop


Satya
------------------------------
Mark as answer if you think
this helped you Yes
0
satya_tanwar
8/20/2007 4:10:39 PM
Reply:

Similar Artilces:

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

selecting one row and inserting it into another table one by one
i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.  after that the process star...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Insert multiple records from one table to another table
Hello. Got a quick problem. I need to post shopping cart items from a temperary table "ShopCart" to a permanent table "tbl_orders". Problem is, I cannot figure out how to iterate through the recordset to post the unique products from "ShopCart" to "tbl_orders". The code below inserts the first product-- but that's it. If there are 6 different products, it will insert the first product 6 times. Public Sub GetShopCart() Dim ordernumber = session("ordernumber") Dim objConn as new SqlConnection(strConn) Dim strSQL as Str...

Getting information from one table and applying it to another table record.G
Hello, I'm creating a website in ASP.NET and C#, My problem is I want to add a new record to a table using data from another table. i.e. Add a book, to set the book category you have to select if from a databound dropdown in another table. All those categories are stored in a different table. I'm not sure how to do that. Any help is appreciated. Thanks a lot!! Gabriel do datasource copy in the server-side or javascript copy in the client-sideRichard Tsuei (Chikun Cui)Contact me via E-mail: ricktsuei@gmail.comWeblog: http://ricktsuei.cnblogs.com+-+-+-+-+-+-+-+-+-+-+-+-+-...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

how to order records from one table by records of another table?
i have two tables: subject ~~~~~~~ name number (primary key document ~~~~~~~~ name, number (primary key) subject_number (foreign key) what SQL query will return a table (view) of the subjects, ordered by the number of documents each subject has? subjects with no documents should not be included. Hello, Following is a query: SELECT subject.name, Document.name FROM subject INNER JOIN Document1 ON subject.number = Document.subject_number ORDER BY Document.name Dear, this is not a SQL centric blog. So its may not possible every time to solve SQL relat...

How to get one database table data in to an input form to put info in another table.
The table data to pull from is place on the same page as the input form, it is also the same number used as the URL Parameter, so if I coiuld pull it from there it would be easier, what code do I need to do this in my input form? You will need two simple scripts set up. The first will bind the data to your form controls when the page loads. The second will insert the data from the form into the database. The syntax will vary a little depending on your database type and the language you want to use to create the code. Check out this tutorial for how to do all it is you need to know re...

Time out error is occur when one user inserting or updating records in table using transaction and another accessing the record
Hi, I am facing a problem in my application when a user inserting record in table or updating using transaction and another user fething the record at sae time then time out is occur.  I am also trying to use no lock with select statement but it's also not work. Please provide me a appropriate solution.Rakesh Gupta It will good to understand your problem If you can provide the code.  Thanks,santosh_maharajaPlease mark as answer if you got expected solution. Hi,   I am using given below code for insertion : - -  DECLARE @TranName VARCHAR(20);SELECT @TranName...

synchronizing chunk of data of one web form for another web form
I'm making a web application. In one web form; i'm taking inputs from user. Now; there is some info given by user in this page which is needed for web form which is going to be displayed next. i want to display that info automatically on that web form so that user doesn't have to re-input that info in this next web form. Info from first page would be inserted into database when user clicks on the submit button. then that next web form would be displayed. and the chunk of info from previous page should be displayed there!!! how can i do this?? cany anybody help?? nobody can help me!! I...

Inserting record from One table to another in Ms Access
Hello guys, I m trying to insert a record from one table to another on button click. But somehow it doesn't like it. I m having a tempStudent, Student Master, tempApplication & Fees table in Access database. When user submits a form it inserts the data into tempStudent table. tempApplication table has details bout whether the application being sanctioned or not. N e student who's application is sanction and is paying the fees then, what it should do -- copy his records from tempStudent to StudentMaster table. The coding I've done so far is as below: The coding inside the code...

inserting/ updating more than one table from a single web form
 i wanted to ask how to insert values from a single web form into two sql tables, i have been looking and the visual web developer i use doesnt seam to allow me to even atempt it i've tried selecting all the values from two different tables and then adding those two tables to an insert function but it doesnt work likewise the update functioni have values in a table currently a reference number and i want to use this reference number to update the address values in this table so update this field.table1 and thisfield.table2 when ref number = @ refnumber the reference number is presen...

1 record from one table N records from another table using gridview in single row?
Hi there, I have 2 tables named question(questionid,question) and options(optionid,options) In that questions table I got one record and in options table 3 records. Whenever I query I got 3 records 1)question1 choice1 2)question1 choice2 3)question1 choice3 I would like to use gridview(I would like to edit those values) to display these in a single row. like Question1 ch1 ch2 ch3(one record) . But the problem is I always getting 3 rows. I have seen some example(nested datalist) but they are using only one table, I am using 2 tables. Can any one help me to acheive th...

inserting data from one table to another table..
Hi Guys!  I am stuck on inserting data. Hope you will help me. I have two tables name Customer and Address; Customer(cust_id,name,add_fk);    fk=foreign key,  cust_id and add_id are primary key Address(add_id,street,city,zip); Now, how can I retrieve add_id from Address table and insert into Customer table ( in the add_fk field) using stored procedure with parameters? I am using Oracle 9i.  I would appreciate your help. Thanks! Are you using any of this data in between these actions? (eg: Are they going to reside in your page in a grid, or formview?) ...

Web resources about - Get records from one table and insert into another one - asp.net.web-forms

Récord - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

The Record
Great new podcast co-hosted by Chris Parrish and Brent Simmons: The Record brings you the stories you should know about the Mac and Cocoa development ...

Lompoc Record - Lompoc, California News
The first time Jerry Brown was governor of California, he earned the nickname “Gov. Moonbeam.” It was, after all, the tail-end of the Hippie ...

Black Friday could power record November sales
Filed under: Car Buying , Car Dealers , United States Big auto incentives on Black Friday could push November sales into record numbers according ...

Lil BUB shares "New Gravity" from her new album, going on a record signing tour including Rough Trade ...
by Bill Pearis As you may recall, feline internet celebrity Lil BUB is releasing an album, Science & Magic, next week. (Preorders are available ...

Golden State sets record for best start in NBA history
Defending champ Warriors' 111-77 rout of Los Angeles Lakers gives them 16-0 mark

Adele covers People Mag, smashes records & performs ‘Hello’ with The Roots
... of People just for fun, to promote her album and to talk about music. Here are some stories/videos going around this week: Adele smashes records ...

FTC Uses ‘Opaqueness’ To Keep Records Behind Closed Doors
'The FTC's opaqueness is synonymous with the quickness with which it disregards the Constitution'

‘Creed’ Sets Tuesday Preview Record As Thanksgiving B.O. Heats Up, ‘The Good Dinosaur’ Isn’t Far Behind ...
... last night, shelling out $1.4M to MGM/New Line/Warner Bros.’s Creed which made a whopping $1.4M. Warner Bros. is calling this an industry record ...

*NSYNC Members Playfully Say 'Bye Bye Bye' to U.S. Sales Record as Adele Says 'Hello'
*NSYNC Members Playfully Say 'Bye Bye Bye' to U.S. Sales Record as Adele Says 'Hello'

Resources last updated: 11/26/2015 11:09:08 AM