|
Thanks. I'm after any example you can give me. Could you possibly give me an example. I tried the following but the whole injection attack thing. I think the ands could be wrog. Please feel free to
string sql = "INSERT INTO employees(Firstname,Lastname) Values ('" + FirsttextBox.Text.ToString() + "' , '" + LasttextBox.Text.ToString() + "' and Insert into Role (Role) Values ('" + RolecomboBox1.Text.ToString() + "')Insert into Manager (MFirstname) Values ('" + ManagercomboBox1.Text.ToString() + "') Insert into Division (DivisionName) Values ('" + DivisioncomboBox1.Text.ToString() + "')";
|
|
|
|
|
A trigger may not always be the best solution. You have to look at performance and other aspects also.
only two letters away from being an asset
|
|
|
|
|
Your best solution would be to create a stored procedure and call it within your code "ExecuteNonQuery." The stored procedure will do all the work of inserting the records into the proper tables. As far as I know, there is not a way, with SQL Server, to insert into multiple tables with one INSERT statement. This also gives you the ability to do error checking on the back-end.
|
|
|
|
|
Thanks, I do have the statement in the nonexecutequery block already.
Is it possible to give me a little exampypoos???;)
|
|
|
|
|
CREATE PROCEDURE sp_InsertEmployee
@Firstname nvarchar(50),
@Lastname nvarchar(50),
@Role nvarchar(50),
@Manager nvarchar(50),
@Division nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO EMPLOYEES (FIRSTNAME, LASTNAME) VALUES (@FIRSTNAME, @LASTNAME)
INSERT INTO [ROLE] ([ROLE]) VALUES (@ROLE)
INSERT INTO MANAGER (MFIRSTNAME) VALUES (@MANAGER)
INSERT INTO DIVISION (DIVISIONNAME) VALUES (@DIVISION)
END
GO
|
|
|
|
|
Thanks so much. Is this case sensitive. You have all the names correct so I copied and pasted this before my executenonquery statment and there are red lines all over it. Okay I feel really dumb about now. It came up with ; errors so I'm guessing that I don't just copy exactly what you have done?
Oh god. I'm waiting to be told off. 
|
|
|
|
|
That was a SQL statement for creating a stored procedure in SQL Server. Once you create it in SQL Server, you can then call it using a class like SqlDataReader and use the method ExecuteNonQuery. Don't forget to add your parameters!
|
|
|
|
|
Don't forget to use "Transaction" when you are trying to manipulate data from more than one table...
Thanks and Regards,
Michael Sync ( Blog: http://michaelsync.net)
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message". Why vote? Plz Read it here. Thank you. 
|
|
|
|
|
As I read the thread, I was wondering about exactly this.
Cheers,
Vıkram.
Be yourself, no matter what they say.
- Sting, Englishman in New York.
|
|
|
|
|
Bye the way. Can I ask if my photo can be seen? I don't know if it saved properly.
|
|
|
|
|
oh my god don't I feel stupid.
I'll give it a try. Thanks
|
|
|
|
|
Okay I apologise for pasting so much info but I've done as you said. My code looks like this, but I get the null error which I've shown you at the bottom.
If you can help, I'll vote you high for every message you've sent to me today. ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif) I notice that I got this same error when I was using my normal insert statement. what am I doing wrong you think?
SqlCommand sqlC = new SqlCommand("sp_InsertEmployee", myConnection);
sqlC.CommandType = CommandType.StoredProcedure;
sqlC.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.VarChar, 0, "Firstname"));
sqlC.Parameters.Add(new SqlParameter("@Lastname", SqlDbType.VarChar, 50, "Lastname"));
sqlC.Parameters.Add(new SqlParameter("@Role", SqlDbType.VarChar, 0, "Role"));
sqlC.Parameters.Add(new SqlParameter("@Manager", SqlDbType.VarChar, 50, "Manager"));
sqlC.Parameters.Add(new SqlParameter("@Division", SqlDbType.VarChar, 0, "Division"));
//sqlC.Parameters[0].Value = 4;
sqlC.Parameters[0].Value = FirsttextBox.Text.ToString();
sqlC.Parameters[1].Value = LasttextBox.Text.ToString();
sqlC.Parameters[2].Value = RolecomboBox1.Text.ToString();
sqlC.Parameters[3].Value = ManagercomboBox1.Text.ToString();
sqlC.Parameters[4].Value = DivisioncomboBox1.Text.ToString();
int i = sqlC.ExecuteNonQuery();
Error::::Cannot insert the value NULL into column 'RoleID', table 'Dev_RST.dbo.employees'; column does not allow nulls. INSERT fails.
The statement has been terminated.
|
|
|
|
|
Okay it works only slightly but what I notice from this is that the firstname and lastname are update in employees but I've noticed also that I just want the ID's corresponding to that name to be updated also in the employee table istead of saving new entries into the other tables. For example there are now multiple roles called developer, or graduate just with different ID's when what I actually need to do is just update the employee table with the new person and the ID that corresponds to their selected role.
Thanks and sorry for being a pain.
|
|
|
|
|
In reponse to Michael & Vikram, yes, you should be using transactions on multiple statements. The example I provided to you was based on your original post. If your intentions are not alwasy to insert records, then you will need to modify the stored procedure to check for the Roles, Managers, etc. If found then update your employee table. You will need to know SQL. If you are using a Listbox or Combobox, you can also save the PK in the control collection and just pass that to your stored procedure. There are many ways to kill this bird, it just depends on your requirements.
|
|
|
|
|
Okay but what about my last question. how do I update the employee table wiht the ID corresponding the combobox selection.
EG. Employee table has empID, RoleID, DivisioID etc. But then I have a Role table with roleid description and role, and division table with divisionid, division etc.
When a user enters a new employee name, and selects role for example, the employee table should update with a new name and put the correct roleID against the role.
Obviously I'm not good with sql at all. 
|
|
|
|
|
I've also been advised that I can do this by creating multiple insert statements in the same block. Is this possible?
If so how?
string sql =
sql =
sql=
etc etc?????? Is this how you would do it? To me it would insert the data but it won't be related to each other. EG. I need to update the division, manager, role table when I insert a new employee.
|
|
|
|
|
Hi!
I am trying to create an XML Element that has XML attribute and appened it to and existing XML document.
how do i do this?
Do the prosedure is:
1)create attributes (2 - 3).
2) create Element and attach the attributes? (How?)
3) append to an Element list? Or XMLDoc? (How)
Thanks
|
|
|
|
|
|
1)
XmlAttribute myAttrib = new XmlAttribute();
myAttrib.Value = "Some value";
2) on then XmlDocument you opened (I'm going to use xmlDoc as an example)
XmlNode myNode = xmlDoc.CreateElement("tagname");
myNode.AppendChild(xmlDoc.CreateTextElement("The tag's text node Value"));
3) use the .AppendChild() as above to attach to the Document node
xmlDoc.DocumentElement.AppendChild(myNode);
*note* you need to use .Save() to well save the changes/appends
-Spacix
All your skynet questions[^] belong to solved
|
|
|
|
|
How do i attch the attribute to the new node?
is it before i append it?
Thanks 
|
|
|
|
|
look at the .Attributes.Append() method for a XmlNode...
|
|
|
|
|
hi all , how you doing ??
I need to create about 50 button every one had an image ..
my Question is how I can determine which button I pressed ?!
please give me a hint ....
and here is my code .....
private void CreateButtons()<br />
{<br />
for (int x =0 ; x < 10 ; x++)<br />
{<br />
bs = new Button();<br />
bs.ImageList = imageList1;<br />
bs.ImageIndex = x;<br />
bs.Size = new Size(17,17);<br />
bs.Location = new Point(x * 17 , 0);<br />
bs.Visible = true;<br />
bs.Click += new EventHandler(bs_Click);<br />
Controls.Add (bs);<br />
}<br />
}<br />
<br />
private void bs_Click(object sender, System.EventArgs e)<br />
{<br />
Console.WriteLine ("Which Button I pressed");<br />
<br />
}
I know nothing , I know nothing
|
|
|
|
|
build the buttons into an array of buttons?
I think you can do this...
Button[] bs = new Button[10];
for (int x = 0; x < 10; x++)
{
bs[x] = new Button();
bs[i].ImageList = imageList1;
bs[x].ImageIndex = x;
bs[x].Size = new Size(17, 17);
bs[x].Location = new Point(x * 17, 0);
bs[x].Visible = true;
bs[x].Click += new EventHandler(bs_Click);
Controls.Add (bs);
}
private void bs_Click(object sender, System.EventArgs e)
{
Console.WriteLine ("The Button I pressed is {0}", sender.ToString().Split(',')[0].Split('.')[4]);
}
edit: to draw 50 in 5 rows of 10 stagger the array
Button[] bs = new Button[50];
for (int y = 0; y < 5; y++)
{
for (int x = 0; x < 10; x++)
{
bs[x] = new Button();
bs[i].ImageList = imageList1;
bs[x].ImageIndex = x;
bs[x].Size = new Size(17, 17);
bs[x].Location = new Point(x * 17, y * 17);
bs[x].Visible = true;
bs[x].Click += new EventHandler(bs_Click);
Controls.Add(bs);
}
}
-- modified at 17:54 Wednesday 29th August, 2007
|
|
|
|
|
thank you so much
this is exactly what i need ,
you solve a big prb ,
I know nothing , I know nothing
|
|
|
|
|
The sender object passed to the event handler is a reference to the button.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
