This Code Does Not Update My Database,
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
ADVERTISEMENT
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
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 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
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
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
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
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
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 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
Aug 20, 2007
SQL Server Express update KB921896 fails to install with error code 00002b2. I've tried this update several times with no luck. HELP offered is no help. Can't find this error code, and there is no text with the code.
View 1 Replies
View Related
Feb 14, 2008
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
View 6 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Sep 13, 2006
Hi, I want to create a database setup on a server. I've scripted my database from sql server express. I've tested the code in the query window and it worked. When i pasted the same code in a sqlcommand command text....the debuger threw an sql exception....saying the sintax is wrong near keywords GO, USE, some forgein keys, and so on. Here is a chunk of the script.USE [master]GO/****** Object: Database [estate_management] Script Date: 09/13/2006 09:19:32 ******/CREATE DATABASE [estate_management] ON PRIMARY ( NAME = N'estate_management', FILENAME = N'D:MSSQLestate_management.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'estate_management_log', FILENAME = N'D:MSSQLestate_management_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[admin]( [admin_id] [int] IDENTITY(1,1) NOT NULL, [log_in_id] [varchar](20) NOT NULL, [password] [varchar](50) NOT NULL, CONSTRAINT [PK__admin__07020F21] PRIMARY KEY CLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 1) ON [PRIMARY], CONSTRAINT [IX_admin] UNIQUE NONCLUSTERED ( [admin_id] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]END Thanks for your time.
View 1 Replies
View Related
Nov 8, 2007
I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data?
Thanks
View 6 Replies
View Related
Nov 29, 2007
VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
View 16 Replies
View Related
Apr 26, 2008
What method would you suggest is the best way to deal with a website?
Say for example, writing SQL. Am I better using Stored Procedures and adding methods for accessing there or just writing the SQL inline. What do you think about this? Is it totally overkill to use stored procedures in a small website?
View 6 Replies
View Related
Jun 6, 2008
Hi Friends, Please any one help me in writing the code in thisI have 3 fileds1)empId-->textbox2)Roles-->4 radiobuttons(MN,PL,TL,CL)3)Responsibilities--->3 check boxes(profile,register,change password)I have 3 tablestable1:RoleMasterTable:roleID roleName1 MN2 PL3 TL4 CLtable2:ResponsibilityMasterTableresId resName1 profile2 register3 changepasswordtables3MasterTable:empId roleId resIdthe form conatains the empId,Roles(radiobuttons),Responsibilies(chechboxes)for example I have to enter empId=1select one radiobutton that is "PL"and I can select check boxes profile,register and manyafter submitting the button("submit) these details has to store in the database table i.e master tableI have to show the final o/p like this in the tableMasterTableempId roleId resId1 2 1,2Please any one help in this I am beginner in database c# programmingPlease its very helpful to me............Thanks & RegardsGeeta
View 7 Replies
View Related
Dec 30, 2005
i have a piece of code in an adp that is currently behind a form but i could put it in a module and make it public if i need to. how can I use DTS to run this piece of code? which connection and task could i use. can this even be done? i have little experiance with DTS.
thanks.
justin
View 1 Replies
View Related
Mar 6, 2008
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True")
Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase)
conDatabase.Open()
comDetach.ExecuteNonQuery()
conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
Thanks!
View 1 Replies
View Related
Jul 6, 2006
Hello.
I think its silly question but i expect some solution .
Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.
Please, if somebody have some solution then guide me in detail.
Thanks in adv.
View 12 Replies
View Related
Apr 11, 2007
I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.
Thank you
View 4 Replies
View Related
Aug 2, 2007
i want to return a Log of my stored procedure to my asp app, how do i do that i have problems with that i tried Dim log As String = cmd.Parameters("@Log").ToString but that gave "An SqlParameter with ParameterName '@Log' is not contained by this
SqlParameterCollection."my code below SQL 1 ALTER PROCEDURE dbo.RevertDB
2 (
3 @Log varchar(MAX) = NULL OUTPUT
4 )
5
6 /* Reverts Database to original "Clean" State */
7 AS
8 SET NOCOUNT OFF
9 DECLARE @RowsInDB AS int
10 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + '<br />'
11
12 /* *** Disable Constraints ***
13 ALTER TABLE Booking NOCHECK CONSTRAINT ALL
14 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL
15 ALTER TABLE PC NOCHECK CONSTRAINT ALL
16 ALTER TABLE Platform NOCHECK CONSTRAINT ALL*/
17
18 /* *** Start Deletes *** */
19 DELETE FROM Booking
20 SET @Log = @Log + 'Clear Table Booking - Done' + '<br />'
21 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking)
22 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
23
24 DELETE FROM InventoryPC
25 SET @Log = @Log + 'Clear Table InventoryPC - Done' + ''
26 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)
27 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
28
29 DELETE FROM PC
30 SET @Log = @Log + 'Clear Table PC - Done' + '<br />'
31 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC)
32 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
33
34 DELETE FROM Platform
35 SET @Log = @Log + 'CLear Table Platform - Done' + ''
36 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)
37 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
38
39 /* *** Enable Constraints ***
40 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL
41 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL
42 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL
43 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL*/
44
45 SET @Log = @Log + '*** End Truncates ***' + '<br />'
46 /* *** End Truncates *** */
47
48 /* *** Start Insert Platform *** */
49 SET @Log = @Log + 'Start Insert Platform' + '<br />'
50
51 EXEC dbo.InsertPlatfrom 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …'
52 EXEC dbo.InsertPlatfrom 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance. Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'
53 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …'
54 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace. Finder: Browse your files like you browse your music with Cover Flow. Time Machine: See how your system looked on any given day and restore files with a click. Website: http://www.apple.com/macosx/leopard/features/'
55 EXEC dbo.InsertPlatfrom 'Red Hat Linux', 'Some description for Red Hat Linux over here …'
56
57 SET @Log = @Log + 'Rows In Platform: ' + CAST((SELECT COUNT(PlatformID) FROM Platform) AS varchar(10)) + '<br />'
58 /* *** Start Insert PC *** */
59 SET @Log = @Log + 'Start Insert PC' + '<br />'
60
61 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int
62 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition')
63 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate')
64 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger')
65 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard')
66 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux')
67
68 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP
69 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista
70 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista
71 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard
72 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista
73 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger
74 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat
75
76 SET @Log = @Log + 'Rows In PC: ' + CAST((SELECT COUNT(PCID) FROM PC) AS varchar(10)) + '<br />'
77
78 /* *** Start Insert Inventory *** */
79 SET @Log = @Log + 'Start Insert Inventory' + '<br />'
80
81 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int
82 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One')
83 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two')
84 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three')
85 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1')
86 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2')
87 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger 1')
88 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux 1')
89
90 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One'
91 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two'
92 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three'
93 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1'
94 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2'
95 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One'
96 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One'
97
98 SET @Log = @Log + 'Rows In Inventory: ' + CAST((SELECT COUNT(InventoryID) FROM InventoryPC) AS varchar(10))
99
100 RETURN @Log
View 11 Replies
View Related
