~TableAdapter.Update Not Updating The Database

Apr 28, 2006

I am designing my first database (Visual Basic & SQL Server Express 2005) and it appears as if the database isn't updating. I am running it by hitting F5, closing by hitting the "X" and then hitting F5 again to check if the changes have stuck. They don't. These are the instructions given in the tutorial.

Any ideas? Thanks.

In particular I have tried two step-by-step tutorials distributed by Microsoft: 1. Absolute Beginner's Video Series, Lesson09; and 2. the Help tutorial: Managing Your Records (ms-help://MS.VSExpressCC.v80/MS.NETFramework.v20.en/dv_vbcnexpress/html/1ccdb8f1-4162-4a54-af17-231007eb529b.htm)

The code for the form is:

Public Class Form1

Private Sub AddressesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddressesBindingNavigatorSaveItem.Click

Me.Validate()

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'FirstDatabaseDataSet.Addresses' table. You can move, or remove it, as needed.

Me.AddressesTableAdapter.Fill(Me.FirstDatabaseDataSet.Addresses)

End Sub
End Class

View 3 Replies

TableAdapter.Update (Data Not Being Writen To The Database)

May 30, 2006

Problem
I downloaded the VB Lesson 9 Visual Basic Project from Microsoft Learning Resources. With out making any changes to the project I compiled and ran it, making changes to the data and saving the changes. Every thing look great until you run it a second time and see that the changes to the data are all lost.

Observations:
1. Changes made to the data from within Visual Studio (Show Table Data) do take effect.

2. The in-memory data (DataSet) is changed as you move from row to row.

3. The TableAdapter.Update call returns 1, ostensibly indicating the number of rows updated.

4. The data in the database is not changed by the TableAdapter.Update call.

Operating System: Windows XP Pro Service Pack 2
Other Software installed on this system
20SQL Server 2005 Express
Visual Web Developer 2005 Express
Visual Studio 6.0 Enterprise
Visual Studio .net Enterprise
Microsoft .NET Framework SDK
Microsoft Office 2003

If anyone can help me understand the cause of this problem I would greatly appreciate it.

Thanks
Bob

View 1 Replies View Related

Tableadapter.update Problem

Feb 26, 2008

If this is the wrong forum, please let me know.

Using .NET Compact Framework, VB.NET, SQL Server CE 3.0

I use tableadapter.update in many places throughout my application. The user can save a record by click on a menu item called Save.

I call this code:

Private Sub Save

bindingsource.endedit
tableadapter.update(me.dataset.datatable)

End Sub

In most places the code works fine. It saves updates to the table. However, in two tables it is not working. It does not throw any exceptions, but does not save the record.

The subs that work and the ones that dont are coded exactly the same.

Thanks for any help

Ryan

View 1 Replies View Related

Problem With Update When Updating All Rows Of A Table Through Dataset And Saving Back To Database

Feb 24, 2006

Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType = CommandType.Text;
snUpdate.CommandText = "Update TestTable set shipdate = @shipdate";
snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds, "Table1");
conn.Close();

-Thanks

View 4 Replies View Related

UPDATE Not Updating

May 23, 2005

Hello all,

I've been working on a stored procedure that updates values I have
stored in a table on a SQL Server 2000 db and I'm not sure what kind of
problem I'm having. I pass the stored procedure the required
parameters, run it, and everything goes fine until I check the db and
no updates have been done. I've gone back and checked the return
value of command.ExecuteNonQuery and it's '0' so it's not indicating
that anything went wrong. I've tried updating values that are
already present values that were previously 'Null' and I get the same
result everytime, no update. Any suggestions? Below is my
stored procedure

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER        PROCEDURE dbo.UpdateApplication

    (
@1 varchar(9),
@2 varchar(50),
@2_DBA varchar(50),
@3 varchar(25),
@3_other varchar(150),
@4_street1 varchar(100),
@4_state varchar(2),
@4_street2 varchar(100),
@4_zip varchar(10),
@4_city varchar(50),
@5_street1 varchar(100),
@5_state varchar(2),
@5_street2 varchar(100),
@5_zip varchar(10),
@5_city varchar(50),
@6 bit,
@6_web varchar(50),
@7 varchar(15),
@7_percent smallint,
@8 varchar(30),
@8_other varchar(100),
@9 varchar(50),
@9_street1 varchar(100),
@9_state varchar(2),
@9_street2 varchar(100),
@9_zip varchar(10),
@9_city varchar(50),
@10 varchar(50),
@10_interest varchar(50),
@10_street1 varchar(100),
@10_state varchar(2),
@10_street2 varchar(100),
@10_zip varchar(10),
@10_city varchar(50),
@11 varchar(50),
@11_interest varchar(50),
@11_street1 varchar(100),
@11_state varchar(2),
@11_street2 varchar(100),
@11_zip varchar(10),
@11_city varchar(50),
@12_Name varchar(50),
@12_Number varchar(15),
@13_Name varchar(50),
@13_Number varchar(15),
@14_years smallint,
@14_months smallint,
@14_man_years smallint,
@14_man_months smallint,
@15_years smallint,
@15_months smallint,
@15_stories smallint,
@16 bit,
@17 bit,
@18 bit,
@19_company varchar(50),
@19_premium money,
@20 bit,
@20_explain text,
@21_sqft int,
@21_apl_sqft int,
@21_apt_sqft int,
@21_units int,
@21_lro_sqft int,
@22_mon_thurs_o varchar(7),
@22_mon_thurs_c varchar(7),
@22_fri_o varchar(7),
@22_fri_c varchar(7),
@22_sat_o varchar(7),
@22_sat_c varchar(7),
@22_sun_o varchar(7),
@22_sun_c varchar(7),
@23 bit,
@23a_date varchar(10),
@23a_type varchar(50),
@23a_paid money,
@23a_reserved money,
@23a_open bit,
@23b_date varchar(10),
@23b_type varchar(50),
@23b_paid money,
@23b_reserved money,
@23b_open bit,
@23c_date varchar(10),
@23c_type varchar(50),
@23c_paid money,
@23c_reserved money,
@23c_open bit,
@23d_date varchar(10),
@23d_type varchar(50),
@23d_paid money,
@23d_reserved money,
@23d_open bit,
@24 bit,
@25 bit,
@26 bit,
@27 bit,
@28 bit,
@29 bit,
@30 bit,
@31 bit,
@32 bit,
@33 bit,
@34 bit,
@35 bit,
@36 bit,
@37 bit,
@38 bit,
@39 bit,
@39_explain text,
@40 bit,
@40_num smallint,
@40_types varchar(50),
@41 varchar(100),
@42a bit,
@42a_num smallint,
@42a_types varchar(100),
@42a_members smallint,
@42a_others varchar(50),
@42b bit,
@42c_sqft smallint,
@42c_week smallint,
@42d bit,
@42e bit,
@42f bit,
@42f_explain varchar(50),
@42g bit,
@42g_explain varchar(50),
@43a bit,
@43b bit,
@43c bit,
@43d bit,
@43e bit,
@43f bit,
@44 bit,
@44a bit,
@44a_states varchar(150),
@44b smallint,
@44c smallint,
@44d varchar(25),
@44d_explain varchar(50),
@45_food_p money,
@45_food_n money,
@45_alch_p money,
@45_alch_n money,
@45_alch_tr_p money,
@45_alch_tr_n money,
@45_cc_p money,
@45_cc_n money,
@45_opc_p money,
@45_opc_n money,
@45_other varchar(50),
@45_other_p money,
@45_other_n money,
@45_total_p money,
@45_total_n money,
@46a_am smallint,
@46a_am_three smallint,
@46b_ff money,
@46b_mf money,
@46c varchar(50),
@47a bit,
@47b bit,
@47c bit,
@47d smallint,
@48_gen money,
@48_fire money,
@48_prod money,
@48_med money,
@48_eac money,
@48_pers money,
@49 bit,
@49_months varchar(100),
@50 bit,
@50_when varchar(50),
@51 bit,
@51_when varchar(50),
@52 bit,
@53 bit,
@54 bit,
@55 varchar(10),
@56_roof smallint,
@56_elec smallint,
@56_plum smallint,
@56_heat smallint,
@57 bit,
@58 bit,
@58_percent smallint,
@59 varchar(20),
@60 varchar(45),
@61 bit,
@61a varchar(100),
@61a_other varchar(50),
@61a_veg bit,
@61b bit,
@61c bit,
@61c_clean varchar(50),
@61c_date varchar(10),
@61d varchar(3),
@62a varchar(6),
@62b varchar(4),
@62c_value varchar(20),
@62c money,
@62d_build money,
@62d_coins smallint,
@62e_con money,
@62e_coins smallint,
@62f_bus money,
@62f_coins smallint,
@62f varchar(3),
@62g varchar(50),
@63a_ins money,
@63a_out money,
@63b_ins money,
@63b_out money,
@63c money,
@63d smallint,
@63e bit,
@Record int
)

AS

UPDATE Application

SET
[1]=@1 ,
[2]=@2 ,
[2_DBA]=@2_DBA ,
[3]=@3 ,
[3_other]=@3_other ,
[4_street1]=@4_street1 ,
[4_street2]=@4_street2 ,
[4_city]=@4_city ,
[4_state]=@4_state ,
[4_zip]=@4_zip ,
[5_street1]=@5_street1 ,
[5_street2]=@5_street2 ,
[5_city]=@5_city ,
[5_state]=@5_state ,
[5_zip]=@5_zip ,
=@6 ,
[6_web]=@6_web ,
[7]=@7 ,
[7_percent]=@7_percent ,
=@8,
[8_other]=@8_other ,
[9]=@9 ,
[9_street1]=@9_street1 ,
[9_street2]=@9_street2 ,
[9_city]=@9_city ,
[9_state]=@9_state ,
[9_zip]=@9_zip ,
[10]=@10 ,
[10_interest]=@10_interest ,
[10_street1]=@10_street1 ,
[10_street2]=@10_street2 ,
[10_city]=@10_city ,
[10_state]=@10_state ,
[10_zip]=@10_zip ,
[11]=@11 ,
[11_interest]=@11_interest ,
[11_street1]=@11_street1 ,
[11_street2]=@11_street2 ,
[11_city]=@11_city , [11_state]=@11_state , [11_zip]=@11_zip , [12_Name]=@12_Name , [12_Number]=@12_Number ,
[13_Name]=@13_Name , [13_Number]=@13_Number , [14_years]=@14_years , [14_months]=@14_months , [14_man_years]=@14_man_years ,
[14_man_months]=@14_man_months , [15_years]=@15_years , [15_months]=@15_months , [15_stories]=@15_stories ,
[16]=@16 , [17]=@17 , [18]=@18 , [19_company]=@19_company , [19_premium]=@19_premium , [20]=@20 , [21_sqft]=@21_sqft ,
[21_apl_sqft]=@21_apl_sqft , [21_apt_sqft]=@21_apt_sqft , [21_units]=@21_units , [21_lro_sqft]=@21_lro_sqft ,
[22_mon_thurs_o]=@22_mon_thurs_o , [22_fri_o]=@22_fri_o , [22_sat_o]=@22_sat_o , [22_sun_o]=@22_sun_o ,
[22_mon_thurs_c]=@22_mon_thurs_c , [22_fri_c]=@22_fri_c , [22_sat_c]=@22_sat_c , [22_sun_c]=@22_sun_c ,
[23]=@23 , [23a_date]=@23a_date , [23a_type]=@23a_type , [23a_paid]=@23a_paid , [23a_reserved]=@23a_reserved ,
[23a_open]=@23a_open , [23b_date]=@23b_date , [23b_type]=@23b_type , [23b_paid]=@23b_paid , [23b_reserved]=@23b_reserved ,
[23b_open]=@23b_open , [23c_date]=@23c_date , [23c_type]=@23c_type , [23c_paid]=@23c_paid , [23c_reserved]=@23c_reserved ,
[23c_open]=@23c_open , [23d_date]=@23d_date , [23d_type]=@23d_type , [23d_paid]=@23d_paid , [23d_reserved]=@23d_reserved ,
[23d_open]=@23d_open , [24]=@24 , [25]=@25 , [26]=@26 , [27]=@27 , [28]=@28 , [29]=@29 , [30]=@30 , [31]=@31 ,
[32]=@32 , [33]=@33 , [34]=@34 , [35]=@35 , [36]=@36 , [37]=@37 , [38]=@38 , [39]=@39 , [39_explain]=@39_explain ,
[40]=@40 , [40_num]=@40_num , [40_types]=@40_types , [41]=@41 , [42a]=@42a , [42a_num]=@42a_num , [42a_types]=@42a_types ,
[42a_others]=@42a_others , [42a_members]=@42a_members , [42b]=@42b , [42c_sqft]=@42c_sqft , [42c_week]=@42c_week ,
[42d]=@42d , [42e]=@42e , [42f]=@42f , [42f_explain]=@42f_explain , [42g]=@42g , [42g_explain]=@42g_explain ,
[43a]=@43a , [43b]=@43b , [43c]=@43c , [43d]=@43d , [43e]=@43e , [43f]=@43f , [44]=@44 , [44a]=@44a ,
[44a_states]=@44a_states , [44b]=@44b , [44c]=@44c , [44d]=@44d , [44d_explain]=@44d_explain , [45_food_p]=@45_food_p ,
[45_food_n]=@45_food_n , [45_alch_p]=@45_alch_p , [45_alch_n]=@45_alch_n , [45_alch_tr_p]=@45_alch_tr_p ,
[45_alch_tr_n]=@45_alch_tr_n , [45_cc_p]=@45_cc_p , [45_cc_n]=@45_cc_n ,[45_opc_p]=@45_opc_p ,
[45_opc_n]=@45_opc_n , [45_other]=@45_other , [45_other_p]=@45_other_p , [45_other_n]=@45_other_n ,
[45_total_p]=@45_total_p , [45_total_n]=@45_total_n , [46a_am]=@46a_am , [46a_am_three]=@46a_am_three ,
[46b_ff]=@46b_ff , [46b_mf]=@46b_mf , [46c]=@46c , [47a]=@47a , [47b]=@47b , [47c]=@47c , [47d]=@47d , [48_gen]=@48_gen ,
[48_fire]=@48_fire , [48_prod]=@48_prod , [48_med]=@48_med , [48_eac]=@48_eac , [48_pers]=@48_pers , [49]=@49 ,
[49_months]=@49_months , [50]=@50 , [50_when]=@50_when , [51]=@51 , [51_when]=@51_when , [52]=@52 , [53]=@53 ,
[54]=@54 , [55]=@55 , [56_roof]=@56_roof , [56_elec]=@56_elec , [56_plum]=@56_plum , [56_heat]=@56_heat ,
[57]=@57 , [58]=@58 , [58_percent]=@58_percent , [59]=@59 , [60]=@60 , [61]=@61 , [61a]=@61a , [61a_other]=@61a_other ,
[61a_veg]=@61a_veg , [61b]=@61b , [61c]=@61c , [61c_clean]=@61c_clean , [61c_date]=@61c_date ,
[61d]=@61d , [62a]=@62a , [62b]=@62b , [62c]=@62c , [62c_value]=@62c_value , [62d_build]=@62d_build ,
[62d_coins]=@62d_coins , [62e_con]=@62e_con , [62e_coins]=@62e_coins , [62f]=@62f , [62f_bus]=@62f_bus ,
[62f_coins]=@62f_coins , [62g]=@62g , [63a_ins]=@63a_ins , [63a_out]=@63a_out , [63b_ins]=@63b_ins ,
[63b_out]=@63b_out , [63c]=@63c , [63d]=@63d , [63e]=@63e , [20_explain]=@20_explain

WHERE [Record] = @Record

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 11 Replies View Related

Update Not Updating

Dec 27, 2005

Basically I'm trying to use these new 2.0 features but not having success although the thing doesn't break.
The command .Update() appears to work but nothing is changed.
The code elements are below:
<code>

Sub Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs)Try      SpecificJOB.Update()Catch except As Exception      Label1.Text = except.ToStringEnd Try
      Label1.Text = "The record was updated successfully!"End Sub

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SpecificJOB"OnItemUpdating="Update" >
<Fields>
<asp:CommandField ButtonType="Button" ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
<asp:TemplateField HeaderText="Job Details">

<EditItemTemplate>   JobID:<asp:Label ID="lbJobID" runat="server" Text='<%# Eval("JobID") %>'></asp:Label>
   Title:<asp:TextBox ID="txtTitle" runat="server" Text='<%# Bind("Title") %>' ></asp:TextBox><br />
   Type:<asp:DropDownList ID="ddTYPE" runat="server"                DataSourceID="JobTYPE" DataTextField="description"               DataValueField="jobTypeID" SelectedValue='<%# Bind("jobTypeID") %>'> </asp:DropDownList></EditItemTemplate>

</asp:TemplateField>
</Fields>
</asp:DetailsView>



<asp:SqlDataSource ID="SpecificJOB" runat="server" ConnectionString="<%$ ConnectionStrings:DBConn %>" ProviderName="<%$ ConnectionStrings:DBConn.ProviderName %>"
SelectCommand="SELECT JobID, jobTypeID, .................WHERE (JobID = ?)"UpdateCommand="UPDATE [Jobs] SET [jobTypeID] = ?, [Title] = ? WHERE ([JobID] = ?)">

<SelectParameters><asp:ControlParameter ControlID="GridView1" DefaultValue="0" Name="?" PropertyName="SelectedValue" /></SelectParameters>

<UpdateParameters><asp:ControlParameter Name="ddTYPE" ControlId="DetailsView1" PropertyName="SelectedValue"/><asp:ControlParameter Name="txtTitle" ControlId="DetailsView1"/><asp:ControlParameter Name="lbJobID" ControlId="DetailsView1" /></UpdateParameters>

</asp:SqlDataSource>
</code>
I think a couple of days is easily long enough to try to figure out this problem. Would love some assistance if it is obvious. Thanks!

Geoff

View 1 Replies View Related

SQL Update Not Updating

Mar 21, 2008

Hi, I have a problem, it is that when I try to update a record in my SQL server database, it is not updated and I recieve no error messages. This is the code behind the update button. The stored procedure is "sqlupdate".

Code Snippet

Dim ListingID As String = Request.QueryString("id").ToString
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand("sqlupdate", con)
cmd.CommandType = CommandType.StoredProcedure
Dim id As SqlParameter = cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier)
id.Direction = ParameterDirection.Input
id.Value = ListingID

Dim PlaceName As SqlParameter = cmd.Parameters.Add("@PlaceName", SqlDbType.VarChar)
PlaceName.Direction = ParameterDirection.Input
PlaceName.Value = PlaceNameTB.Text

Dim Location As SqlParameter = cmd.Parameters.Add("@Location", SqlDbType.VarChar)
Location.Direction = ParameterDirection.Input
Location.Value = LocationTB.Text

Dim PropertyType As SqlParameter = cmd.Parameters.Add("@PropertyType", SqlDbType.VarChar)
PropertyType.Direction = ParameterDirection.Input
PropertyType.Value = PropertyTypeTB.Text

Dim Description As SqlParameter = cmd.Parameters.Add("@Description", SqlDbType.VarChar)
Description.Direction = ParameterDirection.Input
Description.Value = DescriptionTB.Text

Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception

End Try

View 5 Replies View Related

Update Statement Not Updating

Jun 26, 2006

I can't get my form to update my db. Is there something wrong with the update statement? I'm not getting any errors either. I have two check Box Lists:
Dim strStep1 As String = ""Dim s As ListItemFor Each s In cblStep1.ItemsIf s.Selected ThenstrStep1 += s.Text + ", "End IfNext
Dim strStep2 As String = ""Dim s2 As ListItemFor Each s2 In cblStep2.ItemsIf s2.Selected ThenstrStep2 += s2.Text + ", "End IfNext
Dim ConnString As String = ConfigurationManager.AppSettings("ConnectionString")Dim Conn As New SqlConnection(ConnString)Dim CommandString As String = "UPDATE [tblAccess] SET [complete1] = ?, [complete2] = ?" & _"WHERE empName = @empName"
Dim Command As New SqlCommand(ConnString, Conn)Command.Parameters.AddWithValue("complete1", strStep1)Command.Parameters.AddWithValue("complete2", strStep2)
Any help is appreciated!

View 9 Replies View Related

Update Command Is Updating All My Records!

Feb 16, 2007

I wrote a sproc which does four things:
1. It looks at an option master to see if the record exists before inserting a new one
2. If the record is not there it inserts the optino record
3. Once the record is inserted I have to run a CASE statement on the record to determine its level
4. Once the level is determined, the record needs to be updated with the correct level.
1-3 work fine (when run without the update command).
However, even though I set a criteria, UPDATE still updates and not the one records.
Any idea why? set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]

@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit

AS

--check to see if the option record exists

IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)

BEGIN
SELECT ' This option already exists in your Option Master'
END
ELSE BEGIN

--if the option record option does not exist, insert it

INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )

SELECT ' Added to Option Master Successfully'
END

--once the option record is inserted, case it to find the its level (1-9)
--update the record with the approciate level.

UPDATE Optionmaster
SET optionlevel (

SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND
PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND
PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND
PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND
ElevationID > '0' THEN '1'
END AS OptionLevel --provides the option level required to update the record
FROM optionmaster
WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID))
--even through I specify the above criteria, it upates all records.

View 9 Replies View Related

Coalesce Is Not Working And Update Is No Updating

Nov 21, 2007

My code worked a few weeks ago and has since stop working, reasons are totally not clear to me as to what happended.
However, I need to get this thing up and running. It will not longer Coalesce data entry. Iran the debugger and the correct values are in the specified objects as if it is the first time I run the page for a person it will input data, but not of subsequent data entry attempts.
My code: ( I trully appreciate your help)
Ayo
'Using "With/End With" pass content to columns from text objects and datatime variables (see above)With cmdCommentUpdate
.Parameters.Add(New SqlClient.SqlParameter("@UserID", ddlEmployeeSuperCmt.SelectedValue)).Parameters.Add(New SqlClient.SqlParameter("@Today", bDate))
.Parameters.Add(New SqlClient.SqlParameter("@Comments", dtToday & " " & UCase(userNamedbInsert) & " " & txtComment.Text & " " & vbCrLf)).Parameters.Add(New SqlClient.SqlParameter("@CommenterLogon", UCase(userNamedbInsert)))
.Parameters.Add(New SqlClient.SqlParameter("@CommentDate", dtNow))
'Establish the type of commandy object
.CommandType = CommandType.Text
'Pass the Update nonquery statement to the commandText object previously instantiated.CommandText = "UPDATE ATTTble" & _
" SET Comments = COALESCE(Comments, '') + @Comments, CommenterLogon = @CommenterLogon, CommentDate = @CommentDate" & _
" WHERE (UserID = @UserID) AND (Today = '" & lblDate.Text & "') "
End With

View 5 Replies View Related

Update Stored Procedure Not Updating

Apr 12, 2008

Hello,I'm working on a grant management database for a project in my databases class and I'm having some issues updating grants into the database.Here is my situation:How this page works is, it gets a query string from a search_grant.aspx page. In this query string, it gets the grant ID of the grant the user wants to edit. If the grant id is a valid grant, it then, upon page_load in C#:1.) Creates an Sql connection set to a viewGrant stored procedure,2.) Adds in all the necessary parameters as output variables3.) Sets private members declared inside of the partial class to those values it gets from the stored procedure4.) Sets textbox controls on the page to those values5.) Displays the page with all the populated data from the stored procedureThat part works fine. I was having an issue where clicking the update button would not grab the new values that the user input into the textboxes. I later realized that the Page_Load code was being re-executed BEFORE the button was being clicked (kind of dumb but...whatever). To fix it, I placed all of the code to do the above statements inside of a: if (!Page.IsPostBack){ // Do code here}That works fine. The problem, however, is that it's STILL not updating. The stored procedure works just fine inside of the management studio, but not in the ASP Page. The code is similar to that of my new_grant.aspx page, which creates a grant into the database. Why that works and this doesn't, I don't know. Even when I hard code the values into the parameters in C#, it's not updating the data! There are no errors that are being returned, so this has really boggled my mind.Any help is greatly appreciated! Here is some sample code of what I'm doing:protected void Update_button_Click(object sender, EventArgs e){ // Create SQL connection to update Grant string ConnectionString = "connection string which works fine"; SqlConnection sqlConnection2 = new SqlConnection(); try { sqlConnection2.ConnectionString = ConnectionString; sqlConnection2.Open(); } catch (Exception Ex) { if (sqlConnection2 != null) { sqlConnection2.Dispose(); } SQLErrorLabel.Text = Ex.Message; SQLErrorLabel.Visible = true; return; } // ------------------ Update values into database ------------------- // Create the statement to use on the database SqlCommand editGrant = new SqlCommand("editGrant", sqlConnection2); editGrant.CommandType = CommandType.StoredProcedure; editGrant.Connection = sqlConnection2; // Set our values for each variable GrantName = GrantName_input.Text; ProjectDescription = ProjDesBox.Text; ReportingYear = Int32.Parse(ReportYearBox.SelectedItem.ToString()); ActivityStarted = Activity_Date.Text; DateSubmitted = Date_Submitted.Text; Audit = chkAudit.Checked; TypeID = Type_ID_input.SelectedValue; FunderID = Funder_List.SelectedValue; StatusID = Status_ID_input.SelectedValue; AcademicDepartmentID = AcademicID_List.SelectedValue; PIID = PI_List.SelectedValue; ContractNumber = txtContractNum.Text; ESUAccountNumber = txtESUAccountNum.Text; AmountAwarded = txtAmount.Text; AwardDate = Award_Date.Text; DateContractSigned = txtDateSigned.Text; ReportingNotes = ReportingNotesbox.Text; NotesNotes = GrantNotesbox.Text; string ReportingTimestamp = DateTime.Now.ToString(); string ReportingWho = Membership.GetUser().ToString(); string NotesTimestamp = DateTime.Now.ToString(); string NotesWho = Membership.GetUser().ToString(); #region insertParams // Add our parameters that SQL will be using editGrant.Parameters.AddWithValue("@GrantID", GID); editGrant.Parameters["@GrantID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@GrantName", GrantName); editGrant.Parameters["@GrantName"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ProjectDescription", ProjectDescription); editGrant.Parameters["@ProjectDescription"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingYear", ReportingYear); editGrant.Parameters["@ReportingYear"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ActivityStarted", ActivityStarted); editGrant.Parameters["@ActivityStarted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateSubmitted", DateSubmitted); editGrant.Parameters["@DateSubmitted"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@Audit", Audit); editGrant.Parameters["@Audit"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@TypeID", TypeID); editGrant.Parameters["@TypeID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@StatusID", StatusID); editGrant.Parameters["@StatusID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@FunderID", FunderID); editGrant.Parameters["@FunderID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AcademicDepartmentID", AcademicDepartmentID); editGrant.Parameters["@AcademicDepartmentID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@PIID", PIID); editGrant.Parameters["@PIID"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ContractNumber", ContractNumber); editGrant.Parameters["@ContractNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ESUAccountNumber", ESUAccountNumber); editGrant.Parameters["@ESUAccountNumber"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AmountAwarded", AmountAwarded); editGrant.Parameters["@AmountAwarded"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@AwardDate", AwardDate); editGrant.Parameters["@AwardDate"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@DateContractSigned", DateContractSigned); editGrant.Parameters["@DateContractSigned"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingNotes", ReportingNotes); editGrant.Parameters["@ReportingNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingTimestamp", ReportingTimestamp); editGrant.Parameters["@ReportingTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@ReportingWho", ReportingWho); editGrant.Parameters["@ReportingWho"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesNotes", NotesNotes); editGrant.Parameters["@NotesNotes"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesTimestamp", NotesTimestamp); editGrant.Parameters["@NotesTimestamp"].Direction = ParameterDirection.Input; editGrant.Parameters.AddWithValue("@NotesWho", NotesWho); editGrant.Parameters["@NotesWho"].Direction = ParameterDirection.Input; #endregion // Execute the UPDATE statement to Grant editGrant.ExecuteNonQuery(); sqlConnection2.Close(); sqlConnection2.Dispose(); Response.Redirect("editedGrant.aspx?id=" + GrantIDBox.Text);}EDIT: Problem Solved! Problem was the that the GrantID wasn't being properly set.

View 6 Replies View Related

Update Statement Not Updating All Records

Jul 27, 2012

I have the following code:

TRUNCATE TABLE [Temp_Export];
INSERT INTO [Temp_Export]
(

[Code]....

The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?

View 6 Replies View Related

Updating Sql DB. My Code Compiles, But Doesn't Update.

Sep 15, 2006

C#, Webforms, VS 2005, SQL Hi all, quick hit question. I'm trying to update a table with an employee name and hire date. Session variable of empID, passed from a previous page (successfully) determines which row to plop the update into. It's not working even though i compiles and makes it all the way through the code to the txtReturned.Text = "I made it" debug line...Any thoughts? 1 string szInsSql;
2
3 string sConnectionString = "Data Source=dfssql;Database=MyDB;uid=myID;pwd=myPWD";
4 SqlConnection objConn = new SqlConnection(sConnectionString);
5
6 objConn.Open();
7
8 szInsSql = "UPDATE empEmployee SET " +
9 "Name = '" + this.txtName.Text + "', " +
10 "HireDate = '" + this.txtHireDate.Text + "', " +
11 "WHERE empID = '" + Session[empID] + "'";
12
13 SqlCommand objCmd1 = new SqlCommand(szInsSql, objConn);
14 objCmd1.ExecuteNonQuery();
15
16 txtReturned.Text = "I made it";
It's got to be a ' or a , out of place but I've looked at this code for a half hour straight, trying a variety of changes...and it still doesn't update the DB...Any help would be great. Thank you! -Corby-

View 3 Replies View Related

Updating The Same Column Multiple Times In One Update Statement

Jul 23, 2005

I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0

View 8 Replies View Related

Why Is This SQL UPDATE Query Not Updating When This Code Is Used Under Button.click.

Mar 20, 2008

Why is this SQL UPDATE query not updating when this code is used under button.click.

Dim ListingID As String = Request.QueryString("id").ToString
Dim sqlupdate As String = "UPDATE Listings SET PlaceName = '" & PlaceName.Text & "', Location = '" & Location.SelectedValue & "', PropertyType = '" & PropertyType.SelectedValue & "', Description = '" & Description.Text & "', Price = '" & Price.Text & "' WHERE ListingID ='" & ListingID & "'"
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand(sqlupdate, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()

View 12 Replies View Related

SQL Parameter Update Not Updating Changed Null/blank Values

Oct 24, 2006

I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.

SqlConnection1.Open()

strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "

cmdCategoriesUpdate.CommandText = strSQLu

With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()

View 3 Replies View Related

Updating A Production Database With Back Up Of Development Database

May 11, 2007

Can someone provide a step by step tutorial for this? I'd like to safely update a database that is used for a website without much or any downtime.

View 1 Replies View Related

Updating Online SQL 2005 Database From Local Database

Jan 30, 2007

I have my first small SQl Server 2005 database developed on my localserver and I have also its equivalent as an online database.I wish to update the local database (using and asp.net interface) andthen to upload the data (at least the amended data, but given thesmall size all data should be no trouble) to the online database.I think replication is the straight answer but I have no experience ofthis and I am wondering what else I might use which might be lesscomplicated. One solution is DTS (using SQL 2000 terms) but i am notsure if I can set this up (1) to overwrite existing tables and (2) notto seemingly remove identity attributes from fields set as identities.I know there are other possibilities but I would be glad of advice asto the likely best method for a small database updated perhaps onceweekly or at less frequent intervals,Best wishes, John Morgan

View 3 Replies View Related

Updating A Table On My Hosted Database From A Local Database

Aug 1, 2007

Hi,

How do I insert data that I have collected in a local database onto a table on my online ie hosted database which is on a different server?

At the moment I am just uploading all the data to the hosted DB but this is wasting bandwith as only a small percentage of data is actually selected and used.

I thought that if i used a local DB and then update the table on my hosted DB this would be much more efficient, but I am not sure how to write the SQL code to do this!

Do I do some kind of

INSERT INTO sample_table

SELECT xxx

FROM origanal_table

Or is it more complicated than this?

Thanks

View 6 Replies View Related

Updating Sql Database From Linked Access Database

Jul 1, 2004

I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database.

What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (such as 0). And when a record is updated in Access, have it updated in SQL.

Needless to say this is a bit out of my area and not sure how to accomplish this.

Any help is greatly appreciated.

View 2 Replies View Related

Updating Sql 2000 Database From Sql 2005 Database

Jul 27, 2006

i have sql 2005 installed on my personal machine, but our server has sql 2000 on it. the structure of my database was made on the server, but i'm not sure how to update the server copy from my local copy. when i try to export my data from my local machine to the server (or import from my local machine to the server), i get pre-execute errors.

roughly every other week, i'll need to be able to update the server version from my local version, so i'm trying to find the most efficient method. what is the best way to update a 2000 database from a 2005 database? it doesn't matter if i append or overwrite, but i do use identity fields. the error i get when trying to use the import/export wizard is:

- Pre-execute (Error)

Messages

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination 3 - ReleaseNotes" (202) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

View 4 Replies View Related

SQL Login With TableAdapter

Jul 31, 2006

Ok, just made a TableAdapter and the SELECT statement works fine and pulls data. But doing an INSERT is something different, it craps out wanting a login.
No neat Wizard (or so I haven't found yet) that lets me give the TableAdapter the login info for the SQL Server, which I have.
Do I need to write code with the login and password for the SQL connection instead?
-Ed

View 1 Replies View Related

Problem With T-SQL In TableAdapter

Jul 11, 2007

Hi,
I have a TableAdapter created in a Dataset. I'm creating a search function for a table, and here's how my code looks like in the "Add Query" wizard:
Select * from Event where eventname like '%@EventName%'
But when I click on preview data, I do not get the prompt to enter a value for the @EventName parameter, where went wrong?

View 4 Replies View Related

I/O Blob Through TableAdapter

May 26, 2006

What's a good way to work with Blobs and TableAdapters, in terms of declaring compatible column types in SQL Server and DataTable fields?

View 2 Replies View Related

TransactionScope And TableAdapter

Jul 30, 2007

This thread has also been posted under '.NET Data Access and Storage'. However, I have realized that the same code contruction using SQL Server connection- and tableadapter objects work fine so I am trying to get answers here also.

I am attempting to do transactional updates to SQL Compact Edition database using TableAdapter and TransactionScope like this:

using (TransactionScope ts = new TransactionScope())

{

SqlCeConnection sqlConn = new SqlCeConnection(connectionString);

myTableTableAdapter ta = new myTableTableAdapter();

ta.Connection = sqlConn;

ta.Update(dsmyTable.myTable);
ts.Complete();

}

dsmyTable is a strongly typed dataset created through the Dataset Designer and populated with data from the database prior to the code sample above.

This all works fine. However, when removing the call to 'ts.Complete()' to simulate the transaction rolling back, data is still stored into the database.

Am I missing something here or does TransactionScope not support SQL Compact Edition? Any help is appreciated!

Regards.

View 3 Replies View Related

TableAdapter And Variable

Sep 5, 2006

Hello,

I want to do something like this in a TableAdapter's SQL Statement:

SELECT Type.*

FROM Controllers INNER JOIN

@Type AS Type ON Type.ConfigurationId = Controllers.ActiveConfig INNER JOIN

Tank ON Controllers.ControllerId = Tank.ControllerId

WHERE (Tank.TankId = @TankId)

But I get an error because of the @Type variable. Is there any other way to choose a table in a TableAdapter with variables?

Thanks for your help!

View 3 Replies View Related

Passing Parameters In TableAdapter

Jul 28, 2006

A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows:
------
TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter();
// Add a new modem
modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN );
--------
And the error I get when loading the ASP.NET page is as follows:
Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments
---------------
Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter?
-Ed

View 4 Replies View Related

TableAdapter Class Not Generated

Aug 2, 2006

I created a new DataSet object using the
wizard and had no probs, it's very straightforward. I created a
GetProducts() method and also added a GetProductCount() method. I
read somewhere that when the DataSet is saved, it will generate the
TableAdapter classes and store them in the project nested under the
DataSet object. This isn't happening, the files aren't there and
yes I'm displaying all files. There *is* however the TableAdapter
class but it's in not in the project, it's a temporary file w/ the
following path:

C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET
Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.

I saved the DataSet several times, compiled the entire solution and
even closed the solution yet this temporary file isn't getting saved
where it should be. Does anyone have any ideas abt this?
I'm eager to start developing my DAL and def want to use these
TableAdapter classes.

Thx in advance,
-Pete

View 2 Replies View Related

Tableadapter Query Question

Nov 20, 2006

hii am using the nothwind database for a current exercise, i am wanting to user to be able to search for products based on two input methods (product name, category).here is the code i currently have: SELECT DISTINCT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, ProductName, ProductID, CategoryIDFROM ProductsWHERE (CategoryID = @categoryID) OR (ProductName LIKE '%' + @ProductName + '%')i'm wanting to have it set so the user can use both input methods, or either, when one input method is used by the user it works fine, but when both are used it generates results for 2 seporate queriesi'm wanting to have it set so the user can search by product name in certain categories (when both search input methods are used)how would i go about doing this? is there some kind of equivalent to an AND/OR statement?eg. WHERE (CategoryID = @categoryID) AND OR
(ProductName LIKE '%' + @ProductName + '%') thanks in advance!

View 2 Replies View Related

Going From Sqlconnection And Sqlreader To Using A Tableadapter And ????????

Apr 13, 2007

Below is my code right now Im using a direct sqlconnection for each request.� I would like to use Tableadapters and/or my BLL code like the rest of my sites pages.� I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)Thanks for the help.NeilPrivate Sub GetAllEvents(ByVal SqlString)� � � � Response.Clear()� � � � Response.ContentType = "text/xml"� � � � Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)� � � � objX.WriteStartDocument()� � � � objX.WriteStartElement("Events")� � � � Dim objConnection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|*********.mdf;Integrated Security=True;User Instance=True")� � � � objConnection.Open()� � � � Dim sql As String = SqlString� � � � Dim objCommand As New SqlCommand(sql, objConnection)� � � � Dim objReader As SqlDataReader = objCommand.ExecuteReader()� � � � While objReader.Read()� � � � � � objX.WriteStartElement("Event")� � � � � � objX.WriteAttributeString("EventId", objReader.GetInt32(0))� � � � � � objX.WriteAttributeString("EventName", objReader.GetString(1))� � � � � � objX.WriteAttributeString("EventDescription", objReader.GetString(2))� � � � � � objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))� � � � � � objX.WriteAttributeString("CurrentDate", Date.Now.ToString)� � � � � � If Not objReader.IsDBNull(12) Then� � � � � � � � objX.WriteAttributeString("EventImage", objReader.GetString(12))� � � � � � End If� � � � � � objX.WriteEndElement()� � � � End While� � � � objReader.Close()� � � � objConnection.Close()� � � � objX.WriteEndElement()� � � � objX.WriteEndDocument()� � � � objX.Flush()� � � � objX.Close()� � � � Response.End()� � End Sub

View 2 Replies View Related

If/then Parameterized Queries Using Tableadapter

Jun 29, 2007

Hey fellas. Here's my situation. I have two textboxes where the user enters a "start" date and an "end" date. I want to search a table to find records who's "expired" column date is between those two dates provided by the user. The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond. Essentially, I think I'm looking for a SQL statement along the lines of:
SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE

CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END

AND

CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END

AND (Role.Description = 'Requestor')

I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around. Anyway, how do I do this? Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL? If I can tackle it with SQL, where have I gone astray? I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
Thanks for the help everyone!

View 3 Replies View Related

TableAdapter And Connection Strings

Sep 19, 2007

Hello,
I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file. How can I get it to do so? It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.

View 2 Replies View Related

Select SCOPE_IDENTITY() From TableAdapter

Oct 8, 2007

I like to run a query from my tableadapter, but if i insert a row i like to know the id of it.So i was looking on asp.net and found a tutorial for it. But it doesnt work!
My inset query:
"INSERT INTO Klanten (BedrijfsNaam, Contactpersoon, Adres, Postcode, Woonplaats, Telefoon, Mobiel, [E-mail], Website, Kvk, BTWnr)VALUES (?,?,?,?,?,?,?,?,?,?,?);
Select SCOPE_IDENTITY()"
As database i user MS SQL Express.Also i tried it with @@Identity, but that works eighter.If i try the insert code with the scope identity above, and i put it in the Query builder from MS Visual Studio, it returns an error.. :
"The following errors were encountered while parsing the contents of the SQL pane:
Unable to parse query text."This error is only there if i add scope_idititiy or @@identity to it.Also if i run it in the SQL part of SQL Studio Express i get the same error.
So should i change this?Or how can i get the id of it ?Of course i can read the database after the insert, but its not save enough i think.

View 4 Replies View Related