February 20, 2008 at 1:36 am
Hi All, this is my first post so please be nice 🙂
We've just started using Visual Studio 2005 to build applications for our users. I have been on the 2310B course, so so far thats pretty much all i know! I've been playing around and can build the sites fine, problem comes when linking to a database.
We used Visual Studio 2002 on the Course however at work we have 2005, not vastly different but still just enough to confuse me!
Basically what i want to do, is on the TEXT CHANGED event of a Text Field, pass the ID that is entered, into a Stored Procedure (sp_GetClientDetails, input of pID) return the 1 record it obtains and put the fields it returns (Address line 1 to 5, Postcode) into the fields on the form.... All i have so far, is the connection to a database, the DataAdapter and DataSet, I'm not even sure if i need all those!
Sounds simple, but I'm really struggling with the code to link to a Stored Procedure. If anyone can help or point me to some sample code on the web, I've tried looking but i suck at searching it seems as i don't get anything close to what i want!
February 23, 2008 at 10:17 pm
you'll need a sqlconnection and sqlcommand object. You may add parameters to the sqlcommand object to get data in, and you can retrieve results into either a dataset or datareader class depending on your use. There is thorough documentation on the specifics, but this should point you in the right direction.
February 25, 2008 at 1:18 am
Thanks for the advice, i think im half way there, i have it connected to the SP and returning the record into a datagrid. I just now want to return it into some text fields instead, allowing the user to amend it as it might need updating.
February 25, 2008 at 7:31 am
If your data grid is bound to your dataset and you use the form's Load event to populate it then all you need to do is make the grid editable by right-clicking the grid, selecting Show Smart Tag and checking the Enable Editing and Enable Deleting check boxes.
February 25, 2008 at 7:38 am
The dataset is bound to the datagrid, but its not on the form load. You enter an ID into the ID field on the form, click the Submit button which then has the code to call the SP and produce the datagrid.
I right clicked the grid, enabled the smart tag but dont get the option to make it editable.
Presume because its not on form load?
February 25, 2008 at 9:39 am
Are you binding your grid to the data source each time you call you sp? For example:
mysource=GetClientDetails(I'm calling my sp_GetClientDetails here...)
mydatagrid.DataSource=mysource
mydatagrid.DataBind()
February 25, 2008 at 9:55 am
Is the stored procedure always going to return 1 row?
Are you using a datareader or a dataset?
When the user makes a change to the record how are you going to do the update (call another sp)?
If you're sp is always returning 1 row I would use a data reader and manually set the textbox.text poperty to the appropriate column.
txtFirstName.Text = dr("first_name")
txtLastName.Text = dr("last_name")
...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2008 at 1:44 am
All the code i have written to connect and stuff is on the Submit button, so i guess i am calling the connection everytime you click the button, dosent seem very good!
Will change that, when our network drives come back up, grr!
I am using a Dataset which i then bind to a Datagrid, i wasnt aware that i could just use the colums from the Dataset and reference them directly, i was wanting too but couldnt find anyway to do it.
The SP will only ever return 1 row, you enter a persons (ParisID) which is unique in our database. The update side of it will be via another SP, but this i havent looked into yet. Want to get the first part working 🙂
February 26, 2008 at 6:02 am
With only 1 row being returned you may want to use a datareader. It takes fewer resources than a dataset.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2008 at 8:14 am
Hmmm…does this work for you? Have you tried entering an ID with more than 1 character? What I’m getting at, is simply this: the TextChanged event will fire each time a character is keyed in. If you’ve not gotten far enough along in the project to be testing with multi-character IDs, you may not have noticed that…or perhaps your IDs will always be single-character (0 – 9).
I just thought I’d mention that. It may be in your best interest to code the TextBox’s Validating event. This way, you can enter the ID and press Tab (or remove focus from the TextBox).
Good luck.
Software Developer
I teach; therefore, I learn.
February 28, 2008 at 2:50 am
The ID i am entering is always 7 digits long, and because the code is on the submit button it should only fire once... Ive not looked into alot, still got lots to learn 🙁
Thanks for the hint on a DataReader, will have a look into that, thankfully our drives are back working!
February 28, 2008 at 7:44 am
Hmm, if this is an ASP.NET Web application, another alternative would be to use an SQLDataSource control. It uses a configuration wizard to guide you through setting up the control (even binding a form control to be used as an input parameter to a stored procedure). It handles the connection object, data adapter, data reader, etc. stuff for you.
The only slight issue is that you can't data bind a text box control "out of the box". But, I think you could handle that in one of the SQLDataSource control's "finished loading data" events or use another control that can be data bound (e.g. drop down list).
February 28, 2008 at 7:59 am
Okay. I was just reading your original post which indicated that you intended to (perhaps originally) code the Text_Changed event. Looks like you've got it under control.
Good luck.
Software Developer
I teach; therefore, I learn.
February 29, 2008 at 1:18 am
Finally got back to my Drives to paste the Code im using! It might be completely rubbish, but it does work... I want to try using a DataReader but have no idea how so im reading up on that at the moment!
Dim ds As DataSet
Dim cn As SqlConnection
Dim da As SqlDataAdapter
'Create a connection to the SQL Server.
cn = New SqlConnection("Data Source="";Initial Catalog=ComSuppTimeRec;Integrated Security=True")
'Create a DataAdapter, and then provide the name of the stored procedure.
da = New SqlDataAdapter("pr_GetClientDetails", cn)
'Set the command type as StoredProcedure.
da.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.
da.SelectCommand.Parameters.Add(New SqlParameter("@Client", SqlDbType.VarChar, 7))
'Assign the search value to the parameter.
da.SelectCommand.Parameters("@Client").Value = Trim(txtParisID.Text)
ds = New DataSet() 'Create a new DataSet to hold the records.
da.Fill(ds, "Clients") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.
grdClients.DataSource = ds.Tables("Clients").DefaultView
'Bind the DataSet to the DataGrid.
grdClients.DataBind()
da.Dispose() 'Dispose of the DataAdapter.
cn.Close() 'Close the connection.
March 3, 2008 at 2:16 am
Changed it to a DataReader and im much happier with it, less code and seems to run a little quicker imo!
Dim dr As SqlDataReader
Dim cn As SqlConnection
'Create a connection to the SQL Server.
cn = New SqlConnection("Data Source="";Initial Catalog=ComSuppTimeRec;Integrated Security=True")
Dim cmd As New SqlCommand("pr_GetClientDetails", cn)
cmd.Parameters.Add(New SqlParameter("@Client", SqlDbType.VarChar, 7))
cmd.Parameters("@Client").Value = Trim(txtParisID.Text)
cmd.CommandType = CommandType.StoredProcedure
cn.Open()
dr = cmd.ExecuteReader()
Do While dr.Read()
txtClientName.Text = dr("ClientName")
txtAdd1.Text = dr("Add1")
txtAdd2.Text = dr("Add2")
txtAdd3.Text = dr("Add3")
txtAdd4.Text = dr("Add4")
txtAdd5.Text = dr("Add5")
txtPCode.Text = dr("PCode")
Loop
dr.Close() 'Dispose of the DataReader.
cn.Close() 'Close the connection.
Thanks for all your help 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply