Using Varchar To Store A Sting Of Values From A Checkboxlist

Aug 25, 2004

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

View 6 Replies


ADVERTISEMENT

Insering Multiple Checkboxlist Values

Nov 14, 2007

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

View 4 Replies View Related

Checkboxlist And SQL Search Using AND/OR On Selected Checkboxlist Items.

May 22, 2006

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

View 1 Replies View Related

Can Varchar(max) Store Images In Sql ?

Jun 18, 2007

If you serialize the image into a string ?  If you want to save a byte array to a string, do you have to use a base64 encoded string ?

View 1 Replies View Related

Convert Sting To Date

Mar 1, 2006

Hi,I am a Newbie to SQL and am therefore sorry if this is a stupidquestionI am trying to run the following commandSELECT OriginationNumber, DestinationNumber, StartTime, State, DurationFROM dbo.IpPbxCDRWHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like'MMColParam' and StartTime Between '%MMColParam2%' AND'%MMColParam3%'I am passing the variables MMColParam from a Web page but I always getthe following error when I try to run the commandError converting string Datetime from Charactor String.I have checked the DB and the value is defined as DateTime entryhere is an example of Table entry 16/11/2005 18:27:24Can anyone help me with this?Many ThanksIan

View 7 Replies View Related

Convert Sting To Datetime Datatype

Jul 29, 2015

How to convert in MS SQL server 2005 from string to Datetime datatype

SELECT
[customer_key] ,
[company_code],
[distributor_code] ,
[dist_center],
[cust_code_ref] ,
[customer_name],

[Code] ....

Giving error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

View 5 Replies View Related

Store As VARCHAR Or Normalise In A Child Table?

Jun 1, 2007

Should I change my current design which has a Lookup Child Table and a Parent Table containing a ID link tot he Child Table, and instead just putting a varchar for the Value in the Parent Table?

The issue is that the value is unique for a large percentage of cases (although some specific values reoccur very frequently).

However, the parent table has a high number of inserts, and that in turn often requires inserts to the Child Table, which in turn pushes the Child Table out of shape, and performance suffers.

The data is very seldom used (usually for debugging only)

Should I de-Normalise this for performance?

Details of the actual circumstances:

I have a table that Logs each "page hit" on our web site. That stores the "Referrer".

We store the Referrer for the SESSION separately (the first page viewed will have an external referrer, thereafter all pages will have an "internal" referrer).

This Referrer data is used VERY LITTLE - just for the occasional debugging issue.

Within some URLs are frequently used (www.mydomain.com/HOME.ASP) and others contain &Parameters and stuff and are either relatively unique(www.mydomain.com/ViewBasket.ASP&BasketID=1234) or variable (www.mydomain.com/ViewProduct.ASP&ProductID=1234)

Currently I store the Referrer in a Child "Lookup" Table, and the PK ID in the "Logging" Table.

Some analysis of the data shows:

119,509 rows (pages logged [purged after a couple of days, only includes data where the Referrer is not blank!])

Of those there are 10,760 distinct Referrer values. An average of 11 Page Hits per Referrer value.

On the face of it that suggests that using a child table is good! However, for each row inserted into the Page Hit table I have to do the following to get the Referrer ID:

SELECT @MyReferrerID = ID FROM MyLookupTable WHERE MyValue = @MyReferrerValue
IF @@ROWCOUNT = 0
INSERT ... @MyReferrerValue ...
SELECT @MyReferrerID = @@IDENTITY

(MyLookupTable has a PK on [ID] and a Unique Index on [MyValue] )

(In a busy month we will add 100,000 rows to this Lookup table, in a slack month 20,000. These get "purged" after a while, so the table size stays about the same. The table is defragged [if required] daily and the Stats updated. There are 350,000 rows in MyLookupTable)

Obviously some values in MyLookupTable reoccur, others are unique - and get purged after a while. Inserting these new entries pushes MyLookupTable out of shape, the SHOW_STATISTICS start to look awful, an sp_recompile sorts it out (Why? The query plan doesn't change ... although obviously the "shape" of the table has, maybe the Stats got auto-updated, would that change anything?)

So I'm thinking about just storing @MyReferrer as a VARCHAR in the Logging table, and throwing away MyLookupTable.

So really I'm just looking at performance, not Normalisation ...

What do you think?

Kristen

View 2 Replies View Related

I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar

Mar 21, 2008

My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ")        Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection)        cmdUpdate.CommandType = Data.CommandType.StoredProcedure        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int))        cmdUpdate.Parameters("@QuestionID").Value = QuestionID               cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar))        cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output        cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int))        cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output        'open connection        dbConnection.Open()        'Execute non query        cmdUpdate.ExecuteNonQuery()        'close connection        dbConnection.Close() 

View 8 Replies View Related

Easiest And Performance Effective Way To Store Blob Into Varchar Column

Feb 8, 2007

Hi,
My package dumps the errors into a table. The problem is, it couldnt dump Error Output column to a varchar field. I have added an script component in between to transform to string but no success.

I tried ErrorOutput.GetBlobData(0, ErrorOutput.Length)

but when I query the database, it says "System.Byte[]'

I will appreciate the responses to this post.


Thankyou,
Fahad

View 12 Replies View Related

Check If Varchar Has Non 0 Values In The Decimal?

Apr 25, 2008

Hi All,

I have a table Saleshistory with 89481412 records. I have a field Sales of nvarchar type, all the records in this field are numeric, and the records are with 2 decimal places. I expect all the records to be ending at .00 (for example 5.00,345.00,25.00), but I want to verify if something is for example 5.6 etc.

So in short I want to check if any Salesrecords have more than zero values in the decimal. How can I check this.

Please help.

Thanks,

Zee

View 8 Replies View Related

Converting Varchar To Numeric Values

Oct 31, 2005

Not sure how I can convert varchar to numeric values in SQL Server? Trying to do so in Design and get error:
Unable to modify table.
ODBC error: [Microsoft][ODBC Server Driver][SQL Server] Error converting data type varchar to numeric.
Any suggestions?

View 5 Replies View Related

IF...ELSE's Evaluation Of NULL Varchar Values

Nov 16, 2007



I have a stored procedure that accepts a parameter of type varchar(32). This parameter gets tested against NULL as part of an IF statement. The behavior I'm seeing is that this test never evaluates to TRUE, even in cases where NULL has been passed in as the value of the parameter.


CREATE PROCEDURE uspNewVital
@Description varchar(32),
@Type tinyint,
@DeviceType varchar(32),
@Dimension varchar(32),
@Unit varchar(32)
AS
IF (@Dimension != NULL AND @Unit != NULL)
BEGIN
RAISERROR ('You must specify either a dimension or a unit.', 15, 1) ;
RETURN 0 ;
END
IF NULL = @Dimension
BEGIN
DECLARE @UnitID AS int ;
SELECT @UnitID = ID FROM tblUnit WHERE Description=@Unit ;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('Unit not found.', 15, 1) ;
RETURN 0 ;
END
INSERT INTO tblVitalType VALUES (@DeviceTypeID, @DatumID, NULL, @UnitID) ;
END
ELSE
BEGIN
DECLARE @DimensionID AS int ;
SELECT @DimensionID = ID FROM tblDimension WHERE Description=@Dimension ;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('Dimension not found.', 15, 1) ;
RETURN 0 ;
END
INSERT INTO tblVitalType VALUES (@DeviceTypeID, @DatumID, @DimensionID, NULL) ;
END
GO


When I pass in a NULL for @Dimension, code execution still goes to the ELSE block.

At first I thought this might have something to do with the ANSI_NULLS option, but the database has this defaulting to OFF.


Any insights would be greatly appreciated. Thank you.

View 4 Replies View Related

Need To Store Duplicate Values To DB

May 7, 2007

Hi,I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP.  SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](    @ReportDate smalldatetime,    @SiteID int,    @DataFeedID int,    @FromCode varchar,    @Sent int,    @Delivered int,    @TotalOpens REAL,    @UniqueUserOpens REAL,    @UniqueUserMessageClicks REAL,    @Unsubscribes REAL,    @Bounces REAL,    @UniqueUserLinkClicks REAL,    @TotalLinkClicks REAL,    @SpamComplaints int,    @Cost int)ASSET NOCOUNT ON    DECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCodeSELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN    SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost    FROM CrmPublisherSummary        WHERE @LastReportDate < @ReportDate        AND SiteID=@SiteID        AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET    Sent=@Sent,    Delivered=@Delivered,    TotalOpens=@TotalOpens,    UniqueUserOpens=@UniqueUserOpens,    UniqueUserMessageClicks=@UniqueUserMessageClicks,    UniqueUserLinkClicks=@UniqueUserLinkClicks,    TotalLinkClicks=@TotalLinkClicks,     Unsubscribes=@Unsubscribes,    Bounces=@Bounces,    SpamComplaints=@SpamComplaints,    Cost=@Cost,    TagID=@TagID    WHERE ReportDate=@ReportDate        AND SiteID=@SiteID        AND TagPKID=@PKIDENDELSE        INSERT INTO CrmPublisherSummary(    ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,     UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,     Bounces, SpamComplaints, Cost, DataFeedID, TagID)         VALUES(    @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens,     @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes,     @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagID)SET NOCOUNT OFF this is the one to clear: SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[spCRMPublisherSummaryClear](     @SiteID INT,     @DataFeedID INT,     @ReportDate SMALLDATETIME) AS    DELETE LandingSiteSummary        WHERE SiteID=@SiteID AND ReportDate=@ReportDatebut it doesnt seem to be working.Please suggest. 

View 2 Replies View Related

Best Way To Store Simple Up Or Down Values

Jul 20, 2005

I'm creating a table for maintenance records.In each record, many of the values are simply checkboxes (on the UI).In the database table for these attributes, is a good way to store thestate of these checkboxes as simple as 0 for false, 1 for true?-David

View 6 Replies View Related

How To Store Values During Dataflow

Aug 2, 2007

The point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.

How can i get a value that was calculated before? Can i store it in a variable?

Many Thanks!

View 5 Replies View Related

How To Check Numeric Values In A Varchar Field

Feb 11, 2003

Hi all,

I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?

Thanks,
Jannat.

View 5 Replies View Related

Inserting Varchar Values Which Is Int Value In Int Column In Server

Aug 6, 2015

IF I have a table like the below one and i have to insert a number value which is inserted as varchar in an int column  then what is expected behavior of this statements .

create
table stud
(id
int)
insert
into stud
values ('1').

I thought it should fail but it succeeds...

View 4 Replies View Related

Optimized Way To Store Hashed Values?

Jul 12, 2007

What is the most optimized way to store Hashed values (obtained by Object.GetHashCode() method) in SQL Server?

Thanks.

View 4 Replies View Related

Option To Store The Inserted Values

Jan 4, 2005

Hi,

In oracle there is "RETURNING" Clause which stores the values inserted to a variable. Is there any option in sql server. i.e storing and fetching takes place at one shot.

eg:
insert into tab (name) values ('hello') returning name into val;

-cheeku

View 4 Replies View Related

Store Multiple Values In A Single Value

Nov 6, 2005

was hoping someone couild provide some insight into a problem I'm trying to solve.

I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.

Has anyone done anything like this before?

If so, any help would be appreciated!

Thanks in advance!

View 1 Replies View Related

Can Store A List Of Values In A Variable?

Apr 16, 2014

I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

View 7 Replies View Related

Registry To Store Configuration Values ?

Jan 3, 2006

I try to store a SSIS-configuration value in the registry.

But it's failed. I get a warning, that the registry-key is not found.

What is the syntax ? I have searched in BO,MSDN and  Google/Internet ==> nothing.

There is no example.

 

 

View 23 Replies View Related

Checkboxlist Sql Database

Dec 21, 2007

I 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,

View 5 Replies View Related

CheckBoxList Selection

Mar 14, 2008

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

View 3 Replies View Related

CheckBoxList In Database

Aug 9, 2004

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

View 5 Replies View Related

T-SQL (SS2K8) :: Using Not Like To Filter Out Non-numeric Values From Varchar Field

Mar 26, 2014

I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.

For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.

This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.

I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"

case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'

The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.

I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.

How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?

View 6 Replies View Related

Transact SQL :: Values In Table - Convert Varchar To Decimal

Jul 22, 2015

I have one of the sample values in my table. I need to convert below value to Decimal(18,5)

DECLARE @i
VARCHAR
SET @i
= '0.9'

Output i m looking for is 0.90000

View 16 Replies View Related

Check Table Values In The Store Procedure...

Oct 22, 2007

HI
I have a problem related Store Procedure, that i  am trying to extact a  value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly.
Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com))
how i can do that in the store procedure.
Comments will be appreciated.

View 3 Replies View Related

Images - Type Of Values To Store Pics

Apr 21, 2013

What is the type of values I have to use to tell the database to store pics?

View 4 Replies View Related

Want To Store Arabic Values In The Sql Server Database

Feb 27, 2008

Hi,

I want to store arabic values and dates in sql server database.

I am using visual basic 2005 with sql server 2005. I entered the the arabic values through visual basic forms. But when i open the database table, i see the values in english format.

I change my regional settings of the operating system is arabic (platform vista).

Can you give how to see arabic values.

Thanks.


Regards
Kashif Chotu

View 3 Replies View Related

Store Into Database Report Parameters Values?

Feb 3, 2007

Hallo
is it possible to perform an insert/update query upon the execution of a report, using the report Visual Basic expressions?
I mean, the user enters some values for the report parameters, and i would like to store them, along with the resulting recordset data, into a dedicated table in the database.

View 1 Replies View Related

To Display Two Values In Combobox But In Database Will Store Only One Value Out Of That

Jan 24, 2008



hi friends
i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table.
what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only
bold line which is difficult for me to inserting only "designationId" in customer table.




Inventory_Master.cntData.Open()

cmdstr = "Select DesigID,Designation from Designation_Master"

cmd = New SqlCommand(cmdstr, Inventory_Master.cntData)

cdrCustomer = cmd.ExecuteReader()

If cdrCustomer.HasRows = True Then

While cdrCustomer.Read()

cmddesig = cdrCustomer.Item(0).ToString.PadLeft(1) + cdrCustomer.Item(1).ToString.PadLeft(25)

cbodesignation.Items.Add(cmddesig)

End While

Else

End If

Inventory_Master.cntData.Close()



Inventory_Master.cntData.Open()

cmd = New SqlCommand("insert CUSTOMER(Membership_No,Member_Initial,Member_Name,Member_Birthdate,Gender,Res_Telno,Mobile_No,Res_Addr1,Res_Addr2,Res_City,Res_Pincode,Landmark,Company_Name,Comp_Addr1,Comp_Addr2,Comp_Telno,Comp_City,Comp_Pincode,Designation,Credit_Limit,Income_Mthly,Email_ID,Note) values (@Membership_No,@Member_Initial,@Member_Name,@Member_Birthdate,@Gender,@Res_Telno,@Mobile_No,@Res_Addr1,@Res_Addr2,@Res_City,@Res_Pincode,@Landmark,@Company_Name,@Comp_Addr1,@Comp_Addr2,@Comp_Telno,@Comp_City,@Comp_Pincode,@Designation,Convert(money,@creditlimit),@Income_Mthly,@Email_ID,@Note)", Inventory_Master.cntData)

cmd.Parameters.AddWithValue("@Membership_No", txtmebno.Text)

cmd.Parameters.AddWithValue("@Member_Initial", cbomebname.Text)

cmd.Parameters.AddWithValue("@Member_Name", txtmebname.Text)

cmd.Parameters.AddWithValue("@Member_Birthdate", dtime.Value)

cmd.Parameters.AddWithValue("@Gender", cbogender.Text)

cmd.Parameters.AddWithValue("@Res_Telno", txttelno.Text)

cmd.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text)

cmd.Parameters.AddWithValue("@Res_Addr1", txtresaddr1.Text)

cmd.Parameters.AddWithValue("@Res_Addr2", txtresaddr2.Text)

cmd.Parameters.AddWithValue("@Res_City", txtrescity.Text)

cmd.Parameters.AddWithValue("@Res_Pincode", txtrespin.Text)

cmd.Parameters.AddWithValue("@Landmark", txtlandmark.Text)

cmd.Parameters.AddWithValue("@Company_Name", txtcompname.Text)

cmd.Parameters.AddWithValue("@Comp_Addr1", txtcompaddr1.Text)

cmd.Parameters.AddWithValue("@Comp_Addr2", txtcompaddr2.Text)

cmd.Parameters.AddWithValue("@Comp_Telno", txtcomptelno.Text)

cmd.Parameters.AddWithValue("@Comp_City", txtcompcity.Text)

cmd.Parameters.AddWithValue("@Comp_Pincode", txtcomppin.Text)

cmd.Parameters.AddWithValue("@Designation", cbodesignation.Text.PadLeft(1, CChar(CStr(1)))) [ help me for inserting designation in this line]

cmd.Parameters.AddWithValue("@creditlimit", txtcreditlimit.Text)

cmd.Parameters.AddWithValue("@Income_Mthly", txtincomemthly.Text)

cmd.Parameters.AddWithValue("@Email_ID", txtemailid.Text)

cmd.Parameters.AddWithValue("@Note", rtxtNote.Text)

cmd.ExecuteNonQuery()

Inventory_Master.cntData.Close()

MsgBox("RECORD SAVED")


View 1 Replies View Related

Dataset Store Procedure Return Values

Apr 1, 2008



Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.

Thanks in advance.

Glenn

View 5 Replies View Related







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