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


ADVERTISEMENT

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

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

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

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

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

Parameters.AddWithValue Vs. Parameters.Add

Jan 2, 2008

Hello all,
Given: 
string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure;
   Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over  myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder]  
It seems that ADO.NET makes an implicit conversion to the valid type.  If this is correct then I can move on fat dumb and happy.  Anyone have any good insight?
Thanks,
 

View 11 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View 1 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Stored Proc Won't Update From C# .NET Code, But Will Update When Testing On Its Own.

Jul 23, 2006

I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
   SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
 The Stored Procedure:
  ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)

/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId

IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId

IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN 

View 2 Replies View Related

Update Function: Why SQL Server Update An Empty String With 0?

May 13, 2008

I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.

View 23 Replies View Related

Plz Help...update Value In Multiple Db Table Using Single 'update Command'

Mar 18, 2005

hi,friends

we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------

i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.

i want to update value in multiple database table(more than one) using single 'update command'

i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.

it's urgent.

thanks in advance.

View 2 Replies View Related

Seperate Date And Time Merge; Done At SQL Update Or C# .NET Then Update

Feb 18, 2006

I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar

View 1 Replies View Related







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