SET FMTONLY Being Added To In-line SQL

Jul 23, 2005

Here's the deal.

I've been working on an Intranet application for my clients, and today
I went and installed the first prototype. It's a fairly standard thing
- VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.

I restored the database onto their db server, and installed the
application on their intranet server, and made the necessary changes to
the web.config and other configuration files.

I logged on with no problem. The default form is a Search/Finder form
with no default recordset. I fired up the filter to return all the
records and the thing crashed. The filter form wouldn't get any data
(there *was* data in the database - I checked that!). So I set a trace
on the database and this is the SQL that was being sent to the server
(note that this SQL is dynamically built and is being sent in-line):

SET FMTONLY OFF; SET FMTONLY ON;SELECT fldID, fldReferenceNumber,
fldRevision, fldPartNumber, fldIndentNumber, fldDescription,
fldBatchNumber, fldCreatedDate FROM tblMyTable SET FMTONLY OFF;

Note those SET FMTONLY OFF; SET FMTONLY ON;...SET FMTONLY OFF;
directives. These direct SQL Server to return only metadata, and not
data rows. The SQL which SHOULD have been sent, and which I have just
captured in the SQL Profiler on MY system here is:

SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,
fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROM
tblMyTableWHERE (((tblMyTable.fldReferenceNumber LIKE N'%')))

Again, note that the WHERE clause is missing from the first lot of SQL.

Does anyone have any ideas about why this might be happening? I think
it might have something to do with ownership/permissions, but I
couldn't find anything different about this database than the system on
which this new application was modelled, which has no such problems.

Edward

View 3 Replies


ADVERTISEMENT

Fmtonly Problem (bug ??)

Apr 13, 2006

I am having a problem with "SET fmtonly ON" and a function Iimplemented in my database. (The function is actually Erland'sdelimited string to tmp table function for purposes of passing inmultiple values to a stored procedure.)The stored procedure which uses the function is used to return aresultset. But sometimes the stored procedure is called with bogusvalues (preceded by "SET fmtonly ON") to get the column names. And thisis where the problem happens.If fmtonly is ON and the function is called with the list containing asingle value (with no delimiters) (as in 'item1' versus 'item1,item2')then SQL throws a "Invalid length parameter passed to the substringfunction" error.After much testing it seems that certain parts of code in the functiongets IGNORED. (In this case it is the WHILE loop condition check.)(Happens with IF statements also.)I created some sample code to prove this.--SET fmtonly ONDECLARE @flag INTSET @flag = 0IF 0 > 0 -- always falseSELECT @flag = 1 -- should never executeSET fmtonly OFFPRINT 'DEBUG | ' + LTRIM(STR(@flag))If executed as is the code executes correctly and the value printed is"0". But if fmtonly is ON then for whatever reason 0 > 0 evaluates totrue and the @flag variable gets set to "1".While this seems to be by design (I tested it on SQL 2000 sp3, sp4, andSQL 2005 with the same results) this just seems silly to me.

View 6 Replies View Related

Odd FMTONLY Statements

Jul 21, 2006

Hi,
I recently started using ADO
for connecting to a remote SQL Server from Delphi.
Prior to this I used BDE (Borland Database Engine).



I noticed that all SQL communication seemed slow €“ only
half the speed of using BDE!

I ran a trace using the Profiler (from MS SQL Server)
and noticed that ADO
generated roughly twice the amount of traffic compared to BDE. Every SQL statement (SELECT,
INSERT and UPDATE) send from my application was preceded by the exact same
statement encapsulated in €˜SET FMTONLY ON/OFF€™.



-Example Start-

SET FMTONLY ON SELECT * FROM TabelVersion SET FMTONLY
OFF

SELECT * FROM TabelVersion

-Example End-



Why are these odd statements being passed to the
server?

Shouldn€™t the provider be able to handle the returned
dataset without sending the FMTONLY statement in advance? And if not, why doesn€™t
it cache this information?



Is it possible to minimize this traffic?



I also spotted quite a lot €˜SET NO_BROWSETABLE ON/OFF€™
statements. What is the use for these?



I found this article (http://support.microsoft.com/kb/836830/en-us)
on MSKB, but I should already have the hotfix for this installed by MDAC 2.8 SP1!


This slow pace is killing me €“ please help - egeskov



My configuration:

Workstation:

WinXP SP2

MDAC 2.8 SP1
(2.81.1117.0)

ConnectionString=€™Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Application Name=PD:KE;Data Source=SMIL;Initial
Catalog=PDDebug€™

Server:

Windows
Server 2000
SP4

MS SQL
Server 8

View 12 Replies View Related

How Can I Remove The Line Feed/carriage Return In The Last Line Of The Exported Text File ?

Feb 27, 2007

Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:

original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line

The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90

Should I use script component to do it ? I am new for VB . Anyone would help me ?

Thank you all.

View 1 Replies View Related

Reporting Services :: Draw Trend Line For SSRS Line Chart 2005

May 4, 2012

I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.

This is the link [URL] ....

I need the same trend line for the Bar-Chart in SSRS 2005.

View 5 Replies View Related

Storing And Retrieving Line Breaks/newlines From Multi-line Textbox (C#)

Aug 31, 2007

I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it.  I wrote a basic commenting system in which someone my post a comment about something written on the site.  I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that.  Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!

View 4 Replies View Related

ISQL: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect Syntax Near ' '

Nov 8, 2006

G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies

View 3 Replies View Related

Displaying A Trend Line (in Line Chart) In SSRS

Feb 7, 2007

We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.

View 1 Replies View Related

Dynamically Change The Color Of The Line On A Line Graph

Oct 26, 2007

I have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?

kam

View 4 Replies View Related

How To Monitor Store Procedure (Line By Line)

Sep 29, 2001

HEllo can anybody tell me how to monitor a long store procedure
line by line. Also how to put progress bar in it to tell user how
much is done.

Sabih.

View 1 Replies View Related

BIDS Line Charts - Cant Remove Line?

Dec 12, 2007

Hi,

When creating a line chart, I would like to be able to show Markers for the data points, but no line between these points (as you can do in excel).

I have set the line setting to none (for the lines of interest), however the lines still show.

Is this a bug, or am i missing something obvious settings-wise?

Cheers,

M

View 7 Replies View Related

Line 1: Incorrect Syntax Near '-'. But There Is No '-' In First Line!

Dec 28, 2007

  HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
 and i face this error: Line 1: Incorrect syntax near '-'.

View 3 Replies View Related

Line Chart - Line Styles

Jan 16, 2008



Hi

Anyone have any idea how to make a line style dashed or dotted in a line chart please?

If I change the series style to dashed or dotted it still appears as a solid line, yet the legend displays a dashed or dotted line....

Thanks

View 7 Replies View Related

Style: Constraints - In-line Vs Out-of-line?

Apr 18, 2008

My question is about coding style for specifying constraints when creating tables.

Two styles for defining constraints:



In-line:
CREATE TABLE Fruit
(
FruitID INT IDENTITY(1,1)
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
FruitName NVARCHAR(50),
FruitTypeID INT
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
REFERENCES FruitTypes (FruitTypeID) ON UPDATE CASCADE,
DateCreated DATETIME DEFAULT GETDATE()
)
Out-of-line:
CREATE TABLE Fruit
(
FruitID INT,
FruitName NVARCHAR(50),
FruitTypeID INT,
DateCreated DATETIME
)

ALTER TABLE Fruit ALTER COLUMN FruitID INT NOT NULL

ALTER TABLE Fruit ADD
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (FruitID),
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (FruitTypeID)
REFERENCES FruitTypes (FruitTypeID),
CONSTRAINT DF_fruit_date_created DEFAULT
GETDATE() FOR DateCreated

Which style do you prefer and why?

View 2 Replies View Related

Problem Showing Data From Select Line With Another Select Line In It

Apr 7, 2007

Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.

View 2 Replies View Related

No Record Added On My DB

Nov 22, 2006

Hi! could you please check my code on adding record on my DB.. Im using SQL Server included on VWD.Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim con As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Request.mdf;Integrated Security=True;User            Instance=True"        Dim sqlcon As New Data.SqlClient.SqlConnection(con)        Dim sqlcmd As New Data.SqlClient.SqlCommand("Insert into uRequest (eticket, ename, edept, edetails, ejobpend, edate) values (@eticket,@ename,@edept,@edetails,@jobpend,@edate)")        sqlcon.Open()        sqlcmd.Parameters.Add("@eticket", Data.SqlDbType.Char, 10).Value = "11-0010-06"        sqlcmd.Parameters.Add("@ename", Data.SqlDbType.Char, 100).Value = User.Identity.Name        sqlcmd.Parameters.Add("@edept", Data.SqlDbType.Char, 50).Value = DropDownList1.Text        sqlcmd.Parameters.Add("@edate", Data.SqlDbType.DateTime).Value = Date.Now        sqlcmd.Parameters.Add("@edetails", Data.SqlDbType.Text).Value = TextBox1.Text        sqlcmd.Parameters.Add("@ejobpend", Data.SqlDbType.Text).Value = TextBox2.Text        sqlcon.Close()    End Sub  I've check the Data on my DB but nothing has been added. nwy, how can u create a confirmation message if the record has been successfully added.Thanks and sorry for the trouble. 

View 1 Replies View Related

How To Get The Last Item Added

Dec 14, 2004

Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?

TABLE Sys_Con
SystemID int
ContactID int

Table Contact
ContactID int Identity
ContactName
...

Table System
SystemID
...

View 3 Replies View Related

Delete Last Added Row -how To

Jul 23, 2005

hi i have a question how can i delete last added row. I have 2 tables .source and destination . I take a 1 row from source table , do someoperation on it and save to destination table . after succesfull written Iwant to delete added row from source table.. i'm using a coursors. the mainproblem is : is there any function to check which row was last added. Now Iam doing it using select * from destionation where (and necessaryconditions). but if destination table will be 100000000 rows for example ittakes too much time... Is there another possibility to do it ???please helpMarcin Wolkuwolku

View 2 Replies View Related

Can't See Added Columns In ASPNETDB.mdf

Sep 27, 2006

In developing a VWDE project I added 2 columns to the User table in the ASPNETDB.mdf database.  I can see the columns in the Data Definition and I can see the values I added when I Show Table Data but I cannot access them with a SQLDataSource control?   The SQLDataSource shows all of the columns in that table except the ones I added.  Any suggestions? Thanks.

View 2 Replies View Related

Retrieve The ID Of The Record Just Added

Dec 12, 2003

I would like to retreive the identity field value of a record that was just added to the table.

In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.

This may be overkill, but here is my code to process the form:

Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @RegionID, " & _
"DistrictID = @DistrictID, " & _
"DateReported = @DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@RegionID, " & _
"@DistrictID, " & _
"@DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select

Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DateReported", SqlDbType.NVarChar))
...CODE...

sqlComm.Parameters.Item("@RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...

Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub

The response.redirect at the end of the sub is where I would like to put the identity field value.

View 6 Replies View Related

How Many Triggers Can Be Added In A Table

Jan 4, 2006

How many triggers can be added in a table?

View 2 Replies View Related

Positioning An Added Column...

Jan 28, 2004

how do I position a newly added column in the table?
the default is at the very end.

what would I have to add to this code:

ALTER TABLE Vehicles
ADD Model varchar(55)

Thanks

View 2 Replies View Related

Emailing After A Record Has Been Added

Jan 8, 2008

Hi,
Is there a built in capability with sql server 2005 which sends emails to users upon a record insertion.
Thanks

View 2 Replies View Related

Sp To Email Me If No Records Added

Mar 30, 2008

is there a way to set up a scheduled sp to email me if no records are added to a certain table within the hour?

is this possible?

View 1 Replies View Related

Unique ID Added To A Result Set

Oct 5, 2005

Hello all, i have a question i've beenk thinking for a little bit andcant seem to come up with a solution. What i want to do is add a uniquecounter to a resultset in query. For exampleSELECT ColumnA, ColumnB, ColumnC, ColumnDFROM AnyCombinationOfJoinsOrTablesWHERE SomeConditionIsTruewThe query above will produce a resultest of 4 columns.'A1', 'B1', 'C1', 'D1''A2', 'B2', 'C2', 'D2''A3', 'B3', 'C3', 'D3'......'An', 'Bn', 'Cn, 'Dn'Now what i want to do is add a unique identifier to every row. Theeasiest way would be to add a counter integer so the resultset wouldlook like1, 'A1', 'B1', 'C1', 'D1'2, 'A2', 'B2', 'C2', 'D2'3, 'A3', 'B3', 'C3', 'D3'......n, 'An', 'Bn', 'Cn, 'Dn'Is there a way to achieve this in T-SQL without using cursors.Thanks,Gent

View 3 Replies View Related

Added Two More User Parameters

Sep 14, 2007



I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.





Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]
@Question char(80)
AS
BEGIN

SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date


END
SET NOCOUNT OFF


Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.



Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]
(@Region_Key int=null,@QuestionCode char(5),@Question char(80))
AS
BEGIN

SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo'
AND REGION_KEY=@Region_Key
AND LEFT(Qry_Questions.[Question Code],2)IN (@QuestionCode)
AND Qry_Questions.Question=@Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date



END
SET NOCOUNT OFF




View 6 Replies View Related

Added /3GB To Boot.ini And OS Will Not Start

Sep 19, 2007

Here is a new one for me. I added the /3GB switch to the boot.ini, restarted the server and now receive a number of messages when the OS tries to load.

Explorer.exe - Application Error
The application failed to initialize properly.

If I click OK the server sits on a blue screen with no information on the screen.

The other messages are the result of me trying to do anything within Task Manager, with the exception of rebooting the server. These messages make sense since Explorer.exe had a problem.

The server has the following configuration.

HP DL580
Windows Server 2003 Enterprise Edition - SP2
SQL Server 2000 Enterprise Edition - SP3a
14GB Memory
(4) dual core procs
PAE is enabled in the boot.ini

If I remove the /3GB the OS starts without a problem. Any suggestions?

Thanks, Dave

View 8 Replies View Related

Starting A VB Routine When A SQL Row Is Added

Jan 3, 2008



I'm running vb 2088 express with SQL express. I have limited VB and very limited SQL knowledge.



I have a VB app that inserts candidate data into the SQL table (called front) all works fine.



I now have a second VB app (separate from the above VB app) that runs at the same time; it does some extra work on the candidate data.



My question is when a new candidate is added to the SQL table, how I can get the second VB app to automatically know that a new record has been inserted (and the record key) and then start the VB subroutine.



I'm unsure if I should look at triggers or stored procedures (or something else) for this task.



Any help or pointers to additional resources would be appreciated.



Thank you!



TJ


View 7 Replies View Related

I Added A Column To A Table, But, I Can't See It.

May 15, 2007

I ran the code below that adds a column to an SQL table.

I refreshed Data Sources, but, the new column does not appear.

I refreshed Server Explorer and the new column does not appear.

I tried running the code again and I got:

"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."

so the column did actually get added, but, I can't see it.



Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Try

Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Patient.mdf;Integrated Security=True;User Instance=True")

Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"

Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)

sqlConnection.Open()

command.ExecuteNonQuery()

sqlConnection.Close()

'Catch

'End Try

Close()

End Sub

End Class

View 5 Replies View Related

Returning The Identity Of The Last Value Added...

Nov 20, 2006

Hi All,

I have a createStudent SProc.  When this is called it calls a createContact SProc.  This in turn calls a Create Address Stored procedure.  Now when I create the address, an Identity column automatically creates a new Identity for me.  I need to return this value back to the calling stored procedure.  I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably?  I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...

Thanks heaps

Chris

View 6 Replies View Related

Odd Characters Added To T-SQL Automatically

Aug 2, 2007



I am using Reporting Services 2000 and therefore VS 2003 and am having a problem editing the T-SQL query in the data view of Visual Studio.

Basically if I manually edit the T-SQL query or touch anything in the designer view of the query I get odd characters added in the T-SQL and the query no longer works. I have tried re-installing VS2003 and RS 2000 but it's still happening.

FYI I also have SQLServer 2005 and VS 2005 on my machine as all applications we develop are in .Net 2.0 and 3.0. We only use VS2003 for RS.

Does anyone have a clue how to fix this? Thanks

View 3 Replies View Related

Retrieve The Identifier Of The Newly Added Row

Apr 14, 2005

Howdie y'all,
I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one....
CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO
I actually would like to get value of the UserId column for the newly added record.
Can anyone of you folks help me with this?
Cheers,
Wes

View 4 Replies View Related

Trailing Spaces Added To All Fields

Mar 15, 2006

For some reason, there are extra trailing spaces being added to all my
data as it is placed in the db.  I am collecting information from,
processing a TRIM-like function in javascript, and then again in the
ASP.net code before it is being placed in the db. 

The extra spaces are causing problems with my application.

Any help would be greatly appreciated.

View 3 Replies View Related







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