Help with Query

I have a table which holds system variables for a program I am writing.

It looks like this...

(AccountName VarChar(128), SettingName VarChar(20), Sequence Int, Setting VarChar(255))

There is a 'ALL USERS' AccountName and then individual users account names... such as DOMAIN\USERNAME. I Would like to Select all of the records containing 'ALL USERS' but overide the 'All USERS' entries with the individual user name settings where the SettingNames are equal.

AccountName SettingName Sequence Setting
ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
ALL USERS RECENTFILESTODISPLAY 1 16
DOMAIN\USERRECENTFILESTODISPLAY 1 100

I would like...

ALL USERS DRIVESEARCH 1 D
ALL USERS DRIVESEARCH 2 G
ALL USERS DRIVESEARCH 3 H
ALL USERS DRIVESEARCH 4 I
ALL USERS DRIVESEARCH 5 J
ALL USERS JOBAUTHVIEWER 1 \\Path
ALL USERS OBJMDLWORD 1 wpfiles
DOMAIN\USERRECENTFILESTODISPLAY 1 100

Notice Recentfilestodisplay is down to one instead of two.
Is there a way to query this?something like this should do it shouldn't it?

select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where AccountName = @.SpecificName
Union
select AccountName, SettingName, Sequence Int, Setting
from tblSystemVariables where
AccountName = 'ALL USERS'
and SettingName not in
(select SettingName from tblSystemVariables where AccountName = @.SpecificName)

where tblSystemVariables is your table and @.SpecificName is your specific domain/user combo.

Does that work?

Not really that elegant I know...|||Thank You! Works Great

Ended up with:

SELECT AccountName, SettingName, Sequence, Setting
FROM LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER'
UNION
SELECT AccountName AS AccountName1, SettingName AS SettingName1, Sequence AS Sequence1, Setting AS Setting1
FROM LoadSystemSettings
WHERE AccountName = 'ALL USERS' AND SettingName NOT IN
(SELECT SettingName
FROM dbo.LoadSystemSettings
WHERE AccountName = 'DOMAIN\USER')

Was Missing the "NOT IN" Clause.

Kent|||Glad to be of service. Have a good one.

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken