I am a new user to Visual Studio and SQL Server. This is probably a question that has been asked before but I can't seem to find it anywhere. I have an SQL database with two tables. One has a primary key and the second has a primary key with a foreign key referring back to the first table. I display the fields that are of interest in about 9 text boxes. My question is how do I use the text box containing the key for the second table and derive the rest of the information to display in the other text boxes I type in the number and should be able to get the rest of the information but I'm just plain stuck. I know that I can use the views and the sql queries native to visual studio/sql server, but I was trying to find a different solution.
Thanks
Ken
SQL lookup
nx886
The binding on binding each text box was very helpful. I'm only missing one step. The datasets are already defined within visual studio. Is there a 'this' field that will translate from each of the keys to the row number in the sql database The commands you lined out don't seem to work in my version visual studio 2005 express edition.
Thanks for your help
Ken
Darren Clark
ok so you have a key textbox lets call it txtPrimaryKey.
on the after Update event of txtPrimaryKey create a sqlConnection, DataAdapter, and DataSet. then bind the rest of the text boxes to the dataset.
C# Code is like this
[code]
SqlConnection conn = new SqlConnection("myconnectionstring");
string strSql = "SELECT * FROM TABLE2 where ForeignKey = @PK;";
SqlDataAdapter da = new SqlDataAdapter(strSql,conn);
DataSet ds = new DataSet();
try{
conn.open();
da.Fill(ds);
conn.close();
}
catch(SqlException ex){
throw(ex);
}
//bind for each text box
this.txtField1.Text = ds.Tables[0].Rows[0].ItemArray[columnIndex].ToString();
[/code]
the binding can be done a couple ways, the above example shows im filling in txtField1 with the first columns data of the first row, in the first table in the dataset (your dataset will only include one table, but can include more)
hth,