How Do You Enter Checkbox Status In A Database Column??

Jun 4, 2008

Hi,

On my page I am using a wizard control with textboxes to collect data from the visitor.
When they click the finish button - this inserts their data into a Sql database.

On the form is a checkbox.
I dont know what type to use in the database column to accept this and also
how to configure the checkbox on the page so I know if its checked.

Also what would be inserted into the database column "yes" "no" or a numerical value??


Thanks for any advice?

amereto

View 1 Replies


ADVERTISEMENT

Trying To Enter More Than One Selected Value Into One Column In The Database.

Feb 1, 2008

Hi i am trying to enter more than one value into my constructor field in my database, at the moment when the values are entered into the database through the front end, they appear in the database as seperate records however they have the same ID,  the only thing that changes is are the data in the fields which i want in just one. This is the part of the stored procedure I am sure is causing this to happen;
IF @Access_Right_ID=8  or @Access_Right_ID=3  or @Access_Right_ID=9   BEGIN     DECLARE @Delimiter char(1)     SET @Delimiter = ','     DECLARE @CommaPos int     SET @CommaPos = Charindex(@Delimiter,@Constructor_IDs)       DECLARE @Remaining_Constructor_IDs VARCHAR(8000)     SET @Remaining_Constructor_IDs = @Constructor_IDs            DECLARE @Constructor_ID int                     WHILE (@CommaPos>=0)     BEGIN     
     SET @CommaPos = Charindex(@Delimiter,@Remaining_Constructor_IDs)             if   @CommaPos >0            SET @Constructor_ID = ltrim(rtrim(Substring(@Remaining_Constructor_IDs,1,@CommaPos-1)))           else            SET @Constructor_ID = @Remaining_Constructor_IDs                        INSERT INTO tblUser_Constructor      ( User_ID,  Constructor_ID)     VALUES     (@User_ID, @Constructor_ID )         SET @Remaining_Constructor_IDs = SUBSTRING(@Remaining_Constructor_IDs,@CommaPos+1,LEN(@Remaining_Constructor_IDs)-@CommaPos)           if @CommaPos=0      set @CommaPos = -1          END

View 2 Replies View Related

Use GETDATE() To Return System Date When Checkbox Column Is Checked

Jul 14, 2006

Hi, I need to return the system date to a column when a checkbox in another column becomes true, e.g. the instant a user updates a table where checkbox.value = 'true' it will record the date that it happened in another column.

View 1 Replies View Related

How Do I Call A Sql Column Dynamically With A Checkbox And Search Term In Textbox

Jan 25, 2008

 hi, I have a question regarding calling sql table columns dynamically? workflow would go as:1. user enters search term into a textbox2. user checks a checkbox to search by column in sqldb (eg.. firstname or surname) pseudo sql would go like......SELECT +%column1(checkbox1.value)%+ OR +%column2(checkbox2.value)%+ OR +%column3(checkbox3.value)%+WHERE  column1 = +%TextBox.Text%+ OR column2 = +%TextBox.Text%+ 3. display results in gridview my sql needs to improve greatly so any code insight(good book or link) would be terrific . thanks  

View 10 Replies View Related

Reporting Services :: Using SharePoint Checkbox / Choice Column In Multi-value SSRS Filter

Jul 7, 2015

In one of my datasets, my field "Team" is a SharePoint choice column that is a checkbox, so multiple entries are in it. In my Parameter, I have it as a multiple-value, and I'm specifying the values directly in both available and default.For my filter, I have [Team] IN [@Team], which is where the problem comes in. It will only filter the results of entries that only have ONE listed in Team. 

For example: One entry has "Building" in Team column which SSRS is displaying. But it will NOT display entries where "Building" and "Clerks" are displayed. I believe this is because SSRS sees this as 1 line of text, so it does not just see"Building" but "Building" and "Clerks" so it will omit it.I've tried to have my parameter set to "Get values from a query" but the problem there is the drop-down is too confusing since it interprets it as "Building" and then "Building, Clerks", and then "Building, Clerks, Economic Development' as another drop down, which defeats the purpose of the filter.

how I can get SSRS to show ALL entries that contain "Building" when I choose "Building" as a drop-down choice in my parameter? Instead of only showing ones that is Building only and dismissing other teams?

View 3 Replies View Related

Asp:Checkbox Addition To Database

May 12, 2008

I have a form set-up and am trying to add multiple items in my checkbox to the database; however, the only thing added to the database is the first item checked.  Here is the code and any help will be greatly appreciated.  Thank you!:<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConConnectionString %>" InsertCommand="INSERT INTO Consultations(FullName, Email, Business, BusinessInfo, Services, Comments) VALUES (@FullName, @Email, @Business, @BusinessInfo, @Services, @Comments)"
SelectCommand="SELECT Consultations.* FROM Consultations">
<InsertParameters>
<asp:ControlParameter Name="FullName" Type="String" ControlId="fullname" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlId="emailTextBox" PropertyName="Text" />
<asp:ControlParameter Name="Business" Type="String" ControlId="bisDropDownList" PropertyName="SelectedValue" />
<asp:ControlParameter Name="BusinessInfo" Type="String" ControlId="businessTextBox" PropertyName="Text" />
<asp:ControlParameter Name="Services" Type="String" ControlID="checkboxlist" PropertyName="SelectedValue" />
<asp:ControlParameter Name="Comments" Type="String" ControlID="commentsTextBox" PropertyName="Text" />
 
</InsertParameters>
</asp:SqlDataSource>

View 3 Replies View Related

Copying Database Disables Pwd And Must Enter New

Jan 24, 2008

I'm copying some of my development databases over to a new server and have noticed that even when I enable the acct the original password will no longer work. I have to then reenter each pwd for every acct that is in the database. I'm on sql server 2005 and I'm using the Task >> Copy Database.

View 3 Replies View Related

Storing Checkbox Values To Database

Mar 19, 2008

Hi i am trying to store the checkbox values on my page to the database, but im stuck on what to do. I have the following code already;protected void Btn_Subscribe_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
string strSQL = "INSERT INTO Newsletter(emailAddress) VALUES (@emailAddress)";SqlCommand Command = new SqlCommand(strSQL, conn);
Command.Parameters.Add("@emailAddress", SqlDbType.VarChar);Command.Parameters["@emailAddress"].Value = txtEmail.Text;
conn.Open();
Command.ExecuteNonQuery();
conn.Close();
}
How do i now insert the values from my checkbox, this is how i am setting the checkbox;public void Page_Load(object sender, EventArgs e)
{ArrayList values;if(!IsPostBack)
{
//Build array of data to bind to checkboxlistvalues = new ArrayList();
values.Add("Sport");values.Add("Gardening");values.Add("technology");
AlertList.DataSource = values;
AlertList.DataBind();
}
If i am going about it the wrong way please let me know, thanks

View 15 Replies View Related

Insert Into Sql Database With Checkbox List Items

May 1, 2008

I am trying to insert into a database Checkbox list items, I get good values up till the checkBox List and then it gives me all O's, My fields in my database are bit columns so I need the checkbox list itmes to be converted to this format.  This is what i have so far.  One thing is I don;t think I am inserting into the correct database columns.  bitLOD, bitInjury, bitIllness, bitreferral are the database fileds.Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
'Check for the page ID to make sure not an update page then either insert new data or update existing data.
Dim id As String
Dim LOD As Byte
Dim Injury As Byte
Dim Illness As Byte
Dim Referral As Byte
id = Trim(Request.QueryString("ID"))For Each LItems As ListItem In CheckBoxList1.Items
If LItems.Selected = True ThenSelect Case LItems.ValueCase "1"
LOD = 1Case "2"
Injury = 1Case "3"
Illness = 1Case "4"
Referral = 1
End Select
End If
Next
'Put data into the Database
If id = "" Then
'save data into the database
sql = "INSERT tblSADHealth (intTaskForceID, intUICID, strSSN, dtInjury, strNotes, LOD, Injury, Illness, Referral,) " _
& "VALUES (" & ddlTaskForce.SelectedValue & ", " & DDLUIC.SelectedValue & ", '" & txtSSN.Text & "', '" & txtStatus.Text & "', " _
& "'" & txtDate.Text & "','" & txtNotes.Text & "', " & LOD & ", " & Injury & ", " & Illness & ", " & Referral & ")"
Response.Write(sql)
Response.End()

View 4 Replies View Related

!!help!! How To Save User Inputs From Checkbox List To Database??

Dec 11, 2007

Hi,
 
I'm new to ASP.NET 2.0. I'll like to ask how do one save user input from txtbox, radiobttnlist or checkboxlist into database?
Im implementing a suvrey form here by the way.
 -any reference webs,
-any pointers from experts?
Thank you for your time in reading this email
 
Kayln

View 7 Replies View Related

Inserting CheckBox Values Into A Database (MSSQL 2000)

Sep 12, 2005

I have been trying to add values to a database and it keeps failing
i have no idea what i am doing wrong please help the code is asp.net
using vb. I have been having serious trouble passing check boxes in
forms from day one both singularly and dynamically from datagrids
if someone could show me some sample code of how to pass these
sort of values into the component and on to the query in this way
i would very much appreciate it.

Fuzzygoth

the error returned is

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.Data.SqlClient.SqlException:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'tblStation_FK00'. The conflict occurred in database 'TealSQL', table
'tblTravelPoint', column 'travelpointID'.
The statement has been terminated.

I have marked the area of the code the error is returned in the colour Violet and with a ##

The code i am using is below


## The aspx page ##

<%@ Page Language="vb" Debug="true" Trace="True"
Inherits="Devotion2Motion.AdminComp"
Src="../CodeBehind/AdminModule.vb"   %>
<!-- Binds the ActivityResortInfo.ascx user control to the page -->
<%@ Register TagPrefix="UserContol" TagName="D2MHeader" Src="../UserControls/Header.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="D2MFooter" Src="../UserControls/Footer.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="TravelPointDD" Src="../UserControls/TravelPointDD.ascx" %>
<script language="vb" runat="server">
   
    Sub Page_Load()
   
        If IsPostback = True Then
       
        Dim aInternational As Integer
       
        Dim Station As String = Request.Form("StationFrm")
        Dim Type As String = Request.Form("TypeFrm")
        Dim Address1 As String = Request.Form("address1Frm")
        Dim Address2 As String = Request.Form("address2Frm")
        Dim City As String = Request.Form("cityFrm")
        Dim International As String = Request.Form("InternationalFrm")
        Dim TravelPoint As Integer = Request.Form("_ctl5:dsTravelPointDD")

            If IsNothing(International) Then
                aInternational = "0"
            Else
                aInternational = "1"
            End If

            Dim AdminTravelPoints As New Devotion2Motion.AdminComp()
            ' Select the country dropdown list
           
AdminTravelPoints.AddStation(Station, Type, Address1, Address2, City,
aInternational, TravelPoint)
               
        End If
       
        Dim ReadResultTable As New Devotion2Motion.AdminComp()
        ' Select the country dropdown list
        dsResultSet.DataSource = ReadResultTable.GetStationtbl()
        dsResultSet.DataBind()
       
    End Sub

</script>


<!-- This UserControl Pulls in the header UserControl and the Div Tag Positions it #css reffrence is TopControl -->
<Div Class="TopControl">
    <UserContol:D2MHeader runat="server"/>
</Div>

<form method="post" action="AdminTravelPoint.aspx" runat="server">
<Div Class="CentreControl">
<table border="0">
<tr>
    <td>
        <asp:Label Text="Station Name: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="StationFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Type: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="TypeFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Address1: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="Address1Frm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="Address2: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="Address2Frm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="City: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:TextBox ID="CityFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
<tr>
    <td>
        <asp:Label Text="International: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <asp:CheckBox ID="InternationalFrm" CssClass="PrimaryGrid" runat="server"/>
    </td>
<tr>
</tr>
    <td>
        <asp:Label Text="Travel Point: " CssClass="BodyStyle" runat="server"/>
    </td>
    <td>
        <UserContol:TravelPointDD runat="server"/>
    </td>
<tr>
</tr>
    <td>&nbsp;
       
    </td>
    <td>
        <input value="Submit" type="submit">
    </td>
</tr>
</table>
</Div>

<asp:DataGrid ID="dsResultSet" AutoGenerateColumns="False" CssClass="PrimaryGrid" ShowHeader="true" runat="server">
<Columns>
    <asp:BoundColumn DataField="StationID" ReadOnly="true" HeaderText="Station ID"/>
    <asp:BoundColumn DataField="Station" ReadOnly="true" HeaderText="Station"/>
    <asp:BoundColumn DataField="Type" ReadOnly="true" HeaderText="Type"/>
    <asp:BoundColumn DataField="Address1" ReadOnly="true" HeaderText="Address"/>
    <asp:BoundColumn DataField="Address2" ReadOnly="true" HeaderText="&nbsp;"/>
    <asp:BoundColumn DataField="City" ReadOnly="true" HeaderText="City"/>
    <asp:BoundColumn DataField="International" ReadOnly="true" HeaderText="International"/>
    <asp:BoundColumn DataField="TravelPoint" ReadOnly="true" HeaderText="City"/>
</Columns>
</asp:DataGrid>

</form>


<Div Class="CenterControl">
    <UserContol:D2MFooter runat="server"/>
</Div>


##the vb componet that passess to the sql query ##

Public Function AddStation(ByVal Station As String, ByVal Type As
String, ByVal Address1 As String, ByVal Address2 As String, ByVal City
As String, ByVal aInternational As Integer, ByVal TravelPoint As
Integer) As SqlDataReader

            ' Create Instance of Connection and Command Object
            Dim
myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
            Dim
myCommand As New SqlCommand("sp_call_Station_Insert", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim
parameterStation As New SqlParameter("@Station", SqlDbType.NVarChar, 50)
            parameterStation.Value = Station
            myCommand.Parameters.Add(parameterStation)
           
            ' Add Parameters to SPROC
            Dim
parameterType As New SqlParameter("@Type", SqlDbType.NVarChar, 50)
            parameterType.Value = Type
            myCommand.Parameters.Add(parameterType)
           
            ' Add Parameters to SPROC
            Dim
parameterAddress1 As New SqlParameter("@Address1", SqlDbType.NVarChar,
50)
            parameterAddress1.Value = Address1
            myCommand.Parameters.Add(parameterAddress1)

            ' Add Parameters to SPROC
            Dim
parameterAddress2 As New SqlParameter("@Address2", SqlDbType.NVarChar,
50)
            parameterAddress2.Value = Address2
            myCommand.Parameters.Add(parameterAddress2)

            ' Add Parameters to SPROC
            Dim
parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50)
            parameterCity.Value = City
            myCommand.Parameters.Add(parameterCity)

            ' Add Parameters to SPROC
            Dim
parameteraInternational As New SqlParameter("@aInternational",
SqlDbType.Int, 4)
            parameteraInternational.Value = aInternational
            myCommand.Parameters.Add(parameteraInternational)

            ' Add Parameters to SPROC
            Dim
parameterTravelPoint As New SqlParameter("@TravelPoint", SqlDbType.Int,
4)
            parameterTravelPoint.Value = TravelPoint
            myCommand.Parameters.Add(parameterTravelPoint)

            ' Execute the command
            myConnection.Open()
           
            ## Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            ' Return the datareader
            Return result

    End Function


## The sql stored procedure ##

CREATE PROCEDURE [dbo].[sp_call_Station_Insert]
(
@Station As nVarChar(50),
@Type As nVarChar(50),
@address1 As nVarChar(50),
@address2 As nVarChar(50),
@City As nVarChar(50),
@aInternational As nVarChar(50),
@TravelPoint As Int
)
AS


DECLARE @ConVale As nVarChar(50)

SET @ConVale = (SELECT Station FROM tblStation WHERE @Station = Station)

If @ConVale = @Station
   
    BEGIN
        SELECT * FROM tblStation
    END
ELSE
    BEGIN
        insert into tblStation
            (Station, Type, address1, address2, city, International, TravelPoint)
        values (@Station, @Type, @address1, @address2, @City, @aInternational, @TravelPoint)
        SELECT * FROM tblStation

    END
GO

View 1 Replies View Related

Data Conversion Failed. The Data Conversion For Column Value Returned Status Value 4 And Status Text Text Was Truncated Or On

Jan 7, 2008

Hi Experts,

I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.

If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.

I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.

Can anyone suggest how to solve this issue.

View 11 Replies View Related

Status Column

Jun 28, 2006

Hi everyone,
while executing sp_who sub directory, I see a column which makes me totally confused. This column is status column.
I understood nothing about its usage. So would you please inform me ?

Thanks

View 1 Replies View Related

Sysconstraints And Status Column

May 12, 2004

How to interprete the colum status in sysconstraints table ?
(I have some thing like 133141).

View 5 Replies View Related

QUESTION: Status Column Of FK Constraints In Sysobjects - ????

Jul 23, 2005

Greetings:I have to do a one-off forceful change of some data in a database. Ineed to disable some FK constraints, make the data change, and thenre-enable the constraints.My process will be:ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2I wanted to get some evidence that the constraints were back up after Idid the 'check constraint' statements, so I looked in sysobjects.There's a status column for the constraints, but I can't make muchsense of the numbers. Most of the FK constraints that I have notchanged are of status 0, but when I NOCHECK a constraint, it goes to2304. When I CHECK it again, it's 2408.Can someone explain this, and suggest a way to double-check that myconstraints are in fact enabled once I run the CHECK statement?Thanks!!

View 2 Replies View Related

Dynamic Order Status Column Function

Apr 7, 2008

I am looking for assistance coming up with a function (or maybe not a function if there is a better way) to make the Status column in my order table dynamic. The default value for new records will always be "1" to designate a quote. At this point the field is not dynamic. Once the customer confirms the order, the user needs to execute a command to change the status to "3" to designate a Confirmed order. At this point the field needs to be dynamic based on the shipping records. There are two order details tables. One for sales items and one for rental items. Each of these details tables has their own shipping record. the CheckInOut Tables are for rental while the Ship tables are for sales. So, if some (but not all) of the items in either of these order details tables has a shipping record associated with it, then the status should be changed to "5". If everything has been shipping, the status is changed to "4". If everything has been shipping but some items have been returned, the status is "6" if everything has been shipping and all of the RentalDetail items have been returned then the status is "7" and if there is any other combination of a variety of ships and returns, the status is "8". Also, at any time, the user needs to be able to execute a command to change the value to "2". once the value is changed to "2" the field stops being dynamic again.

Below are my tables creation commands.


CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderRentalDetail
(
OrderRentalDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE CheckInOutHeader
(
CheckInOutID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE CheckInOutDetail
(
CheckInOutDetailID int identity primary key,
CheckInOutID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderRentalDetailID int,
Qty int
)
CREATE TABLE OrderSalesDetail
(
OrderSalesDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderSalesDetailID int,
Qty int
)

View 3 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

Between [Enter The Start Date] And [Enter The End Date])

Mar 28, 2007

Hi all can you help me please, I need the users to be able to enter in a date for a self generating report. Usually I use the @Enter_Date but I need them to be able to enter a start date and a end date
But when I run this query SQL gives me an error message Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the Start Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Enter the End Date'.

What should use in place of BETWEEN [Enter the Start Date] And [Enter the End Date]??


SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]

View 4 Replies View Related

Getting SQL Or Database Status On Webpage.

Feb 27, 2007

Hi is it possible to have a page of a website display the status of a database or MS SQL with .Net code ?, if so what code would i use. Any help would be appreciated.Thank You 

View 6 Replies View Related

How To See The Status Of All Triggers In A Database

Aug 23, 2001

I used the sp_msforeachtable stored proc to disable all triggers in a database. Once the command is complete, I want to see the statuses of all
these triggers . I am used to an Oracle database so I miss the data dictionary views that easily show this info. Also, I need to view the statuses of all FKs once they are all disabled as well.

View 4 Replies View Related

Suspect Database Status

Feb 14, 2008

Hello,

A hard drive crashed on one of our MS SQL Server 2005 and brought the server down
with it. We had replaced the hard drive and rebuilt the RAID 5 container and got the server rebooted.
However, one of the databases got corrupted after that. THis is the error logs snippet:
===============
,An error occurred during recovery<c/> preventing the database 'survey_p0765392' (database ID 439) from restarting. Diagnose the recovery errors and fix them<c/> or restore from a known good backup. If errors are not corrected or expected<c/> contact Technical Support.,
===============

We have backup which is 2 days old, and restoring from the backup would result in us losing a lot of records that we
have created since then. Is there any way to recover the data from this database? How canI repair it? Please advice. Thanks

View 5 Replies View Related

Database Design For Status Field

Mar 1, 2007

Hello,
I have a database design question. I want to store a status in a table. In the ASP.NET interface for the user this status will be adapted to the language of the user.
This is how it would look like:
StatusID 1 = "yes" for English, "oui" for French, "ja" for DutchStatusID 2 = "no" for English, "non" for French, "neen" for DutchStatusID 3 = "error" for English, "erreur" for French, "fout" for Dutch
I don't want to do the translation in ASP.NET and it should be done in one query.
How would it look like in the database design.

View 5 Replies View Related

Timestamp Ans Status Of Database File

Apr 21, 2007

Our database is running in full recovery mode and we backup the logfile every hour and the database file every night. I notice thetimestamp of database file *.mdf remains unchanged a few days. Forexample one database has its timestamp at Apr 15 while it is Apr 20already.I am wondering if this is acceptable. How do I force the system toflush the data and dirty blocks to the disk more frequently? What isthe downside to this?Thanks for any idea.

View 1 Replies View Related

Database Mail - Status Sent But Email Never Received

Jan 29, 2007

From SQL Management Studion I go to Management > Database Mail and I am trying to send a test email but I never receive anything.  I checked my SMTP Mail Server Logs and I saw no entry of my test email. 
I also I checked my SQL Database Mail Logs and everything seems fine, no errors are reported.
The msdb.dbo.sysmail_allitems shows my email status as "sent".
So what am I missing? What steps would you recommend for troubleshooting my problem?
Thank you,
Ric

View 7 Replies View Related

Can We Toggle The Database Status Between Recovery And Nonrecovery?

Jun 15, 2006

Hi,

1) We will setup logshipping for a large database.

2) 2 systems are located at different city

3) Instead of use 'backup from primary and restore with norecovery to secondary ', we planned to use

SAN disk clone to get a second set of disks for related database files and log files.

4) We will mount the disk to the secondary server, however, how can we put the database on the new

server as 'norecovery' mode so that we can setup logshipping to this server ??



Another easy example is:

1) detach database

2) Copy database files to another disk

3) bring the disk to another city and attached to the new server

The database is recoveryed, how can we attach the database or mount the disk and make

the database as norecovery so that we can keep apply new logs??



For 2 identical database image, how can we toggle one from normal to norecovery to setup logshipping?

Is change 'status' from master database working?



Thank you very much for any words.

If this is the wrong forum, please point me to a correct one since I am new here and I can't find a

backup/restore related forum.



Jane



View 1 Replies View Related

How To Recover SQL Server Database From Suspect Status

May 24, 2007

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.



The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.


Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.



Since the database is in suspect status, we are neither able to export the data nor able to back up the database.



Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

View 9 Replies View Related

How To Query The Progress Status Of A Database Restore

Apr 23, 2007

Hi,

we have a reporting database that needs to be refreshed every week. I normally get a big backup file (50GB) from our business partner very Sunday morning; I have setup a job to restore the database once I got the backup file. The restore job normally will take about 4 or 5 hours to finish, but sometime it may take much longer (more than 10 hours). We knew there is some kind of disk storage contention going on the SAN. When this problem happens the system guy always try to pick my db restore process to be the victim. I hate to kill a job when it has 90% done, but I have no way to tell the restore progress status. Does someone know if I can query database restore progress information from any DMVs?

Thank you for your help in advance.

David Zhang

View 3 Replies View Related

How Do I Change The Status On My Database? Currently Is Is Assigned As Restricted User

Jun 19, 2008

I restored a  sql server database today. There is a problem though. In SQL server, right next to the name of the database are the words "Restricted User". Their seem to be some limitations on my access to this database now as I am not able to run some of my C# code against the database. How can I fix this in SQL server so that I am not a Restriced user.
 
Example of what I am talking about. In SQL server my database is named Gorlaz. Just to the left of this is a yellow Icon that represents the database "Gorlaz". For illustration purposes I will use the character "$" to substitute for the yellow database icon. So this is how it reads in SQL server management studio.
$Gorlaz(Restricted User)
 

View 3 Replies View Related

After Restore The Database Success Stll The Status Loading.

Dec 17, 2001

I Restored the database from Backup with a Different database Name. I got a message the Restore Database was successful. Still the database has Loading Status. Can you let me know what I have to take that status.

View 1 Replies View Related

Reporting Services :: RDL Files For Database Fragmentation Status

Nov 24, 2015

i need a .rdl file for reporting..pull the data using below SQL script 

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 0.5 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

sql server 2008r2  database fragmentation report for .rdl file

View 7 Replies View Related

DB Engine :: 2012 Database Stuck In Recovery Pending Status?

Jan 12, 2013

I have a SQL cluster which ran out of disk the other day.  I threw plenty more disk at the SAN.  However, 1 of the databases came back with Recovery Pending and has been over 24 hours.  I have tried to backup the database with no success.  I don't have a clean backup from the day it occured so would have a good amount of work missing.

Running a dbcc check returns the following:

Msg 945, Level 14, State 2, Line 1

Database 'sqldb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

I have not tried a dbcc check with REPAIR_ALLOW_DATA_LOSS as there seems to be a high risk of losing whatever was going on.  how I can get the db back online?

View 10 Replies View Related

SQL Server 2014 :: Capture Each Change In Database Without Updating Earlier Status

Jan 22, 2015

I have a matrix table. These status can be changed by the user and I want to capture each change in database with out updating the earlier status

Pending
Activated
In PROGRESS
Submitted
Completed

Pending can be changed to submitted or completed. For one form there can be different status at different time. And each status must be saved in the database table. How can I design a table...

View 2 Replies View Related

Customize Database Maintenance Using SSIS (Select Databases Based On Status)?

Mar 7, 2008

I want to customize database maintenance to run only on selected databases based on their status in sys.databases, without having to statically select them in the GUI.


I'm trying to write an SSIS package that will do the following:


1. Query for a list of databases that are not read-only or recovering, etc.
2. Perform database maintenance tasks (e.g., rebuild indexes) on those databases.

I'm doing this because the database maintenance tasks do not provide a way to select databases based on criteria or a SQL query filter.

My package includes an Execute SQL task to get the list of databases, a Foreach loop container to loop through the databases, and then...this is where I get stuck. I can't use the existing maintenance tasks (Rebuild Index, Update Statistics, etc.) because I don't know how to provide an input variable for the list of databases. I'd rather not write dynamic SQL to generate commands for each database object.

Does anyone know how to do this?

Thanks much.

David

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved