SQL Data Convertion Error??

Dec 19, 2006

HI,

I'm encouter some data convertion string problem especially during database update use asp.net (vb.net). Please help advice how can i solve the error??

I guess integer value need convert to string before able execute update function..

The error i get: -

1) failed to convert nvarchar to int

2) Convertion string is not in correct format...

let say i drag a textbox (txtquantity) and allow input and click update (with call procedure) updatexx set quantity=2 select from YY.

Wondering if i missed some important code??

Another data converting errorsituation is: let say i create a class call "AA" then have properties quantity as string, sProductID as string

i retrieve the data

........function get info(by ProductID as string) 

If reader.Read() Then
            OrdInfo.quantity = reader("Quantity").ToString()
            OrdInfo.ProductID = reader("ProductID").ToString()

.....

sub update BB (byval aa as AA)

...

   command.Parameters.Add("@Quantity", Data.SqlDbType.Int)
        command.Parameters("@Quantity").Value = AA.quantity


        command.Parameters.Add("@ProductID", Data.SqlDbType.Int)
        command.Parameters("@ProductID").Value = AA.sProductID

Cheers:)

View 1 Replies


ADVERTISEMENT

Data Convertion Error Help

Dec 15, 2006

Hi,
 I have an error: "*erro while update quantity. Error converting data type nvarchar to int "while i try update data through form page. Does anybody have any idea how can i correct the error??
 I didnt try two methods but both given same error and failed update: -
1)   Dim sqlcomm As New SqlCommand(sSaveQuote, rConnect).....
 sqlcomm.Parameters.AddWithValue("@employeeID", sUserID)            sqlcomm.Parameters.AddWithValue("@quantity", txtquantity.Text)
 
2) Error converting data type nvarchar to int ??        Dim rConnect As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("CRNS_CustomerConnectionString").ConnectionString)        Dim command As SqlCommand = New SqlCommand("UpdateOrder", rConnect)        command.CommandType = Data.CommandType.StoredProcedure
If OrderInfo.Verified.ToUpper = "True" Or OrderInfo.Verified = 1 Then
            command.Parameters.Add("@Verified", Data.SqlDbType.Bit)            command.Parameters("@Verified").Value = 1        Else            command.Parameters.Add("@Verified", Data.SqlDbType.Bit)            command.Parameters("@Verified").Value = 0        End If
  command.Parameters.Add("@Comment", Data.SqlDbType.VarChar, 100)        command.Parameters("@Comment").Value = OrderInfo.Comment
 command.Parameters.Add("@ProductID", Data.SqlDbType.Int)        command.Parameters("@ProductID").Value = OrderInfo.ProductID
        command.Parameters.Add("@OrderDate", Data.SqlDbType.SmallDateTime)        command.Parameters("@OrderDate").Value = OrderInfo.OrderDate
Cheers:)
Christe

View 3 Replies View Related

Data Convertion Error

Jul 19, 2007

I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.



Stored Procedure



ALTER Procedure [dbo].[WDR_CityAdd1]

(

@CountryID int,

@CityName nvarchar(50),

@InternetAvail bit,

@FlowersAvail bit,

@CityID int OUTPUT

)

AS

IF EXISTS(SELECT 'True' FROM city WHERE CityName = @CityName AND CountryID = @CountryID)

BEGIN

SELECT

@CityID = 0

END

ELSE

BEGIN

INSERT INTO City

(

CountryID,

CityName,

InternetAvail,

FlowersAvail

)

VALUES

(

@CountryID,

@CityName,

@InternetAvail,

@FlowersAvail

)

SELECT

@CityID = 'CityID' ( I have also tried = @@Identity but that never returned anything it is an identity column 1,1)

END



Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.



Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)

myCommand.CommandType = CommandType.StoredProcedure



Dim parameterCityName As New SqlParameter("@CityName", SqlDbType.NVarChar, 50)

parameterCityName.Value = CityName

myCommand.Parameters.Add(parameterCityName)



Dim parameterCityID As New SqlParameter("@CityID", SqlDbType.Int, 4)

parameterCityID.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterCityID)

Try

dbConn.Open()

myCommand.ExecuteNonQuery()

dbConn.Close()

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

'AlertMessage = "City Added"

Catch ex As Exception

AlertMessage = ex.ToString

End Try



Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?



System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError



Thanks

Jerry

View 3 Replies View Related

Convertion VarChar Error

Jan 12, 2005

Ive got a small problem at the moment

I have ran a query that has been used for a while now and have recieved this error


Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'N' to a column of data type int.


Ive searched the data and the only value of N that i can find is currently sitting in a field where the field type is varchar

is there a workaround for this, Ive tried running a case statement to set the N to 0 and also tried casting

Cheers in advance
Dave

View 4 Replies View Related

Convertion Datetime Error

Jan 29, 2008

i am getting error while convering datetime datatype using

convert(x,101)
function

Arithmetic overflow error converting expression to data type datetime

please help me

View 11 Replies View Related

Data Convertion

May 15, 2008

Hi guys,

need your help.

what can I do to convert the ff:

00001250 to 000001250

what syntax should I use?

tnx

View 3 Replies View Related

SQL Data Convertion Problem Help

Dec 20, 2006

HI,

I'm encouter some data convertion string problem especially during database update use asp.net (vb.net). Please help advice how can i solve the error??

I guess integer value need convert to string before able execute update function..

The error i get: -

1) failed to convert nvarchar to int

2) Convertion string is not in correct format...

let say i drag a textbox (txtquantity) and allow input and click update (with call procedure) updatexx set quantity=2 select from YY.

Wondering if i missed some important code??

Another data converting errorsituation is: let say i create a class call "AA" then have properties quantity as string, sProductID as string

i retrieve the data

........function get info(by ProductID as string)

If reader.Read() Then
OrdInfo.quantity = reader("Quantity").ToString()
OrdInfo.ProductID = reader("ProductID").ToString()

.....

sub update BB (byval aa as AA)

...

command.Parameters.Add("@Quantity", Data.SqlDbType.Int)
command.Parameters("@Quantity").Value = AA.quantity


command.Parameters.Add("@ProductID", Data.SqlDbType.Int)
command.Parameters("@ProductID").Value = AA.sProductID

Cheers:)











View 1 Replies View Related

SSIS Data Convertion Problem

Dec 14, 2006

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1017518&SiteID=1

View 3 Replies View Related

Date Convertion && Data Cleaning Questions

May 5, 2008

I have few questions

1.Input column has dates in this format.
90Q1(this is 1990,march)
90Q2(this is 1990 june)

Is there a way to convert 90Q1 to 1990 march?

2.Patient Name column has to be split into Last name,First Name, Middle Name

Can this be done??

3.For one column i have _9 at the end of each ID

How do i remove _9??Is there a way to remove?


Please let me know

View 1 Replies View Related

Convertion

Mar 5, 2008

can any one help how to convert the 20070701( varchar data type)
to 'JUL-07'

View 4 Replies View Related

SQL Money Convertion,

Apr 24, 2005

Hi All,
 
Here in Belgium, we work with a comma as decimal seperator, also in all the web apps...
I've tried to update a money table on the sql with following statement
update parts set article = '" & art & "' and price= cast(" & p & "  as decimal(5,2)) where supid=202
in this example p is a variable and contains figures like 5,7
This statement always give following error Incorrect syntax near the keyword 'as'
does some have an idea how to fix this one ?
 
thx

View 4 Replies View Related

Int To Datetime Convertion

Nov 19, 2007

Hi,

Actually i tried to convert the datetime value in yyyymmdd format from the int data type field.But except the first record it is looking fine.i cann't understand why this is happening? can you help me to resolve this problem?


Select ABECDT,case when ISDATE(cast(ABECDT as varchar))=1 then cast(ABECDT as varchar)
when len(cast(ABECDT as varchar))=8 then right(cast(ABECDT as varchar),4)+''+left(cast(ABECDT as varchar),2)+''+substring(cast(ABRDAT as varchar),3,2)
when len(cast(ABECDT as varchar))=7 then right(cast(ABECDT as varchar),4)+'0'+left(cast(ABECDT as varchar),1)+''+substring(cast(ABRDAT as varchar),2,2)
when len(cast(ABECDT as varchar))=6 then case when right(cast(ABECDT as varchar),2)<50 then '20' else '19' end
+right(cast(ABECDT as varchar),2)+''+substring(cast(ABECDT as varchar),3,2)+''+left(cast(ABECDT as varchar),2)
else NULL end from parm

Result i got it from the above query

Actual Value Result from the query

1101200120011111
1001200220021001
1201199819981201
2011998 19980210
6012002 20020601

Can anyone help me to resolve this?

Thanks and Regards

Senthil

View 2 Replies View Related

Date Convertion

Feb 28, 2008

can anybody tell how to convert the date into the below format
datetime 2007-12-01 00:00:00.000
To 20071201

View 14 Replies View Related

Convertion Problem

May 12, 2008



How to convert unicode string data into int.?????


My lookup shows up with this error when i map id column

TITLE: Microsoft Visual Studio
------------------------------
The following columns cannot be mapped:
[COPY OF IID, Id]
One or more columns do not have supported data types, or their data types do not match.
------------------------------
BUTTONS:
OK
------------------------------

View 1 Replies View Related

Fixed Decimal Convertion

Dec 6, 2007

I am trying to show latitude and longitude with 5 decimal points. Now its showing (for example: 55.744025477, -4.1256633333333 etc.). How do I get  data in 5 decimal points? Your help with example would be appreciated.  aspx code:  <asp:GridView  ID="GridView1" runat="server" DataSourceID="odsGPS" AllowPaging="true" AllowSorting="true"     AutoGenerateColumns="False" CellPadding="1" CellSpacing="1" BackColor="White" GridLines="None"     BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" PageSize="20" Width="100%" Font-Size="8pt"     OnLoad="GridView1_Load" >        <Columns>            <asp:TemplateField HeaderText="Show">                <ItemTemplate>                    <asp:CheckBox ID="CheckBox2" onclick="MarkerForThisRow(this);" ToolTip="Click to show on map." runat="server" OnCheckedChanged="CheckBox2_CheckedChanged" />                </ItemTemplate>                <ItemStyle HorizontalAlign="Center" />                <HeaderStyle HorizontalAlign="Center" />            </asp:TemplateField>                                  <asp:BoundField DataField="Latitude"  HeaderText="Latitude ( ° )"  >                <ItemStyle HorizontalAlign="Center"  />                <HeaderStyle HorizontalAlign="Center" />            </asp:BoundField>            <asp:BoundField DataField="Longitude"  HeaderText="Longitude ( ° )" >                <ItemStyle HorizontalAlign="Center" />                <HeaderStyle HorizontalAlign="Center" />            </asp:BoundField> 
 

View 9 Replies View Related

Monay Convertion Problems

Jan 15, 2008

Hi Guys:
I want to convert a String from .net to money in sql server 2005. I use VB.net and sql 2005 stored procedures too.
I don’t care if this convert is done in the .net side or in sql 2005 side.
This is the exactly case: as I am in south America, for currency or money data type the people write 1234,12 this is similar to the US format 1234.12 (we change the . for the , )
So I want let the people to introduce our format 1234,12 and in the database convert it to 1234.12My problem is that I pass it like string and in the database I do
CONVERT(MONEY,RTRIM(LTRIM(@payment)))
 But it converts my number 1234,12 in 123412.00 and this two number are not equivalent. Somebody can help me please?
thanks !
Marcos

View 3 Replies View Related

Date And Time Convertion

Jan 26, 2008

Hi, I am trying to update my database i am using the select statement:
SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment];
 however when i try to update this i am not able to select my date and time columns.
Thanks Mike

View 4 Replies View Related

Date Convertion Problem

Aug 16, 2005

I had the following user defined function working on a test server.

CREATE FUNCTION dbo.GetFirstDayOfWeek (@WeekNo Integer, @YearNo VarChar(4))
RETURNS DateTime AS
BEGIN
DECLARE @Date VarChar(10)
DECLARE @FirstDayOfWeek DateTime

SET @Date = '01/01' + @YearNo
SET @Date = CONVERT(VarChar(10), CONVERT(DateTime, @Date,101) - (DATEPART(DW, @Date)-1),101)
SET @FirstDayOfWeek = DateAdd(WW, @WeekNo-1, @Date)

RETURN @FirstDayOfWeek

END

It should return the date of the first day in the week when provided with a week number and year, however, the test server has been rebuilt and after transferring my database over, this function no longer works. I now get the error message "Syntax error converting DateTime from character string".

The database is exactly how it was on the previous test server build - could my problem be caused by the server being setup differently (i.e. different regional settings and therefore different date format) or am I completely missing something in the SQL?

Unfortunately I do not have enough rights on the server to check the SQL Server settings!

View 1 Replies View Related

SQL Server To Oracle Convertion

Oct 19, 2004

Hello experts...

Recently tried to covert one of my database table pub(authors) in Oracle9i
schema using SQL SERVER's "Import and Export Data". The convertion was succesful nut when i logged in Oracle and tried to select that authors table its show me error...

oracle shows its a table when i issue...

SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
authors TABLE / * this table comes from SQL server */

but when i issued as..

SQL> select * from authors;
select * from authors
*
ERROR at line 1:
ORA-00942: table or view does not exist

So, Please help me anyone.I will greatfull to him...Thnaks a Lot.

View 3 Replies View Related

Datatype Convertion Prob

Dec 4, 2007

Hi Friedns,



I am transfering data from oledb source to excel destination i am getting this error





error : First name cannot be converted unicode datatype to non-unique code datatype



any body plz help me





Thx

subu


Meti BEST OF THE BEST

View 1 Replies View Related

Datatype-convertion In TSQL

Jun 20, 2006

Dear all,Tables: COMPANY: COM_ID, COM_NAME, .....PROPERTY: PRP_ID, PRP_NAME, PRP_DATATYPE_ID, PRP_DEFAULT_VALUE( nvarchar)COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE(nvarchar)Use: Without adding new field the user can add new properties to thecompanies just by adding a new property in table PROPERTY and mapping thenew property to a companie. CPRP_VALUE contains all kind of datatypes but isstored as text.Problem: when I query the database ( SP, views, etc) I have problems withfloats and date bacause in the interface ( Access2000.adp) :[color=blue]> the float-format is 0,11 and in TSQL is 0.11>the date-format is DD/MM/YYYY and in TSQL it is YYYY-MM-DD[/color]Can I convert the data within the Stored Procedure for selecting and sortingon those fields.for example: How to sort on CPRP_VALUE containing floatvalues stored asnvarchar ??The client wants to stay with the comma-format because it is common-usedhere.The format can be TSQL-format in the resultset of a Stored Procedure withoutchanging the format in the database because this is used for the output tothe clients.[color=blue][color=green][color=darkred]>>> Same problem with dates !!![/color][/color][/color]thanks,Filip

View 7 Replies View Related

Convertion In Derived Column

Oct 16, 2007



I have a column age ( smallint) in derived transformation .. I would likt to convert it to A or B which is char(1)
hwo can i convert smallint to char(1) in derived column?
age > 17 ?"A" : "B"

View 1 Replies View Related

Problem In Datatype Date Convertion

Mar 20, 2006

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish



View 1 Replies View Related

SSIS Convertion Function From Integer To String

Dec 1, 2005

Hello all

View 22 Replies View Related

Problem With Convertion Of Datatypes From Derived Column To Slowly Changed Dimensions

Jul 24, 2006

Hi,

I am facing the problem with datatype conversions, the scenario is using derived column transformation for add additional columns and then later i am trying to impliment Slowly changing dimensions(SCM) in my job, while mapping the columns at SCM from Input to Output it gave the error like suppose if i am using the numeric(3,0) at SRC system then i converted it into single byte unsigned at derived column and it recognized at SCD but the job fail while run the package it gave the error as task can not able to conversted given data type to target system dtata type.. ifi am not given the single byte unsigned data type at dervied column at the level of SCD mapping the input to output its not accapring this mapping and return the error as can be convert from system.decimal to system.byte..



Sreenivas Amirineni

View 4 Replies View Related

Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error Accessing Windows Event Log

Dec 13, 2007



Hi,

I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.

Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."

Please help me

thanks in advance

Srinivas



View 1 Replies View Related

Convertion Of 24hrs Time To 12hrs Time

May 14, 2008

Hi All,

I need to dispaly time part only from the date that in 12 hrs format.

Can any plz help me on this.

Thanks in advance

View 1 Replies View Related

Error = Arithmetic Overflow Error Converting Expression To Data Type Smalldatetim

Mar 22, 2007

  $exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
 
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
 
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
 

View 3 Replies View Related

Error: 26 Error Locating The Server/instance Specified (.NET SQLClient Data Provider)

Jun 8, 2007

All,



One of our developers decided to load 2K5 client tools on to their laptop. They cannot connect getting the following error:



....Error: 26 Error Locating the Server/instance specified (.NET SQLClient Data Provider)....



1.) Allowing remote connections IS enabled.

2.) SQL Browser Service is ON

3.) Windows Firewall is NOT running

4.) All protocols (except VIA) are enabled



Others at this office, including myself, can connect fine in Management Studio. Only this one developer cannot.



Any thoughts and/or help would be much appreciated. I think that the problem has something to do with the first sentance above but I am not positive.



Thanks in advance,

DBA Ben

View 4 Replies View Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
 And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
 1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

 
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Stored Proc Error: Error Converting Character String To Smalldatetime Data Type

Feb 12, 2005

I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.

Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---

Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.

Source Error:

Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---


Below is what I currently have for my stored procedure and the pertinent code from the page itself.

Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN

--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--

SET NOCOUNT ON
DECLARE @error int, @rowcount int

--
-- Make sure that there isn't already a user with this username
--

SELECT userID
FROM users
WHERE username=@username

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END

--
-- Set expiration date
--

DECLARE @expDate AS smalldatetime

IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END

IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END

DECLARE @paidCopies AS decimal
SET @paidCopies = 5


--
-- Add this user to the database
--

IF @acct_type <> "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

IF @acct_type = "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END



COMMIT TRAN
RETURN 1
GO
---


Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand


db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()

cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")

cmd.ExecuteNonQuery()

(...)

---

View 1 Replies View Related

Mysterious An Error Occurred While Receiving Data: '64(error Not Found)'

Nov 20, 2007

Hi.
I am stuck with error: An error occurred while receiving data: '64(error not found)'.
My Service Broker configuration:
Server A initiator,
Server B target.
Server A sends message to Server B, Server B sends back reply.
On this stage I receive problem. Server B message does not come to server A.
It stays on sys.transmission_queue on server B.
This problem occurs only during initial setup. During initial setup, on Server B messages, queues, services, contracts, routes, bindings are created. On Server A: routes and bindings. As a last step, a message is sent to Server B.
So, reply on this message never comes. Initial setup is run using procedure with execute as €˜owner€™, owner=€™dbo€™.
But, whenever I send messages after that, everything works fine.

Any suggestions?

Please :-)

View 7 Replies View Related

An Error Occurred While Receiving Data: '10054(error Not Found)'.???

Oct 31, 2007



Hi,

I have set up SB between 2 databases, and I keep geting a variety of error messages in the queue on both sides. The first is:
An error occurred while receiving data: '10054(error not found)'.

And on the other side its
Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.

The only difference between this and the tutorials is that the TCP port have been moved to 4321 instead of 4022, and this has been done both sides, because something else was blocking the 4022 port. This one is definately free on both sides. I have recreated the certificates, the users, the end points, the queues and the services multiple times, and checked them all in the sys.routes etc and they all seem fine.

I do also get a message in the queue that I can receive sometimes that tells me I don't have rights to the service on the other machine. I can send a message to itself and it doesn't complain.

Both machines are on the same domain, and I have also tried to grant rights to public to no avail.

Help!

TIA

Ian.

View 2 Replies View Related







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