Awkward question

We have a program built around MS SQL Server, front end is Access project with a lot of VBA code (I think). My programmer moved on and it's unlikely that my superiors can afford a replacement, so I'm trying to learn enough to complete our project. (I work for the USMC, but I'm a manager/Family Therapist.)

Here's my problem/question:

I have a report which can provide details based upon a Command/Battalion selection. I want the user to be able to choose the the Command and a filtered list of associated Battalions for that Command. The choices then populate the report with appropriate data.

I've used a stored procedure to acquire the data for the report and it works beautifully . . . if you have memorized the Commands and Battalions so that you get the right data. I do not understand how to present the Command and Battalion information to user and then pass the user selection to my procedure to get the right data.

Any assistance is very gratefully received!

Peter MorrisThere is more than one way to skin this cat, but none of them are very difficult. Here is a general solution:

I assume (hope) you have a table of Commands with a unique key, and then a table of Battalions with a field linked to the primary key of the Commands table. The battalions table should also include a unique index, since I suppose it is possible for two battalions to have the same name if they are in different commands?

Create a form, (it does not have to have a datasource) and add a combo box do it that uses the Commands table as its source and includes the primary key field.

Create a second dropdown on the form for battalions, but leave it's rowsource blank and leave it disabled by default.

Next, create an ON CHANGE event for the Command dropdown that performs the following actions
1) Set the value of the battalion dropdown to null, clearing any existing value.
2) Set the datasource of the battalion query to an SQL query string similar to this: "select battalion_id, battalion from battalion_table where command_id = " + [command_dropdown].
3) Requiries the battalion dropdown

Add a button to your form that opens the report.

Modify the report to filter on the battalion_ID field from your form.

...yes, the details of this are going to depend on your table structure, but this should give you an idea of the direction you need to go.

Now MOVE IT, MAGGOT!

blindman|||Thanks so much for the reply! I'll get on it as soon as events allow!

Sir, YES SIR!|||Blindman:

OK, took a look at your directions and the structural elements are already in place. Where I get stuck is little things like: "1) Set the value of the battalion dropdown to null, clearing any existing value." I'm a novice, and while I have examples of what the orignal programmer did, when I copied and suitably modified (I thought) his code, it didn't work.

Would you be willing to communicate directly via email so I could show the code I have and perhaps you could point out what I'm missing? [I will not be offended with a no. 8-)]

I certainly appreciate you assistance!

Peter

Avoiding SQL Injection with Dynamic SQL

I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure?

Jason PachecoFrom herehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx

Preventing SQL Injection

So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.

Validate All Input

The following suggestions should be considered best practices:
- Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
- How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?
- How will your application behave if a DROP TABLE statement is embedded in a text field?
- Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.
- Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
- When working with XML documents, validate all data against its schema as it is entered.
- Never build Transact-SQL statements directly from user input.
- Use stored procedures to validate user input.
- In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.
- Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.

For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.
- Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
- Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.

When possible, reject input that contains the following potentially dangerous characters.
Input characterMeaning in Transact-SQL

; Query delimiter

' Character data string delimiter

-- Comment delimiter

/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.

Xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.

Use Type-Safe SQL Parameters

The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using the Parameters collection:

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
"@.au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;

In this example, the @.au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @.au_id does not conform to the specified type and length constraints, an exception will be thrown.

Use Parameterized Input with Stored Procedures

Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:

SqlDataAdapter myCommand =
new SqlDataAdapter("LoginStoredProcedure '" +
Login.Text + "'", conn);

If you use stored procedures, you should use parameters as their input.

Use the Parameters Collection with Dynamic SQL

If you cannot use stored procedures, you can still use parameters, as shown below.

SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @.au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@.au_id",
SqlDbType.VarChar, 11);
Parm.Value = Login.Text;

Filtering Input

Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}

LIKE Clauses

Note that if you are using a LIKE clause, wildcard characters still need to be escaped:

s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");|||

Many thanks to you DarrellNorton,

Very helpfull information.
this was my posthttp://forums.asp.net/926297/ShowPost.aspx

BR

Avoiding clear text passwords and editing of packages

Hi!

I have an SQL Server where only a group of sysadmins have access to install DTSX packages. Those DTSX packages are developed by another team that does not have access to the production SQL Server. They use their own SQL Server.

In order to make it as simple as possible to install these packages by the sysadmins, I suggested the use of configuration files. The files are associated with the job that executes the package and all that has to be done to install the package is copy it to the file system or import it into the SQL Server. Developers use their configuration file, sysadmins user theirs. Nothing new here.

The problem is that some of the packages have to access some old systems and we cannot use integrated authentication. We have to use SQL authentication and therefore specify a user account and password in the connection string. If this is stored in the configuration file, it is available in clear text! If I store the configuration in the package itself using ProtectSensitiveWithPassword protection level, the sysadmins will have to edit every DTSX package to reset the connections to the production environment (the developers always send them with their development configurations) and I don't want that. If I store it in a SQL Server database, it seems the sysadmins also have to edit the package to point the package configuration to the correct database and set the configuration filter.

Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

Rui Covelo wrote:

Hi!
Another solution is to store the credentials in clear text in the configuration file but set the file system permissions on that file so only the account that executes the package can read them (this is what I'm implementing if nothing better comes up...)

Is there any other way to do this? Am I doing something wrong?

Thanks in advance.

I don't see any other way. Unless that you want to save you the time of oppening each package to change the credentials in every package; I think you are better dealing with the passwords in plain text and securing the place where they live. BTW, you can use Env. variables or a SQL table to place those connection strings if that makes you feel more secure.

This is just my opinion and not an entry for the 'best practice' book

Rafael Salas

|||

I don't see using configuration files is not that big issue that people seem to think of it. So what if they use clear text, all that means is that you need secure them correctly. You probably have a password store already. Does it have password complexity? Does it have group membership? Do passwords expire? Can you manage access centrally? If not it would seem obvious that AD is going to do a better job, so use it :)

The issue I think is more about how people have viewed file permissions in the past, or rather the lack of them. Use them correctly and will be more secure than a single password embedded in a package, and don't forget those jobs that also have the password too. AD is much harder to circumvent any controls around it. People just don't use file permissions, because it is "hard", well get it right and it is more secure, so what sounds better?

|||

I would re-iterate what Darren has said.

At some point someone somewhere needs to know what some password is to open up access to a securable. If that password is the password of a Windows AD account then so much the better. Security is one of the main things that AD is there for.

-Jamie

|||Thanks for your answers!

avoid to push the password through OLE to OLEDB provider

Hi!
I use OLEDB to access SQL server. During connect I put login information to
OLEDB provider(User and password) in a "connection string".
Can I do this in a more secured (encrypted) form?
Can I use other authentication mode (for instance kerberos) with MS SQL 2000
.
Thank for all idea,
ImreYou can use NT authentication.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Imre,
You can use Kerberos if the machines are part of a Windows 2000 or 2003
domain. You'll need to set the SPN for SQL.
See Books online for SetSPN syntax:
To encrypt the traffic, you can enable Protocol Encryption.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

avoid an ODBC bug

Hi!
I have to manage a program what can copy data from MS Access to SQL server
(2000 and 2005).
I work with C++ and DAO 3.6.
I have a bug from the SQL server ODBC driver : "Invalid locator
de-referenced" during copy BLOB columns.
The columns bigger than 250K (I found Article ID : 245714).
I do not want to set the ODBC driver to 6.5 compatibility mode (I am afraid
other bugs will appear on the other parts of my program).
I try to update columns one by one with SQL UPDATE command but receive an
E_DAO_OutOfMemory (about 500 kbyte SQL statment).
Can anyone help me to avoid this bug (for example install specified MDAC
version)?
Regards,
ImreI managed to avoid this bug by passing SQL_LEN_DATA_AT_EXEC(250000) as the
nullindicator to SQLBindParameter and then put the data in 250000 byte
chunks

avoid an ODBC bug

Hi!
I have to manage a program what can copy data from MS Access to SQL server
(2000 and 2005).
I work with C++ and DAO 3.6.
I have a bug from the SQL server ODBC driver : "Invalid locator
de-referenced" during copy BLOB columns.
The columns bigger than 250K (I found Article ID : 245714).
I do not want to set the ODBC driver to 6.5 compatibility mode (I am afraid
other bugs will appear on the other parts of my program).
I try to update columns one by one with SQL UPDATE command but receive an
E_DAO_OutOfMemory (about 500 kbyte SQL statment).
Can anyone help me to avoid this bug (for example install specified MDAC
version)?
Regards,
Imre
I managed to avoid this bug by passing SQL_LEN_DATA_AT_EXEC(250000) as the
nullindicator to SQLBindParameter and then put the data in 250000 byte
chunks

AVG Function

I've imported several reports from Access that I am now trying to convert use
a shared SQL data source. Several of these report show an average value for a
datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
Does anyone have a workaround for RS to be able to average a time value on a
report. Thank youTry this:
=DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> I've imported several reports from Access that I am now trying to convert
use
> a shared SQL data source. Several of these report show an average value
for a
> datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> Does anyone have a workaround for RS to be able to average a time value on
a
> report. Thank you|||Thank you Robert,
This command returned an error "FromBinary is not a memvber of Date"
Any help is appreciated, thank you very much.
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > I've imported several reports from Access that I am now trying to convert
> use
> > a shared SQL data source. Several of these report show an average value
> for a
> > datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> >
> > Does anyone have a workaround for RS to be able to average a time value on
> a
> > report. Thank you
>
>|||Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
Try this for RS 2000:
=new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> Thank you Robert,
> This command returned an error "FromBinary is not a memvber of Date"
> Any help is appreciated, thank you very much.
>
>
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > I've imported several reports from Access that I am now trying to
convert
> > use
> > > a shared SQL data source. Several of these report show an average
value
> > for a
> > > datetime field in SQL, ex. a textbox with a value of
=AVG(Fields.T1.Value)
> > >
> > > Does anyone have a workaround for RS to be able to average a time
value on
> > a
> > > report. Thank you
> >
> >
> >|||Robert,
That was a huge help! Thank you very much. I am getting a #Error in some of
the footer fields using this calculation but I think this might be a data
issue.
Thank you!
"Robert Bruckner [MSFT]" wrote:
> Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> Try this for RS 2000:
> =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > Thank you Robert,
> >
> > This command returned an error "FromBinary is not a memvber of Date"
> >
> > Any help is appreciated, thank you very much.
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Try this:
> > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > I've imported several reports from Access that I am now trying to
> convert
> > > use
> > > > a shared SQL data source. Several of these report show an average
> value
> > > for a
> > > > datetime field in SQL, ex. a textbox with a value of
> =AVG(Fields.T1.Value)
> > > >
> > > > Does anyone have a workaround for RS to be able to average a time
> value on
> > > a
> > > > report. Thank you
> > >
> > >
> > >
>
>|||Another similar situation which help would be appreciated for.
I have a table with 4 datetime fields, storing only the time. In the report
I need to produce an average of these 4 fields like, (T1.Value + T2.Value +
T3.Value + T4.Value)/4. This obviously throws a similar error.
Any help is appreciated.
"halej51" wrote:
> Robert,
> That was a huge help! Thank you very much. I am getting a #Error in some of
> the footer fields using this calculation but I think this might be a data
> issue.
> Thank you!
>
> "Robert Bruckner [MSFT]" wrote:
> > Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> > Try this for RS 2000:
> > =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > > Thank you Robert,
> > >
> > > This command returned an error "FromBinary is not a memvber of Date"
> > >
> > > Any help is appreciated, thank you very much.
> > >
> > >
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Try this:
> > > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > > I've imported several reports from Access that I am now trying to
> > convert
> > > > use
> > > > > a shared SQL data source. Several of these report show an average
> > value
> > > > for a
> > > > > datetime field in SQL, ex. a textbox with a value of
> > =AVG(Fields.T1.Value)
> > > > >
> > > > > Does anyone have a workaround for RS to be able to average a time
> > value on
> > > > a
> > > > > report. Thank you
> > > >
> > > >
> > > >
> >
> >
> >

Availability report

I have a table in Access with fields as follows:

EmpCode
Month_Year
Percentage of Availability (PA)

I have another table called StaffMaster which has the following fields:

EmpCode
EmpName
Dept

In the report, I wish to retrieve and display records as follows:

Month1 Month2 Month3 Month4

EmpCode EmpName PA PA2 PA3 PA4

Can this be accomplished? Please help. Urgent!

ThanksTry this query and use labels for Month1, Month2, etc...

SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA
FROM StaffMaster AS SM
LEFT JOIN Table1 AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = 0104
LEFT JOIN Table1 AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = 0204
LEFT JOIN Table1 AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = 0304
LEFT JOIN Table1 AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = 0404

This will return all the records in StaffMaster and the matching records in your other table (which I referred to as Table1), it will return a NULL if no records are found in Table1.|||But I don't have 4 tables. I have only 2 tables, where T1 is the StaffMaster table and T2 is the Availability table.|||The query I posted is only based on 2 tables, it just calls Table1 4 times to get each month's data. SM is the StaffMaster table and T1-T4 are all from the Availability Table. In my example, T1 gets January's data (0104), T2 gets February's data (0204), T3 gets March's data (0304), and T4 gets April's data (0404).

Try running the query (substituting the real table names and the values in the Month_Year column) and see if it produces the results you're looking for.|||I tried running the query, but I get this error.

Synatx error (missing operator) in query expression ".

This is how I changed the query.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.PA, 'PA2' = T2.PA, 'PA3' = T3.PA, 'PA4' = T4.PA " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

Thanks|||What is the data type of the field Month_Year? Maybe your syntax error is in there. If it's a string, try replacing the # with a single quote(').

If you want to post a few rows of sample data from your table, I might be able to help you further.|||The data type of Month_Year is date. Hence the #.

This is from the StaffMaster Table

EmpCode| EmpName| Designation| Discipline |Status

X059| $Dummy-SE1| Structural Engineer| Structure| A|
X060| $Dummy-SE2| Structural Engineer| Structure| A|
X061| $Dummy-SE3| Structural Engineer| Structure| A|
X062| $Dummy-SD1| Structural Drafter| Structure| A|
X063| $Dummy-SD2| Structural Drafter| Structure| A|
X064| $Dummy-SD3| Structural Drafter| Structure| A|
X065| $Dummy-SE1| Specifications Engineer| Specifications| A|

This is from the Availability Table

Empcode| Discipline| Month_Year| Avail| AvailDate| Id|

X059| Structure| June 2004| 0| 01 July 2004| Admin|
X059| Structure| July 2004| 0| 01 August 2004| Admin|
X059| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| July 2004| 0| 01 August 2004| Admin|
X060| Structure| June 2004| 0| 01 July 2004| Admin|
X060| Structure| July 2004| 48| 17 July 2004| Admin|
X060| Structure| August 2004| 0| 01 September 2004| Admin|
X060| Structure| September 2004| 48| 11 September 2004| Admin|
X060| Structure| October 2004| 0| 01 November 2004| Admin|
X060| Structure| November 2004| 0| 01 December 2004| Admin|
X060| Structure| December 2004| 24| 04 December 2004| Admin|
X060| Structure| January 2005| 46| 01 January 2005| Admin|
X061| Structure| June 2004| 0| 01 July 2004| Admin|
X061| Structure| July 2004| 0| 01 August 2004| Admin|
X061| Structure| August 2004| 0| 01 September 2004| Admin|
X062| Structure| August 2004| 7| 21 August 2004| Admin|

Thanks|||You need to replace the PA's after SELECT with the actual name of the column that you use to represent 'Percentage of Availability' (per your first post).

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{ColumnName}, 'PA2' = T2.{ColumnName}, 'PA3' = T3.{ColumnName}, 'PA4' = T4.{ColumnName} " & _
"FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"|||I replaced the PA after the Select with the actual name of the column. This is how it is now.

"SELECT SM.EmpCode, SM.EmpName, 'PA' = T1.{Avail}, 'PA2' = T2.{Avail}, 'PA3' = T3.{Avail}, " & _
"'PA4' = T4.{Avail} FROM StaffMaster AS SM " & _
"LEFT JOIN Availability AS T1 ON T1.EmpCode = SM.EmpCode AND T1.Month_Year = #June 2004# " & _
"LEFT JOIN Availability AS T2 ON T2.EmpCode = SM.EmpCode AND T2.Month_Year = #July 2004# " & _
"LEFT JOIN Availability AS T3 ON T3.EmpCode = SM.EmpCode AND T3.Month_Year = #August 2004# " & _
"LEFT JOIN Availability AS T4 ON T4.EmpCode = SM.EmpCode AND T4.Month_Year = #September 2004#"

When I run this query, I get this error.

Malformed GUID. in query expression ''PA=T1{Avail}'.|||Thanks malleyo for the reply. I got it to work. The code is below:

strsql = "TRANSFORM Sum(Availability.Avail) AS SumOfAvail " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName " & _
"FROM StaffMaster INNER JOIN Availability ON StaffMaster.EmpCode = Availability.Empcode " & _
"WHERE StaffMaster.Discipline='Architecture' " & _
"AND StaffMaster.Designation<>'Project Manager' " & _
"AND StaffMaster.Designation NOT LIKE 'Head%' " & _
"AND StaffMaster.Designation NOT LIKE '%Manager%' " & _
"AND StaffMaster.Designation<>'Designer' " & _
"AND StaffMaster.EmpCode NOT LIKE 'X%' " & _
"AND Availability.Month_Year BETWEEN #May 2004# AND #October 2004# " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT Availability.Month_Year"

R0.Open strsql, Cn, adOpenDynamic, adLockReadOnly

Report.DiscardSavedData
Report.Database.Tables.Add "", , R0

Here I have one problem more to be solved. I hope it can be solved. The problem is that , if the database table does not have a record for that month, it displays blank in the report for that month. Instead of displaying blank, I wish to display it as 100. Can this be accomplished? Please help.

Thanks|||Any help please...|||Use a Conditional Suppress on that field.|||You mean this?

crField.ConditionFormula(crEnableSuppressConditionFormulaType) = "If (IsNull(Report.Database.Tables(1).Fields(1).Name)) then 100"

If so, when the code is executed, it gives a message saying

'The ) is missing'

Thanks|||Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).|||Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"|||Originally posted by harmonycitra
Try this,

crField.ConditionFormula
(crEnableSuppressConditionFormulaType) = "If IsNull(Report.Database.Tables(1).Fields(1).Name) = True then 100"

This gives me an error.

'The ) is missing.'

Thanks|||Originally posted by malleyo
Try creating a Formula instead of using the Conditional Suppress. I think I read your last post too quickly, I don't think the Conditional Suppress will work in this case.

If you're creating you report files with the designer in VB, right-click on the word 'Formula' in the list on the Left and Click 'New'. Give it a Name. Type something similar to this:

If {FieldName} = NULL Then
100
Else
{FieldName}

I don't know the proper syntax for If statements in Crystal Reports, so you'll have to look it up. Once you get the Format correct, drop the Formula field into place where you have your field (delete the field).

But I am creating every object in the crystal report with code. Also, adding the database into the report too with code. Everything is done on the fly.

Thanks

auto-number/Identity column

I am migrating a web application I wrote from ASP to ASP.Net, and from
Access to MS SQL server.

In the Access version, I did not use the auto number for creating
invoices and other documents, because I heard somewhere (perhaps
incorrectly) that if the db was ever compacted or otherwise changed,
it could change the values of the auto-numbers. Not a good thing.
So I wrote a routine that, just before creating a new record, would
look for the highest value in the table and create the new record with
the next number.

So my question is, am I safe in assuming that in MS SQL that I can set
a starting number for the next, let's say, invoice and that new
numbers will be issued in sequence, and that these numbers will never
change? What happens if an invoice is deleted? is the number gone
forever? Just wondering how others deal with these issues...thanks.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."Larry Rekow (larry@.netgeexdotcom) writes:
> So my question is, am I safe in assuming that in MS SQL that I can set
> a starting number for the next, let's say, invoice and that new
> numbers will be issued in sequence, and that these numbers will never
> change? What happens if an invoice is deleted? is the number gone
> forever? Just wondering how others deal with these issues...thanks.

If you need sequential numbers, and cannot accept gaps, you should not
use the IDENITY property. If you attempt to insert a row, and the
insert fails, that consumes a number. The whole point is that the number
is not transactional, so that it scales better. If you need a contiguous
series of numbers, roll your own.

What does not happen is that once the number has been given to a row,
the number will not change at whim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 31 Aug 2004 21:06:39 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Larry Rekow (larry@.netgeexdotcom) writes:
>> So my question is, am I safe in assuming that in MS SQL that I can set
>> a starting number for the next, let's say, invoice and that new
>> numbers will be issued in sequence, and that these numbers will never
>> change? What happens if an invoice is deleted? is the number gone
>> forever? Just wondering how others deal with these issues...thanks.
>If you need sequential numbers, and cannot accept gaps, you should not
>use the IDENITY property. If you attempt to insert a row, and the
>insert fails, that consumes a number. The whole point is that the number
>is not transactional, so that it scales better. If you need a contiguous
>series of numbers, roll your own.
>What does not happen is that once the number has been given to a row,
>the number will not change at whim.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
thanks; glad I asked.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
>> We are testing the upload of an Access 2002 data database to SQL
>> Server 2000. Some of the tables had AutoNumber fields that were
>> Random (due to it being a replicated table in Access). The upload
>> created triggers similar to the example below. However, Access
>> applications fail on insert because the primary key field is not
>> populated until the trigger runs. Any help is appreciated.
>> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
>> INSERT AS SET NOCOUNT ON
>> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
>> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
>> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
>> SELECT @.newc = (SELECT DocID FROM inserted)
>> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
>> David
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
David
David C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber in SQLServer 2000

Hi
Please help me URGENT
How do i make an tableField with a
Auto-Incr, the same like in an access DB where it's
called AutoNumber
Thanx in advance
__________________________________________________________________ Flemming
Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
__________________________________________________________________Please dpo not post the same message independently to multiple groups. If
you must post o . 1 group then you can include them all in the same post.
See Andrew's post in pne of the other groups.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"VideoSmeden" <software@.picsign.dk> wrote in message
news:%23qO7%23oThDHA.2164@.TK2MSFTNGP09.phx.gbl...
> Hi
> Please help me URGENT
> How do i make an tableField with a
> Auto-Incr, the same like in an access DB where it's
> called AutoNumber
> Thanx in advance
> __________________________________________________________________
Flemming
> Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
> __________________________________________________________________
>

Autonumber field type in SQL Express 2005

Hope that this nisn the best place for this posting.
I am new to SQL Server using 2005 express with Visual basic express. I have
used Access databases before and used a AutoNumber field type to help with
indexing. Is this available from SQL Express 2005?
Hope you can assistOpps, found it using IDENTITY property.
any know of good book to get me up and running on SQL Server from VB.NET?
Matt A
"matt a" wrote:
> Hope that this nisn the best place for this posting.
> I am new to SQL Server using 2005 express with Visual basic express. I have
> used Access databases before and used a AutoNumber field type to help with
> indexing. Is this available from SQL Express 2005?
> Hope you can assist|||> any know of good book to get me up and running on SQL Server from VB.NET?
I don't believe there are any books out on SQL Server 2005, but here's on
for SQL Server 2000 + VB.Net:
http://www.amazon.com/exec/obidos/tg/detail/-/0735615357/
Also see http://www.aspfaq.com/2423

Autonumber field type in SQL Express 2005

Hope that this nisn the best place for this posting.
I am new to SQL Server using 2005 express with Visual basic express. I have
used Access databases before and used a AutoNumber field type to help with
indexing. Is this available from SQL Express 2005?
Hope you can assist
Opps, found it using IDENTITY property.
any know of good book to get me up and running on SQL Server from VB.NET?
Matt A
"matt a" wrote:

> Hope that this nisn the best place for this posting.
> I am new to SQL Server using 2005 express with Visual basic express. I have
> used Access databases before and used a AutoNumber field type to help with
> indexing. Is this available from SQL Express 2005?
> Hope you can assist
|||> any know of good book to get me up and running on SQL Server from VB.NET?
I don't believe there are any books out on SQL Server 2005, but here's on
for SQL Server 2000 + VB.Net:
http://www.amazon.com/exec/obidos/tg.../-/0735615357/
Also see http://www.aspfaq.com/2423

Autonumber field type in SQL Express 2005

Hope that this nisn the best place for this posting.
I am new to SQL Server using 2005 express with Visual basic express. I have
used Access databases before and used a AutoNumber field type to help with
indexing. Is this available from SQL Express 2005?
Hope you can assistOpps, found it using IDENTITY property.
any know of good book to get me up and running on SQL Server from VB.NET?
Matt A
"matt a" wrote:

> Hope that this nisn the best place for this posting.
> I am new to SQL Server using 2005 express with Visual basic express. I ha
ve
> used Access databases before and used a AutoNumber field type to help with
> indexing. Is this available from SQL Express 2005?
> Hope you can assist|||> any know of good book to get me up and running on SQL Server from VB.NET?
I don't believe there are any books out on SQL Server 2005, but here's on
for SQL Server 2000 + VB.Net:
http://www.amazon.com/exec/obidos/t...l/-/0735615357/
Also see http://www.aspfaq.com/2423

Autonumber field

I have converted an access database to sql server database.I am having problems with my autonumber field which was converted to int.
Have researched on this forum on such a case and someone said make the field identity.
However my sqlserver database does not give that identity variable type.
Please help !!!

Quote:

Originally Posted by Touch

I have converted an access database to sql server database.I am having problems with my autonumber field which was converted to int.
Have researched on this forum on such a case and someone said make the field identity.
However my sqlserver database does not give that identity variable type.
Please help !!!

Identity is not a datatype. its in datatype discreption

Autonumber equivalent in SQL Server?

Hi

So far, I have only used Access which has an autonumber data type so that in some of my tables the id is automatically generated.
I guess this is a simple question but is there an equivalent data type in sql server?

Thanks in advance.Yes - Int with an Identity property set to Yes

Autonumber problem Error "Data type mismatch in criteria expression"

Hi All,

I am already busy for several hours on this problem WHO CAN HELP ME ;(
I got a Access database with with the field "Password", "Title", And the field PMID (PMID is a AUTONUMBER FIELD in ACCESS 1,2,3,4,5,6,7)

I got this script
SQL = "Select Title, Password From Checklist " _
& "Where Title = '"&Title&"' And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

THIS WORKS GREATTTTTTTTTTTTTTT!!!!!!!!!
But now here it comes.
I want to change TITLE to PMID in this script.

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = '"&PMID&"' And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

Know i think is should remove the singel '. So i did that. I canged the script to
SQL = "Select PMID, Password From Checklist " _
& "Where PMID = #" & PMID & "# And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

But i keep getting errors like
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'PMID = ## And Password = 'test''.
/pm/login.asp, line 17

It seems that it doesn't reed the PMID...
WHO CAN HELP ME ;)?This is not a SQL problem - it looks to me like your VBScript VARIABLE called PMID is empty. Try putting this debug message in your code instead of the Execute:

Response.Write( "PMID = " & PMID )

I'll bet when you run it you see this:

PMID =

So the problem is you need to assign a value to PMID.|||Hi andrewst

You Are right :), It is emty then... How is this posible.

How so i fix that. THis field is a AUTONUMBER.
And there are numbers in it...

When i look in Access and go with the DESIGNS VIEW
to the "PMID" field. It tells me this properties:
- Field Size : Long Integer
- New Values : Increment
- Format :
- Caption :
- Indexed : YES (No Duplicades)

When i look in the normal VIEW in the table PMID it give just the normal Numbers automaticly generated. 1,2,3,4,5,6,7,8,9,10... until 86|||PS
Here is the whole script

----------------
<HTML>
<BODY>

<%
PMID = Request.Form("ID")
Password = Request.Form("passw")
'grab the form contents

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open ("problemman")

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = " & PMID & " And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

If Not RS.EOF Then
Session("allow") = True
'if there is a match then show the page
%>

<html>
'HERE IS MY HTML PAGE
</html>

<%
Else
Response.Redirect "http://www.testl.com/main.htm"
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
End If
%>

</BODY>
</HTML>|||Originally posted by jvdzwaan
Hi andrewst

You Are right :), It is emty then... How is this posible.

How so i fix that. THis field is a AUTONUMBER.
And there are numbers in it...

When i look in Access and go with the DESIGNS VIEW
to the "PMID" field. It tells me this properties:
- Field Size : Long Integer
- New Values : Increment
- Format :
- Caption :
- Indexed : YES (No Duplicades)

When i look in the normal VIEW in the table PMID it give just the normal Numbers automaticly generated. 1,2,3,4,5,6,7,8,9,10... until 86
Yes, but the problem has nothing to do with the COLUMN called PMID in the table, it is to do with the VBSCript VARIABLE that happens to have the same name. The variable is not AUTONUMBER, it is just a variable. If you don't assign a value to it, it will not get one for itself!|||Originally posted by jvdzwaan
PS
Here is the whole script

----------------
<HTML>
<BODY>

<%
PMID = Request.Form("ID")
Password = Request.Form("passw")
'grab the form contents

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open ("problemman")

SQL = "Select PMID, Password From Checklist " _
& "Where PMID = " & PMID & " And Password = '"&Password&"'"
Set RS = MyConn.Execute(SQL)

If Not RS.EOF Then
Session("allow") = True
'if there is a match then show the page
%>

<html>
'HERE IS MY HTML PAGE
</html>

<%
Else
Response.Redirect "http://www.testl.com/main.htm"
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
End If
%>

</BODY>
</HTML>
So the question is: why has the field called "ID" in the form not been populated? Is this field displayed? Did you type a value into it before hitting Submit?|||yes... I got a form with 2 fields
one called ID
and one called passw

when i fill in the form I entered for the ID 61 (is in the DB)
in the passw field i entered test

Then i get the error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/PM/login.asp, line 14|||Originally posted by jvdzwaan
yes... I got a form with 2 fields
one called ID
and one called passw

when i fill in the form I entered for the ID 61 (is in the DB)
in the passw field i entered test

Then i get the error

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/PM/login.asp, line 14
So have you solved the variable problem yet? I mean, if you put:

Response.Write "PMID = " & PMID

in your program, does it now say:

a) PMID = 61

or:

b) PMID =

If the answer is still (b) then you are sure to get an error since the SQL you send to Access is:

Select PMID, Password From Checklist Where PMID = And Password = 'test'

which is invalid. In this case, you need to debug the variable assignment, not the SQL!

If it seems OK, then try writing out the whole SQL statement:

Response.Write SQL

and see if it a sensible SQL statement.

Autonumber

What is the equivelant to Autonumber coming from an Access World to
SQL?

TIA

Michael"Michael" <Mikey@.yahoo.com> wrote in message
news:9hvksv0pu1ojp8acu1en8j1vaj6k1p63ic@.4ax.com...
> What is the equivelant to Autonumber coming from an Access World to
> SQL?

IDENTITY

> TIA
> Michael|||> What is the equivelant to Autonumber coming from an Access World to
> SQL?

A proper primary key.

http://www.sqlteam.com/item.asp?ItemID=2599

--
David Portas
----
Please reply only to the newsgroup
--

"Michael" <Mikey@.yahoo.com> wrote in message
news:9hvksv0pu1ojp8acu1en8j1vaj6k1p63ic@.4ax.com...
> What is the equivelant to Autonumber coming from an Access World to
> SQL?
>
> TIA
> Michael|||On Sun, 30 Nov 2003 23:56:37 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:

R U referring to uniqueidentity
>"Michael" <Mikey@.yahoo.com> wrote in message
>news:9hvksv0pu1ojp8acu1en8j1vaj6k1p63ic@.4ax.com...
>> What is the equivelant to Autonumber coming from an Access World to
>> SQL?
>>
>IDENTITY
>
>>
>> TIA
>>
>> Michael|||On Mon, 01 Dec 2003 03:46:21 GMT in comp.databases.ms-sqlserver,
Michael <Mikey@.yahoo.com> wrote:

>On Sun, 30 Nov 2003 23:56:37 GMT, "Greg D. Moore \(Strider\)"
><mooregr@.greenms.com> wrote:
>R U referring to uniqueidentity

No, that's a GUID, make it an "Int" data type and set the "Identity"
property to "yes"

--
A)bort, R)etry, I)nfluence with large hammer.|||On Mon, 1 Dec 2003 00:08:13 -0000 in comp.databases.ms-sqlserver,
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>> What is the equivelant to Autonumber coming from an Access World to
>> SQL?
>A proper primary key.
>http://www.sqlteam.com/item.asp?ItemID=2599

hmmm, I use identity columns, their meaningless is nothing to me, I
don't let users see them or touch them, they mean an awful lot to the
computer though :-)

From that page:
>using only the ID and BossID columns, identify each person's boss. Can't do it, huh?

This assumes you display raw data to the user, in reality you join
tables and make menaingful presentation data to show the user.

In the second table, the candidate is easy, the URL will be unique
although imagine you've exported to another system, then import data
back from that, one or more of your meaningful PKs was misspelled and
corrected in the meantime, there would not be a match when the data
comes back in.

--
A)bort, R)etry, I)nfluence with large hammer.|||My point was not that the OP should necessarily avoid IDENTITY altogether
but that he shouldn't assume he can just port an Access database to SQL by
changing an Autonumber column to IDENTITY. In Access you can get away
without primary keys - not always so in SQL.

It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
have a natural key as well then you can't define the entity for the table
and it may contain redundant data. Consider:

CREATE TABLE foo (id INTEGER IDENTITY PRIMARY KEY /* ? */, a INTEGER NOT
NULL, b INTEGER NOT NULL, c INTEGER NOT NULL)

If (a,b,c) are your attributes then why would they not be unique? What's the
point of storing the same information multiple times? Clearly Id isn't an
attribute of you entity so ([1],x,y,z) and ([2],x,y,z) are redundant tuples.
This is the best way to fill up your table with garbage and make life very
difficult when it comes to joins and other queries.

--
David Portas
----
Please reply only to the newsgroup
--|||On Tue, 2 Dec 2003 10:09:17 -0000 in comp.databases.ms-sqlserver,
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>My point was not that the OP should necessarily avoid IDENTITY altogether
>but that he shouldn't assume he can just port an Access database to SQL by
>changing an Autonumber column to IDENTITY. In Access you can get away
>without primary keys - not always so in SQL.
>It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
>have a natural key as well then you can't define the entity for the table
>and it may contain redundant data. Consider:
>CREATE TABLE foo (id INTEGER IDENTITY PRIMARY KEY /* ? */, a INTEGER NOT
>NULL, b INTEGER NOT NULL, c INTEGER NOT NULL)
>If (a,b,c) are your attributes then why would they not be unique? What's the
>point of storing the same information multiple times? Clearly Id isn't an
>attribute of you entity so ([1],x,y,z) and ([2],x,y,z) are redundant tuples.
>This is the best way to fill up your table with garbage and make life very
>difficult when it comes to joins and other queries.

There's nothing to stop you putting a unique index on the other keys.
--
A)bort, R)etry, I)nfluence with large hammer.|||> There's nothing to stop you putting a unique index on the other keys.

Yes, as I said:

> It may be reasonable to use IDENTITY as a *surrogate* key but if you don't
> have a natural key as well then you can't define the entity for the table

Unfortunately this is too often forgotten as posts to this group frequently
demonstrate. See also Northwind for MS's bad examples.

--
David Portas
----
Please reply only to the newsgroup
--