MSAccess/SQL lookup table for match field based on sum of current table.field

I've been battling this for the last week with many attempted solutions. I want to return the unique names in table with the sum of their points and their current dance level based on that sum. Ultimately I want compare the returned dance level with what is stored in the customer table against the customer and show only the records where the two dance levels are different (the stored dance level and the calculated dance level based on the current sum of the points.

The final solution will be a web page using ADODB connection to MSAccess DB (2013). But for starters just want it to work in MSAccess.

I have a MSAccess DB (2013) with the following tables.

PointsAllocation
CustomerID  Points
100            2
101            1
102            1
100            1
101            4

DanceLevel
DLevel           Threshold
Beginner            2
Intermediate        4
Advanced            6

Customer
CID   Firstname Dancelevel1
100   Bob      Beginner
101   Mary     Beginner
102   Jacqui   Beginner

I want to find the current DLevel for each customer by using the SUM of their Points in the first table. I have this first...

SELECT SUM(Points), CustomerID  FROM PointsAllocation GROUP BY CustomerID

Works well and gives me total points per customer. I can then INNER JOIN this to the customer table to get the persons name. Perfect.

Now I want to add the DLevel from the DanceLevel table to the results where the SUM total is used to lookup the Threshold and not exceed the value so I get the following:

(1)     (2)     (3)        (4)
Bob     3      Beginner   Intermediate
Mary    5      Beginner   Advanced

Where...
(1) Customer.Firstname
(2) SUM(PointsAllocation.Points)
(3) Customer.Dancelevel1
(4) Dancelevel.DLevel

Jacqui is not shown as her SUM of Points is less than or equal to 2 giving her a calculated dance level of Beginner and this already matches the her Dancelevel1 in the Customer table.

Any ideas anyone?

Entity Framework Intermittent Timouts On SaveChanges

We have been getting intermittent timeouts on calling SaveChanges with entity framework, I decided to turn on the Database.Log on DbContext to try and work out what was going on, the output was as follows:

Opened connection at 5/9/2015 4:56:57 PM +00:00

Started transaction at 5/9/2015 4:56:57 PM +00:00

UPDATE [Search].[IndexingStatuses]
SET [DateTimeLastUpdated] = @0, [IndexStores_IndexStoreId] = @1
WHERE ([CustomerId] = @2)

-- @0: '5/9/2015 4:03:57 PM' (Type = DateTime2)

-- @1: '2' (Type = Int16)

-- @2: '1' (Type = Int32)

-- Executing at 5/9/2015 4:56:57 PM +00:00

-- Completed in 1 ms with result: 1



Failed to commit transaction at 5/9/2015 4:57:13 PM +00:00 with error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Closed connection at 5/9/2015 4:57:13 PM +00:00

This is very strange, it's a simple update and as you can see the transaction is started at 4:56:57, the update takes 1 ms but for some reason there is a timeout at 4:57:13 which is only 16 seconds later which is less than our timeout limit anyway.

Does anyone know why we are intermittently getting these?

create two foreign key from one table sql server

i am creating user table like code PK Table

CREATE TABLE TblUser (UserId int identity primary key ,Name varchar(20))

AND creating one more table FK Table

CREATE TABLE TblAnnouncements (Id int identity primary key ,Announcements 
varchar(20),CreatedBy INT FOREIGN KEY REFERENCES TblUser (USERID)  ON  DELETE  
CASCADE, UpdatedBy INT FOREIGN KEY REFERENCES TblUser (USERID)  ON DELETE  SET NULL)

i am getting this error

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK__TblAnnoun__Updat__60A75C0F' on table 'TblAnnouncements' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

how to maintain createdby and updatedby in an table....

can you give suggest me any other way to do that ?

thank u.

Execute complex SQL queries in VB.NET

I'm having trouble executing a complex SQL query in VB.NET using OledbDataAdapter. This SQl query works fine in the W3school's SQL Tryit Editor. Following is my existing VB.NET code and is there any way to execute that kind of a SQL query directly in VB.NET or can anyone change this query to work with VB.NET with same results?.

    Dim con As New OleDb.OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = C:\database.mdb"
    con.ConnectionString = dbProvider & dbSource
    con.Open()
    sql = "With query1 as (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='0'),Query2 as (SELECT Val,DateAndTime FROM [FloatTable] where TagIndex='1')select query1.val as 'TT348',Query2.val as 'TT358',Query2.DateAndTime as 'DateAndTime' From query1,Query2 where query1.DateAndTime=Query2.DateAndTime"
    da = New OleDb.OleDbDataAdapter(sql, con)

    da.Fill(ds, "Log")
    con.Close()

    DataGridView1.DataSource = ds

When I run this code snippet it gives an error telling

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Greatly appreciate any help and thank you...

SQL Server Update one row blocking

I use Sql Server and have simple table:

Person(ID bigint,Name nvarchar(255))

It contains 1000 rows(for example). I'm using SQL query:

Update Person
Set Name = 'Jason' where Id = 100

I want to understand - how Sql Server will do read/write locking. Will it block one row (which i'm updating) or it'l block all Person table?

Will the situation change if i will use query?

Update Person
Set Name = 'Jason' where Name = 'Piter'

Find all Duplicates from a Table In Sql Server

I have a table which contains two columns of Urls and it contains duplicates.I'm looping through each row but have no idea to check duplicates.I want to send oldurl as parameter to check duplicates and get them.Can anyone help me

OldUrl     NewUrl    
---------------------
Google     Google.in
Amazon     Amazon.in
300r       300r/en-us
300r       300r/en-us

Weird 'returned data that does not match expected data length for column' error while the expected length is much bigger - SQL SERVER 2012

In my project I am rebuilding my Access database to an SQL Database. So to do this I am transferring the Access DATA to the SQL Database. I made sure they both have the same structure and the Access fields are modified correctly in the SQL database.

For most of the data this works. Except for 1 table. This table gives me the following weird error message:

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server 'OPS_JMD_UPDATE' returned data that does not match expected data length for column '[OPS_JMD_UPDATE]...[OrderStatus].Omschrijving'. The (maximum) expected data length is 100, while the returned data length is 21.

So here some more information about both the Access and SQL field/column:

• Access type: Short text
• SQL type: nvarchar(MAX)
• Access column data in it: Normal letters and & - % é + € . , : being the 'not normal ones'.
• A few empty Access records (which is allowed)
• A total of 135314 record in the Access table

Iv'e set the SQL datatype to nvarchar(MAX) so that the field can never be to small, this didn't seem to help though..

*The OPS_JMD_UPDATE is the linked Access database

What causes this problem? Is it because some characters aren't allowed or..?

Prepare single where clause

I have the following table with two fields namely a and b as shown below:

create table employe
(
    empID varchar(10),
    department varchar(10)
);

Inserting some records:

insert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X'),
             ('A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X');


select * from employe;

empID   department
------------------
A101    Z
A101    X
A101    Y
A102    Z
A102    X
A103    Z
A103    Y
A104    X
A104    Y
A105    Z
A106    X

Note: Now I want to show the employee who is only and only belongs to the department Z and Y. So according to the condition the only employee A103 should be displayed because of he only belongs to the department Z and Y. But employee A101 should not appear because he belong to Z,X, and Y.

Expected Result:

If condition is : Z and Y then result should be:

empID
------
A103

If condition is : Z and X then result should be:

empID
------
A102

If condition is : Z,X and Y then result should be:

empID
------
A101

Note: I want to do it in the where clause only (don't want to use the group by and having clauses), because I'm going to include this one in the other where also.

asp.net error Object reference not set to an instance of an object when I retrieve records from database

I'm trying to retrieve records from SQL database, but the returned value is NULL. I debugged it and I discovered that the returned DataTable is null

Here is the code of Database class

    public DataTable ModelOfBookProfileElements(int id)
    {


        String Query = string.Format("SELECT Book_Name,Book_Author,Book_Description FROM Book WHERE Book_ID = {0}",id);

        return RunQuering(Query);


    }



    public DataTable RunQuering(String Query)
   {

       table = new DataTable();
       cmd.CommandType = CommandType.Text;
       cmd.CommandText = Query;
       table.Load(cmd.ExecuteReader());

       return table;

   }

the code of in .ascx.cs file

      protected void Page_Load(object sender, EventArgs e)
    {
        Book book = new Book();
        if (Session["Student"] == null)
        {
            Response.Redirect("LoginSite.aspx");
        }
        else
        {
            DataTable bookdata = new DataTable();
            string id = Request.QueryString["id"];

             int index = Convert.ToInt32(id);
           bookdata = book.GetModelOfBookProfileElements(index);
           if (bookdata.Rows.Count > 0)
           {


               for (int i = 0; i < bookdata.Rows.Count; i++)
               {
                   Label1.Text = bookdata.Rows[i][0].ToString();
                   Label2.Text = bookdata.Rows[i][1].ToString();
                   Label4.Text = bookdata.Rows[i][2].ToString();
               }
           }


        }
    }

in book class

    public DataTable GetModelOfBookProfileElements(int id)
   {

       DB = Database.DB_Object();
       DB.OpenConnection();



       DB.ModelOfBookProfileElements(id);

       DB.CloseConnection();
       return table;
   }

How to use Wildcard Search Parameterized?

public DataTable Search()
{
    DataTable dt = new DataTable();
    SqlCommand searchcommand = new SqlCommand(
        "Select * from [ITEM MASTER] WHERE Item_Description LIKE  '%' @Item_Description  '%' ", con);

    searchcommand.Parameters.Add("@Item_Description", SqlDbType.NVarChar).Value = search;
    SqlDataAdapter da = new SqlDataAdapter(searchcommand);
    da.Fill(dt);
    return dt;
}

Here is my code. I need help on how to create a parameterized search using Object Oriented Programming

connecting to local database (sql server ) in c++

I am new to database connection in sql server using c++. Here I am trying to connect to localdb instance which I created in my sqlserver management studio, and the name of database is "filingdb", server is "(localdb)\filing". I copied the code from some other site and trying to query, the problem which I am facing is here as I have shared the error list:

Error   8   error C2440: '=' : cannot convert from 'System::Data::SqlClient::SqlCommandBuilder *' to 'System::Data::SqlClient::SqlCommandBuilder ^' 143 1   cvtest
Error   6   error C2440: '=' : cannot convert from 'System::Data::SqlClient::SqlDataAdapter *' to 'System::Data::SqlClient::SqlDataAdapter ^'   139 1   cvtest
Error   4   error C2440: 'initializing' : cannot convert from 'System::Data::DataSet *' to 'System::Data::DataSet ^'    126 1   cvtest
Error   2   error C2440: 'initializing' : cannot convert from 'System::Data::SqlClient::SqlConnection *' to 'System::Data::SqlClient::SqlConnection ^'  125 1   cvtest
Error   3   error C2750: 'System::Data::DataSet' : cannot use 'new' on the reference type; use 'gcnew' instead  126 1   cvtest

'System::Data::SqlClient::SqlCommandBuilder *' this is the major issue which iam facing

I have tried other way too, but I am but still getting the same problem

Error   12  error C2440: '=' : cannot convert from 'System::Data::SqlClient::SqlCommandBuilder *' to 'System::Data::SqlClient::SqlCommandBuilder ^' 140 1   cvtest
Error   10  error C2440: '=' : cannot convert from 'System::Data::SqlClient::SqlDataAdapter *' to 'System::Data::SqlClient::SqlDataAdapter ^'   136 1   cvtest
Error   6   error C2440: 'initializing' : cannot convert from 'System::Data::DataSet *' to 'System::Data::DataSet ^'    126 1   cvtest
Error   3   error C2440: 'initializing' : cannot convert from 'System::Data::SqlClient::SqlConnection *' to 'System::Data::SqlClient::SqlConnection ^'  125 1   cvtest
Error   5   error C2750: 'System::Data::DataSet' : cannot use 'new' on the reference type; use 'gcnew' instead  126 1   cvtest
Error   11  error C2750: 'System::Data::SqlClient::SqlCommandBuilder' : cannot use 'new' on the reference type; use 'gcnew' instead 140 1   cvtest
Error   2   error C2750: 'System::Data::SqlClient::SqlConnection' : cannot use 'new' on the reference type; use 'gcnew' instead 125 1   cvtest
Error   9   error C2750: 'System::Data::SqlClient::SqlDataAdapter' : cannot use 'new' on the reference type; use 'gcnew' instead    136 1   cvtest

Can any one please offer advice on how to solve this problem, any other method of connection, and can any one suggest any good link for future use reg connecting db using sql server? Thanks in advance

SQL Server Cursor Converting INT to Date

I have a column where the date is stored as an integer. This is something that we cannot change because it is a part of an older ERP system. If I could change the column type to date or datetime I would.

The dates are stored as yymmdd

I am trying to run a cursor to update off of this column to only grab rows where the date equals yesterday's date. My cursor below. Any advice?

DECLARE @ID NVARCHAR(50)
DECLARE @D1 NVARCHAR(50)
DECLARE @D2 NVARCHAR(50)
DECLARE CUR CURSOR FOR
    SELECT IMITNO, IMITD1, IMITD2 FROM [AS400].S062F7AR.APLUS83FDS.ITMST
        WHERE ISDATE(CONVERT(CHAR(6), IMLMDT)) >= DATEADD(DAY,-1,GETDATE());

OPEN CUR
FETCH NEXT FROM cur INTO @ID, @D1, @D2
WHILE (@@FETCH_STATUS = 0)
BEGIN

    UPDATE ProductBase
        SET new_AS400Description = @D1 + ' ' + @D2 
                WHERE ProductNumber = @ID

FETCH NEXT FROM CUR INTO @ID, @D1, @D2
END
CLOSE CUR
DEALLOCATE CUR

same date format for MYSQL and MSSQL

I have a insert function that insert data to server. there is ACTION_DATE column that keep the insert date of data. ...

insert into mytable(.., .., ACTION_DATE) VALUES(.., .., NOW())

But in my application database connection is dynamic.Default connection is MySQL and my insert function works fine (because I code it based on MySQL)

But when database changed to sql server, In MySQL its NOW(), But in SQL server it looks like getdate()..

insert into mytable(.., .., ACTION_DATE) VALUES(.., .., GETDATE())

but not exactly.. because I need both of dates as same format. so getdate() includes miliseconds that I don't need.. And also I don't want to change the insert statement or anything in code. How can I achieve this ?

T-SQL trigger that checks if airplane seats are taken

I have a question due to an assignment i am working on at the moment. I need to make a trigger that checks if an airplane seat is taken before a customer can be inserted into the table. I have the following Trigger so far:

CREATE TRIGGER CheckIfSeatIsUnique
ON PassagierVoorVlucht
AFTER insert, update
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN
    SET NOCOUNT ON

    BEGIN TRY

    IF EXISTS (SELECT 1 FROM PassagierVoorVlucht P Join inserted I on P.vluchtnummer=i.vluchtnummer Where P.stoel = I.stoel)
    BEGIN
        RAISERROR('The chosen seat is taken', 16, 1)
      END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
    END CATCH
END

The problem i have, is that the trigger checks if the seat is taken AFTER the insert was done, So the seat will always be taken no matter what.

Is there some way to check if the seat is taken before the insert is done?

thanks in advance

SQL permissions based on record value (if column X = Y for user1 allow Inserts/Updates etc.)

I need to run queries as a "user" which is a record in a table, with permissions based on a record value of that user.

I have a database with a tUsers Table, such as:

ID    Username       Email         Role
1      Pieman       mail.com       Admin
2     Cakedude     mail.co.uk   Receptionist
3      Muffin        gh.com        Other

I need to have it so only "users"/records with "Role" of "Admin" can view and edit the table, and "Receptionist" view it etc.

I know of and use GRANT for permissions, but don't know how to run a query as a user based on a table record and to have the permission only GRANTED if that users' role is "Admin"

So if I have:

USE DB1;
GRANT SELECT ON OBJECT::tUsers TO Admins;
GO 
SELECT * FROM tUsers

How do I make that run as say tUser with ID 1, and the GRANT if the users' role = "Admin"

I'm sure I've seen this done before.

I'm fairly new and still learning the correct terminology, so if this is a duplicate question, or is essentially just describing an sql Function sorry.

SQL Server 2008 R2: Tricky query - Finding certain rows in segments of rows

I don't know how to explain this in a general way, so allow me to give an example.

select ParentId, Id, Number
from SomeTable
order by ParentId, Id

gives

ParentId Id     Number
2997    1145445 400
2997    1145449 400
2997    1145577 400
2997    1146518 405
2999    1145470 400
2999    1145502 400
2999    1145504 400
3016    1145633 400
3016    1145636 400
3016    1145677 400
3016    1145686 405
3037    1145885 400
3037    1145906 405
3037    1145922 400
3037    1145925 400
3162    1147324 400
3162    1147327 400
3162    1147329 400
3162    1147332 400
3162    1147334 405
3162    1147339 400
3162    1147341 405
3162    1147345 406
3162    1147347 410

(this is just a subset of the real data, but enough to explain the problem).

The problem:

A query (as elegant and efficient as possible, preferably without sub-queries, cursors etc.) that selects exactly all rows with Number = 400 but with the following columns added:

Id_405: The Id of the row with Number 405 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 405. Id_405 is null if there is no such 405.

Id_406: The Id of the row with Number 406 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 406. Id_406 is null if there is no such 406.

Id_410: The Id of the row with Number 410 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 406. Id_410 is null if there is no such 410.

With the above example data, the query would return:

ParentId    Id  Number  Id_405  Id_406  Id_410
2997    1145445 400     null    null    null
2997    1145449 400     null    null    null
2997    1145577 400     1146518 null    null
2999    1145470 400     null    null    null
2999    1145502 400     null    null    null
2999    1145504 400     null    null    null
3016    1145633 400     null    null    null
3016    1145636 400     null    null    null
3016    1145677 400     1145686 null    null
3037    1145885 400     1145906 null    null
3037    1145922 400     null    null    null
3037    1145925 400     null    null    null
3162    1147324 400     null    null    null
3162    1147327 400     null    null    null
3162    1147329 400     null    null    null
3162    1147332 400     1147334 null    null
3162    1147339 400     1147341 1147345 1147347

Please help!

best practice Executing Multiple SQL Server stored procedures from c# with minimum round trips to DB Server

I had a windows service running in the background that need to execute about 1000 sql parametrized update query every N minutes ,So I want to do this in the best practice way , one that a code reviewer won't blame me for using . I had a look at multiple posts here , also here and I wonder if i can just write a large sql text query in my code .Will this lead to my code smells bad to a Microsoft code reviewer ? thanks

Appointment Timeslots in SQL

I have a clinic table which contains:

CREATE TABLE tClinic
(   clinic_id INTEGER IDENTITY(1,1) NOT NULL,
    clinic_name CHAR(30) NOT NULL,
    CONSTRAINT tClinic1 PRIMARY KEY(clinic_id)
);

Doctor table

CREATE TABLE tDoctor
(   doctor_ID INTEGER IDENTITY(1,1) NOT NULL, 
    staff_id INTEGER NOT NULL,
    specialisation CHAR(40) NOT NULL,  
    clinic_id INTEGER NOT NULL, 
  available_time TIME NOT NULL, 
    password CHAR(7) NOT NULL,
    CONSTRAINT tDoctor1 PRIMARY KEY(doctor_ID),
    CONSTRAINT tStaff4 FOREIGN KEY(staff_id) REFERENCES tStaff(staff_id),
    CONSTRAINT tClinic2 FOREIGN KEY(clinic_id) REFERENCES tClinic(clinic_id)
);

Appointment Table

CREATE TABLE tAppointments 
(   appointment_id INTEGER IDENTITY(1,1) NOT NULL, 
    doctor_ID INTEGER NOT NULL,
    patient_id INTEGER NOT NULL,
    date_of_appointemnt DATE NOT NULL,
    time_of_appointment TIME NOT NULL, 
    CONSTRAINT tAppointments1 PRIMARY KEY(appointment_id),
    CONSTRAINT tDoctor2 FOREIGN KEY(doctor_ID) REFERENCES tDoctor(doctor_ID),
    CONSTRAINT tPatient2 FOREIGN KEY(patient_id) REFERENCES tPatient(patient_id)
);

Now I am unsure of how to implement so that each time slot of appointment is be default 30 minutes. I need it display dates and time slots for each of the doctors working at the clinic...

SQL Update row column with random lookup value

I am trying to update a lead table to assign a random person from a lookup table. Here is the generic schema: TableA (header), ID int, name varchar (30)

TableB (detail), ID int, fkTableA int, (foreign key to TableA.ID) recordOwner varchar(30) null other detail colums..

TableC (owners), ID int, fkTableA int (foreign key to TableA.ID) name varchar(30)

TableA has 10 entries, one for each type of sales lead pool. TableB has thousands of entries for each row in TableA. I want to assign the correct recordOwners from TableC to and even number of rows each (or as close as I can). TableC will have anywhere from 1 entry for each row in tableA or up to 10.

Can this be done in one statement? It doesn't have to be. I can't seem to figure out the best approach for speed. Any thoughts or samples are appreciated.

Unable to connect to SQL Server 2012 Express using osql or other 3rd party client

I installed SQL Server 2012 Express, and is able to successfully connect using SQL Server Management Studio and create databases.

Screenshots:

However, when I try to connect to through the osql client, I get the following error:

I also notice that there are no protocols listed inside the SQL Server Network Interfaces section of SQL Server Configuration Manager, though the SQL Native Client 11.0 section do have the protocols. Not sure, if this behaviour has anything to do with the issue seen:

Convert nvarchar to date and Get data between two date

I'm collecting data between two dates 01/12/2014 and 31/12/2014 but my data type is nvarchar.

Is my query right?

SELECT * 
FROM customer 
WHERE date >= CONVERT(datetime, '01/12/2014', 105) 
  AND date <= CONVERT(datetime, '31/12/2014', 105)

Result

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Can anyone solve this problem?

How do I add sqljdbc_auth.dll to my project build path?

I have a project that connects to msSQL Server, the project is exported as a war file then run in Apache, but when I try to connect to the SQL Server it gives me this error. com.microsoft.sqlserver.jdbc.AuthenticationJNI WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path. - See screenprint below

I've added the dll to the jre native library in eclipse, I've added it to the bin and lib folders of the jre folder in program files, I've added it to the eclipse.ini file with java -Djava.library.path= and I've also tried the -djava line in cmd prompt. Where am I going wrong.

Compare Two Dates in Between SQL Query Using DateTimePicker Jquery Calender in Asp.Net C#

Compare Two Calender Date Use Between Command

Compare Calender Step by Step Snap Shots

How to to Open the XML from Database to Windows Form

In SQL table I have XML data. I want to open that XML in windows forms application on Button Click.

   private void button2_Click(object sender, EventArgs e)
    {
        string s = textBox1.Text;
        con.Open();
        SqlCommand cmd1 = new SqlCommand("select XMLfrom XMLTable where ID='" + textBox1.Text + "'", con);
        SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
        con.Close();
        DataSet ds1 = new DataSet();
        da1.Fill(ds1);
        gv_InputXML.AutoGenerateColumns = true;
        gv_InputXML.DataSource = ds1;
    }

When I write this I could not get any thing in DS.

How can I read XML from DB?

Storage options for win 8/10 universal app?

Assuming I want to develop a Win 8/10 universal app eg calendar, the user has a two devices tablet/phone.

How can my calendar share a local SQL database?

I don't want to maintain or administer an azure service or any other remote DB service ie AWS, VPS running SQL Express etc.. which is overkill for such a basic scenario.

I have considered SQLite and dumping the DB file in the users MS OneDrive folder but as we know this could result in sync/lock issues.

So using a purely self contained & free model, how can my two devices share this basic SQL data?

SQL Installation command from vb.net code

I am using following command through a batch file to install my required instance of SQL server. Problem is, when it runs, statement is shown on screen and .bat file is open for view. How can I run this statement through my vb.net application?

start /wait SQLEXPR32and64.EXE /qb username="MyName" companyname="COName" addlocal=ALL disablenetworkprotocols="2" instancename="ERPTesting" SECURITYMODE="SQL" SAPWD="abc123"

Thanks

SQL Query Help - Explanation Below

Stuck on how to query this result set.

A       B       C
162     0       NULL
162     1       NULL
162     2       NULL
163     0       000
163     1       000
163     2       000
164     0       000
164     1       NULL
164     2       NULL

I want to show only these results

A       B       C
162     0       NULL
162     1       NULL
162     2       NULL
164     0       000
164     1       NULL
164     2       NULL

I do not want to return results if all of an unique instance of column A has a value. So, in this example the new result set has removed 163 from the result set as all rows containing 163 had a value (Which was 000). The others show because one instance of the row contains NULL, and all rows unique to it should display.

I did try nested queries and playing around with GROUP BY, but haven't been able to crack it.

Any advice?

Thank you.

declare scalar variable exception while populating datagridview with data from ms sql

I am trying to put data from an sql query into datagridview, but when I try to run the program I am getting the exception

must declare scalar variable @cathedra

Here is the code:

string connectionString = "user id=bogdan_db; password=1234;server=localhost; Trusted_Connection=yes; database=cafedrascience; connection timeout=30";
        string sql = @"select *
                    from researc r inner join research_cafadra rc on r.id = rc.researc_id
                    inner join cathedra c on c.id = rc.cafadre_id
                    where c.name like @Cathedra;";

    using (var connection = new SqlConnection(connectionString))
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@Cathedra", SqlDbType.VarChar, 50).Value = comboBox1.Text.ToString();
        connection.Open();
        command.ExecuteNonQuery();
        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connectionString); //c.con is the connection string
        DataTable table = new DataTable();
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
        DataSet ds = new DataSet();
        dataAdapter.Fill(ds);
        dataGridView1.ReadOnly = true;
        dataGridView1.DataSource = ds.Tables[0];

Where I am mistaking?

SSIS take oracle variable and use them in a ssis resulset

Hi I have an execute sql task in one of my ssis package. I need to check if some of my tables have values inside them and store the result in a variable.I then need to use those variables later on in my package. My question is how do I assign those oracle variable from my sql task to my user variable in an ssis package.

I have something that look like this:

Variable
s5 NUMBER 
variable s6 number 
variable s7 number 
begin
select count(*) into s5 from GC_somethin where rownum < 100;
select count(*) into s6 from GC_somethingabc where rownum < 100;
select count(*) into s7 from GC_dddcccc where rownum < 100;
END;
select s5 AS 's51' from dual;

I have also fix my resultset to single row and in result set I have avariable name like s51 and the name of the variable I assigned to that. IT gives my the following error:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "TSERVADD_AFTER": "Exception from HRESULT: 0xC0015005".

Do you have any ideas how to fix this?

How to duplicates in database using SQL Server

I have a database where I have data and I would like to get all those duplicate values (I meant which appears twice).

This is my code

DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM RewriteTable where OriginalUrl like '%@rowValue%'");
db.AddInParameter(cmd, "original", DbType.String, rowValue);
DataSet ds = db.ExecuteDataSet(cmd);
return ds;

But it's not showing any duplicates even if not present

SQL Server : there are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key 'FK'

I've been trying to solve this error for several minutes but I don't know what am I missing from table definition.

The code for the tables are below:

Table Autocare:

CREATE TABLE [dbo].[Autocare] 
(
     [IDAutocar]    NUMERIC (18, 0) NOT NULL,
     [IDTipAutocar] NUMERIC (18, 0) NOT NULL,

     PRIMARY KEY CLUSTERED ([IDAutocar] ASC, [IDTipAutocar] ASC),

     CONSTRAINT [FK_Autogari_TipAutocar] 
        FOREIGN KEY ([IDTipAutocar]) 
        REFERENCES [dbo].[TipAutocar] ([IDTipAutocar])
);

Table Curse:

CREATE TABLE [dbo].[Curse]
(
    [IDCursa] NUMERIC (18, 0) NOT NULL PRIMARY KEY,
    [IDTraseu] NUMERIC (18, 0) NOT NULL, 
    [Data] TIMESTAMP NOT NULL, 
    [IDCompanie] NUMERIC (18, 0) NOT NULL, 
    [NrLocuri] NUMERIC (18, 0) NOT NULL, 
    [IDAutocar] NUMERIC (18, 0) NOT NULL, 

    CONSTRAINT [FK_Curse_Trasee] 
       FOREIGN KEY ([IDTraseu]) 
       REFERENCES [Trasee]([IDTraseu]), 
    CONSTRAINT [FK_Curse_Companii] 
       FOREIGN KEY ([IDCompanie]) 
       REFERENCES [Companii]([IDCompanie]), 
    CONSTRAINT [FK_Curse_Autocare] 
       FOREIGN KEY ([IDAutocar]) 
       REFERENCES [Autocare]([IDAutocar])
)

When I try to execute the second script I get the following error (and I know is something related to the relationship between tables) and I don't figure out where the problem might be. I'm a newbie into C# and SQL Server, so please forgive if I asked a stupid question.

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Autocare' that match the referencing column list in the foreign key 'FK_Curse_Autocare'.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

SQL Server: Conversion failed when converting the nvarchar value 'na' to data type int

Getting the above mentioned error in SQL.

I have tried below coding but nothing is working for me. Any suggestions??

cast(fieldvalue as nvarchar)

How to capture and store network packets using C#?

I am sorry if the question seems vague. However, I am interested in this project which I found in the link below

http://ift.tt/1GVe5kx.

If I am correct, I think this application captures and stores the network packets into a SQL database. But I am not sure, if I can capture it using C#

I would love an explanation because this is an awesome project and I know it would be a good challenge for me

Thanks All

SQL Server and Entity Framework don't save changes

I have the following issue: I created a SQL Server LocalDB instance and created a simple table called player. Then I added an ADO.Net Entity Framework Data Model (database to code) and instantiated the POCO class Player.

Then I added the instance to the DbContext's Player property and I executed the SaveChanges() method - with no effect at all.

Maybe I took the wrong SQL provider (cause there are 2 SQL Server providers: localdb and SQL Server)?

That are the properties of my .mdf file (the db):

Provider: .NET Framework-Datenanbieter für SQL Server
Type: Microsoft SQL-Server
Connection string: Data Source=(LocalDB)\v11.0;AttachDbFilename="C:\Users\Me\documents\visual studio 2013\Projects\MsSQLSample\MsSQLSample\myLocalDb.mdf";Integrated Security=True

I created the database like this:

New Element --> Service-based Db.

Then I added a table and set the primary key as Identity ad NOT Null etc...

I don't know why but none of the instances I added to the contexts property was taken by the db.

using (var db = new PlayerContext()) 
{ 
    Table table = new Table() 
    { 
        IpAddress = "test";
    }; 

    db.Table.Add(table); 
    db.SaveChanges(); 
}

EDIT: Thats how I made a new localdb-instance

Thats how I made a new table:

Thats how I use the EntityFramework

Error SQL query

I have two table a house table and a flat table

When I did a SQL filter

I get a false result, gold, I have filled fields.

CREATE TABLE IF NOT EXISTS biens_appartements (   bien_id int(10) unsigned NOT NULL AUTO_INCREMENT,   bien_date_modif datetime DEFAULT NULL,   bien_categorie_id int(10) unsigned DEFAULT NULL,   bien_nombre_pieces varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_nombre_chambres varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_surface_habitable varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_surface_carrez varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_surface_sejour varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_exposition_sejour_id int(10) unsigned DEFAULT NULL,   bien_etage varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   bien_nombre_etages varchar(50) COLLATE latin1_general_ci DEFAULT NULL,   etc ......   bien_notes text COLLATE latin1_general_ci,   PRIMARY KEY (bien_id),   KEY bien_id (bien_id),   KEY bien_categorie_id (bien_categorie_id) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4500 ;

and SQL

SELECT biens_maisons.bien_nombre_pieces AS BM, biens_appartements.bien_nombre_pieces AS BA, offres.offre_reference_publique , offres.offre_ville , offres.offre_id , offres.offre_type_id , offres.offre_bien_id , offres.offre_codepostal , offres.offre_prix_public , offres_photos.photo_fichier, types_biens.type_libelle FROM offres LEFT JOIN biens_appartements ON biens_appartements.bien_id = offres.offre_bien_id AND offres.offre_type_id = 1 LEFT JOIN biens_maisons ON biens_maisons.bien_id = offres.offre_bien_id AND offres.offre_type_id = 2 LEFT JOIN biens_terrains ON biens_terrains.bien_id = offres.offre_bien_id AND offres.offre_type_id = 3 LEFT JOIN offres_photos on offres_photos.photo_offre_id = offres.offre_id LEFT JOIN types_biens on types_biens.type_id = offres.offre_type_id

WHERE offre_diffusion = 1 AND offres.offre_dossier_statut_id = 1

AND offres.offre_ville = 'PARIS'

AND biens_maisons.bien_nombre_pieces = 4 IS NOT NULL

or biens_appartements.bien_nombre_pieces = 4 IS NOT NULL

GROUP BY offres_photos.photo_offre_id

SqlDataReader stopped when the value in database is NULL

I'm new to vb and I'm creating a project where I can edit a account so far I'm having trouble with it. The .read function stopped when the value in my database is NULL

ElseIf ButtonChoice = 2 Then
            txtIDnumber.Text = val
            Dim EditFamilyProfile As New SqlCommand("Select * From [186].[dbo].[familyInfo] WHERE pFnumber = @pFnumber", Connection)
            EditFamilyProfile.CommandType = CommandType.Text
            EditFamilyProfile.Parameters.AddWithValue("@pFnumber", txtIDnumber.Text)
            Dim drEdit As SqlDataReader
            drEdit = EditFamilyProfile.ExecuteReader
            With drEdit
                .Read()
                txtLastName.Text = .Item(1)
                txtFirstName.Text = .Item(2)
                txtMiddleName.Text = .Item(3)
                txtExt.Text = .Item(4)
                txtAddress.Text = .Item(5)
                cmbGender.Text = .Item(6)
.close()
            End With
        End If

It stopped in txteExt.text because it has a NULL value. There is no error shown. Its just go to the code:

  ButtonChoice = 2
        Dim obb As New frm_FamilyReg
        obb.val = Me.txtIDnumber.Text()
        obb.Show()
        Me.Close()

How to create connection with the database in another computer using a SQL Server database?

I am creating a network based application client will be using the data from the database which is in server computer so I want to create a SqlConnection with the database on the server computer so could you please help me with this. If any confusion with the question please ask.

Which technology has better future?

I'm not sure, if this is a right place to ask this question or not. But, I wanted to know the scope of SAP Data Service when compare to PL/SQL or T-SQL. Which one has better future ?

PS: Please move this question to appropriate website instead of marking close. Thank you.

Access 2013 with Azure SQL - Dynamic IP

I have created a simple Access 2013 database that is linked via ODBC to an Azure SQL server. Due to the limitations of Azure SQL (must declare IP addresses) and the security risks involved of opening the IP range to encompass all IP's, what is the simplest way to have the Access database talk to the Azure SQL server with unknown IP addresses without opening up all the IP's? I would like to keep this all in Azure if possible.

Is there a way to create a simple web layer in which the Access database will speak to, thus I only need to open the IP address for the web layer? If so, will I need to change how the Access database functions? There are some SQL functions that change the database directly when users change/add information...

I have done a little research and it appears I must host my own web layer, but how could I set it up in Azure to work with the existing Access database?

looking for master/child table data base on condition?

I have a 2 tables likes below,

DECLARE @MASTER TABLE (MAST_ID INT, MAST_NAME NVARCHAR(10), IS_ACTIVE CHAR(1))
INSERT INTO @MASTER VALUES (1, 'MAST1', 'A'), (2, 'MAST2', 'I'), (3, 'MAST3', 'A'), (4, 'MAST4', 'A')
SELECT * FROM @MASTER

DECLARE @CHILD TABLE (CHD_ID INT, MAST_ID INT, CHD_NAME NVARCHAR(10), IS_ACTIVE CHAR(1))
INSERT INTO @CHILD VALUES (1, 1, 'CHD1', 'I'), (2, 2, 'CHD2', 'A'), (3, 4, 'CHD3', 'A'), (4, 4, 'CHD4', 'I') 
SELECT * FROM @CHILD

1. need all active master table data, which are active and for those there are active child data

2. need all active child for above master data

Output should be like below Thanks for help.

Can't get stored procedure result

In my stored procedure with a cursor, I got the results but when I try to get that value into table I can't get it. Please help me.

Here is my C# code

string sql = "ForTaxReport";
tdsTPDPay.TaxReportRow dataRowLog = new tdsTPDPay.TaxReportDataTable().NewTaxReportRow();

if (base.Transaction != null)
{
    base.Command = new SqlCommand(sql, base.Connection, base.Transaction);
}
else
{
    base.Command = new SqlCommand(sql, base.Connection);
}

base.CommandType = CommandType.StoredProcedure;
base.Parameters.AddWithValue("@PayrollDate", GetNull(date));
base.Parameters.AddWithValue("@ID", GetNull(ID));

base.OpenConnection();
SqlDataAdapter adp = new SqlDataAdapter(base.Command);
tdsTPDPay.TaxReportDataTable dt = new tdsTPDPay.TaxReportDataTable();
adp.Fill(dt);
return dt;

SQL Server : Sum over a field changed after adding a left join

I have a sample query like this:

;WITH view1 AS (
    SELECT 1 AS id, 1 AS pId, 'a' AS pName, 1 AS kind 
    UNION ALL SELECT 2, 1, 'a', 2 
    UNION ALL SELECT 3, 1, 'a', 1 
    UNION ALL SELECT 4, 2, 'b', 2 
    UNION ALL SELECT 5, 2, 'b', 2 
    UNION ALL SELECT 6, 2, 'b', 1 
), view2 AS (
    SELECT 1 AS id, 1 AS pId, 'a' AS pName, 5 AS price
    UNION ALL SELECT 3, 3, 'c', 3 
    UNION ALL SELECT 6, 2, 'b', 3 
)   
SELECT 
    ISNULL(v2.pId, v1.pId) AS pId,
    ISNULL(v2.pName, v1.pName) AS pName,
    SUM(CASE v1.kind WHEN 1 THEN 1 ELSE 0 END) AS countKind1,
    SUM(v2.price) AS sumPrice
FROM 
    view1 v1
    LEFT OUTER JOIN
    view2 v2 ON v1.id = v2.id
GROUP BY
    ISNULL(v2.pId, v1.pId),
    ISNULL(v2.pName, v1.pName)

Its result is:

pId | pName | countKind1 | sumPrice
----+-------+------------+-----------
1   | a     | 1          | 5
2   | b     | 1          | 3 
3   | c     | 1          | 3

But I wanted this result after adding ISNULLs and sumPrice:

pId | pName | countKind1 | sumPrice
----+-------+------------+-----------
1   | a     | 2          | 5
2   | b     | 1          | 3 
3   | c     | 0          | 3

SQL server 2012 CPU usage spikes to 100%

I am using MSSQL server 2014 in AWS and the AWS image configuration is 4 cores and 16 GB RAM(m3.xlarge). I am running the following simple query,

select * from user_table where user_id = '10'

user_table contains 1000k records, user_id is primary key. When the above simple query is executed from my application through EJB hibernate, the CPU spikes to 10% for a moment and again it backs to normal.

So my use case is, 100 users will concurrently try to hit the application, so in a fraction of second 100 times the above query will try to execute in a fraction of second. So the CPU usage spikes to 100%. Once all the query execution is completed the CPU usage is back to normal at 1%.

• Why it so? whether I need to increase my AWS instance type?
• What should I have to do in-order to make SQL server to handle 100 or more concurrent hits without making high CPU usage? If my query is so complex then there might have a chance to get spike but my query is simple and straight forward.
• Is there is any bench mark metrics available for SQL server 2014?
• Any solution to make support for concurrent hits with SQL server by low CPU usage consumption?

Execute stored procedure using variables entered and send output as parameter to save

What I am trying to do

I have a stored procedure in SQL Server that takes in a first and last name as parameters and outputs an ID. The goal is to execute this stored procedure and send it to my database on save.

What I have done

I have imported my stored procedure via the model browser and made a function import. I selected a collection of scalar string because i read this ("Scalars: it means stored procedure will return single value of selected type like binary, Boolean, byte etc. )

This is the code that I am working on right now but I do not think I am on the right even after making many variations.

public ActionResult Create([Bind(Include="id,firstname,lastname")] school_client school_client)
{
    using (var ctx = new invent_stuEntities())
    {
        var idParam = new SqlParameter {
                     ParameterName = "@firstname", school_client };

        var idParamB = new SqlParameter {
                     ParameterName = "@lastname", 
                     Value = school_client.lastname };

        //Get student id based on first and last name  
        var studentlist = ctx.Database.SqlQuery<Assign_studentid>("exec Assign_studentid @firstname,@lastname", idParam, idParamB).ToList<Assign_studentid>();

        if (ModelState.IsValid)
        {
            school_client.clientid = studentlist; // Send to database
            //  school_client.clientid = "TEST001";
            db.school_client.Add(school_client);
            db.SaveChanges();

            return RedirectToAction("Index");
        }
    };

    return View(housing_client);
}

The problem

I cannot get to pass the result of my stored procedure into my database like how I hardcoded it here

school_client.clientid = "TEST001";

Would like some assistance in editing my code so that my procedure can create run on insert.

Thanks

How to manipulate a returned datetime value in T-SQL

I need to determine the average amount of time it takes our fulfillment team to process orders.

I plan to simply count the number of "seconds" between the "PaidDateUtc" and "ShippedDateUtc" value for each order, and then get the average.

My simplified query (minus the date range filter) is like this:

SELECT
    dbo.Shipment.ShippedDateUtc - dbo.[Order].PaidDateUtc AS TimeToFulfill
FROM
    dbo.[Order] INNER JOIN
    dbo.Shipment ON dbo.[Order].Id = dbo.Shipment.OrderId

The returned value looks like this:

1900-01-01 00:25:27.263

Which means: 1 day, 1 hour, 25 minutes, and 27 seconds

My question is: How can I manipulate it so that the returned value looks like this:

91527     --(total of seconds)

Generate unique, non-serial order numbers (similar to Amazon)

How do I generate unique, non-serial order numbers (similar to Amazon, flipkart) in Sql Server?

SQL Server Instance Public Website Security

Is it best practices to separate databases that are used by public website applications into their own database instance, from those databases that have PII info and company IP info? The idea being for security reasons. I have around 30 databases that I am migrating to a new environment and I am finding this to be the hardest decision to make. Does anyone have any advise?

sum of same year and same Id query

my table is :

     Year    Type Id     Amt
    2015-16    1         100     
    2015-16    1         50 
    2015-16    2         20 
    2016-17    2         100    
    2016-17    2         10 

I need a result with sum of Amt depend upon group by 2 column year & typeId :

  2015-16  1 150
  2016-17  2 130

i can easily sum with single group by column but don't know how can i sum with multiple columns. please let me know if anyone know

Need to get data from multiple tables with same foreign key

I have table-1 with

InjID - (PK) and Name

Table 2 with Tid (RefK) ever InjId(Table1) and Name

Table 3 with ID (RefK) refer again InjID (Table1) and Name

There is no link between Table 2 and Table 3.

I have data as follows:

Table1

________________
InjID     Name
----------------
1         xxxx
2         bbbb
3         cccc
4         yyyy

Table 2

--------------
TID      Name
--------------
1        A3434
1        R5678
2        G6789

and Table 3

-------------
ID     Name
-------------
2      89T
2      78P
3      66J

I want the results like this:

--------------------------
injid    Table1-name Table2-name table3-name
--------------------------------------------
1         xxxx        A3434        null
1         xxxx        R5678        null 
2         bbbb        G6789        89T
2         bbbb        null         78P
3         cccc        null         66J
4         yyyy        null         null

How to get data by a condition and sum of every row with another condition?

I have a table with 5 columns ReportId, Date, Area, BuildingName, Amount.

Sample data looks like this :

-------------------------------------------------------
ReportId |    Date    | Area | BuildingName | Amount
-------------------------------------------------------
   1     | 01/01/2013 |  S1  |    A1-01     |   5
   2     | 01/01/2013 |  S1  |    A1-03     |   5
   3     | 01/01/2013 |  S2  |    A1-05     |   4
   4     | 02/01/2013 |  S2  |    A1-05     |   7
   5     | 02/01/2013 |  S2  |    A1-03     |   9
   6     | 03/01/2013 |  S1  |    A1-03     |   2
   7     | 04/01/2013 |  S2  |    A1-02     |   6
   8     | 05/01/2013 |  S1  |    A1-01     |   7
   9     | 06/01/2013 |  S1  |    A1-02     |   5
   10    | 06/01/2013 |  S1  |    A1-05     |   8
   11    | 06/01/2013 |  S1  |    A1-07     |   5

I need to write a query to get the result like this :

-----------------------------------------------------
    Date    | Area   | BuildingName | Amount | Sum
-----------------------------------------------------
 01/01/2013 |    S1  |    A1-01     |   5    |  12
 01/01/2013 |    S1  |    A1-03     |   5    |  7
 01/01/2013 |    S2  |    A1-05     |   4    |  11

Date value passed as a parameter to the query.

"Area", "BuildingName", "Amount" are records which have the same "Date".

"Sum", is Sum of All "Amount" in the table where has the same "Area" And "BuildingName" in the result of query.

I searched much, but I can't get anything about this ...