Click here to Skip to main content
15,891,942 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
leDbConnection con = new OleDbConnection(); 
DataTable dt = new DataTable(); 
con.ConnectionString = "Provider=MSDAORA;Data Source=DATA;Password=**********;User ID=********"; 
con.Open(); 
string sqlcommand = "INSERT INTO GROUPS(GROUP_ID, GROUP_NAME,DEPT_ID) " + "VALUES(SEQ_MAX_GROUP_ID_NO.NEXTVAL,@groupName, SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT WHERE DEPARTMENT_NAME=@depName)"; 
OleDbCommand command = new OleDbCommand(sqlcommand, con); 
command.Parameters.AddWithValue("@groupName", textBox1.Text); 
command.Parameters.AddWithValue("@depName", comboBox1.SelectedItem); 
OleDbDataAdapter oda = new OleDbDataAdapter(command); 
oda.Fill(dt); 
dataGridView2.DataSource = dt; 
con.Close();
Posted
Updated 24-Jun-15 0:02am
v5
Comments
TenmanS14 24-Jun-15 4:11am    
hope you don't reuse that uname/password elsewhere....
Richard MacCutchan 24-Jun-15 4:12am    
Use your debugger to check the values set in your parameters, which you are inserting without any validity checks.
Sinisa Hajnal 24-Jun-15 5:00am    
Try enclosing subselect for DEPT_ID in parentheses.
Change your user and password immediately.
Add validation to textbox1 and combobox1 to ensure there are indeed values provided.
Name your controls something indicative of their function (i.e. txtGroupName and cboDepartmentName or something like that)
Wrap the whole block in try..catch..finally and dispose of the connection and command in finally (or use using blocks)
Jörgen Andersson 24-Jun-15 6:00am    
What are you actually trying to do?
Are you trying to insert values into the database? Or do you want to query the database to fill your Gridview with data?
As it is at the moment, this whole code snippet does not make any sense!

1 solution

The problem is with the sub query to insert "DEPT_ID" within the insert statement. Your sub query actually returns a table which is not valid.

SELECT DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT WHERE DEPARTMENT_NAME=@depName

You actually need to use top 1. The query should be like this:

SELECT top 1 DEPT_ID FROM PERSONNEL_TEMP.DEPARTMENT WHERE DEPARTMENT_NAME=@depName
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900