<asp:SqlDataSource> need a look over to see If I am writing this correctly.

This is a subset question of one of my other threads, the gist is while using forms authentication, I am trying to enter additional information into another table on the same db as the aspnet_membership tables. I think I need an <asp:SqlDataSource> to do this because it is about the only thing left I haven't tried to get this code to work so any help is appreciated. Environment: sql server 2000, windows server 2003, asp.net 2.0, vb, no GUI

<ASP:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlServerConnection %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (@.username,@.email,@.company,@.firstname,@.lastname,@.phone)" runat="server" />

looking over some of my books and online it says in the connectionStrings that it is supposed to be the database name that goes next to the : but when I try that I get an error that it is looking for the name of the connection string, so I enter the name of the connection string (as above) and nothing happens, which is basically the ongoing event with this whole problem, the form fields (username, email, password) that go into the aspnet_membership tables just fine, I don't have any problems with those, it is getting the additional information into another table that has been stalling me the whole time.

So does this sqldatasource look right? Any help is appreciated.

this is the full page of register.aspx

<%@. Page Explicit="true" debug="true" language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data.Mail" %>
<%@. Import Namespace="System.Net.Mail" %>

<script runat="server">

Public Sub CreateUser_OnClick(sender As Object, args As EventArgs)
Dim result As MembershipCreateStatus
Try
' Create new user.

Dim newUser As MembershipUser
If Membership.RequiresQuestionAndAnswer Then
newUser = Membership.CreateUser(UsernameTextbox.Text,PasswordTextbox.Text,EmailTextbox.Text,PasswordQuestionTextbox.Text, PasswordAnswerTextbox.Text,false,result)
Else
newUser = Membership.CreateUser(UsernameTextbox.Text,PasswordTextbox.Text,EmailTextbox.Text)
End If


Response.Redirect("login.aspx")

Catch e As MembershipCreateUserException
Msg.Text = GetErrorMessage(e.StatusCode)
Catch e As HttpException
Msg.Text = e.Message
End Try
End Sub

Public Function GetErrorMessage(status As MembershipCreateStatus) As String

Select Case status
Case MembershipCreateStatus.DuplicateUserName
Return "Username already exists. Please enter a different user name."

Case MembershipCreateStatus.DuplicateEmail
Return "A username for that e-mail address already exists. Please enter a different e-mail address."

Case MembershipCreateStatus.InvalidPassword
Return "The password provided is invalid. Please enter a valid password value."

Case MembershipCreateStatus.InvalidEmail
Return "The e-mail address provided is invalid. Please check the value and try again."

Case MembershipCreateStatus.InvalidAnswer
Return "The password retrieval answer provided is invalid. Please check the value and try again."

Case MembershipCreateStatus.InvalidQuestion
Return "The password retrieval question provided is invalid. Please check the value and try again."

Case MembershipCreateStatus.InvalidUserName
Return "The user name provided is invalid. Please check the value and try again."

Case MembershipCreateStatus.ProviderError
Return "The authentication provider Returned an error. Please verify your entry and try again. If the problem persists, please contact your system administrator."

Case MembershipCreateStatus.UserRejected
Return "The user creation request has been canceled. Please verify your entry and try again. If the problem persists, please contact your system administrator."

Case Else
Return "An unknown error occurred. Please verify your entry and try again. If the problem persists, please contact your system administrator."
End Select
End Function

</script>

<form runat="server" id="form1">
<h3>Create New User</h3>

<asp:Label id="Msg" ForeColor="maroon" runat="server" /><BR>

<table CellPadding="3" border="0">
<tr>
<td><span class="style5">Username:</span></td>
<td><asp:Textbox id="UsernameTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="UsernameRequiredValidator" runat="server"
ControlToValidate="UserNameTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Password:</span></td>
<td><asp:Textbox id="PasswordTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordRequiredValidator" runat="server"
ControlToValidate="PasswordTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Confirm Password:</span></td>
<td><asp:Textbox id="PasswordConfirmTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordConfirmRequiredValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" />
<asp:CompareValidator id="PasswordConfirmCompareValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ControlToCompare="PasswordTextBox"
ErrorMessage="Confirm password must match password." /> </td>
</tr>
<tr>
<td><span class="style5">Email Address:</span></td>
<td><asp:Textbox id="EmailTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="EmailRequiredValidator" runat="server"
ControlToValidate="EmailTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>

<% If Membership.RequiresQuestionAndAnswer Then %>

<tr>
<td>Password Question:</td>
<td><asp:Textbox id="PasswordQuestionTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordQuestionRequiredValidator" runat="server"
ControlToValidate="PasswordQuestionTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td>Password Answer:</td>
<td><asp:Textbox id="PasswordAnswerTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordAnswerRequiredValidator" runat="server"
ControlToValidate="PasswordAnswerTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<% End If %>
</table>
<!-- so much trouble below! -->
<table cellpadding="3" cellspacing="0">
<tr>
<td width="122"><span class="style5">Company:</span>
</td>
<td width="144"><asp:Textbox id="CompanyTextbox" runat="server" /></td>
<td width="387"><asp:RequiredFieldValidator id="CompanyValidator" runat="server" ControlToValidate="CompanyTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">First Name:</span></td>
<td><asp:Textbox id="FirstNameTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="FirstNameValidator" runat="server" ControlToValidate="FirstNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Last Name:</span> </td>
<td><asp:Textbox id="LastNameTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="LastNameValidator" runat="server" ControlToValidate="LastNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Phone Number:</span> </td>
<td><asp:Textbox id="PhoneTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PhoneValidator" runat="server" ControlToValidate="PhoneTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" />
<ASP:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlElectron %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (UserNameTextbox,EmailTextbox,CompanyTextbox,FirstNameTextbox,LastNameTextbox,PhoneTextbox)" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td><asp:Button id="CreateUserButton" CssClass="style5" Text="Create User" OnClick="CreateUser_OnClick" runat="server" /></td>
</tr>
</table>
</form>

From some tests right now the entire code is just being ignored. I've tried to break this by intentionally adding fields that don't exist into the add so that it would return (I forget what it is precisily called) the error where it says that the fields being entered do not match the database. As always any help is appreciated.

|||

For a minute there thought I actually figured out what I didn't do, I didn't add the DataSourceID to the textboxes, so I added it and nothing happens still the same problem :( So below is the current version of the code:

<form runat="server" id="form1">
<h3>Create New User</h3>

<asp:Label id="Msg" ForeColor="maroon" runat="server" /><BR>

<table CellPadding="3" border="0">
<tr>
<td><span class="style5">Username:</span></td>
<td><asp:Textbox id="UsernameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="UsernameRequiredValidator" runat="server"
ControlToValidate="UserNameTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Password:</span></td>
<td><asp:Textbox id="PasswordTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordRequiredValidator" runat="server"
ControlToValidate="PasswordTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Confirm Password:</span></td>
<td><asp:Textbox id="PasswordConfirmTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordConfirmRequiredValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" />
<asp:CompareValidator id="PasswordConfirmCompareValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ControlToCompare="PasswordTextBox"
ErrorMessage="Confirm password must match password." /> </td>
</tr>
<tr>
<td><span class="style5">Email Address:</span></td>
<td><asp:Textbox id="EmailTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="EmailRequiredValidator" runat="server"
ControlToValidate="EmailTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>

<% If Membership.RequiresQuestionAndAnswer Then %>

<tr>
<td>Password Question:</td>
<td><asp:Textbox id="PasswordQuestionTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordQuestionRequiredValidator" runat="server"
ControlToValidate="PasswordQuestionTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td>Password Answer:</td>
<td><asp:Textbox id="PasswordAnswerTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordAnswerRequiredValidator" runat="server"
ControlToValidate="PasswordAnswerTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<% End If %>
</table>
<!-- so much trouble below! -->
<table cellpadding="3" cellspacing="0">
<tr>
<td width="122"><span class="style5">Company:</span>
</td>
<td width="144"><asp:Textbox id="CompanyTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td width="387"><asp:RequiredFieldValidator id="CompanyValidator" runat="server" ControlToValidate="CompanyTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">First Name:</span></td>
<td><asp:Textbox id="FirstNameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="FirstNameValidator" runat="server" ControlToValidate="FirstNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Last Name:</span> </td>
<td><asp:Textbox id="LastNameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="LastNameValidator" runat="server" ControlToValidate="LastNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Phone Number:</span> </td>
<td><asp:Textbox id="PhoneTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="PhoneValidator" runat="server" ControlToValidate="PhoneTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" />
<asp:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlElectron %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (UserNameTextbox,EmailTextbox,CompanyTextbox,FirstNameTextbox,LastNameTextbox,PhoneTextbox)" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td><asp:Button id="CreateUserButton" CssClass="style5" Text="Create User" OnClick="CreateUser_OnClick" runat="server" /></td>
</tr>
</table>
</form>

any help is appreciated. I've been working on this problem now for the better part of two weeks, and I am simply out of ideas as to the problem.

|||

For a minute there thought I actually figured out what I didn't do, I didn't add the DataSourceID to the textboxes, so I added it and nothing happens still the same problem :( So below is the current version of the code:

<form runat="server" id="form1">
<h3>Create New User</h3>

<asp:Label id="Msg" ForeColor="maroon" runat="server" /><BR>

<table CellPadding="3" border="0">
<tr>
<td><span class="style5">Username:</span></td>
<td><asp:Textbox id="UsernameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="UsernameRequiredValidator" runat="server"
ControlToValidate="UserNameTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Password:</span></td>
<td><asp:Textbox id="PasswordTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordRequiredValidator" runat="server"
ControlToValidate="PasswordTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Confirm Password:</span></td>
<td><asp:Textbox id="PasswordConfirmTextbox" runat="server" TextMode="Password" /></td>
<td><asp:RequiredFieldValidator id="PasswordConfirmRequiredValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" />
<asp:CompareValidator id="PasswordConfirmCompareValidator" runat="server"
ControlToValidate="PasswordConfirmTextbox" ForeColor="red"
Display="Static" ControlToCompare="PasswordTextBox"
ErrorMessage="Confirm password must match password." /> </td>
</tr>
<tr>
<td><span class="style5">Email Address:</span></td>
<td><asp:Textbox id="EmailTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="EmailRequiredValidator" runat="server"
ControlToValidate="EmailTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>

<% If Membership.RequiresQuestionAndAnswer Then %>

<tr>
<td>Password Question:</td>
<td><asp:Textbox id="PasswordQuestionTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordQuestionRequiredValidator" runat="server"
ControlToValidate="PasswordQuestionTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td>Password Answer:</td>
<td><asp:Textbox id="PasswordAnswerTextbox" runat="server" /></td>
<td><asp:RequiredFieldValidator id="PasswordAnswerRequiredValidator" runat="server"
ControlToValidate="PasswordAnswerTextbox" ForeColor="red"
Display="Static" ErrorMessage="Required" /></td>
</tr>
<% End If %>
</table>
<!-- so much trouble below! -->
<table cellpadding="3" cellspacing="0">
<tr>
<td width="122"><span class="style5">Company:</span>
</td>
<td width="144"><asp:Textbox id="CompanyTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td width="387"><asp:RequiredFieldValidator id="CompanyValidator" runat="server" ControlToValidate="CompanyTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">First Name:</span></td>
<td><asp:Textbox id="FirstNameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="FirstNameValidator" runat="server" ControlToValidate="FirstNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Last Name:</span> </td>
<td><asp:Textbox id="LastNameTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="LastNameValidator" runat="server" ControlToValidate="LastNameTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" /></td>
</tr>
<tr>
<td><span class="style5">Phone Number:</span> </td>
<td><asp:Textbox id="PhoneTextbox" runat="server" DataSourceID="addedInfo" /></td>
<td><asp:RequiredFieldValidator id="PhoneValidator" runat="server" ControlToValidate="PhoneTextbox" ForeColor="red" Display="Static" ErrorMessage="Required" />
<asp:SqlDataSource id="addedInfo" ConnectionString="<%$ ConnectionStrings:SqlElectron %>" InsertCommand="INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (UserNameTextbox,EmailTextbox,CompanyTextbox,FirstNameTextbox,LastNameTextbox,PhoneTextbox)" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td><asp:Button id="CreateUserButton" CssClass="style5" Text="Create User" OnClick="CreateUser_OnClick" runat="server" /></td>
</tr>
</table>
</form>

any help is appreciated. I've been working on this problem now for the better part of two weeks, and I am simply out of ideas as to the problem.

|||

Public Sub CreateUser_OnClick(sender As Object, args As EventArgs)
Dim result As MembershipCreateStatus
Try
' Create new user.

Dim newUser As MembershipUser
If Membership.RequiresQuestionAndAnswer Then
newUser = Membership.CreateUser(UsernameTextbox.Text,PasswordTextbox.Text,EmailTextbox.Text,PasswordQuestionTextbox.Text, PasswordAnswerTextbox.Text,false,result)
Else
newUser = Membership.CreateUser(UsernameTextbox.Text,PasswordTextbox.Text,EmailTextbox.Text)
End If

dim conn as new sqlconnection(system.configuration.configurationmanager.connectionstrings("SqlElectron").connectionstring)

dim cmd as new sqlcommand("INSERT INTO addInfo(username,email,company,firstname,lastname,phone) VALUES (@.UserNameTextbox,@.EmailTextbox,@.CompanyTextbox,@.FirstNameTextbox,@.LastNameTextbox,@.PhoneTextbox)",conn)

cmd.parameters.add("@.UserNameTextbox",varchar).value=UsernameTextbox.Text

cmd.parameters.add("@.EmailTextbox",varchar).value=EmailTextbox.Text

{Add rest of fields here}

conn.open

cmd.executenonquery

conn.close

Response.Redirect("login.aspx")

Catch e As MembershipCreateUserException
Msg.Text = GetErrorMessage(e.StatusCode)
Catch e As HttpException
Msg.Text = e.Message
End Try
End Sub

|||

First thanks for the help moley

second the code errors out with two errors first it needs varchar set to a value and the connection string it saysSystem.NullReferenceException: Object reference not set to an instance of an object.

I'm working on a variant of what you posted and Ill post it as soon as I get it done with whether it works or not.

|||yep it gives me the beginning of the findcontrol loop logic error that I have in one of my other threadshttp://forums.asp.net/2/1381460/ShowThread.aspx . basically

No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

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: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

Source Error:

Line 37: Line 38: SQLConn.Open()Line 39: cmd.ExecuteNonQueryLine 40: SQLConn.CloseLine 41:

Source File:C:\Inetpub\wwwroot\login\register.aspx Line:39

It can never find the value of the textboxes because the textboxes get wiped out in the process of the membership being made, If I try to put this type of code before the membership stuff it will not find anything, If I use findcontrol() to try and extract the value it will return with nothing and thus I return to my two week+ nightmare of trying to get this to work. I assumed since I needed to trap the info that I could use the <asp:SqlDataSource to do that and then send it's insert seperatly but I guess that doesn't work.

For those forum members that have a custom registration page (basically something that adds more fields than what is in the CreateUserWizard) how do you enter those fields? I honest to gawd don't know what I am missing but basically I have tried every code example I could come across, I've tried everything other forumers have tried, I just have no idea what I am missing or not doing? I can enter the basic forms data fine (username, email, password) that enters the aspnet_membership tabels fine but I can get nothing into the addInfo table that I have in the same database. Any help is appreciated and thanks to everyone who has been helping me.

|||

The big error:

You forgot to supply .Text when assigning the value to one of the parameters.

First error:

Change:

cmd.parameters.add("@.UserNameTextbox",varchar).value=UsernameTextbox.Text

to:

cmd.Parameters.Add("@.UserNameTextbox",sqldbtype.varchar).Value=UsernameTextbox.Text

varchar isn't a variable, it's a value from the system.data.sqldbtype enumeration.

--

Second Error:

Please post your web.config file (Please make sure you remove any passwords by using XXXXXXXX instead of the real password before posting).

|||

<configuration>
<appSettings>
<add key="ConnectionString" value="DataSource=mew@.mew.com;UID=bark;pwd=xxxxxxxxx" />
<add key="ConnectionString" value="Server=xxx.xxx.xxx.xxx;database=customerlogin;UID=yyyyyyyy;pwd=xxxxxxxx;Trusted_Connection=True" />
</appSettings>
<connectionStrings>
<add name="SqlServerConnection" connectionString="Server=xxx.xxx.xxx.xxx;database=customerlogin;UID=yyyyyyyy;pwd=xxxxxxxx;Trusted_Connection=True" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=xxx.xxx.xxx.xxx;Integrated Security=SSPI;Initial Catalog=customerlogin;UID=yyyyyyyyy;pwd=xxxxxxxxx;Trusted_Connection=True" />
</connectionStrings>
<location path="lock">
<system.web>
<authorization>
<deny users="?" />
<allow users="*" />
</authorization>
</system.web>
<system.web>
<authentication mode="Forms">
<forms cookieless="AutoDetect" defaultUrl="~\lock\library.aspx" loginUrl="../login.aspx" name=".ASPXAUTH" />
</authentication>
</system.web>
<system.web>
<membership defaultProvider="MyProvider">
<providers>
<add name="MyProvider" type="System.Web.Security.SqlMembershipProvider" passwordFormat="Clear" connectionStringName="LocalSqlServer" requiresQuestionAndAnswer="false" />
</providers>
</membership>
</system.web>
<system.web>
<profile enabled="true">
<properties>
<add name="company" type="String" />
<add name="firstname" type="String"/>
<add name="lastname" type="String"/>
<add name="phone" type="String" />
</properties>
</profile>
</system.web>
</location>
</configuration>

The highlighted UID's are the same and thus consequently the passwords are the same. I removed the SqlElectron from the previous examples since making a second account didn't help the matter and have gone back to alternating between the SqlServerConnection and LocalSqlServer connections depending on what variable I am testing. Thanks for the help Motley.

|||

Here:

PrivateFunction SuperFindControl(ByVal ctrlAsString,OptionalByVal basectrlAs Control =Nothing)As Control

If basectrlIsNothingThen

basectrl = Page

EndIf

If basectrl.Controls.Count > 0Then

ForEach cAs ControlIn basectrl.Controls

If c.IDIsNotNothingAndAlso c.ID.ToLower = ctrl.ToLowerThen

Return c

Else

If c.Controls.Count > 0Then

Dim c2As Control = SuperFindControl(ctrl, c)

IfNot c2IsNothingThen

Return c2

EndIf

EndIf

EndIf

Next

EndIf

ReturnNothing

EndFunction

Now you can reference your control as SuperFindControl("txtCompanyTextBox") anywhere in your code -- but it will only return the first one it finds with that id if you have more than one.

|||

Motley:

The big error:

You forgot to supply .Text when assigning the value to one of the parameters.

First error:

Change:

cmd.parameters.add("@.UserNameTextbox",varchar).value=UsernameTextbox.Text

to:

cmd.Parameters.Add("@.UserNameTextbox",sqldbtype.varchar).Value=UsernameTextbox.Text

varchar isn't a variable, it's a value from the system.data.sqldbtype enumeration.

--

Second Error:

Please post your web.config file (Please make sure you remove any passwords by using XXXXXXXX instead of the real password before posting).

W00T it works! The highlighted ontop of whatever other changes I've made(ill have to break the page to find out what actually did it) have worked, I get the additional info in the new table and everything works well, Thanks a lot Motley you have been a lifesaver, and more than likely helped me keep my job. I would give you a hug but we dont know each other well enough lol, so

/e handshake

Thanks a lot.

|||

Now only one problem left to fix, but now I can close three threads that were all related to each other.

Login.aspx I'm looking at you next!

Thanks again Motley.

|||

Given your web.config file, the connection should be:

dim conn as new sqlconnection(system.configuration.configurationmanager.connectionstrings("LocalSqlServer").connectionstring)

<asp:SqlDataSource> generate SQL for GridView?

Hi guys.

Im using a gridview to show some training data on my website which is populated by:

<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>"
SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @.EventPrivate) ORDER BY [EventDate]">
<SelectParameters>
<asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" />
</SelectParameters>

So far so good..

However I want to be able to filter the data.

A) Show everything

B) Show individual EventType i.e. Seminar, Training etc etc

Is it possible to generate the Select Command via a function?

(im not using stored procedures for this part of the site, nor really want to at the moment).

Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it!

Cheers guys

Well, repeaters work the exact same way as gridviews when it comes to databinding.

In any case, the sqldatasource control does expose its selectcommand property. So really, just programmatically set the property, then call the databind method of the gridview, and you're set.

Another possibility is to generate a SQL statement that uses parameters (like I beleive you're currently doing). Then on the "Selecting" event of the sqldatasource, you can set the parameter like: e.InputParameters["paramName"] = "whatever" (so if your query ends with "where somefield LIKE @.someparam", you can use % to show everything as a wildcard, or specify a parameter.

Another option is to set the parameter declaratively, and bind it to Session or a control or something (when you create the sqldatasource, thats what the parameter part of the wizard is for), but I tend to find that a little annoying to paintain, so I suggest the above methods.

|||

Thanks very much for your help!

I didnt know you could do define SqlDataSource outwith the aspx page.

Incase anyone found this thread trying to do the same thing I found this URL with an example

http://geekswithblogs.net/azamsharp/archive/2005/07/19/47711.aspx

Thanks again Shados!

|||

Sorry guys,

Im having real problems with this still :(

Ive been trawling through google all morning and im not quite getting there.

So far ive came up with

Public Sub AddDataSource()Dim dsAs SqlDataSource =New SqlDataSource() ds.ID ="mySqlSourceControl" Page.Controls.Add(ds) ds.ConnectionString = ConfigurationManager.ConnectionStrings("ARENAConnection").ConnectionString ds.SelectCommand ="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @.EventPrivate AND [EventType] = @.EventType) ORDER BY [EventDate]"Dim cpAs Parameter cp =New Parameter("@.EventPrivate", TypeCode.Boolean,"False") cp =New Parameter("@.EventType", TypeCode.String,"Training") ds.SelectParameters.Add(cp)Dim paramAs ControlParameter =New ControlParameterEnd Sub

This leaves me the error:

System.Data.SqlClient.SqlException: Must declare the variable '@.EventPrivate'.

I'm that close i can smell it... 

Have pity someone [:'(]

Cheers 

|||

Sorry for taking so long to reply to you. I just woke up, AND I needed to test this by myself before replying to you. It is actualy much simpler than what you're trying to do.

First, make your SQLDatasource normally (you know, like with the wizard, or manually, etc).

Then all what you have to do is, in the designer, double click on the datasource. This will register the Selecting event. Now, one of the parameters is "e", and it has a member, called "command". This is the readonly property that contains the SqlCommand object. Now, the sqlcommand itself is readonly...however, its methods can still be used!

so you can do this:

e.Command.Parameters.Add(newSqlParameter("@.EventPrivate",false)); //note how it will take objects, so you don't need to specify a type

and you're set. No need to do anything else.

Your issue in your case, is that onload, the grid will try to bind on the datasource, and you didn't set its parameters yet, thus it cannot find them.
If your way had worked, you would have ended up with a datasource that binds twice on every load =P Anyway, so just register the Selecting
event of the datasource: this is called -everytime- the datasource is called. And you can do all the work there, as shown above ^_^

|||

Hi Shados

I really appreciate you taking your time helping me with this, it worked perfectly

Sorry i didnt reply sooner, I just moved house and my ISP are being lazy!

It is indeed much simpler!

Thanks again

|||Don't worrie about, most people never even reply once they found their solutions :)