How do I make a field automatically get its values from another fi

Hi,
In my table I have a "PaymentDate" field which is used to store payment
schedules for our clients. I want to add a new field in the same table and
call it "UpdatedPaymentDate" field as most of the time our clients don't
stick to original payment schedules.
How do I make the default value of this new "UpdatedPaymentDate" field to be
the values in the "PaymentDate" field? Do I have to use a trigger for this?
Is there a way to do this without using triggers?
If necessary I'll post the table script but I don't it's necessary for this
simple question. Both fields are in the same table and their data type is
smalldatetime for both.
--
Thanks,
SamSam wrote:
> Hi,
> In my table I have a "PaymentDate" field which is used to store payment
> schedules for our clients. I want to add a new field in the same table and
> call it "UpdatedPaymentDate" field as most of the time our clients don't
> stick to original payment schedules.
> How do I make the default value of this new "UpdatedPaymentDate" field to
be
> the values in the "PaymentDate" field? Do I have to use a trigger for this
?
> Is there a way to do this without using triggers?
> If necessary I'll post the table script but I don't it's necessary for thi
s
> simple question. Both fields are in the same table and their data type is
> smalldatetime for both.
> --
> Thanks,
> Sam
I'm assuming you'll use stored procs for your inserts of course. So use
an optional parameter and assign the default in the proc:
CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
GO
CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
(
@.PaymentDate SMALLDATETIME,
@.UpdatedPaymentDate SMALLDATETIME = NULL
)
AS
INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
GO
EXEC dbo.usp_PaymentScheduleInsert
@.PaymentDate = '2006-04-30T00:00:00.000' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How do I make a field automatically get its values from anothe

David,
Thanks for your response. We're using an Access Form as a front-end to enter
data into this SQL Server table. So I'm not really using a storedproc to
enter data. Would using a trigger the only way to handle this then?
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> I'm assuming you'll use stored procs for your inserts of course. So use
> an optional parameter and assign the default in the proc:
> CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
> UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
> GO
> CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
> (
> @.PaymentDate SMALLDATETIME,
> @.UpdatedPaymentDate SMALLDATETIME = NULL
> )
> AS
> INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
> VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
> GO
> EXEC dbo.usp_PaymentScheduleInsert
> @.PaymentDate = '2006-04-30T00:00:00.000' ;
>
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>Sam (Sam@.discussions.microsoft.com) writes:
> Thanks for your response. We're using an Access Form as a front-end to
> enter data into this SQL Server table. So I'm not really using a
> storedproc to enter data. Would using a trigger the only way to handle
> this then?
Yes, but I guess David's hint is that you should start using stored
procedures.
The trigger would look like:
CREATE TRIGGER sams_trigger ON tbl FOR INSERT AS
UPDATE tbl
SET updatedpaymentdate = i.paymentdate
FROM tbl
JOIN inserted i ON tbl.pkcol = i.pkcol
However, judging from the narrative, it seems to me that it would be
better to leave the column NULL. I'm assuming then that when the data is
entered, there has been no update to the payment date yet.
Then again, it was not clear to me whether this column is intended to
catch the date the client actually paid, or if this is a date agreeed-on
beforehand as the new date for the payment.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

How do I make a column not nullable

This is what I would like to do...

1. Alter Table Status
Add ConsiderOpenFlag int null

2. UPDATE values...

3. Alter Table Status
Alter ConsiderOpenFlag int not null

Steps 1 and 2 are easy. What I cannot figure out is step three.

I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.

Jonathan

The correct syntax is:

ALTER TABLE Status ALTER COLUMN ConsiderOpenFlag int not null

Best is probably to add the column as not null with default and then drop the default later. If there are large number of rows in the table then you may want to go with the UPDATE method and perform the UPDATE in batches to reduce the logging / locking resources.

sql

How do I lose the exponential display in Query Analyzer?

I have a SQL Server 2000 database with a table with a
field defined as simply [float]. I inserted several
values into the table for this field such as 0.0024.
My question is that when I query the table as follows:
select rcf from tableX
I get such values as 2.3999999999999998E-3. Is there
any reason why SQL Server simply does not display
0.0024? How can I prevent exponential display? Thanks!
GusFloat is an approximate data type and some values cannot be precisely
stored. QA is showing the actual value stored in the database. You can
cast the value to an exact type for display purposes:
DECLARE @.x float
SET @.x = 0.0024
SELECT @.x
SELECT CAST(@.x AS decimal(10,4))
See 'Approximate numeric data' in the SQL Server 2000 Books Online
<createdb.chm::/cm_8_des_04_82ic.htm> for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Gus" <gcoll@.yahoo.com> wrote in message
news:be7512a6.0309241846.4892c7ff@.posting.google.com...
> I have a SQL Server 2000 database with a table with a
> field defined as simply [float]. I inserted several
> values into the table for this field such as 0.0024.
> My question is that when I query the table as follows:
> select rcf from tableX
> I get such values as 2.3999999999999998E-3. Is there
> any reason why SQL Server simply does not display
> 0.0024? How can I prevent exponential display? Thanks!
> Gus

How do i just SUM the group header values?

Here's my table so far in RS:

Facility Name | Claim | Fee | Value | Payment | Table Header
Wisconsin West | | | | | Group1 Header
| 2356 | $45 | $23 | | Group2 Header
| | | | $21 | Details
| | | | $7 | Details
| | | | $16 | Details
| 2357 | $85 | $47 | | Group2 Header
| | | | $21 | Details
| | | | $9 | Details
| | | | $13 | Details
| 2358 | $105 | $65 | | Group2 Header
| | | | $35 | Details
| | | | $12 | Details
| | | | $20 | Details
Facility Totals | 3 | $705 | $405 | $154 | Group1 Footer

*Notes = Table and Group2 footers are hidden and contains nothing. The last column is detailing what level each line is and not actually part of the table.

I believe my issue is with the SUM function and how or where to place it. In the Payment field, the SUM function is adding correctly, but for the Fee and Value field, the SUM fuction is adding as if every line item in the payment field had the fee and value amount, hence the huge amount.

How can make the Fee field total to $235 (45+85+105) instead of $705 (45+45+45+85+85+85+105+105+105); the same goes for the Value field of $135 (23+47+65) instead of $405 (23+23+23+47+47+47+65+65+65).

Below are the expressions I have in place at the Group1 Footer for the Claim, Fee, Value, and Payment fields respectively:

=CountDistinct(Fields!ClaimNumber.Value

=Sum(Fields!dAmount.Value)

=Sum(Fields!Value.Value)

=Sum(Fields!cAmount.Value)

I've tried playing with the scope, but to no avail. Any ideas or maybe I'm doing it all wrong? It's almost as if I need a SumDistinct if such a thing exist.

In the meantime, I'm doing some serious researching. Thanks!

I'm sure this isn't the solution to your problem.

But it does have a sum distinct code sample.
http://msdn2.microsoft.com/en-us/library/bb395166.aspx

quite why they choose to embed the code samples as bitmaps is beyond me

I'm sure you can do something with IIF to fix it.

How do I jump to another report based on a value in my current report? report has no parameters.

How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.

Hello,

Does your target report have parameters? For example, you want to go to the target report and run it with the value you selected on the current report?

Or are you saying... If the field value is between a and b, go to report 1, if it's between b and c, go to report 2, otherwise go to report 3.

Can you explain a little more about your situation?

Jarret

|||

the report I am jumping from has no parameters. I want to be able to click on a value and have it jump to another report. That report possibly being jumped too might or might not have parameters.

I just used the following expression and it works to a degree, but it enables all the values in the column for jumping, rather than just the report I want

=iif(Fields!YN_DESC.Value="A","Report 1","Report2")

|||

Sorry for the delay...

This will only enable the "A" values to jump to a report (Report 1), any other values will not have the jump to enabled.

=Switch(Fields!YN_Desc.Value = "A", "Report 1")

If you need to add additional conditions... Example, if the value is "C" - Report 31, "D" - Report 7.

=Switch(Fields!YN_Desc.Value = "A", "Report 1",
Fields!YN_Desc.Value = "C", "Report 31",
Fields!YN_Desc.Value = "D", "Report 7")

Hope this helps.

Jarret

How do I jump to another report based on a value in my current report? report has no parameters.

How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.

Hello,

Does your target report have parameters? For example, you want to go to the target report and run it with the value you selected on the current report?

Or are you saying... If the field value is between a and b, go to report 1, if it's between b and c, go to report 2, otherwise go to report 3.

Can you explain a little more about your situation?

Jarret

|||

the report I am jumping from has no parameters. I want to be able to click on a value and have it jump to another report. That report possibly being jumped too might or might not have parameters.

I just used the following expression and it works to a degree, but it enables all the values in the column for jumping, rather than just the report I want

=iif(Fields!YN_DESC.Value="A","Report 1","Report2")

|||

Sorry for the delay...

This will only enable the "A" values to jump to a report (Report 1), any other values will not have the jump to enabled.

=Switch(Fields!YN_Desc.Value = "A", "Report 1")

If you need to add additional conditions... Example, if the value is "C" - Report 31, "D" - Report 7.

=Switch(Fields!YN_Desc.Value = "A", "Report 1",
Fields!YN_Desc.Value = "C", "Report 31",
Fields!YN_Desc.Value = "D", "Report 7")

Hope this helps.

Jarret

How do I insert values into CE from a DataGridView row/rows?

Hi all... can someone tell me why this does not work and how to make it work?

SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM ProposalDetail
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
'3/20/2007 8:13:10 AM'
)
WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1')

Thanks a ton!

figured it out and oddly enough this is solid and works great...

'SQL STATEMENT......

Dim conGridInsert As New SqlCeCommand

conGridInsert.CommandText = "INSERT INTO ProposalDetail" & _

"(SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date)" & _

"SELECT SQLkey, CreateDateTime, Alias, PropDNumb, '" & ComboRevision.Text & "' as PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date" & _

" " & _

" FROM ProposalDetail ProposalDetail_1 " & _

" " & _

" WHERE (PropDNumb = '" & txtProposalNum.Text & "') AND (PropDRevNumb = '" & OldRevNumber & "')"

conGridInsert.Connection = CnGridInsert

CnGridInsert.Open()

conGridInsert.ExecuteNonQuery()

CnGridInsert.Close()

How do I insert a string value with quotes into a nvarchar column

I am reading data from another data source and storing it in the sqlce database. Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company). When I try to insert the values with the single quotes, it throws an exception. The code I use to insert the records is as follows:

cmd.CommandText = "INSERT sy_company " +

" (company_id, company, co_name, companyid) " +

"VALUES(" +

"'" + dtSYCompany.Rows[x]["company_id"] + "'," +

"N'" + dtSYCompany.Rows[x]["company"] + "'," +

"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +

"'" + dtSYCompany.Rows[x]["companyid"] + "')";

cmd.ExecuteNonQuery();

When the company name (co_name) has a single quote in it, I get the error. How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?

Thanks so much!

Use parameters. It’s also great for performance and security.

By the way, it appears you’re using DataTable. In that case you could use DataAdapter.Update() instead of running commands manually. CommandBuilder can generate parametrized command for you.

|||That did it!!! Thanks so much.

How do I include all dates between 2 parameters in a table?

I'm working with a data set where some values have an aggretate total
(for example, a count of something) in a certain date range and others
do not. The problem I am having is that I need each page of the report
to show all the possible dates and the display a 0 when there are no
values for the date. Currently it is dropping that date.
For Example:
@.StartDate = 1/1/06
@.EndDate = 6/31/06
-- I get this on grouped values where there are empty months:
Jan06 5
Feb06 3
Apr06 7
Jun06 10
-- But I want this:
Jan06 5
Feb06 3
Mar06 0
Apr06 7
May06 0
Jun06 10
Due to other report properties and calculations, I can't really modify
my query to fix this problem. It seems like it should be easy to solve
but I'm not getting it!!!
Thanks.I think you must be using table control. so no way you can substitute 0 for
missing one. you need to modify query to use right join to include all the
rows.
if you hard code all the months value still you need to change the query to
get all the rows to column..
Try to change query, rather than breaking head.
Amarnath
"CanoAko" wrote:
> I'm working with a data set where some values have an aggretate total
> (for example, a count of something) in a certain date range and others
> do not. The problem I am having is that I need each page of the report
> to show all the possible dates and the display a 0 when there are no
> values for the date. Currently it is dropping that date.
> For Example:
> @.StartDate = 1/1/06
> @.EndDate = 6/31/06
> -- I get this on grouped values where there are empty months:
> Jan06 5
> Feb06 3
> Apr06 7
> Jun06 10
> -- But I want this:
> Jan06 5
> Feb06 3
> Mar06 0
> Apr06 7
> May06 0
> Jun06 10
> Due to other report properties and calculations, I can't really modify
> my query to fix this problem. It seems like it should be easy to solve
> but I'm not getting it!!!
> Thanks.
>|||Hmm... that doesn't really help. I admit now that I will probably have
to design a query to do it. The problem is that the query joins 2
tables and the date that I'm pulling is on the second table. So the
date doesn't exist to do an aggregate with a total of 0 in the first
place.
I'm going to try it with CASE to force it to happen, but I'm not too
excited about that. I've only been doing this for a month so I still
haven't figured everything out.
Amarnath wrote:
> I think you must be using table control. so no way you can substitute 0 for
> missing one. you need to modify query to use right join to include all the
> rows.
> if you hard code all the months value still you need to change the query to
> get all the rows to column..
> Try to change query, rather than breaking head.
> Amarnath
>
> "CanoAko" wrote:
> > I'm working with a data set where some values have an aggretate total
> > (for example, a count of something) in a certain date range and others
> > do not. The problem I am having is that I need each page of the report
> > to show all the possible dates and the display a 0 when there are no
> > values for the date. Currently it is dropping that date.
> >
> > For Example:
> >
> > @.StartDate = 1/1/06
> > @.EndDate = 6/31/06
> >
> > -- I get this on grouped values where there are empty months:
> > Jan06 5
> > Feb06 3
> > Apr06 7
> > Jun06 10
> >
> > -- But I want this:
> > Jan06 5
> > Feb06 3
> > Mar06 0
> > Apr06 7
> > May06 0
> > Jun06 10
> >
> > Due to other report properties and calculations, I can't really modify
> > my query to fix this problem. It seems like it should be easy to solve
> > but I'm not getting it!!!
> >
> > Thanks.
> >
> >|||Hi,
thats quite easy, use the following link to see how to generate a
calendar table, you can either persits it or create it on the fly.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

How do I get uppercase values returned only.

I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.

Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.

Thanks,
Philselect * from mytable where name = upper(name)

Joe Weinstein at BEA|||No luck with that

Here is my query
SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE (last_nme = UPPER(last_nme))
ORDER BY last_nme, first_nme

I still see lower case names.|||I fixed it with this
where ASCII(last_nme) = (ASCII(UPPER(last_nme))

Thanks for the responses.
Phil|||As Phillip found out... this will only work if you have set your
instance of SQL Server set to be case-sensitive. The default
installation makes the instance NOT case-sensitive.

-Tom.|||Phillip (pputzback@.ECommunity.com) writes:
> No luck with that
> Here is my query
> SELECT *, last_nme AS Expr1, first_nme AS Expr2
> FROM members
> WHERE (last_nme = UPPER(last_nme))
> ORDER BY last_nme, first_nme
> I still see lower case names.

This should do it:

SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE last_nme COLLATE Finnish_Swedish_CS_AS =
UPPER(last_nme) COLLATE Finnish_Swedish_CS_AS
ORDER BY last_nme, first_nme

You may prefer to use something else than Finnish_Swedish. It's the
CS_AS part that is the important.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How do I get the Y axis Labels to show as a Percent e.g 10%

I have the values in percent but I want the % sign to be on the axis labels.
SQL Reporing Services 2005The y-axis tab has a format code property. You can set it to P0 (to get
percentage values with 0 decimals).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianDotNet" <BrianDotNet@.discussions.microsoft.com> wrote in message
news:C7145CD4-9A8D-4581-B549-EF066C9A21ED@.microsoft.com...
>I have the values in percent but I want the % sign to be on the axis
>labels.
> SQL Reporing Services 2005

How do I get a new record ID

I am taking values entered by a user in a form and inserting them into a
table. I then would like to obtain the record ID after I have added the
record. However I do not understand why the following code does not work.
Set conn = New ADODB.Connection
conn.Open
" Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_Con
nection=yes;"
Set rs = New ADODB.Recordset
rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
With rs
.AddNew
[value1]
[value2]
etc
.Update
End With
Dim strGetID As String
strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
Set rs = conn.Execute(strGetID)
lContractID = rs.Fields("last_identity_value").Value
I have also tried:
lContractID = rs.Fields("ContractID").Value
instead of the scope_identity but this doesn't work either.
The variable lContractID is '0' in both cases and "last_identity_value" is
shown as empty. However the record is successfully entered in the table with
an ID.
Can someone please let me know what I'm doing wrong.Recordset objects are for RETRIEVING data. Please do not use them for
affecting data. Use a stored procedure.
CREATE PROCEDURE dbo.AddContact
@.value1 VARCHAR(32),
@.value2 VARCHAR(32),
..etc,
@.NewContactID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblContact(value1, value2, etc)
SELECT @.value1, @.value2, etc;
SELECT @.NewContactID = SCOPE_IDENTITY();
END
GO
Now, call the stored procedure and retrieve the new id from the output
parameter.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>I am taking values entered by a user in a form and inserting them into a
> table. I then would like to obtain the record ID after I have added the
> record. However I do not understand why the following code does not work.
> Set conn = New ADODB.Connection
> conn.Open
> " Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_C
onnection=yes;"
> Set rs = New ADODB.Recordset
> rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
> With rs
> .AddNew
> [value1]
> [value2]
> etc
> .Update
> End With
>
> Dim strGetID As String
> strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
> Set rs = conn.Execute(strGetID)
> lContractID = rs.Fields("last_identity_value").Value
>
> I have also tried:
> lContractID = rs.Fields("ContractID").Value
> instead of the scope_identity but this doesn't work either.
> The variable lContractID is '0' in both cases and "last_identity_value" is
> shown as empty. However the record is successfully entered in the table
> with
> an ID.
> Can someone please let me know what I'm doing wrong.
>|||Aaron
I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as a
reference which shows the method I used to add a new record. Unfortunately
your Create Procedure is not recognised in Access VB. Maybe I posted this to
the wrong section.
Can you offer me any other help?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Recordset objects are for RETRIEVING data. Please do not use them for
> affecting data. Use a stored procedure.
> CREATE PROCEDURE dbo.AddContact
> @.value1 VARCHAR(32),
> @.value2 VARCHAR(32),
> ...etc,
> @.NewContactID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblContact(value1, value2, etc)
> SELECT @.value1, @.value2, etc;
> SELECT @.NewContactID = SCOPE_IDENTITY();
> END
> GO
> Now, call the stored procedure and retrieve the new id from the output
> parameter.
>
>
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>
>|||No, Aaron is right, and this is the right section. Based on your connection
string, you are connecting to SQL Server, not an Access DB. With SQL
Server, it's usually best to perform modifications with stored procedures.
Invoke the Execute method on the Connection object to create the procedure
(this only needs to be done once). Invoke the Execute method on a Command
object with parameters to execute the stored procedure.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
> Aaron
> I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as
> a
> reference which shows the method I used to add a new record. Unfortunately
> your Create Procedure is not recognised in Access VB. Maybe I posted this
> to
> the wrong section.
> Can you offer me any other help?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Brian thank you for your response. I did migrate the db from Access to SQL
Express and am now trying to amend my code. I think I have more to learn! Ar
e
there any online tutorial which you'd recommend?
"Brian Selzer" wrote:

> No, Aaron is right, and this is the right section. Based on your connecti
on
> string, you are connecting to SQL Server, not an Access DB. With SQL
> Server, it's usually best to perform modifications with stored procedures.
> Invoke the Execute method on the Connection object to create the procedure
> (this only needs to be done once). Invoke the Execute method on a Command
> object with parameters to execute the stored procedure.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
>
>|||I think your best bet is to hit Borders and buy a book, unless you're
comfortable with BOL and MSDN. I'm sure that there are a plethora of good
books on SQL Server, ADO, VB Database, etc. Maybe you should repost and ask
for suggestions. My library's pretty lean on introductory database books.
The only one I own is a vintage 1990 Que book, "Using SQL," that features
dBASE.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
> Brian thank you for your response. I did migrate the db from Access to SQL
> Express and am now trying to amend my code. I think I have more to learn!
> Are
> there any online tutorial which you'd recommend?
> "Brian Selzer" wrote:
>|||Brian, you are probably right there are no quick fixes.
I see that you don't part with your old books either!
"Brian Selzer" wrote:

> I think your best bet is to hit Borders and buy a book, unless you're
> comfortable with BOL and MSDN. I'm sure that there are a plethora of good
> books on SQL Server, ADO, VB Database, etc. Maybe you should repost and a
sk
> for suggestions. My library's pretty lean on introductory database books.
> The only one I own is a vintage 1990 Que book, "Using SQL," that features
> dBASE.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
>
>