Hi! could you please check my code on adding record on my DB.. Im using SQL Server included on VWD.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Request.mdf;Integrated Security=True;User Instance=True"
Dim sqlcon As New Data.SqlClient.SqlConnection(con)
Dim sqlcmd As New Data.SqlClient.SqlCommand("Insert into uRequest (eticket, ename, edept, edetails, ejobpend, edate) values (@eticket,@ename,@edept,@edetails,@jobpend,@edate)")
sqlcon.Open()
sqlcmd.Parameters.Add("@eticket", Data.SqlDbType.Char, 10).Value = "11-0010-06"
sqlcmd.Parameters.Add("@ename", Data.SqlDbType.Char, 100).Value = User.Identity.Name
sqlcmd.Parameters.Add("@edept", Data.SqlDbType.Char, 50).Value = DropDownList1.Text
sqlcmd.Parameters.Add("@edate", Data.SqlDbType.DateTime).Value = Date.Now
sqlcmd.Parameters.Add("@edetails", Data.SqlDbType.Text).Value = TextBox1.Text
sqlcmd.Parameters.Add("@ejobpend", Data.SqlDbType.Text).Value = TextBox2.Text
sqlcon.Close()
End Sub
I've check the Data on my DB but nothing has been added. nwy, how can u create a confirmation message if the record has been successfully added.
Thanks and sorry for the trouble.
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row? thanks
I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.
I'm kind of new to SQL, but I'm learning as much as I can.
I created a web form that sends a new record to a SQL 2005 table I setup.
This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.
I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.
My Database is called Engineering
The table is called ESSPartNumLog
And I have the following Columns that I would like to send in my email..
ESSSequence (PK,int, not null)
MaterialType (nvarchar(255, null)
ESSPrefix (nvarchar(255, null)
PartDescription (nvarchar(255, null)
Project (nvarchar(255, null)
PM (nvarchar(255, null)
Any ideas, or can you point me in the right direction?
Please help me somebody solve my problem with my first :o trigger: ALTER TRIGGER partner_update ON dbo.partner FOR UPDATE AS INSERT INTO partner (name) SELECT name FROM deleted UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted) *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system. Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con SystemID int ContactID int
Table Contact ContactID int Identity ContactName ...
hi i have a question how can i delete last added row. I have 2 tables .source and destination . I take a 1 row from source table , do someoperation on it and save to destination table . after succesfull written Iwant to delete added row from source table.. i'm using a coursors. the mainproblem is : is there any function to check which row was last added. Now Iam doing it using select * from destionation where (and necessaryconditions). but if destination table will be 100000000 rows for example ittakes too much time... Is there another possibility to do it ???please helpMarcin Wolkuwolku
In developing a VWDE project I added 2 columns to the User table in the ASPNETDB.mdf database. I can see the columns in the Data Definition and I can see the values I added when I Show Table Data but I cannot access them with a SQLDataSource control? The SQLDataSource shows all of the columns in that table except the ones I added. Any suggestions? Thanks.
Here's the deal.I've been working on an Intranet application for my clients, and todayI went and installed the first prototype. It's a fairly standard thing- VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.I restored the database onto their db server, and installed theapplication on their intranet server, and made the necessary changes tothe web.config and other configuration files.I logged on with no problem. The default form is a Search/Finder formwith no default recordset. I fired up the filter to return all therecords and the thing crashed. The filter form wouldn't get any data(there *was* data in the database - I checked that!). So I set a traceon the database and this is the SQL that was being sent to the server(note that this SQL is dynamically built and is being sent in-line):SET FMTONLY OFF; SET FMTONLY ON;SELECT fldID, fldReferenceNumber,fldRevision, fldPartNumber, fldIndentNumber, fldDescription,fldBatchNumber, fldCreatedDate FROM tblMyTable SET FMTONLY OFF;Note those SET FMTONLY OFF; SET FMTONLY ON;...SET FMTONLY OFF;directives. These direct SQL Server to return only metadata, and notdata rows. The SQL which SHOULD have been sent, and which I have justcaptured in the SQL Profiler on MY system here is:SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROMtblMyTableWHERE (((tblMyTable.fldReferenceNumber LIKE N'%')))Again, note that the WHERE clause is missing from the first lot of SQL.Does anyone have any ideas about why this might be happening? I thinkit might have something to do with ownership/permissions, but Icouldn't find anything different about this database than the system onwhich this new application was modelled, which has no such problems.Edward
Hello all, i have a question i've beenk thinking for a little bit andcant seem to come up with a solution. What i want to do is add a uniquecounter to a resultset in query. For exampleSELECT ColumnA, ColumnB, ColumnC, ColumnDFROM AnyCombinationOfJoinsOrTablesWHERE SomeConditionIsTruewThe query above will produce a resultest of 4 columns.'A1', 'B1', 'C1', 'D1''A2', 'B2', 'C2', 'D2''A3', 'B3', 'C3', 'D3'......'An', 'Bn', 'Cn, 'Dn'Now what i want to do is add a unique identifier to every row. Theeasiest way would be to add a counter integer so the resultset wouldlook like1, 'A1', 'B1', 'C1', 'D1'2, 'A2', 'B2', 'C2', 'D2'3, 'A3', 'B3', 'C3', 'D3'......n, 'An', 'Bn', 'Cn, 'Dn'Is there a way to achieve this in T-SQL without using cursors.Thanks,Gent
I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.
Code Snippet ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO] @Question char(80) AS BEGIN
SELECT Qry_Questions.Question , Qry_Questions.Date , Qry_Questions.response , B.Total FROM Qry_Questions INNER JOIN Qry_Sales_Group ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code INNER JOIN ( Select COUNT(qq.response)as Total, Question, Date, response FROM Qry_Questions qq Where qq.response in ('Yes','No') GROUP by qq.[Question] , qq.Date,qq.response ) B ON Qry_Questions.Date = B.Date AND Qry_Questions.Question =B.Question and Qry_Questions.response=B.response WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@Question GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total ORDER BY Qry_Questions.Question, Qry_Questions.Date
END SET NOCOUNT OFF
Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.
Code Snippet ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals] (@Region_Key int=null,@QuestionCode char(5),@Question char(80)) AS BEGIN
SELECT Qry_Questions.Question , Qry_Questions.Date , Qry_Questions.response , B.Total FROM Qry_Questions INNER JOIN Qry_Sales_Group ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code INNER JOIN ( Select COUNT(qq.response)as Total, Question, Date, response FROM Qry_Questions qq Where qq.response in ('Yes','No') GROUP by qq.[Question] , qq.Date,qq.response ) B ON Qry_Questions.Date = B.Date AND Qry_Questions.Question =B.Question and Qry_Questions.response=B.response WHERE Qry_Questions.[Response Type]='YesNo' AND REGION_KEY=@Region_Key AND LEFT(Qry_Questions.[Question Code],2)IN (@QuestionCode) AND Qry_Questions.Question=@Question GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total ORDER BY Qry_Questions.Question, Qry_Questions.Date
Here is a new one for me. I added the /3GB switch to the boot.ini, restarted the server and now receive a number of messages when the OS tries to load.
Explorer.exe - Application Error The application failed to initialize properly.
If I click OK the server sits on a blue screen with no information on the screen.
The other messages are the result of me trying to do anything within Task Manager, with the exception of rebooting the server. These messages make sense since Explorer.exe had a problem.
The server has the following configuration.
HP DL580 Windows Server 2003 Enterprise Edition - SP2 SQL Server 2000 Enterprise Edition - SP3a 14GB Memory (4) dual core procs PAE is enabled in the boot.ini
If I remove the /3GB the OS starts without a problem. Any suggestions?
I'm running vb 2088 express with SQL express. I have limited VB and very limited SQL knowledge.
I have a VB app that inserts candidate data into the SQL table (called front) all works fine.
I now have a second VB app (separate from the above VB app) that runs at the same time; it does some extra work on the candidate data.
My question is when a new candidate is added to the SQL table, how I can get the second VB app to automatically know that a new record has been inserted (and the record key) and then start the VB subroutine.
I'm unsure if I should look at triggers or stored procedures (or something else) for this task.
Any help or pointers to additional resources would be appreciated.
I ran the code below that adds a column to an SQL table.
I refreshed Data Sources, but, the new column does not appear.
I refreshed Server Explorer and the new column does not appear.
I tried running the code again and I got:
"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."
so the column did actually get added, but, I can't see it.
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Try
Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Patient.mdf;Integrated Security=True;User Instance=True")
Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"
Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)
I have a createStudent SProc. When this is called it calls a createContact SProc. This in turn calls a Create Address Stored procedure. Now when I create the address, an Identity column automatically creates a new Identity for me. I need to return this value back to the calling stored procedure. I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably? I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...
I am using Reporting Services 2000 and therefore VS 2003 and am having a problem editing the T-SQL query in the data view of Visual Studio.
Basically if I manually edit the T-SQL query or touch anything in the designer view of the query I get odd characters added in the T-SQL and the query no longer works. I have tried re-installing VS2003 and RS 2000 but it's still happening.
FYI I also have SQLServer 2005 and VS 2005 on my machine as all applications we develop are in .Net 2.0 and 3.0. We only use VS2003 for RS.
Howdie y'all, I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one.... CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO I actually would like to get value of the UserId column for the newly added record. Can anyone of you folks help me with this? Cheers, Wes
For some reason, there are extra trailing spaces being added to all my data as it is placed in the db. I am collecting information from, processing a TRIM-like function in javascript, and then again in the ASP.net code before it is being placed in the db.
The extra spaces are causing problems with my application.
We're replicating columns in a table from 7.0 server to SQL2k server. Once we've got the data on SQL2k, we were able to take advantage of DTS that was not available in version 7.0.
I recenlty added a column to the article and changed the DTS accordingly (transformation columns). However, I'm getting this error ever though I added the new column in the DTS.
------- The number of columns in the bcp file does not match what is defined in the DTS package. Regenerate the package.
-----
I've recreated the package with no luck. This is becoming urgent as it's supposed to go into production soon.
i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05
my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one
1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.
DROP TABLE aMgmt.Employee GO EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT' GO
Hi.. I created a publication on my server and I just clicked the box to include all of the stored procedures. If I add a new procedure, will that procedure also be included? Or will I have to modify the publication for each SP I create? Thanks! -Greg
I have a db that grew 8.2GB in one week and don't understand why.There are three tables added to the db daily. I calculated thespaceused by each of the three tables for a period of two weeks. Thetatal amount of data added to the db for the three daily tables overthe past two weeks was about 4MB yet the db grew approximately 8.2GB.WHY?Can someone please tell me what I should look at so that I canunderstand what is going on?
We are running into problems on our development environment with extraparameters being added on to a stored procedure call. Instead of theexpected stored procedure call, the trace shows something like this:declare @P1 intset @P1=NULL<<expected stored procedure call>> , @P1 output, <<repeat of firstthree sp parms>>select @P1The developer has checked the code, and I have checked the SP - bothseem to match what is in production (which works fine). The databasesare on the same server, and the apps are running on seperate webservers.I'm guessing this may be some sort of configuration issue with ADO,SQL, or something else - has anyone run into something similar tothis? Thanks!Dave
I am getting this error when clicking-through in Report Builder on a report using a report model as the data source - An item with the same key has already been added.
I searched for the error but came up empty in the SSRS space. Does anyone out there know when this is generated and where I need to look in my model/properties to see what I have set incorrectly?
A little background/detail - It's a simple report against two fields (type & #count) in one entity (customer) in a (guessing here) medium sized model (~16 transactional/fact entities, ~40 lookup entities, ~15 what I am calling domain entities). The entity has 5 DefaultDetailAttributes, one Identifying. The report has 2 fields type (a lookup role) and #customers in the data area (so I can click it to test the click through). Then I click the # Customers to look at the detail for those X customers and after a long while and no queries against the DB (I have profiler running) I get this error.
