Populate form from database

Hello. I have a backend Microsoft SQL server database that I would like to use to populate a search form.  I have a find by field in my form and right now I just want to be able to search by last name.  When someone types in the last name I would like the web form to be populated with that person's information.   I would like to do this using .net c#.   I figured out how to display data using Gridviews and querys but I don't know how to make a search on the web form.
cdlcidditAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You create a textbox and a button above the gridview.

On the buttonClick you call your query (hopefully a stored proc) and then set the datasource to the grid that passes the textbox text as a parameter.

Watch out for sql injection.

Good tutorial here:
http://www.asp.net/web-forms/overview/data-access/accessing-the-database-directly-from-an-aspnet-page/using-parameterized-queries-with-the-sqldatasource-vb
1
cdlcidditAuthor Commented:
Thanks I will go through the tutorial before I close this question.  I think I have done something like that before and I was told it was the wrong way to do it.  I used visual basic to to create a sqldatasource.  And I used the FilterExpression SelectCommand attributes to pass in my query.  Is that what I should still do? Or go a different route?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
You can do it all from code behind . . . that's may what you were told was wrong.

A lot of people use a data adapter, fill a data table, and bind to that (directly from the code behind)

something like (base taken from http://www.codeproject.com/Questions/273440/sql-command-in-code-behind-Csharp-net but modified)

string sqlConnectString = "YourConnectionString";
string sqlSelect = "SELECT name FROM server WHERE code= @CodeValue";
 
SqlConnection sqlConnection = new SqlConnection(sqlConnectString);
SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
 
sqlCommand.Parameters.Add("@CodeValue", TextBox1.Text)
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCommand);
DataTable sqlDt = new DataTable();
sqlDa.Fill(sqlDt);

myGrid.DataSource = sqlDt;
myGrid.DataBind();

Open in new window

1
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

cdlcidditAuthor Commented:
Ok.  Thanks Kyle. I'm going to try it and let you know.   I'm not that good at this so it will probably take me a while.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Any issues so far?
0
cdlcidditAuthor Commented:
Hi. Thanks for asking. I was trying to figure this out before I posted more questions to you.    Yes. I read through the tutorial. Which showed me how to configure the SQL Data Source. Then I was even able to use the Gridview to display data from the table.  Now I"m stuck trying to figure out how to take all that to populate my form. I'm trying to do this by binding certain data to certain controls (textboxes or labels) on my form.  Basically for example, I would like to be able to type in a last name in the text box on my form and all the information about that person be displayed in my form.    But I can't figure out how to do such a search.  I also know that maybe I should use a stored procedure but once again I would know how to pass the information back and forth form to and from the form.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
http://www.experts-exchange.com/questions/28828519/Populate-form-from-database.html#a41222644

First get your stored procedure up and running.

Using last name as an example:

create proc GetData
@LastName varchar(100) = null
as

select
....
from <tables>
where
(@LastName is null or LastNameField = @LastName)
-- additional where  clauses.

Then in your form
Add the textbox
And add the code to refernece the textbox as shown in my previous answer.

You can bind to the same grid.
1
cdlcidditAuthor Commented:
Hello Kyle.  Thanks. I will try that.
0
cdlcidditAuthor Commented:
Hi @Kyle.  Sorry it has taken me so long to get back to you on this. I had to go through some tutorials on stored procdures, sql injections and Parameterized Queries.    I have been trying to get this to work and I can't seem to do it.  I did everything you said (I think) and I keep getting this error:

Format of the initialization string does not conform to specification starting at index 0.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Source Error:


Line 21:         string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";
Line 22:
Line 23:         System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString);
Line 24:         SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);
Line 25:

Source File: c:\web\ChildDevRegistry\families.aspx.cs    Line: 23


The error is being generated on this line:
System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString);

I knew there was something weird going on because I couldn't just use SqlConnectin.  I had to type the whole System.Data..... thing.  I even put all these using statements at the beginning of my page:
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;

I've been googling this for days and can't seem to find the answer.  So now I'm stuck.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
One question:  Is the LastName field on the database called or txtLastName?  If NOT then, you the query needs to change:

(EG:  "or txtLastName = @LastName"  should be <DB FIELD NAME> = @lastName).

You also need to attach the parameter to the command:

//txtLastName being the field you are trying to pass into the query.
mysqlCommand.Parameters.Add(new SqlParameter("@LastName", txtLastName.text));

Open in new window

1
cdlcidditAuthor Commented:
Hello. And thanks for the quick response.

Oh. No. That is the name of the text box on the page.   I will change that and let you know.  The actual DB field name is P1_LAST. So I need to change this:
string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";

Open in new window

To this?
string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or P1_LAST = @LastName";

Open in new window



I'm not sure where to add the parameters.
Do I replace this:
SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

Open in new window

with this?
mysqlCommand.Parameters.Add(new SqlParameter("@LastName", txtLastName.text));

This is what I set sqlSelect to:
string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";

Open in new window


And this is what myConn is. But I had problems with this because for some reason  could just delcare it using SqlConnection myConn. I had to use this whole thing:
System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString);

Open in new window


I don't know why I had to do all that because at the beginning of the file in all my using statements I had these and I thought they would include the SqlConnection so that I didn't have to type that whole thing:
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;


Here is my entire c# function:
protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlConnectionString = "ChildDevRegistryConnectionString";
        string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";

        System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString); 
        SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

        mysqlCommand.Parameters.Add("@LastName", txtLastName.Text);
        SqlDataAdapter sqlDa = new SqlDataAdapter(mysqlCommand);
        System.Data.DataTable sqlDt = new System.Data.DataTable();
        sqlDa.Fill(sqlDt);

        GridView1.DataSource = sqlDt;
        GridView1.DataBind();

    }

Open in new window

0
cdlcidditAuthor Commented:
This is the error I'm getting.  It seems to be coming from the line I had a question about.

Format of the initialization string does not conform to specification starting at index 0.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Source Error:


Line 21:         string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";
Line 22:
Line 23:         System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString);
Line 24:         SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);
Line 25:

Source File: \\ciddweb2.ad.unc.edu\c$\web\ChildDevRegistry\families.aspx.cs    Line: 23
0
Kyle AbrahamsSenior .Net DeveloperCommented:
so the SQL select is the code that's going to be run on SQL.  Sql only knows about it's own tables, columns, and data . . . nothing about your form.

So you were correct in the other comment where your sql needs to be:
  string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or P1_LAST = @LastName";

Open in new window


Sql doesn't know what @lastName is unless you either declare it, or you pass it as a parameter, which is why you add the parameter to the command in line 9.

For that error:
  string sqlConnectionString = "ChildDevRegistryConnectionString";

That shouldn't be the Actual Connection string correct?  ARe you trying to pull it from the web.config?

eg:

ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;
//or
ConfigurationManager.AppSettings["ChildDevRegistryConnectionString"].ToString();

Open in new window


For SQL:
With SQL username / password:
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Instance:
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Using windows login:
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
1
cdlcidditAuthor Commented:
Hey. Yes. I was wondering how sql would know what @lastname and  txtLastName.text were.  I just don't know how to declare them and store them so that sql will know what they are.  

Yes.  I created that connection string in my web.config and that is what I named it.  And yes I want to pull it from there. Is that possible?

I'm kind of confused on the connection string part.  The reason I put it in the web.config is because I have all that username and password information in the string.  Would I want to put this in the aspx.cs file?

Will this:
ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;
//or
ConfigurationManager.AppSettings["ChildDevRegistryConnectionString"].ToString();

replace this:
string sqlConnectionString = "ChildDevRegistryConnectionString";

If so, how will I know which one to use?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Either one can pull from the web.config . . . it depends on where it's at.  If it's in the connection string part of the web config use the first one.

If it's in the appsettings part use the 2nd.

It will replace the "ChildDevRegistryConnectionString"  eg:

string sqlConnectionString  = ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;

Open in new window


And are you seeing how the parameter is added now?  Post your modified function again and we'll take a look.
1
cdlcidditAuthor Commented:
Hi. Thanks.  I changed my connection string line.  I think I needed to use the 1st one because my connection string is in the <connectionStrings> tags in my web.config
<connectionStrings>
    <add name="ChildDevRegistryConnectionString" connectionString="Data Source=ciddwebdev.ad.unc.edu;Initial Catalog=ChildDevRegistry;User ID=cdev;Password=dmrg23fit"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Open in new window


The problem is that visual studio is showing an error on that connection string. I also get the same error if I try to view the page in a browser.  On this line:
string sqlConnectionString = ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;

Open in new window

I'm getting an error saying:
The name 'ConfigurationManager' does not exist in the current context

Yes. I think I'm understanding how to add the parameters now.  Can you let me know if I have it correct?

Here is my entire function:

 protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlConnectionString = ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;
        string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or txtLastName = @LastName";

        System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString); 
        SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

        mysqlCommand.Parameters.Add(new SqlParameter("@LastName", txtLastName.Text));
        SqlDataAdapter sqlDa = new SqlDataAdapter(mysqlCommand);
        System.Data.DataTable sqlDt = new System.Data.DataTable();
        sqlDa.Fill(sqlDt);

        GridView1.DataSource = sqlDt;
        GridView1.DataBind();

    }

Open in new window

0
cdlcidditAuthor Commented:
Hey. I figured out the configurationManager problem. I had to add a using statement.
using System.Configuration;

Open in new window


I also changed my function a little bit.  I changed the sqlSelect variable:

 
protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlConnectionString = ConfigurationManager.ConnectionStrings["ChildDevRegistryConnectionString"].ConnectionString;
        string sqlSelect = "SELECT P1_LAST FROM  [ChildDevRegistry].[dbo].[518yearolds] WHERE @LastName is null or P1_LAST = @LastName";

        System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString); 
        SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

        mysqlCommand.Parameters.Add(new SqlParameter("@LastName", txtLastName.Text));
        SqlDataAdapter sqlDa = new SqlDataAdapter(mysqlCommand);
        System.Data.DataTable sqlDt = new System.Data.DataTable();
        sqlDa.Fill(sqlDt);

        GridView1.DataSource = sqlDt;
        GridView1.DataBind();

    }

Open in new window

Now this is the error that I get:

Server Error in '/' Application.

Both DataSource and DataSourceID are defined on 'GridView1'.  Remove one definition.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Both DataSource and DataSourceID are defined on 'GridView1'.  Remove one definition.

Source Error:


Line 31:
Line 32:         GridView1.DataSource = sqlDt;
Line 33:         GridView1.DataBind();
Line 34:
Line 35:     }

Source File: c:\web\ChildDevRegistry\families.aspx.cs    Line: 33

Stack Trace:


[InvalidOperationException: Both DataSource and DataSourceID are defined on 'GridView1'.  Remove one definition.]
   System.Web.UI.WebControls.DataBoundControl.EnsureSingleDataSource() +12202967
   System.Web.UI.WebControls.DataBoundControl.ConnectToDataSourceView() +56
   System.Web.UI.WebControls.DataBoundControl.GetData() +9
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +131
   families.Button1_Click(Object sender, EventArgs e) in c:\web\ChildDevRegistry\families.aspx.cs:33
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34248
0
Kyle AbrahamsSenior .Net DeveloperCommented:
On your front end (aspx) remove the datasourceID . . . you no longer need that (or, I'm assuming the SqlDataSource) as you're creating it dynamically in code behind.
1
cdlcidditAuthor Commented:
So I should remove this whole line?

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ChildDevRegistryConnectionString %>" SelectCommand="SELECT [ID], [chFAMID], [chNAMELAST], [chNAMEFIRST], [chDOB], [chSEX], [chRACE], [FamID], [P1_LAST], [P1_FIRST], [P1_TITLE], [P2_LAST], [P2_FIRST], [P2_TITLE], [P1_ADDRS], [P1_CITY], [P1_STATE], [P1_ZIP] FROM [518yearolds]"></asp:SqlDataSource>

Open in new window


Because I'm doing the same thing in the code behind?
System.Data.SqlClient.SqlConnection myConn = new System.Data.SqlClient.SqlConnection(sqlConnectionString); 
        SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

Open in new window

0
cdlcidditAuthor Commented:
Now I'm getting this error after I removed that line from the main familes.aspx page:

Server Error in '/' Application.

The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource.  A control with ID 'SqlDataSource1' could not be found.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource.  A control with ID 'SqlDataSource1' could not be found.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[HttpException (0x80004005): The DataSourceID of 'GridView1' must be the ID of a control of type IDataSource.  A control with ID 'SqlDataSource1' could not be found.]
   System.Web.UI.WebControls.DataBoundControl.GetDataSource() +12203560
   System.Web.UI.WebControls.DataBoundControl.ConnectToDataSourceView() +71
   System.Web.UI.WebControls.DataBoundControl.OnLoad(EventArgs e) +28
   System.Web.UI.Control.LoadRecursive() +71
   System.Web.UI.Control.LoadRecursive() +190
   System.Web.UI.Control.LoadRecursive() +190
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34248

Do I need to keep that <asp:SqlDataSource> control on that page?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
You can remove the asp:sqlDataSource (be sure to update the select to pull back all the columns you need in the code behind)

Also on the GRIDVIEW be sure to remove the datasourceID attribute.  (Remove teh attribute entirely, you bind it in the code behind).
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cdlcidditAuthor Commented:
Hey Kyle. That seemed to have worked.  For simplicity I only pulled the ID and last name but it seems to be doing it.  Thanks so much for your help.  You have taught me a lot.
0
cdlcidditAuthor Commented:
Kyle was awesome.  He basically gave me a crash course in binding gridviews in the code behind, also in prioritized queries and stored procedures. Then  he showed me how to tie it all together to make a search form.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.