Problem with SP

Hello All!
CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
@.Wager Nvarchar(50))
AS
IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
begin
Update BankRoll
Set Wager = Wager + CAST(@.Wager as Nvarchar)
where userID = @.userID
return -1
end
ELSE
begin
Insert Bankroll (Wager) Values (@.Wager)
RETURN @.@.IDENTITY
end
GO
Having a little problem with the SP above. I'm trying to take a value from
the a "Wage" textbox, and update the value in the table. The userID number
is a value in the userID texbox, and already created in the SQL table. I
need to make sure the the wage value will go in the right row, where the use
r
ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
Procedure PlbetII, Line 13
Cannot insert the value NULL into column 'UserID', table
'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'PlbetII' procedure attempted to return a status of NULL, which is not
allowed. A status of 0 will be returned instead."
I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager +
CAST(@.Wager as Nvarchar)
where userID = @.userID" might be my problem, but I thought it's just
returning the value for user id and selecting it. I'm so .
Any help would be great!!!
TIA!!
RudyThe identity value (or autoincrement) for the column userId is not set,
hence the impossibility of inserting a new record at line 13 without
specifying the value for UserId. When creating a new record, you have two
choice: specify the value for UserId or ask SQL-Server to automatically
create a new value each time by setting the identity property.
From you piece of code, it's impossible to tell which one of these two
methods you should use.
Also, I'm not sure if the line Set Wager = Wager + CAST(@.Wager as
Nvarchar) will do what you are expecting it to do.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:B0D0D405-F131-4106-8602-E5F77D684B4D@.microsoft.com...
> Hello All!
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from
> the a "Wage" textbox, and update the value in the table. The userID
> number
> is a value in the userID texbox, and already created in the SQL table. I
> need to make sure the the wage value will go in the right row, where the
> user
> ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
> Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager
> +
> CAST(@.Wager as Nvarchar)
> where userID = @.userID" might be my problem, but I thought it's just
> returning the value for user id and selecting it. I'm so .
> Any help would be great!!!
> TIA!!
> Rudy
>
>|||Rudy (Rudy@.discussions.microsoft.com) writes:
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from the a "Wage" textbox, and update the value in the table. The
> userID number is a value in the userID texbox, and already created in
> the SQL table. I need to make sure the the wage value will go in the
> right row, where the user ID matches. I get an error in SQL,
> "Server: Msg 515, Level 16, State 2, Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID.
Obviously the table does not have the IDENTITY property. If I am to
believe your procedure, the UserID column is nvarchar(50), why IDENTITY
is completely off-track.
Just say:
Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
By the way, the RETURN statement is mainly used to indicate
success/failure, with success being 0 and anything else means an
error. The regular way to return a value is to use an OUTPUT
parameter.
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.mspx|||Thank you Erland and Sylvain!
I did change things up a little bit per you sugestions. Still having
problems, but I suspect something with the table itself. I deleted the table
,
and built it again, and now it works fine.
Thanks again!
Rudy
"Erland Sommarskog" wrote:

> Rudy (Rudy@.discussions.microsoft.com) writes:
> Obviously the table does not have the IDENTITY property. If I am to
> believe your procedure, the UserID column is nvarchar(50), why IDENTITY
> is completely off-track.
> Just say:
> Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
> By the way, the RETURN statement is mainly used to indicate
> success/failure, with success being 0 and anything else means an
> error. The regular way to return a value is to use an OUTPUT
> parameter.
> --
> 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.mspx
>|||Can run the following query and post the output.
select COLUMNPROPERTY( object_id('Bankroll') , 'pub_id' , 'AllowsNull' ) as
nullable,
COLUMNPROPERTY( object_id('Bankroll') , 'userID' , 'IsIdentity' ) as
[autogenID]
And a few observations:
Why is UserID nvarchar?
and why do you do this step?
Wager + CAST(@.Wager as Nvarchar)
when @.wager is already nvarchar.. and are you trying to append the string
wager or add the wager to the existing value? Is Wager a numeric value or ha
s
characters like the dollar and pounds.
It will be better to answer your question if you give table definition and
test data
so that we can reproduce the error here.

Problem with select to another server.

Hello All!

This is the first time I'm working between two SQL servers. Here is a simple SELECT statement.

DECLARE @.Acct nvarchar(50)

SELECT First_Name, Last_Name, Age, DOB, Account_Number

FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

Here is the error

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "MKE01-2NX2461.Pharm Test Local.dbo.Active_Orders" could not be bound.

What am I doing wrong?

Thanks!

Rudy

MKE01-2NX2461 is set up as a Linked Server in MKE01-Demo-XX?

|||

You are attempting to compare a table to a variable.

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

It would be nice, but it isn't going to happen...

But more critically, I don't see any relationship between the two tables from the two databases. How would a row from [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders be connected to the table [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders in such a way that using the variable value against one table 'should' return a row from the other table.

I think that something isn't quite right here... (Perhaps a JOIN is missing.)

|||

Hi guys!

Ah yes. A JOIN would make sense. Mke Demo is the linked serve on MKe 29nx... So let me give that a shot. I'm sure I'll be back here with a question or two.

Thanks!

Rudy

|||

Perhaps something more like this?

Code Snippet

DECLARE @.Acct nvarchar(50)

SET @.Acct = {someValue}

SELECT
x.First_Name,
x.Last_Name,
x.Age,
x.DOB,
x.Account_Number
FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders x
JOIN [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders t
ON x.Account_Number = t.Account_Number
WHERE x.Account_Number = @.Acct

This assumes that the values you wish to return are located in the [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders table, and that both tables have the Account_Number column to link the data.

problem with select stored procedure

i have this stored procedure:

ALTER PROCEDURE dbo.SearchContact
@.searchCriteria nvarchar(128)
AS
Select FstNam1,FstNam02 from Contacts where FstNam1 like '%'+@.searchCriteria+'%'
RETURN

In my .aspx i have a SqlDataSource named SqlDataSource1 which have asocciated this stored procedure to select operation and parameter source of 'searchCriteria' is Control and ControlID is "TextBox1".

Also i have a gridview with source this sqlDataSource1 and a button . When i click this button i want to take value entered in textbox and send it to above stored procedure and show returned infos in my gridview.

i put in

Button1_Click(object sender, EventArgs e)

{

SqlDataSource1.SelectParameters["searchCriteria"].DefaultValue = Session["searchContact"].ToString();

xxxxxx
}

what i need to have to xxxxx to work fine ?

I searched for a solution and i find a lot of posibilities, but all are fragmented.......pls give a solution ...

thx for help.

P.S: Sorry for my english.

try use valid parameter name:

SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = Session["searchContact"].ToString();

|||

Get rid of the Button1_Click event handler, and in the source view of your aspx, make sure that the SqlDataSource is bound to the GridView (DataSourceID="SqlDataSource1"), and add a SelectParameter to the SqlDataSource:

<SelectParameters>
<asp:ControlParameter ControlID="TextBox1"
Name="searchCriteria"
PropertyName="Text"
Type="String" /> />
</SelectParameters>

Also, I don't know why you are storing the search phrase in a Session variable. This doesn't seem necessary, unless you are using it for something else.

|||

nice. I understand do not use session variable for this , but after i do that , what`s next to see a result?

thx.

|||

that`s the code from aspx.cs

protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = TextBox1.Text;
GridView1.DataBind();
}
catch (Exception ex)
{
}
finally
{

}

that`s the code from aspx.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
SelectCommand="SearchContact" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="searchCriteria" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

when i debug that function, i receive a exception : "System.NullReferenceException: Object reference not set to an instance of an object."

how fix this problem???

|||

Like I said earlier, get rid of the code:

protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.SelectParameters["@.searchCriteria"].DefaultValue = TextBox1.Text;
GridView1.DataBind();
}
catch (Exception ex)
{
}
finally
{

}

You don't need this. There are two ways to perform data access. One is to use a SqlDataSource and let that do all the work for you, and the other is to write code. You are mixing the two. All you need in your aspx is a textbox, button, gridview and data source control. No code in the code behind at all.

|||

Mikesdotnetting, i remove the "protected void Button1_Click(object sender, EventArgs e)" and in .apx.cs is clear ( no line of code), and .aspx is the same. I don`t see any change.

Pls help me to fix it.

|||when i making test to gridview - testquery - it works fine. To have nothing in Button1_Click , how gridview know to make databind ?? Pls make some light in my mind.|||

Ah - magic.

The SqlDataSource control takes care of creating a connection object, command object, parameter objects and databinding, behind the scenes. All you have to do is tell the datasource (declare) what the connection string is, or where to find it, where any parameter values come from, and their datatype, and what command to execute. Oh, and you have to tell the GridView what datasource to use. It's called the Declarative DataBinding Model. All the internal workings of how it does its thing are abstracted away from the user. Perfect OOP.

|||

And after all you said, i didn`t make it to work :( .

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Find" Width="65px" /*not have on click, as you said*/ /><br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString1 %>"
SelectCommand="SearchContact" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="searchCriteria" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

when i select gridview "configure data source" an make a test query i works fine, no problem. ...but when i run the page in browser and i click on button ......nothing ...

What are my mistakes?? pls write for me some code...how to do...thx a lot

|||Change AutogenerateColumns to true in the GridView, and in the configuration for the GridView, click Refresh Schema. If you set it to false, you have to create your own ItemTemplate.|||

thx man ! it works.

Thx a lot !

|||

hi

if that work then mark as answer

thanks

|||

there i have another problem:

my gridview is populated ok , but works very slowly. I need to have about 20 - 40 textboxes and dropdownlists on my page. i put on page a scriptManager and all control are putted in an UpdatePanel . when i search a item in database , my gridview is populated very slowly..

what is wrong?..how can i fix this problem?

|||If you have a different problem, you should start a new thread in an appropriate forum for the problem. This one has been marked as resolved, so I'm probably the only person reading it now. Your new problem is related to AJAX. The subject of this thread is stored procedures. No one who knows about AJAX will see your question. I don't use ASP.NET AJAX, so I am afraid I can't help you with this one.

Problem with Searching unicode strings

Hi,
I have problem with Contains command . I've made index on FName column of my
database which is nvarchar(100).
When I call contains command to search for english strings , it works very
well .But when I search for unicode strings ( I use farsi strings and insert
them in unicode ), sometimes it can not find them , in more than 70 percent
unicode searches , it works correctly , but sometimes it can't find very
simple strings, for example : it can't find " ^??? " strings . I use
these commands to find :
Select * from MyDB where Contains(FName, N' ^??? ' )
or
Select * from MyDB where Contains(FName, ' ^??? ' )
but no one can find the string.
please tell me what is wrong in command or what can I do to solve this
problem.
In addition , I want to send result of Select @.@.version command for you .
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Thanks in Advance
Hamid.
what collation are you using? This works for me.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hamid" <hamid@.omid.ca> wrote in message
news:OcaRgT9IFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> I have problem with Contains command . I've made index on FName column of
my
> database which is nvarchar(100).
> When I call contains command to search for english strings , it works very
> well .But when I search for unicode strings ( I use farsi strings and
insert
> them in unicode ), sometimes it can not find them , in more than 70
percent
> unicode searches , it works correctly , but sometimes it can't find very
> simple strings, for example : it can't find " ^??? " strings . I use
> these commands to find :
> Select * from MyDB where Contains(FName, N' ^??? ' )
> or
> Select * from MyDB where Contains(FName, ' ^??? ' )
> but no one can find the string.
> please tell me what is wrong in command or what can I do to solve this
> problem.
> In addition , I want to send result of Select @.@.version command for you
..
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
> ----
> Thanks in Advance
> Hamid.
>
>
|||Hi,
These documents could not help me.
I've used SQL_Latin1_General_CP1_CI_AS collation in database. As I told, all
English strings will find by use of contains where-clause , My problem is
with Unicode stringe , which it can not find them . It find about 70 percent
of strings and it ignores some strings in search.
Please tell me how can I solve this problem.
Any Comments or suggestions appreciated.
Hamid.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u2ZyToEJFHA.2356@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> what collation are you using? This works for me.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Hamid" <hamid@.omid.ca> wrote in message
> news:OcaRgT9IFHA.904@.tk2msftngp13.phx.gbl...
of[vbcol=seagreen]
> my
very[vbcol=seagreen]
> insert
> percent
you
> .
> Windows
> ----
>

Problem with Search Unicode Strings by use of <Contains>

Hi,
I have problem with Contains command . I've made index on FName column of my
database which is nvarchar(100).
When I call contains command to search for english strings , it works very
well .But when I search for unicode strings ( I use farsi strings and insert
them in unicode ), sometimes it can not find them , in more than 70 percent
unicode searches , it works correctly , but sometimes it can't find very
simple strings, for example : it can't find " ^??? " strings . I use
these commands to find :
Select * from MyDB where Contains(FName, N' ^??? ' )
or
Select * from MyDB where Contains(FName, ' ^??? ' )
but no one can find the string.
please tell me what is wrong in command or what can I do to solve this
problem.
In addition , I want to send result of Select @.@.version command for you .
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Also the Collation of Database and FName-field is :
SQL_Latin1_General_CP1_CI_AS
Thanks in Advance
Hamid.
Hamid,
First of all, thank you for providing the @.@.version (SQL Server 2000 SP3 on
Windows 2000 Server SP4) information along with the language (Farsi -
Iranian / Persian) and an example of your CONTAINS queries as this is most
helpful in helping you solve this problem!
Unfortunately, Farsi is not one of the subset of SQL Server 2000 supported
languages that Full Text Search supports, see SQL Server 2000 Books Online
title "Column-Level Linguistic Analysis", Note that it states that you
should "Use neutral when a column contains data in multiple languages or in
an unsupported language". Most likely, you have the FName column's "Language
for Word Breaker" set to US English. Could you confirm this with your reply
via sp_help_fulltext_columns ?
Assuming that you have your FT-enable column (FName) "Language for Word
Breaker" set to US English, you should drop the FT Catalog and re-create it
with the "Language for Word Breaker" set to Neutral and then run a Full
Population and re-test your CONTAINS query.
Additionally, you might want to checkout the new "Microsoft Arabic
Word-Breaker (Arabic Search Engine) - Beta" at
http://www.microsoft.com/middleeast/...v/beta/search/ and download the
Installation Guide and the Microsoft Arabic Word-Breaker. Now, I am not a
linguist, and I do not know how close or far apart Farsi or Persian is from
Arabic, but I'd suggest that you test it and let this newsgroup know if you
find it effective in resolving your FTS issues with Farsi strings.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Hamid" <hamid@.omid.ca> wrote in message
news:e5Z4#hsJFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have problem with Contains command . I've made index on FName column of
my
> database which is nvarchar(100).
> When I call contains command to search for english strings , it works very
> well .But when I search for unicode strings ( I use farsi strings and
insert
> them in unicode ), sometimes it can not find them , in more than 70
percent
> unicode searches , it works correctly , but sometimes it can't find very
> simple strings, for example : it can't find " ^??? " strings . I use
> these commands to find :
> Select * from MyDB where Contains(FName, N' ^??? ' )
> or
> Select * from MyDB where Contains(FName, ' ^??? ' )
> but no one can find the string.
> please tell me what is wrong in command or what can I do to solve this
> problem.
> In addition , I want to send result of Select @.@.version command for you
..
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
> ----
> Also the Collation of Database and FName-field is :
> SQL_Latin1_General_CP1_CI_AS
> Thanks in Advance
> Hamid.
>
>