UPDATE INSERT Code Efficiency
Sep 17, 2005
Not sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:
@ZUserID varchar(10)
AS
SET NOCOUNT ON
DECLARE @counter int
SET @counter = 0
WHILE @counter < 10
BEGIN
SET @counter = @counter + 1
IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter)
BEGIN
UPDATE tblWork SET
TransID = Null,
TransCd = Null,
InvoiceNo = Null,
DatePaid = Null,
Adjustment = Null,
Vendor = Null,
USExchRate = Null
WHERE
UserID = @ZUserID And LineNumber = @counter
END
ELSE
INSERT INTO tblWork
(LineNumber,TransCd,UserID)
VALUES
(@counter,'P',@ZUserID)
END
View 2 Replies
ADVERTISEMENT
Aug 31, 2004
Hi All,
I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.
Simply, is it better to update a table via an UPDATE query or Recordset manipulation?
I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?
i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?
It's a MS Access XP app with ADO 2.8.
My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.
I appreciate your help!
Thanks,
Warren
View 2 Replies
View Related
Mar 13, 2007
All:
I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
Begin begin transaction
--Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE
Begin begin transaction
--Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID;
--Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Blue.
View 5 Replies
View Related
Jun 4, 2008
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
Try
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
Finally
If conn.State <> Data.ConnectionState.Closed Then
conn.Close()
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
Try
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
Server.Transfer("yardim.aspx")
Finally
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
ElseServer.Transfer("urunsat.aspx")
End If
cheers
View 2 Replies
View Related
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
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
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Oct 23, 2014
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
View 1 Replies
View Related
Apr 18, 2008
Hi I and using gridview. And binding the data in the code behind.I need to use update command in code behind. How do I achieve this? I protected void lookUP (object sender, EventArgs e)
{
string strSql, strConn;
System.Text.StringBuilder whereClause = new System.Text.StringBuilder();
strConn = ConfigurationManager.ConnectionStrings["drake_CSMConnectionString1"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (newClientName.Text != "")
whereClause.Append("'" + newClientName.Text + "'");
strSql = "SELECT * FROM [ftsCSM] where [client_name] = " + whereClause.ToString();
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, Conn);
DataSet ds2 = new DataSet();
dataAdapter.Fill(ds2, "ftsCSM");
DataTable dataTable2 = ds2.Tables["ftsCSM"];
int totalRec = dataTable2.Rows.Count;
Clients.DataSource = ds2;
Clients.DataBind();
} // end of lookup()
View 1 Replies
View Related
Dec 15, 2007
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!!
I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas?
Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Mainform.SqlConnection1.Open()
Dim TheNewRow As DataRow
TheNewRow = Mainform.Dataset1.Tables("ChartTable").NewRow
TheNewRow("Chart Number") = TB1CN.Text
TheNewRow("Chart Title") = TB2CT.Text
TheNewRow("Edition Date") = TB3ED.Text
TheNewRow("Print Date") = TB4PD.Text
TheNewRow("Edition Number") = TB5EN.Text
Mainform.Dataset1.Tables("ChartTable").Rows.Add(TheNewRow)
Mainform.SqlDataAdapter1.Update(Mainform.Dataset1)
Mainform.SqlConnection1.Close()
Me.Visible = False
End Sub
Regards,
Steve
View 4 Replies
View Related
May 31, 2008
In the device emulator when it starts I see two records that were added to the SDF in the VS2008 IDE. I added a third record and after tapping on the Save menu my navigation control shows all three records. I then closed the application. I went to Memory and stopped the form. I went to the File Editor and restarted the application. It only showed the original 2 records. In the File Editor I tapped on the SDF to open it in the Query Analyzer. It also only shows the original two records.
The Dataset appears to be updated, but not the bound table in the SDF. Can any one help? Code is below.
bsTEST is the binding SOurce
navTEST is my custom Navigation control
Private Sub HandleMenus(ByVal Sender As Object, ByVal EA As EventArgs) Handles mnuAdd.Click, mnuCancel.Click, mnuSave.Click
With Me
Select Case True
Case Sender Is .mnuAdd
.Menu = .mnuCancelSave
txtLOC.ReadOnly = False
bsTEST.AddNew()
.navTest.Visible = False
Case Sender Is .mnuCancel
.Menu = .mnuMain
txtLOC.ReadOnly = True
With bsTEST
.CancelEdit()
.Position = navTest.CurrentRecord = -1
End With
.navTest.Visible = True
Case Sender Is .mnuSave
.Menu = .mnuMain
txtLOC.ReadOnly = True
With .navTest
.RecordCount += 1
.CurrentRecord = 1
End With
.navTest.Visible = True
bsTEST.EndEdit()
bsTEST.Position = 0
taTEST.Update("Locations")
End Select
End With
View 4 Replies
View Related
Feb 15, 2008
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
View 14 Replies
View Related
Jul 23, 2005
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies
View Related
Feb 7, 2005
When someone clicks my form
i use this code to enter data in Server:
private void Submit_Click(object sender, System.EventArgs e)
{
SqlConnection con = null;
SqlCommand cmd = null;
SqlDataReader rd = null;
try
{
con = new SqlConnection("server=localhost; uid=sa; pwd=123; database=TaskManagement");
cmd = new SqlCommand("INSERT INTO TASK_TYPE (name) VALUES ("+name.Text+")", con);
con.Open();
rd = cmd.ExecuteQuery();
}
catch (Exception e)
{
Response.Write("<p><font color="red">Error: ");
Response.Write(e.Message);
Response.Write("</font></p>");
}
finally
{
if(rd != null)
rd.Close();
if(con != null)
con.Close();
}
}
is that right? I am new in c# and i try to make it work!
Thanks in advance!
View 2 Replies
View Related
Mar 12, 2005
I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.
I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.
Thanks
Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
Dim UserCode as String
Dim PropertyCode as String
Dim UnitCode as String
Dim address1 as String
Dim address2 as String
Dim address3 as String
Dim city as String
Dim zip_Code as String
Dim description as String
Dim price_Range as String
Dim state as String
Dim type_Property as String
Dim property_Status as String
if chkChangeState.Checked = True then
state = ddl_State.SelectedItem.Value
else
state = lblState.Text
end if
if chkPropertyType.Checked = True then
type_Property = ddlPropertyType.SelectedItem.Value
else
type_Property = lblPropertyType.Text
end if
if chkPropertyStatus.Checked = True then
property_Status = ddlPropertyStatus.SelectedItem.Value
else
property_Status = lblPropertyStatus.Text
end if
txtPropertyCode.ReadOnly = False
txtUnitCode.ReadOnly = False
'address1 = txtAddress1.Text
'address2 = txtAddress2.Text
'address3 = txtAddress3.Text
'city = txtCity.Text
'zip_Code = txtZipCode.Text
'description = txtDescription.Text
'price_Range = txtPriceRange.Text
UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
PropertyCode = txtPropertyCode.Text
UnitCode = txtUnitCode.Text
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@Address1, [Address2]=@Address2, [Address3]=@Address3, [City]=@City, [State]=@State, [Zip_Code]=@Zip_Code, [Type_Property]=@Type_Property, [Description]=@Description, [Property_Status]=@Property_Status, [Price_Range]=@Price_Range WHERE ([Property_db].[Code] = @Code) AND ([Property_db].[Prop_Code] = @Prop_Code) AND ([Property_db].[Unit_Code] = @Unit_Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@Code"
dbParam_code.Value = UserCode
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)
Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_prop_Code.ParameterName = "@Prop_Code"
dbParam_prop_Code.Value = PropertyCode
dbParam_prop_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_prop_Code)
Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_unit_Code.ParameterName = "@Unit_Code"
dbParam_unit_Code.Value = UnitCode
dbParam_unit_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_unit_Code)
Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address1.ParameterName = "@Address1"
dbParam_address1.Value = txtAddress1.Text
dbParam_address1.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address1)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address2.ParameterName = "@Address2"
dbParam_address2.Value = txtAddress2.Text
dbParam_address2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address3.ParameterName = "@Address3"
dbParam_address3.Value = txtAddress3.Text
dbParam_address3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address3)
Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_city.ParameterName = "@City"
dbParam_city.Value = txtCity.Text
dbParam_city.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_city)
Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_state.ParameterName = "@State"
dbParam_state.Value = state
dbParam_state.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_state)
Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_zip_Code.ParameterName = "@Zip_Code"
dbParam_zip_Code.Value = txtZipCode.Text
dbParam_zip_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_zip_Code)
Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_type_Property.ParameterName = "@Type_Property"
dbParam_type_Property.Value = type_Property
dbParam_type_Property.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_type_Property)
Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_description.ParameterName = "@Description"
dbParam_description.Value = txtDescription.Text
dbParam_description.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_description)
Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_property_Status.ParameterName = "@Property_Status"
dbParam_property_Status.Value = property_Status
dbParam_property_Status.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_property_Status)
Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_price_Range.ParameterName = "@Price_Range"
dbParam_price_Range.Value = txtPriceRange.Text
dbParam_price_Range.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_price_Range)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
'if Not rowsAffected then
Response.Redirect("./editproperty.aspx")
'end if
End Sub
View 1 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Dec 21, 2007
Hi I am trying to inset data to my sql 2005 database using a webform.. the code I have is
3 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 4 5 ConnectionString="<%$ ConnectionStrings:SQL2005440975 %>" 6 7 InsertCommand="INSERT INTO [dbo.asp.net_Addresstbl] ([Salutation], [fname], [sname], [Daydb], [Monthdb], [Yeardb], [txtOrg], [txtLine1], [txtLine2], [txtLine3], [txtTown], [txtPostcode], [UserID]) 8 9 VALUES (@Salutation, @fname, @sname, @Daydb, @Monthdb, @Yeardb, @txtOrg, @txtLine1, @txtLine2, @txtLine3, @txtTown, @txtPostcode, @UserID)" 10 11 <InsertParameters>12 <asp:FormParameter FormField="Salutation" Name="Salutation" Type="String" />13 <asp:FormParameter FormField="fname" Name="fname" Type="String" />14 <asp:FormParameter FormField="sname" Name="sname" Type="String" />15 <asp:FormParameter FormField="Daydb" Name="Daydb" Type="Decimal" />16 <asp:FormParameter FormField="Monthdb" Name="Monthdb" Type="String" />17 <asp:FormParameter FormField="Yeardb" Name="Yeardb" Type="Decimal" />18 <asp:FormParameter FormField="txtOrg"Name="txtOrg" Type="String" />19 <asp:FormParameter FormField="txtLine1" Name="txtLine1" Type="String" />20 <asp:FormParameter FormField="txtLine2" Name="txtLine2" Type="String" />21 <asp:FormParameter FormField="txtLine3" Name="txtLine3" Type="String" />22 <asp:FormParameter FormField="txtTown" Name="txtTown" Type="String" />23 <asp:FormParameter FormField="txtPostcode"Name="txtPostcode" Type="String" />24 <asp:FormParameter FormField="UserID" Name="UserID" Type="Object" />25 </InsertParameters>26 </asp:SqlDataSource>27 28 <asp:DropDownList ID="Salutation" runat="server" ValidationGroup="Address">29 <asp:ListItem>Choose One</asp:ListItem>30 <asp:ListItem>Mr.</asp:ListItem>31 <asp:ListItem>Mrs.</asp:ListItem>32 <asp:ListItem>Ms.</asp:ListItem>33 <asp:ListItem>Miss.</asp:ListItem>34 <asp:ListItem>Rev.</asp:ListItem>35 <asp:ListItem>Doc.</asp:ListItem>36 <asp:ListItem>Other.</asp:ListItem>37 </asp:DropDownList>38 39 <asp:TextBox ID="fname" runat="server" CausesValidation="True"></asp:TextBox>40 41 <asp:TextBox ID="sname" runat="server" CausesValidation="True"></asp:TextBox>42 43 <asp:DropDownList ID="Daydb" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">44 <asp:ListItem>Day</asp:ListItem>45 <asp:ListItem>01</asp:ListItem>46 <asp:ListItem>02</asp:ListItem>47 <asp:ListItem>03</asp:ListItem>48 <asp:ListItem>04</asp:ListItem>49 <asp:ListItem>05</asp:ListItem>50 <asp:ListItem>06</asp:ListItem>51 <asp:ListItem>07</asp:ListItem>52 <asp:ListItem>08</asp:ListItem>53 <asp:ListItem>09</asp:ListItem>54 <asp:ListItem>10</asp:ListItem>55 <asp:ListItem>11</asp:ListItem>56 <asp:ListItem>12</asp:ListItem>57 <asp:ListItem>13</asp:ListItem>58 <asp:ListItem>14</asp:ListItem>59 <asp:ListItem>15</asp:ListItem>60 <asp:ListItem>16</asp:ListItem>61 <asp:ListItem>17</asp:ListItem>62 <asp:ListItem>18</asp:ListItem>63 <asp:ListItem>19</asp:ListItem>64 <asp:ListItem>20</asp:ListItem>65 <asp:ListItem>21</asp:ListItem>66 <asp:ListItem>22</asp:ListItem>67 <asp:ListItem>23</asp:ListItem>68 <asp:ListItem>24</asp:ListItem>69 <asp:ListItem>25</asp:ListItem>70 <asp:ListItem>26</asp:ListItem>71 <asp:ListItem>27</asp:ListItem>72 <asp:ListItem>28</asp:ListItem>73 <asp:ListItem>29</asp:ListItem>74 <asp:ListItem>30</asp:ListItem>75 <asp:ListItem>31</asp:ListItem>76 </asp:DropDownList>77 78 <asp:DropDownList ID="Monthdb" runat="server" style="text-align: left">79 <asp:ListItem>Month</asp:ListItem>80 <asp:ListItem>January</asp:ListItem>81 <asp:ListItem>February</asp:ListItem>82 <asp:ListItem>March</asp:ListItem>83 <asp:ListItem>April</asp:ListItem>84 <asp:ListItem>May</asp:ListItem>85 <asp:ListItem>June</asp:ListItem>86 <asp:ListItem>July</asp:ListItem>87 <asp:ListItem>August</asp:ListItem>88 <asp:ListItem>September</asp:ListItem>89 <asp:ListItem>October</asp:ListItem>90 <asp:ListItem>November</asp:ListItem>91 <asp:ListItem Value="12">December</asp:ListItem>92 </asp:DropDownList>93 94 <asp:DropDownList ID="Yeardb" runat="server" style="text-align: left" 95 OnSelectedIndexChanged="Year_SelectedIndexChanged" 96 DataSourceID="YearDataSource" DataTextField="Year" DataValueField="Year">97 <asp:ListItem Selected="True">Choose Year..</asp:ListItem>98 </asp:DropDownList>99 <asp:AccessDataSource ID="YearDataSource" runat="server" 100 DataFile="~/App_Data/year.mdb" SelectCommand="SELECT [Year] FROM [Year]">101 </asp:AccessDataSource>102 103 <asp:TextBox ID="txtFind" runat="server" CausesValidation="True" ValidationGroup="address"></asp:TextBox>104 105 <asp:Button ID="btnFind" runat="server" Text="Find" 106 OnClick="btnFind_Click" ValidationGroup="address" 107 OnClientClick="lstProperties" />108 109 110 <asp:ListBox ID="lstProperties" runat="server" AutoPostBack="True" 111 OnSelectedIndexChanged="lstProperties_SelectedIndexChanged" Visible="False" 112 Width="200px"></asp:ListBox>113 114 <asp:TextBox ID="txtOrg" runat="server" OnTextChanged="txtOrg_TextChanged" 115 ReadOnly="True" ValidationGroup="address2" Visible="False"></asp:TextBox>116 117 <asp:TextBox ID="txtLine1" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>118 119 <asp:TextBox ID="txtLine2" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>120 121 <asp:TextBox ID="txtLine3" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>122 123 <asp:TextBox ID="txtTown" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>124 125 <asp:TextBox ID="txtPostcode" runat="server" ReadOnly="True" Visible="False"></asp:TextBox>126 127 <asp:Label ID="lblAddress" runat="server" CssClass="style9"></asp:Label>128 129 <asp:Button ID="submitaddress" runat="server" Text="Add Details" 130 style="text-align: centre" CommandName="Submit" 131 ValidationGroup="address" PostBackUrl="~/MemberPages/account.aspx" />132 133 </asp:Content>
Will this code work? if not can you explain why not and offer an example that will work. In the mean time I am reading up on these insert statements.
Regards
Mal
View 5 Replies
View Related
Nov 9, 2011
I am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replies
View Related
Sep 11, 2015
Trying to optimise the below query, I believe it's do with the estimated rows on the unpivot using Supratimes this seems to be the only sticking point.The query below is an example replicating what I'm trying to do in live, it takes around 2 seconds to run on my pc.
Create --drop --alter
Table #Actuals
(
Period1 FLOAT
, Period2 FLOAT
, Period3 FLOAT
, Period4 FLOAT
[code]....
View 8 Replies
View Related
Feb 23, 2008
Hello,
I am creating an ASP.NET web site which inserts data in a SQL database using LINQ.
One table column will hold HTML code. I am using a simple TextBox to input the data.
I would like to create my text in my computer and then copy/paste the HTML code to my TextBox and insert in the database.
What software should I use to create my text? Microsoft Office 2007, ... ? Any suggestion would be great.
Can I do it this way?
Thanks,
Miguel
View 9 Replies
View Related
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
Apr 22, 2007
Hi all
My error is as follows: Incorrect syntax near '('.Line 27: acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)Line 28: acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)Line 29: acceptOrDeclineFriendship.Update()Line 30: Line 31: End Sub
Bear with me... I have a page where i use a repeater control to list users who have requested to be friends with the currently online user. The 'getFriendRequests' query looks like this:
SelectCommand="SELECT * FROM Friends, UserDetails WHERE (Friends.UserID = UserDetails.UserID) AND (FriendID = @UserID) AND (ApprovedByFriend = 'False') ORDER BY Friends.Requested DESC">This works.
Within each repeater template, there are 2 buttons, 'Accept' or 'Decline', like this: <asp:Repeater ID="Repeater1" runat="server" DataSourceID="getFriendRequests">
<ItemTemplate>
(other stuff like avatar and username etc)
<asp:Button ID="accept" runat="server" Text="Accept" commandName="Accept" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
<asp:Button ID="decline" runat="server" Text="Decline" commandName="Decline" commandArgument='<%#Eval("UserID")%>' onCommand="Accept_Decline_Friends"/>
</ItemTemplate>
</asp:Repeater>
The code-behind (VB) which deals with this is as follows: Protected Sub Accept_Decline_Friends(ByVal sender As Object, ByVal e As CommandEventArgs)
'retrieve id of requestee and the answer accept/decline
Dim friend_id As String = e.CommandArgument.ToString
Dim answer As String = e.CommandName.ToString
'add the parameters
acceptOrDeclineFriendship.UpdateParameters.Add("Response", answer)
acceptOrDeclineFriendship.UpdateParameters.Add("FriendID", friend_id)
acceptOrDeclineFriendship.Update()
End Sub
Since the buttons are being created dynamically, this is how i track 1. the response from the currently logged in user 'Accept/Decline' and 2. who they are responding to (by their uniqueid)
This relates to a sqlDataSource on my .aspx page like this: <!---- update query when user has accepted the friendship ---->
<asp:SqlDataSource ID="acceptOrDeclineFriendship" runat="server" ConnectionString="<%$ xxx %>"
UpdateCommand="UPDATE Friends SET (ApprovedByFriend = @Response) WHERE (FriendID = @UserID) AND (UserID = @FriendID)">
<UpdateParameters>
<asp:ControlParameter Name="UserID" ControlID="userIdValue" />
</UpdateParameters>
</asp:SqlDataSource>
Which is meant to update my 'Friends' table to show that 'ApprovedByFriend' (the logged in user) is either 'Accept' or 'Decline', and record who's request was responded to.
I hope this is clear, just trying to suppy all of the information! The error appears to be saying that I have an issue with my code-behind, where i am telling the sqlDataSource above to UPDATE. What I can say is that for each button in the repeater, the 2 variables 'friend_id' and 'answer' are picking up the correct values.
Can anyone see any obvious problems here? Any help is very much appreciated as i am well and truley stuck!
View 1 Replies
View Related
Jan 7, 2008
hi there, i have been wrestling with this for quite a while, as in my other post http://forums.asp.net/t/1194975.aspx, what someone advised me was to put in try catch blocks ot see whats going on, problem is i have never really done it whit this kinda thing before, and i was wondering if someone could point me in the right direction.
For example where would i put the try catch block in here, to show me if its not working public int getLocationID(int ProductID, int StockLoc)
{
// Gets the LocationID (Shelf ID?) for the stock column and product id
// passed
// The SQL will look Something like: string strSQL;
strSQL = "SELECT " + " location" + StockLoc + " " + "FROM " + " tbl_stock_part_multi_location " + "WHERE " + " stock_id = " + ProductID;string sConnectionString = "Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx";
SqlConnection objConnGetLocationID = new SqlConnection(sConnectionString);SqlCommand sqlCmdGetLocationID = new SqlCommand(strSQL, objConnGetLocationID);
objConnGetLocationID.Open();int intLocation = Convert.ToInt32(sqlCmdGetLocationID.ExecuteScalar());
return intLocation;
}
View 6 Replies
View Related
Apr 14, 2008
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried
<%$ strUserGuid %>and<% strUserGuid %>
any help appreciated.Thanks
Dave
View 2 Replies
View Related
Jul 1, 2004
I have dataadapter and dataset that reads/writes to SQL tables.
I can read. I can create "new" records.
However, I have not been able to master the "updating" of an existing row.
Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below.
The code I using is below. I don't get error, but changes do not get written to SQL dbase.
For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row.
thanks to any who can help
Dim drow As DataRow
drow = Me.dsRequests1.Tables("REQUESTS").NewRow
drow.BeginEdit()
drow.Item("Request_Name") = Me.txtRequestName.Text
drow.Item("Request_Comments_Txt") = Me.txtRequestComments.Text
drow.Item("Requestor_Contact_Id") = Me.txtRequestor.Text
drow.Item("Request_BigX_Status_Type_Cd") = Me.ddlBigXStatus
drow.Item("Request_Action_Type_Cd") = Me.ddlRequestActionRequested.SelectedItem.Text
drow.EndEdit()
Me.DaREQUESTS.Update(Me.dsRequests1.Tables("REQUESTS"))
Me.dsRequests1.AcceptChanges()
View 1 Replies
View Related
Mar 9, 2006
Hi all
I have a GridView on an aspx page, that is enabled for editing, deletion and sorting.
In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView.
When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters.
First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either.
The code for setting the commands, and adding the SqlDataSource to the page are as follows:
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; string selectCommand = "SELECT * FROM rammekategori"; SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand); ds.ID = "RammeKategoriDS"; ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @Kategoribeskrivelse WHERE (Kategorinavn = @Kategorinavn)"; ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @Kategorinavn)"; Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String); Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String); ds.UpdateParameters.Add(Kategorinavn); ds.UpdateParameters.Add(Kategoribeskrivelse); ds.DeleteParameters.Add(Kategorinavn);
Page.Controls.Add(ds);
SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource;
if (m_SqlDataSource != null) { this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID; }
As mentioned - no affect at all!
Thanks in advance - MartinHN
View 4 Replies
View Related
Apr 28, 2007
What does this code mean and what can I do to fix it? I need to update vista when applicable.
Thanks in advance for your help.
View 5 Replies
View Related
Feb 21, 2007
SSIS seems to automatically set the metadata type and for "typed" sources like database and XML connections it seems to take whatever the source column datatype is. If you use a cast or convert in the your source SQL query, it will not change the datatype of the metadata. This becomes an issue when doing things like merge joins on data from different sources and the join columns are different types (e.g. a ZipCode in one system is a varchar and it is an int in another system). I've been working around the issue by editing the XML code and changing the datatype there. Is there any way to do this through the GUI?
View 2 Replies
View Related
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
Nov 15, 2007
I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml
The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>
How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.
View 1 Replies
View Related
Jul 20, 2007
Hi, I am a newbie in using ASP.NET 2.0 and ADO.NET. I wrote a hangman game and want to record statistics at the end of each game. I will create and update records in the database for each authenticated user as well as a record for the Anonymous, unauthenticated user. After a win or loss has occurred, I want to programmatically use the SQLDataSource control to increment the statistics counters for the appropriate record in the database (note I don't want to show anything or get user input for this function).
I need a VB.NET codebehind example that will show me how I should set up the parameters and update the appropriate record in the database. Below is my code. What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated. I have done many searches on this forum and on the general Internet for programmatic examples of an update sequence of code. If there is a tutorial for this online or a book, I'm happy to check it out.
Any help will be greatly appreciated.
Lambanlaa
CODE - Hangman.aspx.vb
1 Protected Sub UpdateStats()2 Dim playeridString As String3 Dim gamesplayedInteger, gameswonInteger, _4 easygamesplayedInteger, easygameswonInteger, _5 mediumgamesplayedInteger, mediumgameswonInteger, _6 hardgamesplayedInteger, hardgameswonInteger As Int327 8 ' determine whether player is named or anonymous9 If User.Identity.IsAuthenticated Then10 Profile.Item("hangmanplayeridString") = User.Identity.Name11 Else12 Profile.Item("hangmanplayeridString") = "Anonymous"13 End If14 15 playeridString = Profile.Item("hangmanplayeridString")16 17 ' look up record in stats database18 Dim hangmanstatsDataView As System.Data.DataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)19 20 gamesplayedInteger = 021 gameswonInteger = 022 easygamesplayedInteger = 023 easygameswonInteger = 024 mediumgamesplayedInteger = 025 mediumgameswonInteger = 026 hardgamesplayedInteger = 027 hardgameswonInteger = 028 29 If hangmanstatsDataView.Table.Rows.Count = 0 Then30 31 ' then create record with 0 values32 statsSqlDataSource.InsertParameters.Clear() ' don't really know what Clear does33 statsSqlDataSource.InsertParameters("playerid").DefaultValue = playeridString34 statsSqlDataSource.InsertParameters("GamesPlayed").DefaultValue = gamesplayedInteger35 statsSqlDataSource.InsertParameters("GamesWon").DefaultValue = gameswonInteger36 statsSqlDataSource.InsertParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger37 statsSqlDataSource.InsertParameters("EasyGamesWon").DefaultValue = easygameswonInteger38 statsSqlDataSource.InsertParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger39 statsSqlDataSource.InsertParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger40 statsSqlDataSource.InsertParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger41 statsSqlDataSource.InsertParameters("HardGamesWon").DefaultValue = hardgameswonInteger42 43 statsSqlDataSource.Insert()44 End If45 46 ' reread the record to get current values47 hangmanstatsDataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)48 Dim hangmanstatsDataRow As System.Data.DataRow = hangmanstatsDataView.Table.Rows.Item(0)49 50 ' set temp variables to database values51 gamesplayedInteger = hangmanstatsDataRow("GamesPlayed")52 gameswonInteger = hangmanstatsDataRow("GamesWon")53 easygamesplayedInteger = hangmanstatsDataRow("EasyGamesPlayed")54 easygameswonInteger = hangmanstatsDataRow("EasyGamesWon")55 mediumgamesplayedInteger = hangmanstatsDataRow("MediumGamesPlayed")56 mediumgameswonInteger = hangmanstatsDataRow("MediumGamesWon")57 hardgamesplayedInteger = hangmanstatsDataRow("HardGamesPlayed")58 hardgameswonInteger = hangmanstatsDataRow("HardGamesWon")59 60 ' update stats record61 'statsSqlDataSource.UpdateParameters.Clear()62 'statsSqlDataSource.UpdateParameters("playerid").DefaultValue = playeridString63 64 If Profile.Item("hangmanwinorloseString") = "win" Then65 66 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 167 statsSqlDataSource.UpdateParameters("GamesWon").DefaultValue = gameswonInteger + 168 Select Case Profile.Item("hangmandifficultyInteger")69 Case 170 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 171 statsSqlDataSource.UpdateParameters("EasyGamesWon").DefaultValue = easygameswonInteger + 172 Case 273 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 174 statsSqlDataSource.UpdateParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger + 175 Case 376 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 177 statsSqlDataSource.UpdateParameters("HardGamesWon").DefaultValue = hardgameswonInteger + 178 End Select79 80 81 ElseIf Profile.Item("hangmanwinorloseString") = "lose" Then82 83 statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 184 Select Case Profile.Item("hangmandifficultyInteger")85 Case 186 statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 187 Case 288 statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 189 Case 390 statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 191 End Select92 End If93 94 statsSqlDataSource.Update()95 96 End Sub97
CODE - Hangman.aspx 1 <asp:SqlDataSource ID="statsSqlDataSource" runat="server" ConflictDetection="overwritechanges"
2 ConnectionString="<%$ ConnectionStrings:lambanConnectionString %>" DeleteCommand="DELETE FROM [Hangman_Stats] WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon"
3 InsertCommand="INSERT INTO [Hangman_Stats] ([PlayerID], [GamesPlayed], [GamesWon], [EasyGamesPlayed], [EasyGamesWon], [MediumGamesPlayed], [MediumGamesWon], [HardGamesPlayed], [HardGamesWon]) VALUES (@PlayerID, @GamesPlayed, @GamesWon, @EasyGamesPlayed, @EasyGamesWon, @MediumGamesPlayed, @MediumGamesWon, @HardGamesPlayed, @HardGamesWon)"
4 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT PlayerID, GamesPlayed, GamesWon, EasyGamesPlayed, EasyGamesWon, MediumGamesPlayed, MediumGamesWon, HardGamesPlayed, HardGamesWon FROM Hangman_Stats WHERE (PlayerID = @playerid)"
5 UpdateCommand="UPDATE [Hangman_Stats] SET [GamesPlayed] = @GamesPlayed, [GamesWon] = @GamesWon, [EasyGamesPlayed] = @EasyGamesPlayed, [EasyGamesWon] = @EasyGamesWon, [MediumGamesPlayed] = @MediumGamesPlayed, [MediumGamesWon] = @MediumGamesWon, [HardGamesPlayed] = @HardGamesPlayed, [HardGamesWon] = @HardGamesWon WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon">
6 <DeleteParameters>
7 <asp:Parameter Name="original_PlayerID" Type="String" />
8 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
9 <asp:Parameter Name="original_GamesWon" Type="Int32" />
10 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
11 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
12 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
13 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
14 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
15 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
16 </DeleteParameters>
17 <UpdateParameters>
18 <asp:Parameter Name="GamesPlayed" Type="Int32" />
19 <asp:Parameter Name="GamesWon" Type="Int32" />
20 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
21 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
22 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
23 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
24 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
25 <asp:Parameter Name="HardGamesWon" Type="Int32" />
26 <asp:Parameter Name="original_PlayerID" Type="String" />
27 <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
28 <asp:Parameter Name="original_GamesWon" Type="Int32" />
29 <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
30 <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
31 <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
32 <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
33 <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
34 <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
35 </UpdateParameters>
36 <InsertParameters>
37 <asp:Parameter Name="PlayerID" Type="String" />
38 <asp:Parameter Name="GamesPlayed" Type="Int32" />
39 <asp:Parameter Name="GamesWon" Type="Int32" />
40 <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
41 <asp:Parameter Name="EasyGamesWon" Type="Int32" />
42 <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
43 <asp:Parameter Name="MediumGamesWon" Type="Int32" />
44 <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
45 <asp:Parameter Name="HardGamesWon" Type="Int32" />
46 </InsertParameters>
47 <SelectParameters>
48 <asp:ProfileParameter Name="playerid" PropertyName="hangmanplayeridString" />
49 </SelectParameters>
50 </asp:SqlDataSource>
View 2 Replies
View Related
Sep 20, 2006
I want to select one field from a table,but it should on some conditionswhich refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND....Should I use case "select sum(a.amount) from a,b,c,... wherea.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)from select b.field1 from select c.field2 from...."?And which case is moreefficiency?thanks!我想计算一个表中的某个字段的和，但此记录需在从多个 表中查询此记录是否满足特定的条件。那么我是用select ..from ...where ..and ..and..and ..and ..还是用select ..fromselect ..from select ..from ......？请问是哪一个效率高？谢谢！
View 2 Replies
View Related
Sep 1, 2006
Hello to everyone, I have this problem If I use this code when I try to update a record in my sql database It does not do anything. This is the code. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim myconn As New SqlConnection("Data Source=.;Initial Catalog=SistemaIntegral; user id=sa") Dim mycmd As New SqlCommand("presup", myconn) mycmd.CommandType = CommandType.StoredProceduremycmd.parameters.Add(New SqlParameters("@txtClave", SqlDbtype.int,4)).Value=txtClave.textmycmd.parameters.Add(New SqlParameters("@txtForig", SqlDbtype.varchar,50)).Value=txtFOrig.textmycmd.parameters.Add(New SqlParameters("@txtFmod", SqlDbtype.varchar,50)).Value=txtFmod.textmycmd.parameters.Add(New SqlParameters("@txtFeje", SqlDbtype.varchar,50)).Value=txtFeje.textmycmd.parameters.Add(New SqlParameters("@txtPorig", SqlDbtype.varchar,50)).Value=txtPorig.textmycmd.parameters.Add(New SqlParameters("@txPmod", SqlDbtype.varchar,50)).Value=txtPmod.textmycmd.parameters.Add(New SqlParameters("@txtPeje", SqlDbtype.varchar,50)).Value=txtPeje.text mycmd.parameters.Add(New SqlParameters("@txtClave", SqlDbtype.int,4)).Value=txtClave.text myconn.Open() mycmd.ExecuteNonQuery() myconn.Close() mycmd.Dispose() myconn.Dispose() End Sub This is the code for the procedure called "presup" in SQL 2000 Server.CREATE PROCEDURE [presup](@txtClave [int],@txtForig [varchar](50),@txtFmod [varchar](50),@txtFeje [varchar](50),@txtPorig [varchar](50),@txtPmod [varchar](50),@txtPeje [varchar](50),@txtIdPresupuesto [int])as Update [Presupuesto]Set [IdClave]=@txtClave,[orig]=@txtForig,[moda]=@txtFmod,[ejer]=@txtFEje,[origr]=@txtPorig,[modar]=@txtPmod,[ejerr]=@txtPejeWhere ([IdPresupuesto]=@txtIdPresupuesto)GO When I click the Update button of my webform it does not do anything. Please Help me I am new to Visual Studio.Net 2003. (2 weeks ago I started using Visual Studio)
View 1 Replies
View Related
