Populate form from database

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

Any issues so far?

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hello Kyle. Thanks. I will try that.

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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";

To this?

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

I'm not sure where to add the parameters.
Do I replace this:

SqlCommand mysqlCommand = new SqlCommand(sqlSelect, myConn);

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";

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);

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();

    }

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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>

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;

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();

    }

ASKER

Hey. I figured out the configurationManager problem. I had to add a using statement.

using System.Configuration;

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();

    }

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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>

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);

ASKER

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?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.