i have string variable as,
String str="Int";
now, while assigning sql parameters, i want
param.SqlDBType=SqlDBType.Int;
but, value of Int is dynamic. it may be string or double so,
i want it to be as,
param.SqlDBType=(SqlDBType)str;
but its not acceptable(its invalid cast).
in any way can i do it and how?
regards---
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.
How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int)
conn.Open()
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue
cmd.ExecuteNonQuery()
End If
Next
conn.Close()
End WithEnd Sub
End Class
Hello,Im calling a stored proc that has a numeric value as a varible.In the asp im using the code:myPrivate.Parameters.Add("@memberID", SqlDbType.Decimal, 18).Value = Decimal.Parse(uxToText.Text);I'm using decimal as there is no numeric sqldb type, is this correct? Also is the decimal parse correct as Im getting a 'Input string was not in a correct format' error.Thanks
View 2 Replies View RelatedI have an overloaded method in my data access layer that I use to load the parameters into a command object. One of these overloaded methods is as follows:
public void LoadSqlParameter(string name, string value, ParameterDirection direction){ param = new SqlParameter(); param.ParameterName = name; param.Value = value; param.Direction = direction;
param.SqlDbType = SqlDbType.VarChar;
cmd.Parameters.Add(param);}
Will using a SqlDbType of VarChar cause me any problems if the actual parameter in the stored procedure is a Char?
Am I better off leaving out the line that specifies the type?
Thanks
Hi,
I am creating a parameter of type text, and using either of the 3 statements below, to specify the size of the text. Unfortunately none of the statements put all the text into the database.
The receiving column is Text(16) and Text in Row is turned off.
I really would appreciate any help with this.
Many thanks,
Polly Anna
sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text) ', 2147483647, ParameterDirection.Input)
or
sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.Text)
or sqlParam = New SqlClient.SqlParameter("@varResponse", SqlDbType.VarChar, 4000)
then
sqlParam.Value = Me.ResponseText
My app uses .net 1.1 and MS SQL 2005 as backend. How to add a SqlParamter that is varchar(MAX)?SqlParameter myParameter = new SqlParameter("@Description",SqlDbType.VarChar,whatToPutHere?,ParameterDirection.Input, true,0,0,"Whatever",DataRowVersion.Current,"Whatever");
View 4 Replies View RelatedI have a field in my SQL Server 2005 database of type numeric(18,3)In code, I treat the value as decimalWhen creating my command parameters, this is how I'm declaring them:prm.SqlDbType = SqlDbType.Decimal;prm.Precision = (byte)int.Parse("18");prm.Size = int.Parse("0");prm.Scale = (byte)int.Parse("3");Inserting a number like 5.687 is rounding to 6.000 anyone know why it is doing that?
View 5 Replies View RelatedHi,
My checkbox status is saved as SqlDbType.Bit in MS SQl Server database.
When I use the following code to retrieve it, it does not work.
myRow[myColumn].ToString()=="1"
I appreciate your kind assistance.
Thank you.
Hi there,
Following is the code I have, which I select and execute as one batch.
declare @intData integer
Declare @strSQL varchar(8000)
Set @strSql = ''
set @strSql = 'select @intData = Age from MasterRecords'
exec (@strSql)
I get the following error in SQL 2000's query analyzer.
Must declare the variable '@intdata'.
Any and all help is highly appreciated.
Hi,
Does anyone know what is the maximum size for a string variable that can be used by Execute statement in SQL 7 and SQL 6.5? If I really need to execute
a long statement around 9000 characters long, how can I do it?
declare lcsql varchar(x) && what is the maximum size for x in SQL 7 and SQL 6.5
select lcsql = 'select ...'
execute (lcsql)
Hi
I have a problem with a string variable which looks something like this
@VARIABLE = 'RTY,YTUK,GHJ'
I need to be able to select (RTY) the first individual values seperated by the commas to be used as a variable elsewhere in my SQL script.
Following this I need to use the second value (YTUK) to be run in the same piece of code. Followed by the 3rd value (GHJ)
Can anyone tell me how to do this?
Thanks
How much if any of a perf gain is there when you specify the SqlDbType on a parameter?
View 1 Replies View Relatedhi there,
i am trying to pass a string which contains a string,
here is the code which is wrong :
{string sqlcommand = "select pnia.pnia_number, pnia.user_name, pnia.date_pnia, pnia.user_pnia, problem.problem, gormim.gorem_name, status.status_name from pnia,gormim,problem,status where (pnia.status='@p1' and status.status='@p1' and pnia.problem=problem.problem_num and pnia.gorem=gormim.gorem)";
OleDbCommand cmd = new OleDbCommand(sqlcommand,con);OleDbParameter p1 = new OleDbParameter("@p1",this.DropDownList4.SelectedItem.Value.ToString());
cmd.Parameters.Add(p1);
}
the problem is that the sql compailer doesnt take the parameter (@p1) as a string
if someone could help me with that it would be great ! tnx
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.
Here's what it looks like:
[after submitting a form on the previous page with a drop down list box titled "fiscal"]
dim strYear
strYear = Request.Form("fiscal")
sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"
[where strYear is the following: PYMNT_DT > ''12/31/98'']
Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?
Thanks in advance.
Sincerely,
Chad Massie
I want to run the following code:
Declare@Testnames as Varchar(500)
Set@Testnames = ('CT,MRI')-- List all radiology type
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In (@Testnames)
Nothing comes back.
When I run:
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In ('CT','MRI')
Then I get results.
Is there a way around the character limit for a variable? I'm throwing the following in a variable and evaulating it as an expression and having an execute sql task do it, but I can clearly see it's being cut off.
"insert work.dbo.data_run select publisher,publisher_db,subscriber,subscriber_db,article,null,getdate(),null
from msdb.dbo.sysreplicationalerts
where error_id<>0
and alert_error_code=20574
and [time] between '" +(DT_STR,50,1252)@[System::ContainerStartTime] +"' and '"+(DT_STR,50,1252)GETDATE()+"'"
Thanks,
Phil
I am trying to insert a large amount of data from a web form I created into a "text" field in my DB. Problem is its oly entering the 1st 16 characters. I can see why but I dont understand what I need to do to avoid this shortcomming. Ive searched the forums here and in google groups to no avail :(
<code>
sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Content", System.Data.SqlDbType.Text, 16, "Content")).Value = ftbContent.Text
</code>
How do I need to restructure the above line?
Any help would be appreciated.
TIA,Stue
Here is my code:
Dim oParameter As New System.Data.SqlServerCe.SqlCeParameter("@pMyParameter", SqlDbType.Binary, 3000)
If you set a watch on this object, the size is set back to 510. I have tried resetting the size back to 3000 after construction using oParameter.Size, but it doesn't change from 510. If the command is executed using ExecNonQuery, this causes the bytes to get cut off at 510 bytes and returns the error: Byte array truncation to a length of 510.
Can I insert data into SQL Server 2005 Mobile Edition, into a field of data type binary(3000) using .NET CF 2.0 via SqlServerCe objects?
Thanks for any help.
Kirk
Hi Folks,
How can i pass a string variable from code behind page to html page on event fire. i want to pass sql qerry to SelectCommand (SELECT * FROM [Tickets] WHERE ([TicketNo] = @TicketNo)) in some variable from codebehind on some event fire.
<asp:SqlDataSource ID="sqldsTickets" runat="server" ConnectionString="<%$ ConnectionStrings:IMTicketingConnectionString %>" SelectCommand="" >
<SelectParameters>
<asp:SessionParameter Name="TicketNo" SessionField="test" Type="Int64" />
</SelectParameters>
</asp:SqlDataSource>
Thanks in advance
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?
example:
@variable varchar(1000)
SELECT *
FROM table
WHERE @variable
(The @variable would reflect the where clause string)
Thanks!
I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:
declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'
Here is the SQL code I'm using:
exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO
The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.
Hi,I am having problems setting the value of a variable in a SQL Stringthat I have to create dynamically in my procedure. The code that Icurrently have is as follows:set @sqlStatement='Set @compare_string=' + '(Select ' +@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' whereIdentity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'exec(@sqlStatement)The error message that I get is as follows:Must declare the variable '@compare_string'.Here @compare_string has already been declared in the procedure and Idon't have a problem using the variable anywhere else but this SQLStatement (when called using the EXEC function).I am not sure why SQL Server can't see the variable declared when usedin a string in conjunction with EXEC. Is this a syntax issue? Any helpon this issue would be greatly appreciated!Thanks in advance.
View 5 Replies View RelatedI have to send an email using a variable as message body... the variable gets its value from database varchar(8000). Due to limited size of string variable in SSIS the value gets truncated.
Any workaround?
Thanks
Hi,
Does anyone know a way to store the ConnectionString of an OLEDB connection in a variable? I want to see exactly what values are in there because the step is failing with an error at that point. Here is what I'm looking for:
Data Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Thanks,
Phil
Hi,
I developed an SSIS package and I'm using the dtexec utility to run it. The package has a variable RunDate (datetime) and i want to set this value to "" (empty string) when i try to run this package using the dtexec.
Currently I'm getting an error Argument option not valid.
Thanks in advance
Hi I was hoping that someone might be able to help me with this.
I'm trying to figure out why my VB.net code below generates 0 or 1 but doesn't insert it when I can execute my stored procedure with: exec sp 0
myParm = myCommand.Parameters.Add("@bolProMembCSNM", SqlDbType.Bit)
myParm.Value = IIf(CBool(rblProMembCSNM.SelectedItem.Value) = True, 1, 0)
I've tried everything I used to use with Classic ASP and am stumped now.
Any ideas? I will have to do this for numerous controls on my pages.
Thanks in advance for any advice.
I have a stored proc that builds a character string from a number of rows returned from a select query. Is there anyway to insert a carriage return after I append the value of each row to the string variable?
Thanks in advance,
Chris
Hi, I was wondering if someone could help out. I need to create a stored procedure, but before i do so, I need to know if I can store a conditional expression in a string and just use that variable as my condition for the WHERE clause. The reason I ask this is because I am trying to create a stored procedure that queries different things depending on what the inputs are.
View 2 Replies View RelatedHI
can i assign a string column to a date column
string column contain date data.
UPDATE TABLE ENT SET EXPIRE_DATE = PREVIOUS_TRN_VALUE
where EXPIRE_DATE is a datetime column
PREVIOUS_TRN_VALUE is a string column having the value '2007-05-07'
thanks
I have created a function with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Output varchar(100)
SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity
RETURN @Output
END
how can I pass the variable @item correctly for the string comparison
WHERE item = @item AND week = @week
to work correctly please?
WHERE item = '@item' AND week = @week
won't work and
WHERE item = @item AND week = @week
won't work.
HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
View 2 Replies View Related