Tables Not Being Written Back To DB From Dataset. I'm Going Nutz!

Sep 26, 2006

ok. the problem: some tables are empty, so i can't be sure why some are updating at the DB and some arent.

I have manually picked thru every line of the xml that i'm reading into the dataset here, and it is fine, data is all valid and everything.

the tables i'm most worried about are bulletins and surveys, but they all have to be imported from my upload, the code steps thru just fine and I've been thru it a million times. Did I miss something in my dataadapter configuration?.
 'daBulletin
        '
        Me.daBulletin.ContinueUpdateOnError = True
        Me.daBulletin.DeleteCommand = Me.SqlDeleteCommand17
        Me.daBulletin.InsertCommand = Me.SqlInsertCommand17
        Me.daBulletin.SelectCommand = Me.SqlSelectCommand25
        Me.daBulletin.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblBulletin", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("BulletinID", "BulletinID"), New System.Data.Common.DataColumnMapping("ContractID", "ContractID"), New System.Data.Common.DataColumnMapping("Msg_Type", "Msg_Type"), New System.Data.Common.DataColumnMapping("DatePosted", "DatePosted"), New System.Data.Common.DataColumnMapping("Subject", "Subject"), New System.Data.Common.DataColumnMapping("B_Body", "B_Body"), New System.Data.Common.DataColumnMapping("I_Read_It", "I_Read_It"), New System.Data.Common.DataColumnMapping("DateRead", "DateRead")})})
        Me.daBulletin.UpdateCommand = Me.SqlUpdateCommand16


 
 
here is my merge function:

 Private Function Merge(ByVal sFilename As String, ByVal User As String) As String

        Dim connMerge As New SqlConnection(ConnectionString)

        Dim dsNew As New dsBeetleTracks

        Dim dsExisting As New dsBeetleTracks

        Dim strResult As String

        SetConnections(connMerge)

        Dim idc As New System.Security.Principal.GenericIdentity(User)

        Dim currentUser As BeetleUser = bu



        dsNew.ReadXml(sFilename)



        If currentUser.IsInRole("Admin") Or currentUser.IsInRole("QA") Then

            If dsNew.tblBulletin.Count > 0 Then

                daBulletin.Fill(dsExisting.tblBulletin)

                dsExisting.Merge(dsNew.tblBulletin)

                strResult += daHelipads.Update(dsExisting.tblBulletin).ToString + " Bulletins updated<br>"

            End If

        End If



        If dsNew.tblHours.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Admin")) Then

            daHours.Fill(dsExisting.tblHours)

            dsExisting.Merge(dsNew.tblHours)



            strResult += daHours.Update(dsExisting.tblHours).ToString + " hours updated<br>"

        End If



        If dsNew.tblHeliPads.Count > 0 Then

            daHelipads.Fill(dsExisting.tblHeliPads)

            dsExisting.Merge(dsNew.tblHeliPads)

            strResult += daHelipads.Update(dsExisting.tblHeliPads).ToString & " helipads updated "

        End If



        If dsNew.tblExpenses.Count > 0 Then

            daExpenses.Fill(dsExisting.tblExpenses)

            dsExisting.Merge(dsNew.tblExpenses)



            strResult += daExpenses.Update(dsExisting.tblExpenses).ToString + " expenses updated<br>"

        End If



        If dsNew.tblPersons.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then

            daPersons.Fill(dsExisting.tblPersons)

            dsExisting.Merge(dsNew.tblPersons)

            strResult += daPersons.Update(dsExisting.tblPersons).ToString + " persons updated<br>"

        End If



        If currentUser.IsInRole("Field") Then

            daSurveys.SelectCommand.CommandText = "exec Surveys_Field_Select"

            daSurveys.InsertCommand.CommandText = "exec Surveys_Field_Insert"

            daSurveys.UpdateCommand.CommandText = "exec Surveys_Field_Update"

        End If



        If dsNew.tblSurveys.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Field")) Then ' Or CurrentUser.IsInRole("Admin")) Then



            daSurveys.Fill(dsExisting.tblSurveys)

            dsExisting.Merge(dsNew.tblSurveys)



            strResult += daSurveys.Update(dsExisting.tblSurveys).ToString + " surveys updated<br>"

        End If



        If dsNew.tblSurveyChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then

            daSurveyChecks.Fill(dsExisting.tblSurveyChecks)

            dsExisting.Merge(dsNew.tblSurveyChecks)



            strResult += daSurveyChecks.Update(dsExisting.tblSurveyChecks).ToString + " survey checks updated<br>"

        End If



        If dsNew.tblTreatments.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Admin")) Then

            daTreatments.Fill(dsExisting.tblTreatments)

            dsExisting.Merge(dsNew.tblTreatments)



            strResult += daTreatments.Update(dsExisting.tblTreatments).ToString + " treatments updated<br>"

        End If



        If dsNew.tblInternalQC.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn") Or currentUser.IsInRole("Survey")) Then ' Or CurrentUser.IsInRole("Admin")) Then

            daInternalQC.Fill(dsExisting.tblInternalQC)

            dsExisting.Merge(dsNew.tblInternalQC)



            strResult += daInternalQC.Update(dsExisting.tblInternalQC).ToString + " internalqc updated<br>"

        End If



        If dsNew.tblTreatmentChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then

            Try

                daTreatmentChecks.Fill(dsExisting.tblTreatmentChecks)

                dsExisting.Merge(dsNew.tblTreatmentChecks)

                strResult += daTreatmentChecks.Update(dsExisting.tblTreatmentChecks).ToString + " treatment checks updated<br>"

            Catch dbex As DBConcurrencyException

                strResult += vbCrLf & dbex.Message

                For x As Integer = 0 To dbex.Row.Table.Columns.Count - 1

                    strResult += vbCrLf & dbex.Row.GetColumnError(x)

                Next

            End Try

        End If



        If dsNew.tblHeliPiles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Planner")CurrentUser.IsInRole("QA") Or CurrentUser.IsInRole("Admin") Or

            daHeliPiles.Fill(dsExisting.tblHeliPiles)

            dsExisting.Merge(dsNew.tblHeliPiles)



            strResult += daHeliPiles.Update(dsExisting.tblHeliPiles).ToString + " piles updated<br>"

        End If



        If dsNew.tblHeliCycles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' CurrentUser.IsInRole("Planner") Or Or CurrentUser.IsInRole("Admin")) Then

            daHeliCycles.Fill(dsExisting.tblHeliCycles)

            dsExisting.Merge(dsNew.tblHeliCycles)



            strResult += daHeliCycles.Update(dsExisting.tblHeliCycles).ToString + " cycles updated<br>"

        End If



        If dsNew.tblHeliTurns.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then 'CurrentUser.IsInRole("Admin") Or CurrentUser.IsInRole("Planner") Or

            daHeliTurns.Fill(dsExisting.tblHeliTurns)

            dsExisting.Merge(dsNew.tblHeliTurns)



            strResult += daHeliTurns.Update(dsExisting.tblHeliTurns).ToString + " turns updated<br>"

        End If



        If dsExisting.HasChanges Then

            dsExisting.Merge(dsNew)

        End If

        dsExisting.AcceptChanges()





        'duh.

        'If dsNew.HasChanges Then

        '    dsNew.AcceptChanges()

        'End If

        If dsExisting.HasErrors Then

            Dim bolError As Boolean

            Dim tempDataTable As DataTable

            bolError = True



            strResult += "<br>"



            For Each tempDataTable In dsExisting.Tables

                If (tempDataTable.HasErrors) Then

                    strResult += PrintRowErrs(tempDataTable)

                End If

            Next

        End If



        dsNew.Dispose()

        dsExisting.Dispose()

        connMerge.Close()

        'edebugging will only track strresult

        Dim fsError As New FileStream(Server.MapPath("./incoming/error.txt"), FileMode.Create, FileAccess.Write)

        Dim swError As New StreamWriter(fsError)

        swError.WriteLine("--==ERROR LOG==--")

        swError.WriteLine(Now.Date.ToShortDateString)

        swError.WriteLine("-----------------")

        swError.WriteLine(strResult)

        swError.Close()

        fsError.Close()



        Return strResult

    End Function

View 2 Replies


ADVERTISEMENT

Job Execution Information Not Written To System Tables

Jul 3, 2007

SS 2005 64Bit SP2 Hello Chaps Intermittent problem with the SQL Agent job history not getting written to the history table. Background:Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs. There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it. Stuff run to try to track the job:EXEC sp_help_jobactivity @job_name = 'MyJob' EXEC sp_help_jobhistory @job_name = 'MyJob' SELECT *FROM dbo.sysjobhistory WHERE job_id = 'MyJob GUID'The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null). sp_help_jobhistory had some historical records but nothing since mid last month. sysjobhistory correlated with sp_help_jobhistory as you would expect. Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June). We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it. We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will. Ta!

View 7 Replies View Related

Write DataSet Back To SQL

Sep 10, 2004

Hello everybody,
I have a dataset that i read from an xml file.
I need to import this database back in to sql..
Is there a easy way.. Or do i need to loop thorugh each record in the dataset to import it.

Thanks

Chris

View 2 Replies View Related

Pull Back Data Using A Dataset

Feb 19, 2008

Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
 
Could someone help me figure this out..
 Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]);
SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand();
comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet();
conn.Open();
 comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
 
comm.ExecuteNonQuery();myadapter.Fill(myset, "Order_Forms");
myset.AcceptChanges();
 
Can yo usee the problem???
 
 
 

View 7 Replies View Related

Getting Data From Dataset Back Into Database

Sep 7, 2006

I'm new at this so I apologize in advance for my ignorance.

I'm creating a website that collects dates in a calendar control (from Peter Blum). When the page containing the control loads it populates the calendar with dates from the database (that have previously been selected by the user). The user then can delete existing dates and/or add new dates.

I create a dataset when the page loads and use it to populate the calendar. When the user finishes adding and deleting dates in the calendar control I delete the original dates from the dataset and then write the new dates to the dataset. I then give execute the data adapter update command to load the contents of the dataset back into the database. This command involves using parameterized queries. For example the Insert command is:

Dim cmdInsert As SqlCommand = New SqlCommand("INSERT INTO Requests VALUES(@fkPlayerIDNumber, @RequestDate, @PostDate, @fkGroupID)", conn)

cmdInsert.Parameters.Add(New SqlParameter("@fkPlayerIDNumber", SqlDbType.Int))

cmdInsert.Parameters.Add(New SqlParameter("@RequestDate", SqlDbType.DateTime))

cmdInsert.Parameters.Add(New SqlParameter("@PostDate", SqlDbType.DateTime))

cmdInsert.Parameters.Add(New SqlParameter("@fkGroupID", SqlDbType.Int))

da.InsertCommand = cmdInsert

The update command is:

da.Update(ds, "Requests")

When I run the program I get the following error:

Parameterized Query '(@fkPlayerIDNumber int,@RequestDate datetime,@PostDate datetime,' expects parameter @fkPlayerIDNumber, which was not supplied.

I've used debug print to establish that the table in the dataset contains what it should.

I would be more than grateful for any suggestions.

View 3 Replies View Related

Dataset.Tables.Count=0 Where There Are 2 Rows In The Dataset.

May 7, 2008

Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.


ALTER PROCEDURE [dbo].[MyStorProc]

(

@Status smallint,

@RowCount int = NULL,

@FacilityId numeric(10,0) = NULL,

@QueueID numeric (10,0)= NULL,

@VendorId numeric(10, 0) = NULL

)

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF



If @Status = 0

BEGIN

SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END



View 4 Replies View Related

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

Feb 24, 2006

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

View 4 Replies View Related

Is It Possible To Just Restore Particular Tables From A Back Up Of A Database

Jan 29, 2007

Hi,
  I backed my sql server database on Fri at 3:15pm and then tried importing some tables individually from a different database and but that didnt work out too good, so is there a way i can just restore particular tables from the backup.
any help will be appreciated.
regards,
Karen

View 4 Replies View Related

SQL 2012 :: Join Tables And Only Pull Back Certain Values?

Oct 22, 2014

I am having problems joining these two tables and returning the correct values. The issue is that i have a work order table and a revenue table. I only want to return the sum of the revenue when the revenue comes after the work order date. That is simple enough, but it gets tricky when there are multiple work orders for the same ID. for those instances, we only want the sum of the revenue if it shows up post the work order date and if it is before any other work order date. So ID 312187014 should only have the 9-5 revenue from below, not the 7/7 or 8/6 revenue because the 8/7 work order date is after those revenue dates and thus will not have any revenue tied to it because there is a 9/3 work order that ties to the 9/5 revenue. Additionally the 412100368 ID has a 7/7 work order that ties to the 7/26 revenue, and the 8/7 work order will tie to the 8/23 and 9/20 revenue

--===== Create the test table with

CREATE TABLE #workorder
(
Id varchar(20),
wo varchar(10),
wodate datetime,
amount float
)
GO
CREATE TABLE #revenue

[code].....

View 2 Replies View Related

Pulling Data Back From 2 Tables - Join On Top 1 Record

Nov 7, 2013

I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.

however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)

Here's my code

SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID

WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID

[Code] ....

View 4 Replies View Related

Populate Dataset With Multiple Tables

Aug 23, 2006

Hi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks

View 2 Replies View Related

Dataset With Multiple Tables Is Not Getting Populated

Dec 25, 2006

Hi,
I have a stored proc which returns multiple result sets.  These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code.  My dataset will have 5 tables.  However when I run the code only 3 tables get populated and the remaining 2 gets no data. 
 I have seen the problem earlier and could not resolved it.  Please let me know if any one can help.
 
Thanks in advance
Rohit

View 3 Replies View Related

T-SQL (SS2K8) :: Union Of Two Tables Returned Back To Main Query

Aug 18, 2014

Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.

I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.

Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2

Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB

View 3 Replies View Related

How To Bring Back The Distinct Values In Single Column From Two Tables

Jul 20, 2005

12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim

View 1 Replies View Related

Reporting Services :: 2 Dataset Tables In One Matrix?

Dec 2, 2015

I am having difficulties with what seems to be a common issue. I want to produce a trial balance. The first row of the tablix would include fields: AccountNo, Description and startingbalance from a dataset called tb. Below that, I would like to draw from a dataset called Entries with multiple rows having fields: AccountNo, Desc1, Desc2, Debit, Credit and Balance.     Balance in the Entries table is already computed from the starting balance and the entries above.  There are many AccountNo's with corresponding entries.  

How do I do this?

View 4 Replies View Related

Code A Function To Return A Dataset In Which There Are Two Tables And Relationship

Aug 9, 2006

I used a function to create dataset as below:
 Public Function GetSQLDataSet(ByVal SQL As String) As DataSet
......
      MyConnection = New SqlConnection(MyConnectionString)
      MyCommand = New SqlCommand(SQL, MyConnection)
      MyDataSet = New DataSet
      MySQLDataAdapter = New SqlDataAdapter(MyCommand)
     MySQLDataAdapter.Fill(MyDataSet)
......
End function
It works fine.
How to code a function to return a dataset in which there are two tables and relationship?
 

View 1 Replies View Related

Error: Name DataSet.Tables Not Allowed . Problems With Inserting

Jul 13, 2007

string fileName = "d:\shiporder.xml";
DataSet dataSet =  new DataSet();
dataSet.ReadXML(fileName);
//connection string
string cmd = "INSERT INTO Orders (OrderID, OrderPerson) VALUES (dataSet.Tables[0].Row[0][0].ToString(),dataSet.Tables[0].Row[0][1].ToString);
SqlConnection con = new SqlConnection(conection string);
SqlCommand mycmd = new SqlCommand(cmd,con);
con.Open();
mycmd.ExecuteNonQuery();
con.Close();
it gives me this error
dataSet.Tables its not permitted in this context. valid expression are constants, constant expression, . Columns name are not permitted

View 2 Replies View Related

Generating Strong Typed DataSet From An SP That Returns Two Tables.

Dec 26, 2007

Hi,
 I have  a little question. I searched google, and could not find good answer for this one.
I have a stored procedure that returns two tables. Usually I generate a dataset out of a stored procedure by dragging it to the dataset.
When I drag this one it creates a DS with only one table, the first one.  How can I make it use both tables?
 
Thank you.

View 2 Replies View Related

SQL Server 2008 :: Dynamic Dataset And Tables In SSRS

Jul 27, 2015

Need to change a Excel report to SSRS.

Excel report has around 15 tables all with different columns.

Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.

Note -- All 15 tables have differnt columns list.

View 3 Replies View Related

Reportviewer - How To Link Multiple Tables Into A Single Dataset

Sep 13, 2007

Hi,

I want to design a report in which it will contain fields derived from 2 different stored procedures. I understand a 'table' can display data from a single dataset. How can i bind these two stored procedures into a single dataset so as when i click on the table and use its property 'DataSetName', to be able to select the dataset which holds all columns from stored proc 1 and stored proc 2. How can i link multible tables ( multible stored procedures with different column names in each one) into a single dataset to feed the report?

Thank you
George

View 1 Replies View Related

Integration Services :: How To Create New Dataset From Two Tables In SSIS

Jul 13, 2015

I have OLE Db data source (SQL Server 2008) with 5 rows. One of the column in claimID. Another data source is IBM DB2 Iseries database. The table in DB2 has 93 million rows. I need to get only rows from DB2 table where ClaimID matces to OLE DB datasource dataset. In fact its just inner join but in two different serves.  How do I create new dataset from these two tables in SSIS. I tried using Lookup transformation. I cannot use OLE DB as datasource for DB2. 

View 5 Replies View Related

Report Generation Using A Single Dataset, Which Combines Two Tables.

Jan 18, 2008

Hi,

I have an issue in generating the report in sql reporting services. I need to display a report in a table format. The datas of the table should be from two different sql tables. I have tried to write a stored procedure that returns two result sets from two different tables. As reporting services takes datas only from the first result set, i tried to write two different stored procedures each displays one result set. Then i have created two datasets with that two different stored procedure. Even then i cannot proceed as i was not able to use two different dataset in a single table because i was setting the datasetname to one dataset, when i try to retrieve the fields from another dataset i was able to retrieve only first and count values. Then i tried using sub reports. As sub reports for a dataregion(table) repeats for every row of the main report i was not able to fetch the correct datas. Atlast i have tried combining the query using join and wrote a single stored procedure. This stored procedure returns a single result set retrieves data from two tables satisfying the conditions. The issue i am facing with this is, the first table has only one row satisfying the condition and the second table has three rows satisfying the same condition, as i am using join query for the three rows returned by the second table the first table datas are getting duplicated for the rest of the rows in the second table. As I found using join query is the only resolution for the output which I need, and also I have to avoid the duplication of the records. Hence let me know for any solutions.

I have shown the sample datas that is duplicating which is indicated as bold. Phonenumber, Attemptdate and calloutcome are from first table and start time and endtime is from second table. As there are three different datas for the second table, first table datas are duplicated





Phone Number

Attempt Date

Call OutCome

Start Time

End Time


843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:08

15:12


843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:25

15:25


843-577-0800

2007-09-10 15:20:00.000

Contact with Prospect - Expressed Interest

15:26

15:27

Thanks for any help in advance


Thanks,
Sangeethaa

View 3 Replies View Related

Correct Syntax To Pull Back Duplicate Vendors Based On 6 Fields From Two Different Tables

Feb 20, 2015

I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:

select *
from VendTable1 a
join ( select firstname, lastname
from VendTable1
group by firstname, lastname
having count(*) > 1 ) b
on a.firstname = b.firstname
and a.lastname = b.lastname

I'm running into issues when trying to add the other table with the 4 other fields.

View 5 Replies View Related

SQL Server 2014 :: Insert Dataset Into Two Tables And Grabbing Identity From One For Other

Jan 2, 2015

Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.

I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.

Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.

What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.

I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.

View 9 Replies View Related

Query To Select Multiple Tables In Dataset Through ODBC Connection

Apr 10, 2007

Hi there,



I have a MS Access database (mdb) containing the following tables:



Crime

Criminal

CrimeCommitted

Hideout

CriminalType



The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:




Code Snippet



SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;







This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).



The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?

View 2 Replies View Related

RDLC Newbie Questions (hopefully)...connecting Dataset To Report That Contains Multiple Data Tables

Sep 12, 2007

Hi All,

We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).

I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.

I'm not having any problems creating basic reports (e.g. flat data).

However, I am having problems creating reports where there are related tables in a dataset.

The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.

I'm not having as much luck with RDLC.

I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report

A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.

Thank you so much (if, for nothing else, reading this post )

View 4 Replies View Related

Having Difficulty Setting Back Up To Back Up File Wihout Datetime Stamp SQL 2K

Apr 24, 2007

Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick

View 5 Replies View Related

Mirroring :: Principal Database Get Role Back After Being Back On Line

May 14, 2015

New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.

When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?

View 2 Replies View Related

Reporting Services :: Run Two Reports Back To Back Without Page Eject?

Jun 9, 2015

I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.

View 4 Replies View Related

Same UID Can't Be Written To Database

Dec 18, 2007

I'm having a database insertion problem. I make an entry to my registration form, write it to the database; then the next registration entry displays an error that the same UID can't be written to the database. I don't know where to start tracing this, but here is the stored procedure I use and some of the code that might generate it.
            @UserName nvarchar(128),            @Email nvarchar(50),            @FirstName nvarchar(25),            @LastName nvarchar(50),            @Teacher nvarchar(25),            @GradYr int    DECLARE @UserID uniqueidentifier    SELECT @UserID = NULL    SELECT  @UserID = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName
INSERT INTO [table name](UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr)     Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles cuwCreateUserWizard1.CreatedUser        strEmail = CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text        strUserName = CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower        strFirstName = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text        strLastName = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text        lngGradYr = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text        strTeacher = CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).Text    End Sub    Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles cuwCreateUserWizard1.CreatedUser        Dim cmd As New SqlCommand("sp_CreateUser", con)        cmd.CommandType = Data.CommandType.StoredProcedure        cmd.Parameters.AddWithValue("@UserName", strUserName)        cmd.Parameters.AddWithValue("@Email", strEmail)        cmd.Parameters.AddWithValue("@FirstName", strFirstName)        cmd.Parameters.AddWithValue("@LastName", strLastName)        cmd.Parameters.AddWithValue("@Teacher", strTeacher)        cmd.Parameters.AddWithValue("@GradYr", lngGradYr)        Using con            con.Open()            cmd.ExecuteScalar()            con.Close()        End Using        cmd.Equals(Nothing)    End Sub
Please let me know if any other info is needed to help determine what's wrong.

View 8 Replies View Related

How Can I Check When Something Is Written In A DB?

Oct 6, 1998

Hi,

i have following problem:

I have a DB with 4 tables in MSSQL6.5. In those tables write an NT service (4 threads for each table) 24h/day. I want to know somehow when i have no write in one of those tables for more than 1-2 hours (i dont want to querry those tables everytimes). MSSQL6.5 offer this kind of facilities? Or I can do this using stored procedures or something else?

Thank you very much,
Sebastian Bologescu

View 1 Replies View Related

How Do I Back-up &> Truncate &> Shrink &> Back-up SQL 2000

Jul 20, 2005

Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.

View 2 Replies View Related

Is My SProc Written Correctly?

Dec 31, 2005

I am very new to SQL server and I'm using stored procedures for my program. So far I wrote one tonight, that works just fine.
I haven't really written one before, but its kind of similar syntax since I know C++/C# and VB.
My question is, even though this works for what I need it do, is it written correctly? Can you see any problems with it, or would you have done it differently?
I want to make sure its done correctly, and it runs as fast as possible.
Thanks!
[pre]
CREATE PROCEDURE CreateNewUser
 @UserID   int out, @LoginID   nvarchar(30), @Password   nvarchar(30), @RegisterDate  smalldatetime, @LoginIDExists  nvarchar(30)AS /* Check to see if the loginID is already in use before attempting to save it.  We MUST have a unique loginID for each user */ SELECT @LoginIDExists = loginID FROM users WHERE loginID = @LoginID
 /* If we pulled a value from the database, then the loginID already exists, return with error code 1 */ IF (@LoginIDExists = @LoginID)    BEGIN   SELECT 1   RETURN  END
 ELSE BEGIN /* The loginID does not already exist, attemp to add the new user to the database. */ INSERT INTO users (  loginID,  loginpassword,  registerDate )  VALUES (  @LoginID,  @Password,  @RegisterDate )
 /* Grab the UserID for the new user. */ SELECT @UserID = @@identity
 /* return with error code 0 */ SELECT 0   RETURN  ENDGO
[/pre]

View 6 Replies View Related







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