Significance In &"N&" For String Parameters
Feb 16, 2007
Hello,
I have noticed in many examples of TSQL code, that the developer is putting a "N" in before the single quotes to assign a string value to parameter. Could someone tell me what the significance of "N" is?
Example:
@step_name = N'Step 1 Email Report'
instead of
@step_name = 'Step 1 Email Report'
Does it do some kind of formatting / error checking? I haven't noticed any differences, but would like to write the best code that I can.
Thanks,
JJ
View 2 Replies
ADVERTISEMENT
Oct 12, 2004
I know this is a stupid question, but I just can't find the proper explanation. I often see the letter N preceding a parameter when executing a stored procedure (ie. exec sp_xxx @parm = N'test'). What is the significance of the N and when should it be used?
Thanks,
Roby2222
View 4 Replies
View Related
Jan 30, 2007
Sorry for the basic question but what is the significance of 1024? Is this true in Oracle as well? Is it a Fibonacci number?(just kidding). But why is the limit 1024 as far as fields in a table or 1024 arguements in a stored procedure, etc? Thanks in advance for adding to the "nuts and bolts" of my knowledge.
ddave
View 11 Replies
View Related
Jul 20, 2005
Hi,I'm a newbie in this SQL Server development. I have a database with 7tables that are linked to each other and have created clustered indexfor the primary keys and non-clustered index for the secondary keys.All my indexes are on the same filegroup, what is the effect of thisbecuase the application that I'm using runs very slow duringprocessing.PS: My tables have a grown rate of about 10 000 records a day.
View 1 Replies
View Related
Feb 23, 2008
Hi can any one explain why when I specify an outparameter as a string I only get back the first letter of the string when I call the procedure from .net?
my procedure is...
ALTER PROCEDURE dbo.UpdateUserPACSPASDetails
@UserGUID uniqueidentifier,
@UserPACSName varchar(32),
@PASName varchar(32),
@UserPACSPassword varchar(95),
@UserPACSPasswordIV varchar(95),
@UserFriendlyName varchar(32),
@Message varchar(32) OUTPUT,
@MessageNumber int OUTPUT
AS
SET NOCOUNT ON
DECLARE @tmpGUID as uniqueidentifier /*stores the temp guid from the function */
DECLARE @ReturnDomainName as varchar(32) /*stores the tmp domainname of the user who has the PACS name */
SET @Message=CAST('' as varchar(32))
SET @MessageNumber= 0
/* first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPACSName(@UserPACSName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN
/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 1
RETURN
END
END
/*now check the PAS name to see if it exists or is already in use by someone else
first check if PACS user name already exists or not */
SET @tmpGUID = dbo.fncFindPASName(@PASName)
IF @tmpGUID IS NOT NULL /* if the value is null the the PACSusername doesn't exist
if its not null then does the current user already own that PACSUserName? */
BEGIN
/*if the users ids are different then another user already has the PACS name */
IF @tmpGUID<>@UserGUID
BEGIN
SELECT @ReturnDomainName = UserDomainName FROM tblUsers WHERE UserGUID=@tmpGUID
SET @Message=@ReturnDomainName
SET @MessageNumber = 2
RETURN
END
END
UPDATE tblUsers
SET UserPACSName=@UserPACSName,UserPACSPassword=@UserPACSPassword,
UserPACSPasswordIV=@UserPACSPasswordIV, PASName=@PASName, UserFriendlyName=@UserFriendlyName
WHERE UserGUID=@UserGUID
SET @Message=CAST('' as varchar(32))
SET @MessageNumber=3
RETURN
I call this proc via a vb.net program using a command.executenonquery and then look at the parameter.
For some reason I only get the first letter of the string back! i.e. if the string is LSDNET/nik I get 'L'.
If I run the proc from visual studio step in to procecure it works fine!
the vb I'm using is... (the createaddparameter simply creates a bunch of parameters to add to a command -with the specified names and dbtypes).
I'm obviously missing a trick but what it is I can't see.
I seem to be able to pass all other data types back easily!
Dim LoadUserCommand As New SqlCommand
'create add command
Dim CreateAddParameter() As String = {"@UserGUID", _
"@UserPACSName", "@PASName", "@UserPACSPassword", _
"@UserPACSPasswordIV", "@UserFriendlyName", "@Message", "@MessageNumber"}
Dim UpdateDBTypes() As DbType = {DbType.Guid, DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, _
DbType.StringFixedLength, DbType.StringFixedLength, DbType.StringFixedLength, DbType.Int32}
LoadUserCommand = lclSQLHelper.CreateOLEDBCommand("UpdateUserPACSPASDetails", CreateAddParameter, UpdateDBTypes, lclDBConn.DBConnection)
LoadUserCommand.Parameters(6).Direction = ParameterDirection.Output
LoadUserCommand.Parameters(7).Direction = ParameterDirection.Output
'update the data in the data table in the database
LoadUserCommand.Parameters(0).Value = lclUser.UserGUID
LoadUserCommand.Parameters(1).Value = lclUser.UserPACSName
LoadUserCommand.Parameters(2).Value = lclUser.UserPASName
LoadUserCommand.Parameters(3).Value = lclUser.UserPACSPassword
LoadUserCommand.Parameters(4).Value = lclUser.PACSPasswordIV
LoadUserCommand.Parameters(5).Value = lclUser.UserFriendlyName
LoadUserCommand.Parameters(6).Value = ""
LoadUserCommand.Parameters(7).Value = 0
Try
Dim lclRet As Integer = LoadUserCommand.ExecuteNonQuery()
Dim tmpDomainName As String
tmpDomainName = LoadUserCommand.Parameters(6).Value.ToString 'read the name back
Dim tmpMsgNumber = LoadUserCommand.Parameters(7).Value 'read the message number back
Select Case tmpMsgNumber 'not written yet as I haven't got this far!!!
Case 1 'pacs username already exists
Case 2 'pas username already exists
Case 3 'execute fine and all names updated
End Select
View 3 Replies
View Related
Mar 5, 2007
When I go to Report > Report Parameters... in VS, I create date parameters and specify the type as datetime. I then add the parameters to my SQL statement. I then go back to look at the parameters and they have changed to string. This happens every time I make a modification to the SQL statement. Is this a bug, or am I missing a step? I thought someone would have asked this before, but I can't find a post for it in this forum.
Thanks in advance,
Scott
View 4 Replies
View Related
Dec 26, 2003
I thought I would post this solution. I searched a long time and didnt see anything about how to solve my problem. After avoiding this and simply building strings I decided to dig in my heels and try and figure this out. Well, maybe I am just slow. :) Anyhow, here is some code that should help a lot of folks with this question...
Function GetProductCategories(ByVal departmentID) As DataSet
'set the connection string (comes from a property in this case)
Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringWeb"))
'set the sql text string notice the @DepartmentID is my parameter. protected from sql injections
Dim strSQL As String = "SELECT * FROM ProductCategory WHERE DepartmentID = @DepartmentID"
'set new command object to strsql and the connection. required
Dim command As New SqlCommand(strSQL, connection)
'set parameters to pass to through with the strsql. required for parameters. you can take this a set further. See commented fields below.
command.Parameters.Add(New SqlParameter("@DepartmentID", departmentID))
'additional set up for parameters if you like...
'command.Parameters.Add("@departmentID", SqlDbType.Int, 4)
'command.Parameters("@departmentID").Value = departmentID
'set SQLDataAdapter to your previously created command object
'this enables your adapter access to your strSQL, connection and parameters
Dim da As New SqlDataAdapter(command)
'set proper name of table for data set based upon departmentID
If departmentID = 1 Then
Dim ds As New DataSet()
da.Fill(ds, "dtCarriers")
Return ds
End If
'set proper name of table for data set based upon departmentID
If departmentID = 2 Then
Dim ds As New DataSet()
da.Fill(ds, "dtProducts")
Return ds
End If
End Function
View 4 Replies
View Related
Jun 12, 2000
I guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)
Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )
I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.
I've modified most of the stored procedures so that they actually work. :)
The tables it uses are simple:
sessions: sessionid (uniqueidentifier), date_stamp (datetime), sessionipaddr(varchar(50))
sessionvalues: sessionid (uniqueidentifier), sessionvalname (varchar(100)), sessionvaldata (varchar(8000))
To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)
There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here' + CHAR(39) + "s some data"
This doesn't work. Indeed, even if the concatenation worked, CHAR(39) doesn't in this context.
Then I thought I'd be really clever, and try a trick from mySQL:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here's some data'
Naturally, that one didn't work, either. (That was a long shot, admittedly!)
This is mission-critical. Not only apostrophes, but quotes and other punctuation marks must be able to be transferred. Anyone know a way to do it?
View 3 Replies
View Related
Sep 15, 2005
I've created a search page in my asp.net app that allows the user to enter optional parameters to narrow down the result set. It looks something like:Find all parts where: manuafacturer: <dropdownlist>ANY | manufacturer 1 |... </dropdownlist> model: <dropdownlist>ANY | model 1 |... </dropdownlist> cost: between <textbox> and <textbox> dollarsCurrently I create the SQL command on the fly building the WHERE based on what the user selects. For example if in the form above they select manufacturer = manufacturer1 model = ANY cost = between 10 and 15the WHERE string is ... WHERE manufacturer='manufacturer1' AND cost BETWEEN 10 AND 15Since the user doesn't care about model I leave it out of the WHERE. OK so here is my question. I want to move my queries to strored procedures however I'm not sure how to create the query since it changes based on what the user enters. Using the example above I'm assuming I can create one query with 4 parameters however what value would I use for ANY? parameter1 (manufacturer) = "manufacturer1" parameter2 (model) = ??? parameter3 (price low) = 10 parameter4 (proce high) = 15I see there is an ANY operator in T-SQL but it doesn't look like the right thing to use. Should I use LIKE '%'? Seems that using LIKE would result in addition overhead.ThanksSimon
View 2 Replies
View Related
Jul 2, 2007
Hello,
My basic goal is to try to simplify inputs for the user. I have 3 parameters: Begin Date, End DAte and Duration. Duration will contain 3 choices: All, 2 Years and Range and is meant to give them a shortcut to dates as described below:
All - Would automatically populate the start date to 10/01/2005 and an end date to current date
2 Years - Would automatically populate the start date to current date minus 2 years, and the end date to current date.
Range - Would allow the user to select any dates as desired.
I'm able to get the dates to populate based on the duration field using non-queried values based on the Duration value, but the problem is that if I want to allow them to select Range the calendar control is not available and a text box is displayed.
I've tried to create some code in the properties that would populate, but I keep getting that this item is Read Only. The code I've created is as follows:
public function populateDates(Duration) as String
Select Case Duration
Case = "Range"
Report.Parameters!pBeginDate = Report.Parameters!pBeginDate
Report.Parameters!pEndDAte = Report.Parameters!pEndDAte
Case = "All"
Report.Parameters!pBeginDate = #10/01/2005#
Report.Parameters!pEndDAte = Now().Today
case = "Two"
Report.Parameters!pBeginDate = DateAdd("yyyy", -2, Now().Today)
Report.Parameters!pEndDAte = Now().Today
end select
end sub
My only goal is to give the User the 3 choices, but still keep the calendar control available, and I can't seem to do this?
suggestion please!
Thanks!
Maureen
View 6 Replies
View Related
Jul 30, 2007
Hello,
I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc.
But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
Thanks.
View 3 Replies
View Related
Jan 30, 2008
Hi All,
I created a SSIS package to import an excel spreadsheet into my data warehouse.
When I run the package it runs fine. When I created a SQL Job to run the package I get the following error:
Option "Source=D:HelpDeskImportBook2.xls;Extended" is not valid. The command line parameters are invalid.
Now if I look at my source connection string in the job it looks like the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:HelpDeskImportBook2.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES";
Does anyone know why it appears that my connection string looks like it is being cut off and does anyone know how I can correct the problem?
Thanks,
Scott
View 8 Replies
View Related
Dec 28, 2007
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 Replies
View Related
Jun 12, 2015
I want to setup a link from an SSRS report to Master Data Manager so when the click on the SSRS report item, it will pull up Master Data Manager filtered to the particular member that the user clicked on from the report. I cannot find any references to that being possible. Is it possible? What is the query key value to pass a member codeid or other filter criteria to Master Data Manager explorer?
MID is Model ID
VID is Version ID
EID is Entity ID
Are there other possible parameters? Can a pass a member ID or code?
View 2 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
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
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
View 3 Replies
View Related
Dec 17, 2014
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
View 2 Replies
View Related
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Mar 21, 2007
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
View 7 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
Jul 28, 2015
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
View 8 Replies
View Related
Jan 2, 2008
Hello all,
Given:
string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure;
Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder]
It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight?
Thanks,
View 11 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
