Passing A String Into The InsertCommand Of SqlDataSource At The @color Character

Apr 27, 2007

Ok, so I'm a JSP guy and thing it should be easy to replace "@color" with t_color after I initialized it to red by
        String t_color = "red";
and then calling the insert

        SqlDataSource1.Insert();
here is insert command: 
            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" 
 
I've tried       InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "+ t_color+")"
 
Ive tried        InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, "<%$ t_color %>" )" 
 
Is there any easy way to do this?
 
or
 
Can I set it like
 
@color = t_color?
 
 
Thanks in advance for ANY help
 
JSP turning ASP (Maybe)
Dan 

View 4 Replies


ADVERTISEMENT

How Can I Add 'InsertCommand' In SqlDataSource?

Dec 17, 2007

I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?

View 5 Replies View Related

A SqlDataSource Can Execute Two InsertCommand??

May 3, 2007

hi everyone
i have a SqlDataSourceand i want execute two T-Sql (two InsertCommand)but not successHow did i do this?
Thanks
1      Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted2            Dim SDSTemp As SqlDataSource = Nothing3            Try4                SDSTemp = New SqlDataSource(CnStr, "")5                SDSTemp.InsertCommandType = SqlDataSourceCommandType.Text6                SDSTemp.InsertCommand = "INSERT INTO Table1 (id,t1,t2) VALUES (@id,@t1,@t2)"7                Dim id As String = e.Command.Parameters("@id").Value.ToString8                SDSTemp.InsertParameters.Add("id", id)9                SDSTemp.InsertParameters.Add("t1", CType(FormView1.FindControl("TextBox1"), TextBox).Text)10               SDSTemp.InsertParameters.Add("t2", CType(FormView1.FindControl("TextBox2"), TextBox).Text)11               SDSTemp.Insert()12   13              SDSTemp.InsertCommand = "INSERT INTO Table2 (id) VALUES (@id)"14   15              SDSTemp.InsertParameters.Add("id", id)16   17              SDSTemp.Insert()18           Catch ex As Exception19               Message.Text = ex.Message.ToString20           End Try21       End Sub22    

View 1 Replies View Related

InsertCommand Using Data From A Second SqlDataSource - ASP.NET 2.0

Nov 29, 2007

I have a process that inserts a new record using the InsertCommand of a SqlDataSource.  As part of the process, I need to insert data the is available in a different SqlDataSource.  I was trying this with the Insert Parameter:

View 1 Replies View Related

Sqldatasource InsertCommand && Oracle Sequence.

Jan 10, 2006

I cannot get an Oracle sequence to work in an Sqldatasource InsertCommand:I've tried the following:
InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)And: InsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (:Testing,:CUSTOMER_ID)<asp:Parameter DefaultValue="whsts_id.nextval" Name="testing" />AndInsertCommand='INSERT INTO "WHSTSTICKETS" ("WHSTS_ID", "CUSTOMER_ID") VALUES (whsts_id.nextval,:CUSTOMER_ID)
This is the classic error I get:ORA-01036: illegal variable name/numberI did review the asp.net forums before posting this.  As well, I've looked through the VWD 2005 documentation and cannot find the answer to this question.  Argg!  Any help is appreciated.   I've also worked to try and find out how to view the SQL that the Sqldatasource is generating, but I can't find the answer to this either.

View 8 Replies View Related

How To Change Color Of Character If Some Condition

Feb 15, 2008



Hi SQL experts
How can I make charaters change to color such as red if some condition happen
in a column of database tables?
Using IF.... function?
Thanks
Daniel

View 1 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

Return Left-most Character From 8 Character String

Oct 1, 2014

I'd like to return the left-most character from an 8 character string & the third from the left character too.

Like this ABC00123 returns AC

$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());

echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){

[Code] ....

View 5 Replies View Related

Formatting A String With Color/bold?

Sep 12, 2007

I have a string in which I am combining several fields. Is there a way to add formatting to the string as well


="Baud: " & Fields!BAUDRATE.Value & " DL Status: " & Fields!COMMSTATUS.Value

Example, I want my fields to appear in bold and string in normal.. I can't do this in an individual textbox due to space constraints

View 1 Replies View Related

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)

Nov 2, 2006

I am trying to insert a row into a table of Microsoft SQL Server 2000.

There are various columns.















[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-



StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset

      at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)


Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?

Can anyone please tell me what's wrong?

View 6 Replies View Related

Analysis :: Back Color Property - Match Number To Desired Color?

Aug 6, 2015

When using the back color property for SSAS 2008 R2, is there a good way to match the number to the desired color?  I found some color pickers online, but the numbers don't match the same colors in SSAS.  How can I best determine the number needed for the color I want?

View 2 Replies View Related

Passing Value To SqlDataSource Dynamically

Jul 10, 2006

Hello
Following is my code which is not working.
<asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource>
I want to pass the value of Session("ID") into that query. How can I do that?

View 4 Replies View Related

Passing Arrays As Parameter (SqlDataSource)

Jan 12, 2007

My sql-string looks like this:

 SelectCommand="SELECT * FROM Table1 WHERE Field1 IN @target"

 And my parameter looks like this:

<asp:ControlParameter Name="target" ControlID="CheckBoxList1" PropertyName="SelectedValue" />
This code gives me a syntax error near @target. Someone got a solution?

View 2 Replies View Related

Passing Parameters For SqlDataSource From Code Behind

May 25, 2007

Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1">            <ItemTemplate>                <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' />            </ItemTemplate>        </asp:DataList>        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"                SelectCommand="GetItem" SelectCommandType="StoredProcedure">         </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define:  <SelectParameters>                <asp:Parameter Name="I_GUID"  />                <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" />            </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam  

View 8 Replies View Related

Passing ANY Value To The &<InsertParameters&> Section Of A SQLDataSource

Jun 6, 2007

Ok-I'm new at this, but just found out that to get data off a form and insert into SQL I can scrape it off the form and insert it in the <insertParameter> section by using  <asp:ControlParameter Name="text2" Type="String" ControlID="TextBox2" PropertyName="Text">  (Thanks CSharpSean) Now I ALSO need to set the user name in the same insert statement. I put in a UserName control that is populated when a signed in user shows up on the page. But in my code I've tried:                             <asp:ControlParameter Name="UserName" Type="String" ControlID="LoginName1"  DefaultValue="Daniel" PropertyName="Text"/> and I get teh errorDataBinding: 'System.Web.UI.WebControls.LoginName' does not contain a
property with the name 'Text'. So I take PropertyName="Text" out, and get the error:PropertyName must be set to a valid property name of the control named
'LoginName1' in ControlParameter 'UserName'. what is the proper property value?  SO.....BIG question...Is there a clean way to pass UserName to the insert parameters? Or ANY value for that matter? Id like to know how to write somehting likeString s_test = "test string";then in the updateparameter part of the sqldatasource pass  SOMEHITNG like (in bold)     <asp:Parameter Name="UserName" Type="String" Value=s_test /> Thanks in advance...again! Dan  

View 5 Replies View Related

SQLDataSource Parameters Passing Null

Aug 23, 2007

 I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!?  I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
 I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>

View 3 Replies View Related

Passing SqlDataSource Properties To Class

Nov 14, 2005

Hi,I have a UserControl called DataPage with the following public property:private SqlDataSource sqlDataSource;public SqlDataSource SqlDataSource{   get { return this.sqlDataSource; }   set { this.sqlDataSource = value; }}In a web form i create an instance of the DataPage UserControl and assign the SqlDataSource properties:<%@ Page MasterPageFile="~/MasterPages/Page.master" Inherits="IMS.Pages.ModelType" Title="Model Types" %><asp:Content ID="Content" ContentPlaceHolderID="ContentPlaceHolder" runat="Server">   <ims:DataPage ID="DataPage" runat="server">      <SqlDataSource ID="SqlDataSource" runat="server"         ConnectionString="IMSConnectionString"          DeleteCommand="DeleteModelType" DeleteCommandType="StoredProcedure"         InsertCommand="InsertModelType" InsertCommandType="StoredProcedure"         SelectCommand="SelectModelType" SelectCommandType="StoredProcedure"         UpdateCommand="UpdateModelType" UpdateCommandType="StoredProcedure">         <DeleteParameters>            <asp:Parameter Name="ModelTypeID" Type="Int32" />         </DeleteParameters>         <UpdateParameters>            <asp:Parameter Name="ModelTypeID" Type="Int32" />            <asp:ControlParameter ControlID="UpdateModelTypeTextBox" Name="ModelType" PropertyName="Text" Type="String" />         </UpdateParameters>         <SelectParameters>            <asp:Parameter Name="ModelTypeID" Type="Int32" />         </SelectParameters>         <InsertParameters>            <asp:ControlParameter ControlID="InsertModelTypeTextBox" Name="ModelType" PropertyName="Text" Type="String" />         </InsertParameters>      </SqlDataSource>   </ims:DataPage></asp:Content> Then in the UserControl I try to call the SqlDataSource.Insert() method, and I get the following error:"The SqlDataSource control 'SqlDataSource' does not have a naming container.  Ensure that the control is added to the page before calling DataBind."Now, based on the error message it sounds like that SqlDataSource is not contained in either the web form or the usercontrol. Any ideas, thanks very much for any help people.Grant

View 3 Replies View Related

Passing Parameters To Sqldatasource Stored Procedure

Aug 22, 2006

Hi,
I'm developing a website using vwd express and I have created a GridView that bounds data from a stored procedure. The stored procedure takes one parameter. I tested it by using a default value and it works fine.
Now, instead of the default value i want to pass the current logged in user name as a parameter.
How do i do this. All the info i found around are for passing parameters to the select command of sqldatasource but i cant get it to work when i use a  stored procedure.
Thanks, M.

View 4 Replies View Related

Passing SqlDataSource Object An Array As A Parameter

Feb 22, 2007

Hi,
I am trying to get the selected options from a listbox and either pass a SqlDataSource object the array or loop through it and pass each element of the array. I then need to modify the returned databtable to graphing function, but first drop the last column. I was wondering if anyone can help me with the following:
1. Pass an array into SqlDataSource Select OR 2. Pass a single argument into the Select statement and populate a datatable without it writing over the current row each time it iterates through the foreach statement. I am looking for the dataview to append to dt each time it loops. Is there a property for dataview that behaves like the "ClearBeforeFill" for table adapters?3. Update a parameter programmatically
Below code works, but I think it can be more efficient. Any suggestions would be greatly appreciated.
Thanks in advance!!
 
 
        DataTable dt = new DataTable();        DataTable dt2 = new DataTable();        DataView dv = new DataView();                        
       foreach(ListItem liOptions in ListBox1.Items)       {             if(liOptions.Selected)             {                                      SqlDataSource1.SelectParameters.Add("Parameter1", liOptions);                   dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);                   dt2 = dv.Table;                   dt.Merge(dt2);                   dt2.Dispose();                   SqlDataSource1.SelectParameters.Clear();             }       }
        if (dt.Rows.Count > 0)        {           Graph(dt);                             //Pass original datatable (dt) to Graph();           dt.Columns.RemoveAt(2);      //Reformat datatable (dt) and remove last column before binding to Gridview1
           GridView1.DataSource = dt;           GridView1.DataBind();        } else {
    errorMessage.Text = "No data was returned!"; }

View 3 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page.  It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results.  I've tried several variations of:         string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'";        e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much! 

View 3 Replies View Related

SQL Server 2008 :: Convert RGB Color To HEX Color?

Mar 14, 2015

I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex color code to be used inside SSRS for a conditional color expression?

View 9 Replies View Related

Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter

Mar 28, 2007

Hello,
I'm having trouble executing a Stored Procedure when I leave the input field empty on a 'search' criteria field. I presume the error is Null/Empty related.
The Stored Procedure works correctly when running in isolation. (with the parameter set to either empty or populated)
When the application is run and the input text field has one or more characters in it then the Stored Procedure works as expected as well.
 
Code:
.
.
<td style="width: 3px">
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="LogId" DataSourceID="SqlDataSource1"
Width="533px">
<Columns>
<asp:BoundField DataField="LogId" HeaderText="Log Id" InsertVisible="False" ReadOnly="True"
SortExpression="LogId" />
<asp:BoundField DataField="SubmittedBy" HeaderText="Submitted By" SortExpression="SubmittedBy" />
<asp:BoundField DataField="Subject" HeaderText="Subject" SortExpression="Subject" />
<asp:TemplateField>
<ItemTemplate>
<span>
<asp:HyperLink ID="HyperLink1" runat="server">HyperLink</asp:HyperLink></span>
</ItemTemplate>
</asp:TemplateField>
 
</Columns>
<HeaderStyle BackColor="#608FC8" />
<AlternatingRowStyle BackColor="#FFFFC0" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SmallCompanyCS %>"
SelectCommand="spViewLog" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="txtName" ConvertEmptyStringToNull="true" Name="name" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Stored Procedure:
ALTER PROCEDURE dbo.spViewLog (@name varchar(50) )
 
AS
SELECT * FROM log_Hdr WHERE (log_hdr.submittedby LIKE '%' + @name + '%')
RETURN
 
I have tried the 'convertemptystringtonull' parameter but this didn't seem to work.
 Any guidance would be much appreciated.
Thank you
Lee
 
 

View 2 Replies View Related

Passing Parameter To Stored Procedure Call Within SqlDataSource

Aug 3, 2007

I'm trying to create a Grid View from a stored procedure call, but whenever I try and test the query within web developer it says I did not supply the input parameter even though I am.I've run the stored procedure from a regular ASP page and it works fine when I pass a parameter. I am new to dotNET and visual web developer, anybody else had any luck with this or know what I'm doing wrong? My connection is fine because when I run a query without the parameter it works fine.Here is the code that is generated from web developer 2008 if that helps any...... <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMS3TestConnectionString %>" ProviderName="<%$ ConnectionStrings:IMS3TestConnectionString.ProviderName %>" SelectCommand="usp_getListColumns" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:querystringparameter DefaultValue="0" Name="FormID" QueryStringField="FormID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>   

View 5 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View 2 Replies View Related

SQL Get First Character Of A String

Feb 18, 2005

Is there a function in SQL that will find the first character of a character string?

Select Org from PR where id = '100'

Normally, Org will retuen a string such as '1:00'. But, all I want from this string is the first character, '1'.

Thank You

View 3 Replies View Related

String With 15,500 Character

Apr 12, 2007

Hi all,

I am currently doing a mapping from text file to sql table. there is one column in the text file that contain more than 15,000 characters in it. it causes me an error because i was using varchar data type, in which i believe can only store 8000 char. can anybody tell me which data type to use? and also, will i be able to change the data type in the input column also? if yes, which data type should i change it to?







Thanks,

Jul

View 3 Replies View Related

Reading Each Character In A String

Jun 4, 2004

I want to read a String - character by character.I mean If the string is 'SAMPLE'
then I want to go to each and every character in it 'S','A','M','P','L','E' to compare with another string for equality.
I hope I am clear.Is there a way that I can read it?

View 1 Replies View Related

Repeated Character In String

Jun 19, 2006

How can find out the repeated character in each row value of spacific column

View 1 Replies View Related

Parsing Character String

May 21, 2008

I'm running into a couple of performance issues with regards to the parsing of a text string. We have a function that will take a comma delimited character string, parse out the individual values, and then populate a temp table with those values. The two issues are 1.) the parsing process is VERY slow and 2.) there's a max to how large the string can be - at some point it could easily be 8000 characters or more in length.



Here are the function and the stored procedure wher eit occurs:




CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))

RETURNS @Results TABLE (Item nvarchar(4000))

AS



BEGIN

DECLARE @INDEX INT

DECLARE @SLICE nvarchar(4000)

-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z

-- ERO FIRST TIME IN LOOP

SELECT @INDEX = 1

WHILE @INDEX !=0



BEGIN

-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER

SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE

IF @INDEX !=0

SELECT @SLICE = LEFT(@STRING,@INDEX - 1)

ELSE

SELECT @SLICE = @STRING

-- PUT THE ITEM INTO THE RESULTS SET

INSERT INTO @Results(Item) VALUES(@SLICE)

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING

SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)

-- BREAK OUT IF WE ARE DONE

IF LEN(@STRING) = 0 BREAK

END

RETURN

END

Procedure:


Code SnippetCREATE PROCEDURE [dbo].[RPTPatientAnalysis]

(

@stateList CHAR(2),

@employerIdList VARCHAR(4000),

@payerIdList VARCHAR(4000)

)

AS

SELECT

p.PAT_ID,

p.PAT_FirstName,

ISNULL(p.PAT_MiddleName,'') AS PAT_MiddleName,

p.PAT_LastName,

p.PAT_Gender,

CONVERT(VARCHAR(10),p.PAT_DOB,101) AS DOB,

p.PAT_AddressStreet1,

ISNULL(p.PAT_AddressStreet2,'') AS PAT_AddressStreet2,

p.PAT_AddressCity,

p.PAT_AddressStateProvince,

p.PAT_AddressPostalCode,

ISNULL(p.PAT_EmailAddress,'') AS PAT_EmailAddress,

p.PAT_PhoneNumber,

ISNULL(e.EMPLOYER_Name,'<Unknown>') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

ISNULL(p.PAT_Comment,'') AS PAT_Comment,

ISNULL(p.PAT_PrimCareProv_PRIMCP_ID,'') AS PAT_PrimCareProv_PRIMCP_ID,

ISNULL(p.PAT_PrimCareProvAllowNotification,0) AS PAT_PrimCareProvAllowNotification,

ISNULL(p.PAT_PrimCareProvFullName,'') AS PAT_PrimCareProvFullName,

ISNULL(p.PAT_DoNotMail,0) AS PAT_DoNotMail,

ISNULL(p.PAT_UnderAgePermission,0) AS PAT_UnderAgePermission,

p.PAT_LastEandMCodingDateTime,

p.PAT_Desceased,

p.PAT_PCP_ID,

p.PAT_LastUpdatedDateTime,

ISNULL(p.PAT_PCPRecordType,0) AS PAT_PCPRecordType,

ISNULL(p.PAT_EnableEmailMarketing,0) AS PAT_EnableEmailMarketing,

ISNULL(p.PAT_EnablePortal,0) AS PAT_EnablePortal,

ISNULL(p.PAT_PortalID,0) AS PAT_PortalID,

ISNULL(e2.EMPLOYER_Name,'') AS EMPLOYER_Name,

ISNULL(p.PAT_OtherEmployerName,'') AS PAT_OtherEmployerName,

pcp.PRIMCP_ID,

ISNULL(pcp.PRIMCP_ADDR_ID,'') AS PRIMCP_ADDR_ID,

ISNULL(pcp.PRIMCP_ClinicName,'') AS PRIMCP_ClinicName,

ISNULL(pcp.PRIMCP_PhysicianFullname,'') AS PRIMCP_PhysicianFullname,

pcp.PRIMCP_DateDeactivated,

ISNULL(pcp.PRIMCP_Phone_MedicalRecordFax,'') AS PRIMCP_Phone_MedicalRecordFax,

ISNULL(pcp.PRIMCP_Phone_Voice,'') AS PRIMCP_Phone_Voice,

ISNULL(pcp.PRIMCP_MedicalRecords_Street1,'') AS PRIMCP_MedicalRecords_Street1,

ISNULL(pcp.PRIMCP_MedicalRecords_Street2,'') AS PRIMCP_MedicalRecords_Street2,

ISNULL(pcp.PRIMCP_MedicalRecords_City,'') AS PRIMCP_MedicalRecords_City,

ISNULL(pcp.PRIMCP_MedicalRecords_State,'') AS PRIMCP_MedicalRecords_State,

ISNULL(pcp.PRIMCP_MedicalRecords_Zip,'') AS PRIMCP_MedicalRecords_Zip,

ISNULL(pcp.PRIMCP_Street1,'') AS PRIMCP_Street1,

ISNULL(pcp.PRIMCP_Street2,'') AS PRIMCP_Street2,

ISNULL(pcp.PRIMCP_City,'') AS PRIMCP_City,

ISNULL(pcp.PRIMCP_State,'') AS PRIMCP_State,

ISNULL(pcp.PRIMCP_Zip,'') AS PRIMCP_Zip,

ISNULL(pcp.PRIMCP_DoNotFax,0) AS PRIMCP_DoNotFax,

pati.PATINS_InsuranceTypeID,

ISNULL(pati.PATINS_Account,'') AS PATINS_Account,

ISNULL(pati.PATINS_Group,'') AS PATINS_Group,

ISNULL(pati.PATINS_CopayType,'') AS PATINS_CopayType,

ISNULL(pati.PATINS_CopayAmount,0) AS PATINS_CopayAmount,

ISNULL(pati.PATINS_CollectFullAmount,0) AS PATINS_CollectFullAmount,

ISNULL(pati.PATINS_EmployerPays,0) AS PATINS_EmployerPays,

ISNULL(pati.PATINS_ZeroScreenCopay,0) AS PATINS_ZeroScreenCopay,

ISNULL(pati.PATINS_ZeroVaccineCopay,0) AS PATINS_ZeroVaccineCopay,

ISNULL(pati.PATINS_NonPar,0) AS PATINS_NonPar,

ISNULL(pati.PATINS_MedicarePlan,0) AS PATINS_MedicarePlan,

ISNULL(ipcl.INSPCAT_Description,'') AS INSPCAT_Description,

ISNULL(ip.INSP_Name,'') AS INSP_Name,

ISNULL(ip.INSP_ChargeFullPrice,0) AS INSP_ChargeFullPrice,

ISNULL(ip.INSP_CopayApplies,0) AS INSP_CopayApplies,

CONVERT(VARCHAR(10),ip.INSP_DeactivatedDate,101) AS INSP_DeactivatedDate,

ISNULL(ip.INSP_EligibilityActive,0) AS INSP_EligibilityActive,

CONVERT(VARCHAR(10),ip.INSP_PromoStartDate,101) AS INSP_PromoStartDate,

CONVERT(VARCHAR(10),ip.INSP_PromoEndDate,101) AS INSP_PromoEndDate


FROM dbo.patient AS p

LEFT JOIN dbo.Employer AS e ON p.PAT_EMPLOYER_ID = e.EMPLOYER_ID

LEFT JOIN dbo.Employer AS e2 ON p.PAT_SecondaryEMPLOYER_ID = e2.EMPLOYER_ID

LEFT JOIN dbo.PrimaryCareProvider AS pcp ON p.PAT_PCP_ID = pcp.PRIMCP_ID

LEFT JOIN dbo.PatientInsurance AS pati ON p.PAT_ID = pati.PATINS_PAT_PERS_ID AND PATINS_InsuranceTypeID = 1

LEFT JOIN dbo.InsurancePayer AS ip ON pati.PATINS_INSP_ID = ip.INSP_ID

LEFT JOIN dbo.InsurancePayerCategoryLookup AS ipcl ON ip.INSP_INSPCAT_ID = ipcl.INSPCAT_ID

WHERE p.PAT_AddressStateProvince IN (SELECT Item FROM dbo.SplitVarcharMax(@stateList,','))

AND PAT_EMPLOYER_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@employerIdList,','))

AND pati.PATINS_INSP_ID IN (SELECT Item FROM dbo.SplitVarcharMax(@payerIdList,','))


Is there a faster / more efficient way to accomplish the above?

Any insight would be appreciated!!

View 9 Replies View Related

Adding Character In Between A String

Aug 17, 2012

Basically I need to insert underscore in between a string when there is a space in the content of the string. For Example say we have string 'ABC XYZ', I've to convert it into something like this 'ABC_XYZ'. Some strings do not have space in between and I should not add underscore in such cases. I'm working with MSSQL Server 2008 version.

View 2 Replies View Related

Separate String For Each Row By & Character

Oct 13, 2015

I simply need to separate the string for each row by the & character and then I'm assuming i ll be able to COUNT and GROUP BY the occurrences of each separate value in order to find the most commonly used inputs.I have a column Variables in the table Functions, that contains a string of values separated by the & character that shows the inputs each student inserted into a function.

How would I go about splitting that string without the use of a function or stored procedure and the find the most commonly used variables? (I was thinking the latter part could be easily solved with a COUNT(*) and appropriate GROUP BY.)

Example of data:

StudentID FunctionName Variables

1 Example1 Var1=10&Var2=xy&Sign=True&Role=False

View 2 Replies View Related







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