Hi,
I'm trying to use a CheckBoxList to display certain records in a Grid View. I have a Grid View, a CheckBoxList with four items and a SqlDataSource and when the user check or uncheck one or more items in the CheckBoxList the Grid View should show records accordingly. I’ve tried to make it work but it’s only the first checked item in the Grid View that has any effect. I use VB. Thanks
I have a checkbox list like the one above.
For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else.
Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?
Thank you for any help
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies View RelatedI have a form with text boxes and checkboxlists that a user will fill out and click submit. When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes. Below is the code I have so far that works for a text box:Partial Class windrockform
Inherits System.Web.UI.PageProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click
Dim dashdatasource As New SqlDataSourcedashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("WindrockIssuesConnectionString").ToString
dashdatasource.InsertCommandType = SqlDataSourceCommandType.Text
dashdatasource.InsertCommand = "INSERT INTO IssueLog (initiator) VALUES (@initiator)"dashdatasource.InsertParameters.Add("initiator", initiatorTextBox.Text)
Dim rowsAffected As Integer = 0
Try
rowsAffected = dashdatasource.Insert()Catch ex As Exception
Server.Transfer("problem.aspx")
End Try
If rowsAffected <> 1 ThenServer.Transfer("problem.aspx")
ElseServer.Transfer("confirm.aspx")
End IfEnd Sub
End Class
I am new to asp.net and would appreciate the help.
Thanks,
Hi
I have CheckBoxList to make my selection as follows:if (CheckBoxList2.SelectedValue == "")
{
strSelect = strSelect;
}else if (CheckBoxList2.SelectedValue == "1")if (strWhere == " where")
{strWhere = strWhere + " pic1 = 'true' ";
}
else
{strWhere = strWhere + " and pic1 = 'true' ";
}if (CheckBoxList2.SelectedValue == "2")if (strWhere == " where")
{strWhere = strWhere + " pic2 = 'true' ";
}
else
{strWhere = strWhere + " and pic2 = 'true' ";
}if (CheckBoxList2.SelectedValue == "3")if (strWhere == " where")
{strWhere = strWhere + " pic3 = 'true' ";
}
else
{strWhere = strWhere + " and pic3 = 'true' ";
}
which mean it will addor remove the fields depends upon the user selection, but it will only select one !!
Thanks in advance
Hello all. When using a checkboxlist, is each selection under the checkboxlist suppose to have it's own column in the database?
Thank you,
Mike
Column1 in table 1 has a range from 1 to 5 (int)
A CheckboxList displays these 5 items. When checked (for example 1 and 4) I want a gridview with sqldatasource=sqlProducts to display the records where column1 values 1 or 4.
When I use the code below I only get the records where column1 values 1....
<asp:SQLDataSource id="sqlProducts" Runat="Server" SelectCommand="Select * From Table1 where Column1 IN (@CBLchecked_items)" ConnectionString="<%$ ConnectionStrings:pubs %>"> <SelectParameters> <asp:ControlParameter Name="CBLchecked_items" ControlID="CBL" propertyname="SelectedValue" type="String" /> </SelectParameters></asp:SQLDataSource>
Hello,Intended scenario:I have a grid view that displays a particular column using a repeater to display data stored in a separate lookup table. In the EditTemplate I drop the repeater and utilize a checkboxlist to allow for updates to be made. After the sqlUpdate command is processed on the main table I call a sub to loop through the checkboxlist and update the lookup table accordingly. Problem: I can't seem to access the checkboxlist in the edit template. I have a 'updateServices' procedure that is called by the main sql updated event. I call findcontrol on the gridview but I keep getting this error: "Object reference not set to an instance of an object". Here is my code: Protected Sub updateServices(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sql_respParty.Updated 'Filing type was modified, update lm_Responsible_Party_Filing_Type If FilingTypeChanged = True Then Dim command As System.Data.Common.DbCommand = e.Command 'Insert service_filing_type Dim li As ListItem Dim checkBoxServices As CheckBoxList = grid_parties.FindControl("checkBoxServices") sql_allFilingTypes.InsertParameters("Party_id").DefaultValue = command.Parameters("@Party_id").Value.ToString For Each li In checkBoxServices.Items If li.Selected = True Then sql_allFilingTypes.InsertParameters("Filing_Type_id").DefaultValue = li.Value sql_allFilingTypes.Insert() Response.Write(" check: " + li.Value.ToString) End If Next End If End Sub
Thank you.
Hi,
I'm trying to insert multiple checkboxlist values from a databound checkboxlist into a SQL Server Express DB.
I need to insert the values JobID and CategoryID into an intermediate table which is made of two columns, JobID and CategoryID, which form the primary key. I'm using the following code:Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim insertCommand As SqlCommand
Dim strConnection As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"Dim objConnection As New SqlConnection(strConnection)
Try
objConnection.Open()
Dim ctr As Integer
Dim str As StringFor ctr = 0 To CheckBoxList1.Items.Count - 1
If CheckBoxList1.Items(ctr).Selected Then
str = "INSERT INTO CategoryVacancies (JobID, CategoryID) values (@JobID, @CategoryID)"insertCommand = New SqlCommand(str, objConnection)
insertCommand.Parameters.AddWithValue("@CategoryID", CheckBoxList1.SelectedValue)insertCommand.Parameters.AddWithValue("@JobID", JobIDLabel.Text)
insertCommand.ExecuteNonQuery()
End If
Next
objConnection.Close()Catch ex As Exception
errorLabel.Text = "Failed because:" & ex.Message
End Try
End Sub
I get this error: Violation of PRIMARY KEY constraint 'PK_CategoryVacancies'. Cannot insert duplicate key in object 'dbo.CategoryVacancies'. The statement has been terminated.
It always inserts only the first value of the checkboxlist, instead of looping through and inserting all the rows.
Anyone know what's going wrong?
Many Thanks
I was just wondering what would be a preferred approach in designing the tables against checkboxlist?
For example I have 3 checkboxlist:
chklVendor
chklModel
chklColor
and each will have lets say 4 - 5 list items.
Here are the different types of approaches that I've seen when designing database tables:
1. Creating tables for each checkboxlist.
2. Creating a single table and storing the values in a comma delimited format.
3. Using XML
Also these attributes will be used in the search form as one of the fields.
I have the follow, i get the right amout of checkboxes but they all have the same value(System.Data.Common.DbDataRecord)
Dim objconn As New SqlConnection(connstring_MPR)Dim objcmd As SqlCommand = New SqlCommand("SELECT [Parts Master Table].COMMD_CODE as comcode FROM [Parts Master Table] INNER JOIN [Warehouse balance table] ON [Parts Master Table].PART_NUMBER = [Warehouse balance table].PART INNER JOIN POREPORT ON [Parts Master Table].PART_NUMBER = POREPORT.[Part Number] INNER JOIN [DEMAND TABLE] ON [Parts Master Table].PART_NUMBER = [DEMAND TABLE].PART WHERE POREPORT.[PO Bal] > 0 OR [DEMAND TABLE].QTY > 0 or [Warehouse balance table].ONHAND > 0 and [Parts Master Table].M_B = 1 AND [Warehouse balance table].WHSE = 'sgr' AND ([Parts Master Table].FAMILY NOT LIKE 'lam%' or [Parts Master Table].FAMILY NOT IN ('ULTCH', 'REMOT', 'MKSIN')) GROUP BY [Parts Master Table].COMMD_CODE ORDER BY [Parts Master Table].COMMD_CODE", objconn)
objconn.Open()
chkComCode.DataSource = objcmd.ExecuteReader(CommandBehavior.CloseConnection)
chkComCode.DataBind()
objconn.Close()
I am using the varchar data type in sql to store a comma-delimited string of multiple selections from a checkboxlist.
The string only has about 28-30 characters in it, but it maxes out the sql row length and I get the 8060 error message every time.
Here is some of the code:
"Dim industry1list As String
Dim li As ListItem
industry1list = ""
For Each li In industry1.Items
If li.Selected = True Then
industry1list = industry1list & li.Value & ","
End If
Next
....
MyCommand.Parameters.Add(New SqlParameter("@industry1", SqlDbType.VarChar, 60))
MyCommand.Parameters("@industry1").Value = (industry1list)"
I would appreciate any coaching you have for me to get back on track. Thanks, Bob
I'm using the following Select Command:SELECT MAX(Document) AS DOC FROM dbo.Communicator WHERE (ReleaseDate <= { fn NOW() })It shows the most current date in the ReleaseDate column - even if the date is in the future. I don't want it to show future dates. If I change the command to WHERE (ReleaseDate >= { fn NOW() }) it doesn't work at all. I only want it to return one row - the latest releases date that is equal to or less than now.Any ideas?
View 3 Replies View RelatedI have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
View 5 Replies View RelatedI have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
View 3 Replies View RelatedI currently have a webpage that allows visitors to post links to their own website. As a spam filter I want to create a scheduled task that selects repeat entries in the database under the column name domain. I already made a filter to take everything out of the link the provide and leave it with just the domain name. I tested it and it works. Now I need a SQL command to select all the rows with repeats of the domain. Look at the following table example to better understand what I mean uid x y domain1 100 110 www.spam.com2 100 120 www.spam.com3 110 130 www.homepage.com4 210 220 www.myaspspam.com5 510 560 www.myaspspam.com in this example I would like 1 and 2 to be selected as well as 4 and 5 into a dataadapter so that I can delete them accordingly.
View 2 Replies View Related im trying to write a select command that gets info from 1 table and counts 11 differnt things in it im not sure if this is even posiable but if it is could someone help this is what i got for counting all of them SELECT owner, COUNT(*) AS TotalPots
FROM Items
WHERE (Name = 'Holy Potion') OR
(Name = N'Arcane Potion') OR
(Name = N'Shadow Potion') OR
(Name = N'Fire Potion') OR
(Name = N'Kinetic Potion') OR
(Name = N'Potion of Holy Resistance') OR
(Name = N'Potion of Arcane Resistance') OR
(Name = N'Potion of Shodow Resistance') OR
(Name = N'Potion of Fire Resistance') OR
(Name = N'Potion of Kinetic Resistance')
GROUP BY owner
ORDER BY COUNT(*) DESC the 11 coloums i want are Holy, Arcane, Shadow, Fire, Kinetic, Holy Resist, Arcane Resist, Shadow resist, Fire Resist, Kinetic Resist, And Total Pots Also would like it on my Asp.net page at the bottom of the grid view to have a total row that counts all the colums up
Hello,everyone,i have a problem:(about BOM caculation)
The BOM is B--87700
has one outside service,and two children parts:z--877,and s--877
i have a table,this table which contains columns like this:
part_id description price(unit price) quatity
B--877 FD 82.36$(service price) 1
Z--877 Roughcast 2.36$ 4
S--877 the same 8.36$ 12
and i want to get a result of this:
part_id description price
B--877 FD (82.36+2.36*4+8.36*12)=192.12(just the result 192.12 is okay)
how can i achieve this target?
I am trying to run a select command on an OLE DB Command transform such as:
Select * from table where id = ?
I have the mapping to the parameter working and the command is working but how to map the select output to columns when you can't create output columns on the OLE DB Command?
Is this the correct transform to use?
Thanks
Thanks for this help. My problem is a little different. I have a stored procedure with an output parameter that I want to use in an OLE DB transform. I can see how to add output columns but can't figure out how to set them from the stored procedure return. Any ideas?
View 1 Replies View RelatedI am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:
SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC">
I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried:
LastName LIKE '%" & LastName & "%'"
But I get an error???? Any suggestions, please...
Thanks !!
I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!
View 1 Replies View RelatedHi Guys,
I'm fairly new to .Net and have reached a point in the coding I can't seem to get past.
I've created a table with Visual Studio 2005 and MySQL Server 2008 (Beta) and am trying to make the "Select" button bind a result from that table (in this case, image_id) then open up that image ID in a Image details page, I've created the table in Visual Studio 2005.
Here's what I've got so far;
Page 1Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim row As GridViewRow = GridView1.SelectedRowSession("session_current") = row.Cells(1).TextServer.Transfer("page2.aspx")End SubI think this page isn't at fault as it reads the parameter into a text box on page2
Page2</asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>"ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" SelectCommand="SELECT * FROM [profiles] WHERE ([profile_id] LIKE '%' + ? + '%')"><SelectParameters><asp:SessionParameter Name="profile_id" Type="String"/></SelectParameters>
I'd sincerly appreciate any help, any one may be able to offer,
Cheers,
Craig
select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.
i have to field in sql table
for example :
field1 : Father
Field2 : David,Sarah , Niki,John .
when i want to find for example sarah i can not becase near sarah is "," how can i solve this problem ?
i use this command :
"select * from table where field2 like '" & textbox2.text & '""
but show me another records like sarah kimm or sarah dave but i want exact Sarah
I'm trying to populate a DropDownList from my SQL database. I'm using C# 2005 and when I compile my code I get an error. Compiler Error Message: CS0103: The name 'myConnection' does not exist in the current context
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class Default3 : System.Web.UI.Page
{
private string connectionString = WebConfigurationManager.ConnectionStrings["mewconsultingConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "SELECT * FROM tblPau";
myConnection.Open();
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
myReader.Read(); // The first row in the result set is now available.
lstPau.Items.Add(myReader["PauSiteName"]);
myReader.Close();
myConnection.Close();
}
}
I need to move various records from a production database to an archive database. The fields in the two databases are identical but the archive database has an additional "Transfer Date" field. Can anyone recommend an easy way to create an SQL statement to move the record from production to archive? I've tried
INSERT INTO ARCHIVE (SELECT * FROM PRODUCTION WHERE ACCOUNTNO='XYZ')
but I get an error saying the columns among the two tables do not match (obviously because of the "Transfer Date" column). Is there a way to use a similar SQL statement that will populate the "Transfer Date" column w/ today's date?
Thanks in advance for your help.
I have a table with a column called SortOrder. It's an integer. The table also has a name and a city.I want to do an Insert and fill in SortOrder with the next higher integer, of all rows with city='NY'
Insert (name,city,SortOrder) values ('Dave','NY',
select max(SortOrder)+1 from myTable where city='NY')
Hi All,
Does anyone know if you can add columns to a local pulled table and if so can you use a select command to push the table back and exclude the added columns.
Basically I need to know if a record has been added on the PDA so I can get an ID from the server before pushing it back. I cannot alter the sql server database because it is part another application.
Cheers,
Jiggy!
Hello everybody,
I have following datasource
<asp:SqlDataSource ID="sqlIncidentTemplates" runat="server" ConnectionString="<%$ ConnectionStrings:Standard %>"
SelectCommand="SELECT [IncidentText] FROM [IncidentTemplates] WHERE [ConstrAreaID] = @ConstrAreaID AND [ConstrDeviceID] = @ConstrDeviceID">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="ConstrAreaID" Type="Int32" ConvertEmptyStringToNull="true" />
<asp:Parameter DefaultValue="" Name="ConstrDeviceID" Type="int32" ConvertEmptyStringToNull="true" />
</SelectParameters>
As far as I understand default select command should be "SELECT IncidentText FROM IncidentTemplates WHERE ConstrAreaID IS NULL AND ConstrDeviceID IS NULL". Unfortunately this is not the case. I assume that the "[ConstrAreaID] = @ConstrAreaID " is translated into "ConstrAreaID = NULL".
Could this be the reason and how can I solve this problem?
Thanks in advance
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT?
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource2.Select ();
}
protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e)
{string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString();
}
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected">
<selectparameters>
<asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" />
</selectparameters>
</asp:SqlDataSource>
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:TESTDrivercommunication %>"
SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue"
Type="Int32" />
<asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
I did a successful Insert to a SQL Server today in .NET VS2005.
But before I insert I figure I better make sure the part does not allready exist in the table.
Can a check the command to determine if it has a boolan value of False and if so to INSERT as before? Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "Select PartNumber From Pricing Where PartNumber = '" & var0 & "'"
'myCommand.CommandText = "INSERT INTO Pricing (PartNumber, ListPrice, PartDescription, ProductClassCode, ProductClassDescription, ProductFamilyCode, ProductFamilyDescription, ProductLineCode, ProductLineDescription) VALUES('" & var0 & "', " & var1 & ", '" & var2 & "', '" & var3 & "', '" & var4 & "', " & var5 & ", '" & var6 & "', '" & var7 & "', '" & var8 & "')"
myCommand.Connection = con
con.Open()
myCommand.ExecuteNonQuery()
con.Close()