Database

25-Jun-05 17:08

I want to take all the data from a column of a table. Foreach value i get, I used it as a field to my insert function into another table . I some kind like
for(int i=0;i

Luis Alonso Ramos25-Jun-05 17:20

25-Jun-05 17:20

Yes, you could do it very easily like this:

string sql = "INSERT INTO Table2 VALUES (@value1, .....)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@value1", SqlDbType.Int);  // or whatever type
 
foreach(DataRow row in table1.Rows)  // I like it better than for in this case
{
    cmd.Parameters[0].Value = row[1];  // Second column into the parameter
    cmd.ExecuteNonQuery();  // Make the insert
}

It can be done with a stored procedure in the same way, only if you change the sql variable to the name of a stored procedure that takes one parameter named value1.

I hope it helps.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Blue_Skye26-Jun-05 1:44

26-Jun-05 1:44

I means that we'll do all that things in a single store procedure, not in C# (include the way we take each row from a table). And then, in C#, we just call that store procedure to execute automatic. Can you help me?
Thanks your reply. Smile | :)

Luis Alonso Ramos26-Jun-05 5:31

26-Jun-05 5:31

Ah I see. You could try this:

INSERT INTO Table2 (valueFromTable, fixedColumn1, ...) SELECT column1, fixedValue1 ... FROM Table1

I'm no SQL expert, but if I remember correctly, I once used something like that to copy a table.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

toxcct26-Jun-05 7:33

toxcct

26-Jun-05 7:33

it won't work until you don't provide a values statement into your insert

TOXCCT >>> GEII power
[toxcct][VisualCalc]

Luis Alonso Ramos26-Jun-05 9:12

26-Jun-05 9:12

Yes, it works. I've used the following statement in a stored procedure:

INSERT INTO Shutters(ID_pedido, Fabricado, Precio, Descripcion, Tipo, Madera, Color, Acabado)
    (
        SELECT @ID_pedido, 0, Precio, Descripcion, Tipo, Madera, Color, Acabado
            FROM Cotizacion_Shutters
            WHERE ID_pedido = @ID
    )</code>

and it works as expected, copying some records from one table to another with most columns the same, and changing only the values of one column (Fabricado, set to 0).

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Blue_Skye26-Jun-05 20:13

26-Jun-05 20:13

Wow, it's too strange and really interesting Laugh | :laugh:

. I've never seen it before (even in SQL Help). You're right, it really works... Thanks a lot! Smile | :)

Luis Alonso Ramos27-Jun-05 5:15

27-Jun-05 5:15

Blue_Skye wrote:
You're right, it really works...

I know, I learned it here! Big Grin | :-D

I'm glad to have helped you. Good luck! Smile | :)

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

Blue_Skye26-Jun-05 7:40

26-Jun-05 7:40

I've just found the solution. The way i solve the problem is using cursor :

declare @VariableName datatype<br />
declare  CursorName<br />
for<br />
  select TableColumn from Table1<br />
open CursorName<br />
  fetch next from CursorName into @VariableName<br />
  while(@@FETCH_STATUS = 0)<br />
  begin<br />
    Insert into Table2 values(@VariableName,...,...)<br />
    fetch next from CursorName into @VariableName<br />
  end<br />
close CursorName<br />
deallocate CursorName

The example i used here take just one column but you can use as many columns as you like. This cursor remind me while(Reader.Read()) in C# Smile | :)

Thanks your reply. I'm very happy to talk with you Smile | :)

Luis Alonso Ramos26-Jun-05 9:13

variable queries for vb.net

26-Jun-05 9:13

I'm glad you solved it, but cursors are very inefficient. I would recommend you at least try my idea, it should work much faster. See my response to toxcct above; the code I gave you works.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

updating a database

steve_rm25-Jun-05 2:01

steve_rm

25-Jun-05 2:01

Hello,

When l got to update my database l get this error message "Missing the datacolumn 'DepartmentName' in the DataTable 'Employee' for the sourceColumn 'DepartmentName' ".

I have a database application. 2 tables. Department and Employee.
Department -> DepartmentCode (PK)
DepartmentName

Employee -> EmployeeID (PK)
Name
Surname
DepartmentCode (FK)

I have create this relationship in code using relationships in the form load event. see below:

<br />
private void frmEmployees_Load(object sender, System.EventArgs e)<br />
		{<br />
			try<br />
			{<br />
				cnn.Open();<br />
				//Fill the DataSet with the Employee details<br />
				OleDbCommand cmd = cnn.CreateCommand();<br />
				cmd.CommandType = CommandType.Text;<br />
				cmd.CommandText = "SELECT * FROM Employee";<br />
				da.SelectCommand = cmd;<br />
				//Gets all the details for the database, e.g. Primary keys<br />
				da.FillSchema(ds,SchemaType.Source,"Employee");<br />
				da.Fill(ds,"Employee");<br />
				<br />
				//Fill the DataSet wtih the Department details<br />
				cmd.CommandText = "SELECT * FROM Department";<br />
				da.SelectCommand = cmd;<br />
				da.FillSchema(ds,SchemaType.Source,"Department");<br />
				da.Fill(ds,"Department");<br />
				<br />
				//Assign the parent column of the Department table and child column of the<br />
				//employee table. Remember - DepartmentCode PK in Department table, and <br />
				//FK in the Employee Table.<br />
				DataColumn parentColumn = ds.Tables["Department"].Columns["DepartmentCode"];<br />
				DataColumn childColumn = ds.Tables["Employee"].Columns["DepartmentCode"];<br />
				<br />
				//Clear the relation of any data. Not really nessessary as we are creating from new.<br />
				//However, if we are to reuse then we must clear all contents.<br />
				ds.Relations.Clear();<br />
				drEmployees = new DataRelation("EmployeeDetails",parentColumn,childColumn);<br />
				ds.Relations.Add(drEmployees);<br />
				<br />
				//Display the details of the first employee in the database.<br />
				txtEmployeeID.Text = ds.Tables["Employee"].Rows[0]["EmployeeNumber"].ToString();<br />
				txtFirstName.Text = ds.Tables["Employee"].Rows[0]["FirstName"].ToString();<br />
				txtSurname.Text = ds.Tables["Employee"].Rows[0]["Surname"].ToString();<br />
				txtDateOfBirth.Text = ds.Tables["Employee"].Rows[0]["DateOfBirth"].ToString();<br />
				txtAddress1.Text = ds.Tables["Employee"].Rows[0]["Address1"].ToString();<br />
				txtAddress2.Text = ds.Tables["Employee"].Rows[0]["Address2"].ToString();<br />
				cboDepartment.Text = ds.Tables["Employee"].Rows[0]["DepartmentCode"].ToString();<br />
<br />
				string departmentCode = ds.Tables["Employee"].Rows[0]["DepartmentCode"].ToString();<br />
				DataRow[] departmentRow = ds.Tables["Department"].Select("DepartmentCode = '" + departmentCode + "' ");<br />
				string departmentName = departmentRow[0]["DepartmentName"].ToString();<br />
				<br />
				<br />
				this.UpdateDepartments();//Call to display combo box information<br />
			}	<br />
			catch ( OleDbException ex )<br />
			{<br />
				MessageBox.Show(ex.Message);<br />
			}<br />
			catch ( Exception ex )<br />
			{<br />
				MessageBox.Show(ex.Message);<br />
			}<br />
		}<br />

When l click the update button, l run this code and get an error on the update.

<br />
ds.Tables["Employee"].Rows[0]["FirstName"] = txtFirstName.Text;<br />
ds.Tables["Employee"].Rows[0]["Surname"] = txtSurname.Text;<br />
ds.Tables["Employee"].Rows[0]["DepartmentCode"] = departmentCode;<br />
<br />
da.Update(ds,"Employee"); //Error ocurrs here<br />

I hope someone can help me with this question. very important.

Thanks in advance,

Steve

WetRivrRat24-Jun-05 22:37

WetRivrRat

24-Jun-05 22:37

Hey guys, morning....
Got a prob thats kept me up for a little too long tonight,
I need to find a way (is it possible?) to have a variable query written in vb.net that will change based on a text box input. Yah, i could write an if/then statment to get the solution out the door, but that seems the long way around, plus it would be more time consuming as I have about 60 different possible 'variables'.
Basically I have 60 different machines that record money transactions, and I need to beable to search by either a textbox input or a dropdown list of the machines (named in order from 01-60) to pull the value amount currently in the machines.
Help! Confused | :confused:

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

Re: variable queries for vb.net

Colin Angus Mackay25-Jun-05 2:38

Colin Angus Mackay

25-Jun-05 2:38

wetrivrrat wrote:
I need to find a way (is it possible?) to have a variable query written in vb.net that will change based on a text box input.

Yes, of course.

myCommand.CommandText = "SELECT someColumns From someTable WHERE machineName = @machineName";
myCommand.Parameters.Add("@machineName", someMachineName);

Assumes that your command object is called myCommand.
The variable someMachine contains the name of the machine that you retrieved out of your user interface.

Does this help?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: variable queries for vb.net

WetRivrRat27-Jun-05 2:40

WetRivrRat

27-Jun-05 2:40

in your code i'm assuming you're writing it in a command function? right, if
so then i'm only limited to 'GetType' unless i add the 'as string' or
someother class. so I guess my first Q would be, what class are you
expecting this to go under? if it helps any I was just using a
OleDbDataAdapter and DataSet to connect to sql(so if i had to use access later its already ready for it!).
Second Q was, is my interpretation of your code correct?

Function LoadLastInvoice() 
 
 Dim VAMachineName As String 
 
 VAMachineName = TextBox3.Text
 
 LoadLastInvoice.CommandText = "SELECT last_invoice From Location_table WHERE
 Location_ID = @machineName"
 
 LoadLastInvoice.Parameters.Add("@machineName", VAMachineName)
 
 End Function

Because I'm getting the following crash-related error...
'Object variable or With block variable not set'
and I'm not sure how to set the @machineName variable as I tried several ways and nothing seemed to work... Confused | :confused:

Thanks

Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."

Why is common sense such an un-common comodity?

hello,sqldatabase registering error

Anonymous24-Jun-05 10:53

Anonymous

24-Jun-05 10:53

hello my dear,
want to register the sql database from my local computer using enteprize manager but this error apeared :
sql server does not exist or access is denied.
i am sure about the correction of the user name and password and server name.my local windows is xp2000 but the windows of the server is 20003pro.the version of the sql in my local is personal no enteprize.
thanks in advanced

Re: hello,sqldatabase registering error

NeverHeardOfMe24-Jun-05 12:25

NeverHeardOfMe

24-Jun-05 12:25

Anonymous wrote:
i am sure about the ... server name.

OK on your local intranet, otherwise you will need to use an IP address. Could the problem be as simple as that?

cheers
Phil

Re: hello,sqldatabase registering error

Anonymous24-Jun-05 21:33

Anonymous

24-Jun-05 21:33

thank you,
I used the ip but did'nt get advantage.

bind parameter in a datagrid's column ?

newprog24-Jun-05 2:30

newprog

24-Jun-05 2:30

can i bind a parameter to a column of a datagrid, or it can only be binded in table's columns?

I have the aggregated values of total sales for each year(from sh schema) in a datagrid, and i want to retrieve all the detail tuples from a year which user will 'click'.

i use the following query to do this:

select decode(prod_subcategory,null,'ALL',prod_subcategory) as subcateg,decode(prod_category,null,'ALL',prod_category) as categ,sum(amount_sold) as dolars
from sh.products,sh.sales,sh.times
where products.prod_id=sales.prod_id and times.time_id=sales.time_id and end_of_cal_year=:param1
group by rollup(prod_category,prod_subcategory)

where param1 it's an OracleParameter with parameter->set_SourceColumn("END_OF_CAL_YEAR") and i get an
ORA-01008 error( not all variables binded )

any help?

thanks in advance

SQL Datetime - Independent Format

Seraphin24-Jun-05 0:38

Seraphin

24-Jun-05 0:38

A big problem is a Datetime object in the CommandText property. I always get an error. Is .NET unable to convert Datetime object into the right SQL Server system format? When it's possible ... how to solve it?

Re: SQL Datetime - Independent Format

Colin Angus Mackay24-Jun-05 3:42

Colin Angus Mackay

24-Jun-05 3:42

Seraphin wrote:
A big problem is a Datetime object in the CommandText property.

Why are you putting a DateTime in the CommandText property?

Seraphin wrote:
Is .NET unable to convert Datetime object into the right SQL Server system format?

.NET is perfectly capbable of converting a DateTime object to what ever format you wish in multiple calendars (should you so wish it).

Seraphin wrote:
how to solve it?

If you need to use a DataTime (or anything else for that matter) in a query you should use parameters. This reduces the possibility of security holes.

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE SomeDate > @theDate";
cmd.Parameters.Add("@theDate", myDateTimeObject);

Does this help?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: SQL Datetime - Independent Format

Seraphin25-Jun-05 3:44

Seraphin

25-Jun-05 3:44

Thank you. That's what I want.

DateTime datatype

Blue_Skye23-Jun-05 18:09