Insert With Parameters (SQL Server)

Jun 17, 2006

Hello, this is my code:


 SqlCommand cmd = new SqlCommand("INSERT INTO Users (Username,Password) " +
"VALUES ('@username','@password' ", new SqlConnection(my_ConnectionString));

cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = txtUsername.Text;
cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50).Value = txtPassword.Text

cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close(); 
But in the database, the row inserted is exactly this:

"@username" "@password"

I mean, the parameters are not inserted :S

Please, tell me the error in the code...
Thank you so much,

Carlos.

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Insert One To Many Valued Parameters From A Stored Procedure Into A Table

Jan 14, 2015

I have a SP with Parameters as:

R_ID
P1
P2
P3
P4
P5

I need to insert into a table as below:

R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5

How can I get this?

View 2 Replies View Related

INSERT, SCOPE_IDENTITY, And Parameters...

Mar 14, 2007

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening. SqlConnection conn = new SqlConnection(@"Server=.SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True");
conn.Open();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

// delete all rows
cmd.CommandText = "DELETE FROM Person";
cmd.ExecuteNonQuery();

Response.Write("start... <br><br>");

// ad-hoc insert
cmd.CommandText = "SET IDENTITY_INSERT Person ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Response.Write("ID = ");
Response.Write(cmd.ExecuteScalar());
Response.Write("<br>");
cmd.CommandText = "SET IDENTITY_INSERT Person OFF";
cmd.ExecuteNonQuery();

// parameter insert
cmd.CommandText = "SET IDENTITY_INSERT Person ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@PersonID, @PersonName, @PersonAge)";
p = new SqlParameter("@PersonID", 11);
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
p = new SqlParameter("@PersonName", "Jon Doe2");
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
p = new SqlParameter("@PersonAge", 21);
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Response.Write("ID = ");
Response.Write(cmd.ExecuteScalar());
Response.Write("<br>");
cmd.CommandText = "SET IDENTITY_INSERT Person OFF";
cmd.ExecuteNonQuery();

Response.Write("<br>end.");

}
finally
{
conn.Close();
}
I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...
Thank you.

View 4 Replies View Related

Insert Data With Parameters And Sqldatasource

Apr 24, 2007

I have a sqldatasource in a wizard. When I click the finishbutton in the wizard I want to insert some values (from textboxes, dropdownlists in my wizard) into a mysql-database. For this I use parameters, I add some values in the design mode and some in codebehind. The parameters in codebehind are added perfectly but the other parameters in designmode (name, email) just inserts null. Why?  My datasource: <asp:SqlDataSource
ID="dsInsert"
InsertCommand="INSERT INTO tbltest (name, email, city) VALUES (?name, ?email, ?city)"
runat="server"
ConnectionString="<%$ ConnectionStrings:conn %>"
ProviderName="MySql.Data.MySqlClient">
<InsertParameters>
<asp:FormParameter Name="?name" ConvertEmptyStringToNull="true" Type="string" FormField="name" />
<asp:FormParameter Name="?email" ConvertEmptyStringToNull="true" Type="string" FormField="email" />
</InsertParameters>
</asp:SqlDataSource>   The codebehind:         protected void finishButtonClick(object sender, EventArgs e)        {            dsInsert.InsertParameters.Add("?city", TypeCode.Int32, city.SelectedValue);            dsInsert.Insert();        }

View 3 Replies View Related

Update/Insert Using XML Strings As Parameters

May 20, 2008

Hi All,

We got this little issue of passing around (updated and inserting) small dataSets (20-500 or so records) from fat clients to a remote server running .Net / SQL 2005.

Instead of trying to fudge the data and make updates in .Net, we just decided it would be a lot less nonsense if we just wrap up the dataSets in an XML string, have .Net just pass it thru as a parameter in a SP and let SQL parse it out using openXML. The data is small and server is low use, so I'm not worried about overhead, but I would like to know the best methods and DO's & Don'ts to parse the XML and make the updates/inserts....and maybe a few good examples. The few examples I've come across are kind of sketchy. Seems it's not a real popular method of handling updates.

Thanks in Advance,
Bill

View 5 Replies View Related

Insert From Parameters And Select Statement

May 30, 2006

Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo

View 1 Replies View Related

ADODB 2.8 - SQL Insert Statement - Using Parameters

Nov 20, 2007

Hi there,
I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID.
But instead of it it returns error:

Run-time error '-2147217900 (80040e14)
Must declare the scalar variable "@ii_file"

Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
"?
I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side.
Thanks!

Darek


Public Sub save_import()
Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ado_conn 'an existing connection
.CommandType = adCmdText
.CommandText = "insert into import_main (ii_file, id_file, oi_file, od_file, folder, import_desc, id_client,basis_of_study, id_is, project_leader, urisk_model_basis, as_at_date, extent_benchamark) " & _
"values (@ii_file, @id_file, @od_file, @od_file, @folder, @import_desc, @id_client, @basis_of_study, @id_is, @project_leader, @urisk_model_basis, @as_at_date, @extent_benchmark) " & _
"select @id_im = @@identity"
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
.Parameters.Append .CreateParameter("@id_file", adVarChar, adParamInput, 255, Me.cbo_id)
.Parameters.Append .CreateParameter("@oi_file", adVarChar, adParamInput, 255, Me.cbo_oi)
.Parameters.Append .CreateParameter("@od_file", adVarChar, adParamInput, 255, Me.cbo_od)
.Parameters.Append .CreateParameter("@folder", adVarChar, adParamInput, 1000, Me.txt_folder)
.Parameters.Append .CreateParameter("@import_desc", adVarChar, adParamInput, 255, Me.txt_import_desc)
.Parameters.Append .CreateParameter("@id_client", adBigInt, adParamInput, Me.cbo_client)
.Parameters.Append .CreateParameter("@basis_of_study", adVarChar, adParamInput, 255, Me.txt_basis_of_study)
.Parameters.Append .CreateParameter("@id_is", adSmallInt, adParamInput, Me.cbo_status)
.Parameters.Append .CreateParameter("@project_leader", adVarChar, adParamInput, 255, Me.txt_project_leader)
.Parameters.Append .CreateParameter("@urisk_model_basis", adVarChar, adParamInput, 1000, Me.txt_urisk_model)
.Parameters.Append .CreateParameter("@as_at_date", adDate, adParamInput, CDate(Me.txt_as_at_date))
.Parameters.Append .CreateParameter("@extent_benchmark", adVarChar, adParamInput, 1000, Me.txt_extent_benchmark)
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Execute rec_affected, , adExecuteNoRecords
If rec_affected > 0 Then
Me.cbo_import = .Parameters.Item("@id_im")
End If

End With


End Sub

View 3 Replies View Related

Insert Syntax When Passing Input Parameters

Dec 27, 2000

I'm trying something like this:

CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
BEGIN TRANSACTION
INSERT INTO Junk (Dist)
VALUES (@Dist)
COMMIT TRANSACTION
select @CheckNo=@@IDENTITY

If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?

PS I'm using Microsoft SQL 7.0

View 2 Replies View Related

How To Run Insert Query For Required No.of Times With Different Parameters...?

Apr 22, 2008

Hi All,

I have two tables in my database.
I want to insert date and no.of hours worked on that day for a particular project.
So in a week if end user enters 7 dates and hrs for each day......
i have to insert those values into the table against one project only.
Can any one please help me out how to run insert query for 7 times (in a week) with different parameters

Thanks,
Praveen

View 5 Replies View Related

SqlDataSource With Different Number Of Columns And Insert Parameters And FormView

Dec 7, 2005

Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?
TIA
   Jernej

View 2 Replies View Related

T-SQL (SS2K8) :: Parameters Passing Trough INSERT-SELECT Structures

Jul 4, 2014

I have got two user-defined functions: fn_Top and fn_Nested. The first one, fn_Top, is structured in this way:

CREATE FUNCTION [dbo].[fn_Top]
RETURNS @Results table (
MyField1 nvarchar(2000),
MyField2 nvarchar(2000)

[code]....

ENDI would like to perform a sort of dynamic parameter passing to the second function, fn_Nested, reading the values of the numeric field MyCounter in the table OtherTable.Fact is, x.MyCounter is not recognized as a valid value. Everything works fine, on the other hand, if I set in fn_Nested a static parameter, IE dbo.fn_Nested(17).

View 5 Replies View Related

Sql Insert Conversion Failed When Converting From A Character String To Uniqueidentifier. Error With Parameters

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

ADO.NET 2-VB 2005 Express Form1:Printing Output Of Returned Data/Parameters From The Parameters Collection Of A Stored Procedure

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

Run Report By Different Parameters Without Having To Enter Information For All Parameters At Same Time

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

Linked Server/SQL Server OLE DB Performance Problem With Parameters?

Jul 8, 2004

Hi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.

I have set the dynamic paramters option on, and use collation compatible.

View 5 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

SQL Server Parameters

Sep 3, 1998

Could any one tell me what the following parameters do under SQL Server 6.5:
(right-click on a server and choose configure, under the server options tab,
click on the parameters button).

-T3632
-m

They were present when I installed MS SQL Server on one system and caused
problems, but were NOT present when I installed on another system.
I can`t find them anywhere in the online documentation.. please help!

Thanks in advance..

View 1 Replies View Related

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql

Apr 29, 2007

ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">     protected void runit_Click(object sender, EventArgs e)    {       //SqlDataSource ID = "InsertExtraInfo".Insert();      //SqlDataSource1.Insert();    }      protected void Button1_Click1(object sender, EventArgs e)    {        SqlDataSource newsql;                newsql.InsertParameters.Add("@name", "Dan");        newsql.InsertParameters.Add("@color", "rose");        String t_c = "purple";        string tempname = Page.User.Identity.Name;        Label1.Text = tempname;        Label2.Text = t_c;        newsql.Insert();    }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>mini update</title></head><body>    <form id="form1" runat="server">        &nbsp;name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />        color        <asp:TextBox ID="color" runat="server"></asp:TextBox><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<asp:LoginName ID="LoginName1" runat="server" />        <br />        <br />        <br />        <br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"            ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"            UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">            <DeleteParameters>                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="color" Type="String" />                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </UpdateParameters>            <InsertParameters>        <asp:InsertParameter("@name", "Dan", Type="String" />        <asp:InsertParameter("@color", "rose") Type="String"/>                                       </InsertParameters>        </asp:SqlDataSource>        &nbsp;        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"            AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">            <Columns>                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />                                <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />            </Columns>        </asp:GridView>           </form></body></html>  

View 1 Replies View Related

Query Duration Using Parameters Vrs No Parameters

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

Parameters Dependencies When Using Parameters!Name.Label

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

Sql Server Connectivity - Parameters

Apr 25, 2007

Hi,

I am new to SQL Server. I want to connet to SQL Server database and fetch records. SQL Server will be present in some other server.

For connecting to SQL Server is it necessary to create DSN. Is it possibel to connect to SQL Server with out DSN

I want following parameters are enough for opening connetion are not:
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source=172.16.1.60;" & _
"Initial Catalog=TESTATV;" & _
"User ID=sa;" & _
"Password=p@ssW0rd;"

As per my assumption

Data Source is your server name

Initial Catalog is your database name

please clarify me on this.

thanks

View 4 Replies View Related

Parameters In Sql Server 2000

Jul 20, 2005

Hi!I need how to pass variables struct witt n fields and n recordsto procedures in transac sql in one bbdd sql server 2000,this parameters are in/out.Thanks.

View 1 Replies View Related

Report Server Parameters

Nov 16, 2007

Hi all,

I'm writing a web interface and calling reports from report server. I first used the ReportViewer control. But it seemed to be impressively faster to directly call the report URL and link that directly in a frame.

This all works fine. But I want to dynamically add the parameter values that are used by the report in my URL string. To find out those, I have written a stored procedure:

CREATE PROCEDURE dbo.spIIMGetReportParameters
@sReportname varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xmlParameter xml
SELECT @xmlParameter =
(SELECT Parameter
FROM [catalog]
WHERE [Name] = @sReportname)
SELECT parameter.Name.value('.','nvarchar(50)') AS ParameterName
FROM @xmlParameter.nodes('/Parameters/Parameter/Name') as parameter(Name)
END

Then I loop through the result and add parameters to the Url string.
But sometimes I get parameters that are not needed.

Is there another way to find at runtime which parameters a report expects?

Thanks,
Jan

View 1 Replies View Related

Report Server Parameters

Jun 30, 2006

Hi!

I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:

An error has occurred during report processing.

Query execution failed for data set 'DataSet3_generar'.

Must declare the scalar variable "@Param1".

I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.

But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.

I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)

So, is there a way of solve this using SQL server express? Or I need SQL server 2005?

If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?

Thanks! Any suggestion will help me a lot!

View 5 Replies View Related

Parameters In Report Server

Sep 25, 2007



There is a brown bar in Report Server that holds the report parameters. This bar seems to be CLOSED by default. Is there a way to set the default so that the parameter bar is OPEN and the users can see the parameters?

Thanks.

View 2 Replies View Related

Query A Linked Server With Parameters

Apr 20, 2001

I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.

For example, the following statement works just fine:

SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")


But If I try to use:

DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'

SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL

SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)

it does not work.

Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)

Then is there any way I can accomplish what I want to on the Remote server?

Thanks in advance for your help.

View 1 Replies View Related

DTS Parameters In &#39;execute SQL Task&#39; SQL Server 2k

Jan 8, 2002

Hi

SQL server 2000 DTS Problem

I'm trying to pass the value of a global variable
as an input parameter in a 'execute sql task' DTS object.

However, SQL refuses to accept the global variable
assigned to the input parameter '?'. When you run the
DTS package the error message 'no value given for one or
more parameters' appears.

When you save / exit the DTS object by clicking OK, and then go
back into it, the 'input Global variables'
parameter field is empty again.
Searched msdn/knowledgebase to no avail - please help ! Thanks






Help much appreciated please !

View 6 Replies View Related

SQL Server 2014 :: Like Statement With Parameters

Sep 30, 2015

I have a table :

Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))

and I have a sp for search data in "Person" table:

CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL

[Code] ....

And there exists 500 record in Person.

I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL

SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?

View 9 Replies View Related

How To Pass Parameters To IE From SQL Reporting Server

Aug 27, 2007

I will like to configure the Report or the Report server to pass parameters to Inetrnet Explorer when creating the report.
Is there a way to do that ?

View 1 Replies View Related

SQL 7.0 Compatibility &&amp;/or Linked Server Parameters

Jan 31, 2007

Hi, don't know if anyone can help me but I desperately need to roll a report out which relies on an SQL 7.0 datasource.

Current situation - Reporting Services 2000 up and running on an SQL 2000 box - no problems getting reports to run from the native machine but I cannot get the thing to make a hop to our SQL 7.0 box.

I've created three data sources - OLEDB, Native and ODBC. For the OLEDB and Native I am getting a DATABASEPROPERTYEX error - I assume here that reporting services is polling the SQL 7.0 box with an unsupported method.

For ODBC I CANNOT get the parameters to work - I've read all sorts of stuff about using unnamed parameters, so I've put a ? in the query and I'm getting Invalid parameter number and invalid parameter number errors (I suspect it is getting a bit confused, but then so am I).

So I thought I'd try a linked server, sure enough with a fixed parameter I can get through the linked server no problem, but again I can't work out the syntax/series of events needed to use a parameter with this, I'm starting to suspect it can't be done with an Exec statement.

So - first question - is there any way around the DBPROPERTY EX problem so I can use OLEDB/SQL Client connectivity and secondly please can someone explain what exactly has to go in the parameters box when I press ... on the data source box, 'cos it's doing my head in.

Thanks

Rich

View 2 Replies View Related

SQL Server Stored Procedures, Tables And Parameters

May 19, 2004

Hi,

I was just wondering if something could be explained to me.

I have the following:

1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.

How come i need to specify data type and length in three different places? Am i doin it wrong?

Any information is greatly appreciated.

Thanks

Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..

View 1 Replies View Related

Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server

Feb 2, 2005

The syntax for Oracle's INSTR function is

instr (string1, string2, [start_position], [nth_appearance])

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

Do anybody know the solution for this ????

View 1 Replies View Related

SQL Server 2008 :: SSRS Passing Parameters In URL?

May 20, 2015

i have a url that opens my report with no entries in the parameters i then add &Search=96200 at the end of the URL and still the parameter is blank

View 2 Replies View Related







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