Update Query With Parameters

Apr 18, 2007

I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement -

strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id"

 I have the parameters defined as

cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName))

cmd.Parameters.Add(New SqlParameter("@id", _Id))

 _FirstName & _Id are the private variables whose values a set thru set and get methods.  I expect _FirstName to have the new value I am keying in the form.

Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values.

Please help.

Thanks,
Shaly

View 23 Replies


ADVERTISEMENT

How To Write A SQL Update Query Using Parameters?

Mar 23, 2008

Hi, could someone explain to me with sample code how to write a sql query using parameters to update a record, as I am new to this.

thanks

View 4 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

Update Parameters

Jun 6, 2006

My problem is that I can' t update my gridview. I am stuck at this problem for several days now and have no clue what the sollution could be. Hope somebody can give me some ideas of what I am doing wrong.
<asp:GridView ID="GridView_phl_berichten" runat="server" AutoGenerateColumns="False" BorderColor="DimGray" BorderStyle="Solid" BorderWidth="1px" DataKeyNames="phlb_id" DataSourceID="SqlDataSource_phl_berichten" GridLines="Horizontal" Width="716px">
<Columns>
<asp:TemplateField HeaderText="Van datum" SortExpression="phlb_van">
<EditItemTemplate>
&nbsp;<asp:Calendar ID="phlb_van" runat="server" SelectedDate='<%# eval("phlb_van") %>'
VisibleDate='<%# eval("phlb_van") %>'></asp:Calendar>
</EditItemTemplate>
<ItemStyle VerticalAlign="Top" Width="5cm" />
<ItemTemplate>
<asp:Label ID="Label_datum_van" runat="server" Text='<%# eval("phlb_van", "{0:d}") %>'></asp:Label>
<br />
<br />
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit"
Text="Edit"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"
Text="Delete" OnClientClick=" return confirm('Bent u zeker dat u dit bericht wil verwijderen ?') "></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Tot datum" SortExpression="phlb_tot">
<EditItemTemplate>
&nbsp;<asp:Calendar ID="phlb_tot" runat="server" SelectedDate='<%# eval("phlb_tot") %>'
VisibleDate='<%# eval("phlb_tot") %>'></asp:Calendar>
</EditItemTemplate>
<ItemStyle VerticalAlign="Top" Width="4cm" />
<ItemTemplate>
<asp:Label ID="Label_datum_tot" runat="server" Text='<%# eval("phlb_tot", "{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Bericht" SortExpression="phlb_bericht">
<EditItemTemplate>
&nbsp;<asp:TextBox ID="phlb_bericht" runat="server" Height="98px" Text='<%# eval("phlb_bericht") %>'
TextMode="MultiLine" Width="527px"></asp:TextBox><br />
<br />
&nbsp; &nbsp;&nbsp;
<asp:LinkButton ID="LinkButtonUpdate" runat="server" CausesValidation="False" CommandName="Update"
Font-Size="Small" OnClientClick="return confirm('Bent u zeker dat u dit bericht wil opslaan ?')"
Text="Update"></asp:LinkButton>
&nbsp; &nbsp; &nbsp; &nbsp;
<asp:LinkButton ID="LinkButtonCancel" runat="server" CausesValidation="False" CommandName="Cancel"
Font-Size="Small" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<ItemTemplate>
&nbsp;<asp:TextBox ID="TextBox_bericht" runat="server" Height="98px" ReadOnly="True"
Text='<%# eval("phlb_bericht") %>' TextMode="MultiLine" Width="527px"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="phlb_id" SortExpression="phlb_id" Visible="False">
<EditItemTemplate>
<asp:Label ID="phlb_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label_bericht_id" runat="server" Text='<%# Bind("phlb_id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#F3F3F3" />
</asp:GridView>
and here is my sqldatasource-object :
<asp:SqlDataSource ID="SqlDataSource_phl_berichten" runat="server" ConnectionString="<%$ ConnectionStrings:ConEP %>"
SelectCommand="sp_berichten_PHL_alle" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE T_bericht_PHL set&#13;&#10;phlb_bericht = @phlb_bericht &#13;&#10;where phlb_id = @phlb_id">
<UpdateParameters>
<asp:Parameter Name="phlb_bericht" Type="String" />
<asp:Parameter Name="phlb_id" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
It always stay giving the message that you can ' t insert null values in the column " bericht " ... ; All my variables have the same names as the columns in my table. So it is finding the id , but it doesn' t seem to find the value of my edititemtemplatefield "bericht". Hope somebody can give some suggestions on what I can try to do else.
 

View 3 Replies View Related

Sql Update With Command.Parameters

Oct 10, 2006

 Im looking for example code to make a sql update... I want to use command.Parameters and variables from text boxes and i'm unsure how to do this...  Please help. This code below doesn't work but it is an example of what i've been working with..  <code>     {               string conn = string.Empty;            ConnectionStringsSection connectionStringsSection = WebConfigurationManager.GetSection("connectionStrings") as ConnectionStringsSection;            if (connectionStringsSection != null)            {                ConnectionStringSettingsCollection connectStrings = connectionStringsSection.ConnectionStrings;                ConnectionStringSettings connString = connectStrings["whowantsConnectionString"];                conn = connString.ConnectionString;                using (SqlConnection connection = new SqlConnection(conn))                using (SqlCommand command = new SqlCommand("UPDATE users SET currentscore=5)", connection))                {                    updateCommand.Parameters.Add("@currentscore", SQLServerDbType.numeric, 18, "currentscore");                    connection.Open();                    command.ExecuteNonQuery();                    connection.Close();                }            }        }</code>   

View 3 Replies View Related

Update Parameters Syntax

May 12, 2008

 Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating        Dim quantity As Integer        Dim weight As Integer        Dim packed As Integer        quantity = CInt(bagsOnPallet.Text)        weight = CInt(lbsInBags.SelectedValue)        packed = weight * quantity        e.Command.Parameters("@packedLbs").Value += packed        e.Command.Parameters("@AvailableLbs").Value -= packed    End Sub    

View 4 Replies View Related

Oledb Update With Parameters

Dec 11, 2007

hi, please someone can help me. I get error 0x80040E14L // The command contained one or more errors. I think that the error is in the sql update command.
this is my code:


HRESULT hr = NOERROR;

IDBCreateCommand * pIDBCrtCmd = NULL;

ICommandText * pICmdText = NULL;

IRowset * pIRowset = NULL;

ICommandPrepare * pICmdPrepare = NULL;

ICommandWithParameters * pICmdWParams = NULL;

ULONG ulNumCols;

IColumnsInfo * pIColumnsInfo = NULL;

LONG lNumCols;

IAccessor * pIAccessor = NULL;

ULONG cParams;

DBPARAMINFO * rgParamInfo = NULL;

OLECHAR * pNamesBuffer = NULL;

ULONG ulNumRowsRetrieved;

HROW hRows[5];

HROW * pRows = &hRows[0];

BYTE * pData = NULL;

DBCOLUMNINFO * pDBColumnInfo = NULL;

WCHAR * pStringsBuffer = NULL;

DBBINDING * prgBinding = NULL;

DBBINDING rgBindings[2];

ULONG cbRowSize;

DBPARAMS params;

HACCESSOR hAccessor;


hr = m_pIDBCreateSession->CreateSession(NULL,IID_IDBCreateCommand,(IUnknown**)&pIDBCrtCmd);

if (FAILED(hr))


goto Exit;

//IID_ICommandWithParameters

hr = pIDBCrtCmd->CreateCommand(NULL,IID_ICommandWithParameters, (IUnknown**)&pICmdWParams);

if (FAILED(hr))


goto Exit;

hr = pICmdWParams->QueryInterface( IID_ICommandText,(void**)&pICmdText);

if (FAILED(hr))


goto Exit;

hr = pICmdWParams->QueryInterface( IID_ICommandPrepare,(void**)&pICmdPrepare);

if (FAILED(hr))


goto Exit;

LPCTSTR wSQLString = OLESTR("UPDATE Pendientes SET Pendiente = ? WHERE IDAnimal = ?");

hr = pICmdText->SetCommandText( DBGUID_DBSQL,wSQLString);

if (FAILED(hr))


goto Exit;

pICmdPrepare->Prepare(1);

if (FAILED(hr))


goto Exit;

pICmdWParams->GetParameterInfo(&cParams, &rgParamInfo, &pNamesBuffer);

hr = pICmdText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);

if (FAILED(hr))


goto Exit;

rgBindings[0].iOrdinal = 1;

rgBindings[0].obStatus = 0;

rgBindings[0].obLength = rgBindings[0].obStatus + sizeof(DBSTATUS);

rgBindings[0].obValue = rgBindings[0].obLength + sizeof(ULONG);

rgBindings[0].pTypeInfo = NULL;

rgBindings[0].pObject = NULL;

rgBindings[0].pBindExt = NULL;

rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;

rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;

rgBindings[0].eParamIO = DBPARAMIO_INPUT;

rgBindings[0].cbMaxLen = sizeof(int);

rgBindings[0].dwFlags = 0;

rgBindings[0].wType = DBTYPE_I4;

rgBindings[0].bPrecision = 0;

rgBindings[0].bScale = 0;


rgBindings[1].iOrdinal = 2;

rgBindings[1].obStatus = rgBindings[0].obValue + rgBindings[0].cbMaxLen;;

rgBindings[1].obLength = rgBindings[1].obStatus + sizeof(DBSTATUS);

rgBindings[1].obValue = rgBindings[1].obLength + sizeof(ULONG);

rgBindings[1].pTypeInfo = NULL;

rgBindings[1].pObject = NULL;

rgBindings[1].pBindExt = NULL;

rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;

rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;

rgBindings[1].eParamIO = DBPARAMIO_INPUT;

rgBindings[1].cbMaxLen = 8;

rgBindings[1].dwFlags = 0;

rgBindings[1].wType = DBTYPE_I8;

rgBindings[1].bPrecision = 0;

rgBindings[1].bScale = 0;


cbRowSize = rgBindings[1].obValue + rgBindings[1].cbMaxLen;


hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,2,rgBindings,cbRowSize,&hAccessor,NULL);

if (FAILED(hr))


goto Exit;

pData = (BYTE*) malloc(cbRowSize);
if(!(pData))

{


hr = E_OUTOFMEMORY;

goto Exit;

}

memset(pData,0,cbRowSize);


*(DBSTATUS*)(pData + rgBindings[0].obStatus) = DBSTATUS_S_OK;

*(int*)(pData + rgBindings[0].obValue) = 9;

*(ULONG*)(pData + rgBindings[0].obLength) = 8;


*(DBSTATUS*)(pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;

*(int*)(pData + rgBindings[1].obValue) = 0;

*(ULONG*)(pData + rgBindings[1].obLength) = 4;


params.pData = pData;

params.cParamSets = 1;

params.hAccessor = hAccessor;


hr = pICmdText->Execute(NULL, IID_NULL,&params,&lNumCols,NULL);

if (FAILED(hr))


goto Exit;

Exit:...

View 1 Replies View Related

How Do You Use SQL Parameters To UPDATE A Column?

Sep 27, 2006

Hi all,

I have a problem here where I am trying to use SQL Parameters to update a column in the database, but the problem is that I need to concatenate the same column to the SQL parameter. The code I have is below, but it throws a Format Exception...

UPDATE tbl_NSP_Inspection SET Description = Description + @InspectionDescription

...It is because I am trying to Append teh data in the description column to the SQL Parameter (
@InspectionDescription). How do I actually do this using SQL Parameters?

Thanks

View 3 Replies View Related

SQLDataSource Update Using Parameters Not Working

Jul 10, 2006

I'm passing a parameter to a stored procedure stored on my sqlserver, or trying to atleast.  And then firing off the update command that contains that parameter from a button.  But it's not changing my data on my server when I do so.
I'm filling a dropdown list from a stored procedure and I have a little loop run another sp that grabs what the selected value should be in the dropdown list when the page loads/refreshes.  All this works fine, just not sp that should update my data when I hit the submit button.
It's supposed to update one of my tables to whatever the selected value is from my drop down list.  But it doesn't even change anything.  It just refreshes the page and goes back to the original value for my drop down list.
Just to make sure that it's my update command that's failing, I've even changed the back end data manually to a different value and on page load it shows the proper selected item that I changed the data to, etc.  It just won't change the data from the page when I try to.
 
This is what the stored procedure looks like:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER PROCEDURE [dbo].[UPDATE_sp] (@SelectedID int) AS
BEGIN
UPDATE [Current_tbl]
SET ID = @SelectedID
WHERE PrimID = '1'
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my aspx page:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Editor.aspx.vb" Inherits="Editor" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Data Verification Editor</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SQLDS_Fill" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Current_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataSet">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SQLDS_Update" runat="server" ConnectionString="<%$ ConnectionStrings:Test %>"
SelectCommand="Validation_sp" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"
UpdateCommand="UPDATE_sp" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:ControlParameter Name="SelectedID" ControlID="Ver_ddl" PropertyName="SelectedValue" Type="Int16" />
</UpdateParameters>
</asp:SqlDataSource>
<table style="width:320px; background-color:menu; border-right:menu thin ridge; border-top:menu thin ridge; border-left:menu thin ridge; border-bottom:menu thin ridge; left:3px; position:absolute; top:3px;">
<tr>
<td colspan="2" style="font-family:Tahoma; font-size:10pt;">
Please select one of the following:<br />
</td>
</tr>
<tr>
<td colspan="2">
<asp:DropDownList ID="Ver_ddl" runat="server" DataSourceID="SQLDS_Update" DataTextField="Title"
DataValueField="ID" style="width: 100%; height: 24px; background: gold">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width:50%;">
<asp:Button ID="Submit_btn" runat="server" Text="Submit" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
<td style="width:50%;">
<asp:Button ID="Done_btn" runat="server" Text="Done" Font-Bold="True"
Font-Size="8pt" Width="100%" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="Saved_lbl" style="font-family:Tahoma; font-size:10pt;"></asp:Label>
</td>
</tr>
</table>
</form>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
And here's my code behind:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Imports System.Data
Imports System.Data.SqlClient
Partial Class Editor
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Saved_lbl.Text = ""
Done_btn.Attributes.Add("OnClick", "window.location.href='Rpt.htm';return false;")
Dim View1 As New DataView
Dim args As New DataSourceSelectArguments
View1 = SQLDS_Fill.Select(args)
Dim Row As DataRow
For Each Row In View1.Table.Rows
Ver_ddl.SelectedValue = Row("ID")
Next Row
SQLDS_Fill.Dispose()
End Sub
Protected Sub Submit_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit_btn.Click
SQLDS_Update.Update()
Saved_lbl.Text = "Thank you. Your changes have been saved."
SQLDS_Update.Dispose()
End Sub
End Class
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Any help is much appreciated.

View 4 Replies View Related

Problem With Update Command With Parameters

Aug 2, 2007

Hi,i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.This happens in event DetailsView1_ItemUpdating with my own code.It works without parameters (i know, bad way) like this:SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"But when using parameters like here below, i get the error:"Input string was not in a correct format"Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdatingSqlDataSource1.UpdateCommand = "UPDATE mytable set name= @myname"SqlDataSource1.UpdateParameters.Add("@myname", SqlDbType.NVarChar, na)SqlDataSource1.Update()End SubI don't see what's wrong here.Thanks for helpTartuffe

View 4 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

Update Command In SQL DateSource Mixing Up My Parameters

Dec 14, 2007

I have an update command with 7 parameters, but at run time the order to the parameters gets mixed up.
I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,?
I then named each of the parameters and set their sources.  The parameters are like this (samepl name, then source, then type):
A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime
At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update.
This is how they looked (Parameter index, paramter name):
[0] A[1] B[2] D[3] E[4] F[5] G[6] C
It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same.
I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters>
No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work.
Any ideas as to why this would happen or how to fix it?
(I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)

View 2 Replies View Related

Stored Proc Update Optional Parameters

Dec 3, 2003

I have a stored procedure that updates about a dozen rows.

I have some overloaded functions that I should update different combinations of the rows - 1 function might update 3 rows, another 7 rows.

Do I have to write a stored procedure for each function or I can I handle it in the Stored Procedure. I realise I can have default values but I the default values could overwrite actual data if the values are not supplied but have been previously written.

Many thanks for any guidance.

Simon

View 5 Replies View Related

Passing Two Or More Parameters To An Update Stored Procedure From VB6 Using ADO

May 26, 1999

I am trying to pass two parameters to a stored procedure using the code below:

Dim cmd As New Command
Dim rs As New Recordset
Dim prm As New Parameter
Dim ttt As New Parameter

cmd.ActiveConnection = cnn1
cmd.CommandText = "{call usp_PlcwithinUID_upt(?,?)}"
cmd.CommandType = adCmdStoredProc

Set prm = New Parameter
prm.Type = adInteger
prm.Value = gsPlcCode
cmd.Parameters.Append prm

Set ttt = New Parameter
ttt.Type = adVarChar
ttt.Value = "TEST"
cmd.Parameters.Append ttt

'cmd(1) = gsPlcCode
'cmd(2) = "Test"

Set rs = cmd.Execute

It works when passing one but NOT two ?? Can anyone shed any light ??
I've also tried using the Create Parameter method but again one is fine but passing two parameters doesn't work ?

The stored procedure just performs an update on a table.

Basically I want to be able to update any fields in a table (specific record) when they have been changed by the user. I have placed boolean variables in the change events to detect any user edits. If any field(s) have been edited I then want to send each field within the current record as parameters to the update stored procedure.

Any clues as to how to get an update stored procedure to except multiple variables.

I have trawled through loads of documentation to no avail.

Please help.

Paul

View 1 Replies View Related

Stored Procedure Parameters Update Dynamically

Sep 22, 2014

I set up a stored procedure. My stored procedure has 8 variables declared at the beginning like this:

===========================
declare@IncurredDateStartsmalldatetime
,@IncurredDateEndsmalldatetime
,@PaidDateStart_CUsmalldatetime
,@PaidDateEnd_CUsmalldatetime
,@PaidDateStartsmalldatetime
,@PaidDateEndsmalldatetime

[Code] ....

All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.

I was wondering if these variables can be updated dynamically and if they can how it is done.

View 1 Replies View Related

Stored Procedure Update Parameters Question

Nov 2, 2006

I just posted a question on WHERE clause impotence in SELECT command, I mean in my setting. It obviously should work on a global scale and now I have a problem with UPDATE command WHERE clause.

I am talking about a different SP now. It is supposed to update a record in a table.

Anyhow, the procedure did NOT seem to execute properly (did not update the record) when called from C# code but when I tried to exec it in the server it finally did work but raised a problem of safeguarding the unused parameters.

I do not need to update all columns in every call to the SP that uses UPDATE statement. Some parameters are left the way they are. I found out that if I ignore them they are set to default values which is NULL for DateTime. It essentially wipes out the values I inserted in the previous INSERT command when the record was created. Some of them were not NULLs.

if this is not just my freak observation, I would like to know how this problem should be handled to make sure that all column values are safe. If it is impossible to make them all not being updated every time a single parameter is updated it will create some problems for me in the client code: I will have to retreive all parameters, put them in variables or an ArrayList and then update the whole record wholesale.

How is it done by the experts?

Thanks.

View 11 Replies View Related

Setting SqlDataSource Update Command And Parameters Dynamically C#

Aug 31, 2007

Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
 
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Update Query To Update Separate Chars

Mar 26, 2007

Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it.  1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';  

View 2 Replies View Related

SQL Query Parameters

Dec 19, 2006

I am trying to search a SQL database using a TextBox  and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search.  If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results.   While stepping through the code both parameters do get assigned the correct values to make the sql statement valid.  Any advice would be appreciated.
.... code that does not work .....
            string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString;            string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery";
            conn = new SqlConnection(connection);            comm = new SqlCommand(sqlquery, conn);
            String searchQuery = searchBox.Text;            String searchItem = DropDownList1.SelectedValue;
            comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar);            comm.Parameters["@SearchQuery"].Value = searchQuery;            comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar);            comm.Parameters["@SearchItem"].Value = searchItem;
......

View 1 Replies View Related

Using Parameters In SQL Query

Jul 11, 2007

Hi all,
I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause.
Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters.
This is what I am trying to do:  private DataSet GetSalesDataBas()
{
DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection("...");
SqlCommand command = connection.CreateCommand();

SqlParameter prm_1 = new SqlParameter();
SqlParameter prm_2 = new SqlParameter();
SqlParameter prm_3 = new SqlParameter();
SqlParameter prm_4 = new SqlParameter();
SqlParameter prm_5 = new SqlParameter();
SqlParameter prm_6 = new SqlParameter();
SqlParameter prm_7 = new SqlParameter();

prm_1.ParameterName = "@pNum";
prm_2.ParameterName = "@sNum";
prm_3.ParameterName = "@paramLoc";
prm_4.ParameterName = "@paramDist";
prm_5.ParameterName = "@paramPNum";
prm_6.ParameterName = "@paramDesc";
prm_7.ParameterName = "@paramBuild";

prm_1.Value = TextBox2.Text.Trim();
prm_2.Value = TextBox3.Text.Trim();
prm_3.Value = DropDownList15.SelectedValue;
prm_4.Value = DropDownList12.SelectedValue;
prm_5.Value = DropDownList21.SelectedValue;
prm_6.Value = DropDownList13.SelectedValue;
prm_7.Value = DropDownList18.SelectedValue;

command.Parameters.Add(prm_1);
command.Parameters.Add(prm_2);
command.Parameters.Add(prm_3);
command.Parameters.Add(prm_4);
command.Parameters.Add(prm_5);
command.Parameters.Add(prm_6);
command.Parameters.Add(prm_7);

string strWhere = "";

if (DropDownList23.SelectedIndex == 0)//if no sort field selected
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location = @paramLoc" +
" AND m.[District] = @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")";
}
else
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location= @paramLoc" +
" AND m.[District]= @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")" +
" ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue;
}

string sqlSalesData = @"SELECT * FROM Main m" + strWhere;
command.CommandText = sqlSalesData;
SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}    

View 7 Replies View Related

DTS Query Parameters

Mar 14, 2002

I am creating a package within which I have a query step that needs to accept
a value from a variable step that has already been run, but I cant get it to work and the books online dont help!

The query is simple

insert into table1
select col1 = cola,
col2 = colb,
col3 = ?
from tableA

Even though the variable that I want use has been set, I cant get the query to recognise there are any parameters. If I click the parameter button in the query set-up, I get a message saying that col3 is an invalid column name and then a box saying that an error occured when parsing the statement fro parameters !

Can anybody help ?
Thanks
Tom

View 1 Replies View Related

Parameters For Query

Oct 19, 2006

I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted....

I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...

ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...

Any ideas?!

Thanks!!! :)

View 9 Replies View Related

MDX Query With Parameters

Jul 25, 2007

Hi there,

I'm developing one MDX query to place in my report, but I'm having a lot of troubles when using one of my report parameters.

I hope I can explain me good enough.

I have a report parameter that's used in the WHERE clause to choose the value of one of my dimensions, the problem is that I'm also trying to compare the value of this parameter to decide the value of another dimension, but the comparison isn't working at all.

Let me show one example:

The parameter name in question is: DimFolderWalletDesciption.




Code Snippet


IIF(
@DimFolderWalletDesciption = [Dim Folder].[Wallet Desciption].&[Investimento],
[Dim Indexation].[Indexation Group].[Rendimento Variável],
[Dim Indexation].[Indexation Group].[Indexation Group].ALLMEMBERS
)


This is suppost to do the following: If my parameter as the selected value "Investimento", then the "Indexation Group" selected should be "Rendimento Fixo" if not, there shouldn't be any "filter" in the Dimension "Indexation Group".

I've tryed a lot of combinations, but none works, what am I doing wrong?

Thanks in advance.
Regards.

View 1 Replies View Related

UPDATE Query To Update One Table From Another

Sep 15, 2001

I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.

View 1 Replies View Related

Update Trigger - Update Query

Jul 20, 2005

Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

How Can I Execute SQL Query From DOS With Parameters?

Feb 8, 2007

I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.Possible?

View 4 Replies View Related

Query - Scanning Of Parameters

Mar 6, 2007

Hi all. I have a problem on my query. let me explain first the scenario.

I have a windows application project that views a report through report viewer(Report of all employees that have a religion of adventist, born again, catholic, baptist, etc....). In my windows application, I have checkboxes for a specific religion. So, if I checked baptist and catholic, report generated is all employee that has religion of baptist and catholic.

My concern is, How could i make a query of this?

sample tables.
table - employee
dcno name religion
1 jon baptist
2 rose catholic
3 joy baptist
4 mike adventist
5 soy born again
6 rich protestant

you can supply additional table if needed.

thanks
-Ron-

View 7 Replies View Related

LOOKUP: Specifying Parameters For The SQL Query

Jul 18, 2006

guys i'm trying to use a Lookup in a dataflow that looksup stuff in the results of a query.

Problem I have is that the query needs to take two parameters.. (Source
and BaseCurrency in the code below) and i can't figure out how to
supply the parameters..

Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...



PJ



SELECT ForeignCurrency, RateFromFile AS YesterdaysRate

FROM inputrates IR

WHERE fileheaderid in (

SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)

View 10 Replies View Related

Getting ORA-01036 When Using Query Parameters

Dec 13, 2007

I'm adding parameters to an already working query for a standard report I'm building. The object I am connecting to is an Oracle object (I have SP2 installed in all the appropriate places). I'm changing two date comparisons from hard coded date values to two query parameters. Based upon what MS's Step by Step book on Reporting Services is telling me, I have my query parameters correctly pointing to my Parameters collection. Thing is, when I click the preview button, I get subject error message -- "illegal variable name/number". Here's my query:

SELECT S.COVER_DATE, S.AD_NBR, S.APP_NBR, S.PRODUCT, S.EDITION, S."SECTION", S.PROGRAM_RATE, S.NET_AMT, S.ACCOUNT_ID,
ACT.SALES_ID
FROM MDTI.SCHEDULE S INNER JOIN
MDTI.ACCTSALES ACT ON S.ACCOUNT_ID = ACT.ACCOUNT_ID AND S.PRODUCT = ACT.PRODUCT
WHERE (S.COVER_DATE BETWEEN "@StartCoverDate" AND "@EndCoverDate") AND



(ACT.EXPIRE_DATE > '30-NOV-07') AND
(ACT.EFFECTIVE_DATE < '01-DEC-07')

The query parm names start with "@" in the WHERE clause. Nothing to it, right?

Any ideas why I am getting this error? SBS is telling me that I must select the Oracle data provider instead of the generic ODBC data provider in the Data Link Properties of my data source. I can't get anyone to show me where the Data Link properties are.

I'm looking for a solution to this error.

Respectfully Submitted,

Dave Matthews
Atlanta, GA
aka FlooseMan Dave

View 5 Replies View Related

Query Parameters Vs.Filters

Mar 23, 2008

I am developing a summary report that will have multiple tables, charts and matrixes, all using the same set of data. However, one table may only show one month of data while another will show three months. If they all use the same Dataset with the same data parameters, is the data only pulled once? And then each component can use Filters to further refine the data? If this is true, this would seem to be the best option.

Or does each report component execute the query independantly?

Before I get too far down the road developing this report, I'd like to know the best way to do it from the beginning.

Thank you for your input.

Rob

View 3 Replies View Related

Using Two Parameters To Query The Same Column ID

Mar 17, 2008

I am trying to build a report that has two parameters that are entered by the user. They will be a simple string that is basically a six digit unique identifier number called DEALID. I have my report set so that I can enter one of these Deal ID's and it will pull in all of the fields I need. However, within the same report I want to have the user be able to enter a second Deal ID and have it pull in effectively the same values, yet associated with that new ID.
The problem seems to be that I am trying to have multiple parameters querying the same field in the database. Is this something that can be done? I have tried naming them all uniquely with a new alias and that does not seem to help.

Thank you

View 16 Replies View Related

Parameters In Sql Task Sub Query

Oct 9, 2006

DELETE T1
WHERE EXISTS
(SELECT *
FROM T2 A
WHERE A.C1= T1.C1
AND A.C3 >= ?)


Results in below error (OLEDB SQL TASK):

" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any resolution?

View 9 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related







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