Problem in insert a datetime into SqlServer 2005

I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005

The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "

and the result in Datebase (All the time become 1/1/1900 0:00:00 )

Although i change the datetype from datetime to smalldatetime the result is same

and i try input the date 28/3/2006 0:00:00 into server but

it show the error:Incorrect syntax near '0'.

What wrong ? help me please,Thank.

They have to be passed as strings:
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test','3/28/2006','5/4/2006','5/4/2006' ) "
or as ISO values which is preferable
insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',20060328,20060405,20060405 ) "
HTH, jens Suessmeyer.

|||

Your date is in the future (28th of March this year). That is not allowed:

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Server: Msg 296, Level 16, State 3, Line 5
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

HTH

|||

I prefer to use dates in the following format 'yyyy-mm-dd'.

Have you tried that already (as mentioned above)?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

@.Original Poster: Could you please track the status of the post ? Thanks.

-Jens

sql

Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
John
Hi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> John

Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
JohnHi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> Johnsql

Problem in giving Database Access to a new user

I create a new user and give them a password and hten confirm as
normal. When I subsequently try to give this user access to the
database then I am prompted to 'Confirm Password' jus as I was when I
created the user. I've not seen thi before. Can anyone shed some light
please?
JohnHi,
(Information taken from old post from Kevin Micosoft)
This was a bug with the security rollup for SQL. You need to install build
819. Have a look into the below link:-
http://support.microsoft.com/default.aspx?kbid=826161
Thanks
Hari
MCDBA
"John Lundrigan" <jonel@.glenavon1.demon.co.uk> wrote in message
news:s1ldc01o9nnrsoil1cmep2j23p49eaiise@.
4ax.com...
> I create a new user and give them a password and hten confirm as
> normal. When I subsequently try to give this user access to the
> database then I am prompted to 'Confirm Password' jus as I was when I
> created the user. I've not seen thi before. Can anyone shed some light
> please?
> John

problem in copying a table into another one

I am having problems to copy a table into another one

SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)

with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10

now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10

what can be the problem ? where does the 9 comes from

thank youwas the table empty when you started|||userCopy of course is empty because it is just created

user is full and category too
I want to extract datas from user depending on an ID list in category

thank you for helping|||I created table to test data and as I first though first
your logic is correct

If you return 1,2,3,10
that is what should be used to create your table.

Try to hardcode 1,2,3,10 and see what that returns.
or
just run the select instead of the into and what is returned.

Could you be looking at usercopy created by another owner.
Could you be selecting from a user a category table from another owner.|||ok I try ! and i come back :-)

thank you|||read the sticky at the top of the forum and posts what it asks you to give...

Problem in connecting to the SQL Server

i created a dataset in asp.net and i am trying to view the page in the internet explorer and i am getting the following error

Login failed for user 'EASTERN-DFDRY3R\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'EASTERN-DFDRY3R\ASPNET'.

Source Error:

Line 29: {
Line 30: // Put user code to initialize the page here
Line 31: sqlDataAdapter1.Fill(dataSet11);
Line 32: DataGrid1.DataBind();
Line 33: }

Source File: c:\inetpub\wwwroot\key banking system\webform2.aspx.cs Line: 31

Stack Trace:

[SqlException: Login failed for user 'EASTERN-DFDRY3R\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Key_Banking_system.WebForm2.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\key banking system\webform2.aspx.cs:31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731

please help me with this....and 'EASTERN-DFDRY3R\ASPNET' is a valid user for the database?|||i dont know understand ur question.|||'EASTERN-DFDRY3R\ASPNET' is the the name of '<MACHINE>\ASPNET' account. Sumas is obviously trying to connect to the DataBase using 'Trusted_Connection=True' in his connection string. To do this, he must add the ASPNET account to the DB user list, and give this account the necessary rights.

Open Enterprise mngr and find your Database, right click and choose 'New Database User'. Locate the 'EASTERN-DFDRY3R\ASPNET' account and add the account. Choose permissions and check 'db_owner'. That should do the trick.|||I'm not sure recommending db_owner is good idea, of course I NEVER do that myself <whistle> ;)

Plus if the server is on a different machine, i.e. not EASTERN_DFDRY3R (natty name by the way), then that local user won't be available. So you'll have to change the IIS/ASPNET user.

problem in comparing date

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

Quote:

Originally Posted by cmrhema

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

SQL Server stores dates and time together. Depending on your application and how you are passing your date parameter in as part of your where clause (ie which part of the world you are in) you need to be mindful of your regional settings and look at the CONVERT function in SQL Server

SELECT CONVERT(char(10), GETDATE(), 103)

Will return getdate as character conversion based on the UK regional setting (103)

Regards

Jim :)

problem in adding user and permission

Hi all
I want to create a sp which will from
Master database add user and give permission to that user to any
specified database using t-sql code.
How can I do it?
Thanks
AmHi
sp_addrole and GRANT operate against the current DB, so you can not be in
master and perform those operations against a user DB.
Look at "permissions-SQL Server" in BOL.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AM" <anonymous@.examnotes.net> wrote in message
news:e%23G6glwXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi all
> I want to create a sp which will from
> Master database add user and give permission to that user to any
> specified database using t-sql code.
> How can I do it?
>
> --
> Thanks
> Am
>

Problem getting rid of nondistinct records

Hello,

I have a problem with which I need some help. I have a table called "user" that contains the userid, name, and address. However for users with multiple listed addresses, there are duplicate records...example:

userid, name, address:

0001, John Smith, 123 main rd.
0001, John Smith, 456 second rd.
0002, Bob Jones, 435 another rd.

I need to combine all the duplicate records into a single entry by concatenating the adresses. (I.E. "0001, John Smith, 123 main rd/456 second rd")

I can select the duplicate rows by doing:

SELECT * FROM user
WHERE userid in (
SELECT userid FROM user
GROUP BY userid
HAVING COUNT(*) > 1 )
ORDER BY userid ASC

Does anyone know how I could concat all the duplicate rows into one and then delete the duplicates? I dont care which order the addresses are concatinated in. Any ideas?

Thanks in advance,
Andrei GirenkovFor normalization concerns you should AVOID to do what you ask. A better solution, but requires you to modify the architecture of your database, is to make an external address table:

User Table:

0001, John Smith
0002, Bob Jones

Address Table:

0001, 123 main rd.
0001, 456 second rd.
0002, 435 another rd.

To solve your problem, anyway, i think you have no other way to use cursors, since the number of equals row you need to concatenate will vary.|||Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei|||Originally posted by Andrei_Girenkov
Thanks for the reply, but I already solved the problem. I ended up doing it with for loops. It took a long time to execute but it's a one time operation.

~Andrei

This could be for you or anyother reader.. its an example I put together that would show you efficient ways to point out duplicate records from within a database table. Also applies to most relational database that support the Structured QUery language (SQL)

create table tmp_awahs_dupes_killer
(
record_id varchar(100),
name varchar(100)
)
go
insert into tmp_awahs_dupes_killer values(100, 'In')
insert into tmp_awahs_dupes_killer values(200, 'These')
insert into tmp_awahs_dupes_killer values(300, 'Rows')
insert into tmp_awahs_dupes_killer values(400, ',')
insert into tmp_awahs_dupes_killer values(500, 'These')
insert into tmp_awahs_dupes_killer values(500, 'Are')
insert into tmp_awahs_dupes_killer values(500, 'The')
insert into tmp_awahs_dupes_killer values(500, 'Dupes')
go

select * from tmp_awahs_dupes_killer t1 where (select count(t2.record_id) from tmp_awahs_dupes_killer t2 where t2.record_id= t1.record_id)> 1

drop table tmp_awahs_dupes_killer
go|||Manowar is right. Your database is already denormalized enough. I predict a post six months from now requesting help parsing all those concatenated addresses into separate elements again.

awahteh: I suspect your query would be more efficient using Andrei_Girenkov's linked subquery method, though the optimizer might convert your syntax to this plan prior to execution anyway. If it doesn't, then you will end up executing your subquery once for every record in your main table, while Andrei_Girenkov's method only runs the aggregate once.

blindman

problem getting "LIKE @parameter%" to work

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @.parameter%

Any help or ideas would be greatly appreciated.Hi, my similar line looks like ...

Dim myCommand = New SqlCommand("exec search_telephone '%" & filterValue1 & "%'", myConnection)

... where search_telephone is a stored procedure expecting an input of part of a surname.

NOTE the 2 percentage characters.

Richard|||Like uses a string as its input. So you'd need to use '%' + @.param + '%'|||Is your SQL running inside a stored proc? That's what it sounds like to me.

If that's the case, do something like this:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''' + @.parameter + ''''
EXEC ( @.strSQL )

I do this all the time in my stored procs for searches. I haven't found another way to do this. The trick is getting the number of single quotes right.|||It should be:

declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''%' + @.parameter + '%'''
EXEC ( @.strSQL )

but you get the point.

For help debugging these types of "dynamically generated" SQL statements, use PRINT ( @.strSQL ) and run it in query analyzer.|||in your stored procedure this should work and will not require SELECT permissions on the table like an EXEC(@.sql) would.

CREATE PROC [some_search]
 @.Search nvarchar(50)
AS
 Declare @.LikeSearch nvarchar(52)
 -- you could also add do '%' + @.Search + '%' depending on how you want the search to work
 SET @.LikeSearch = @.Search + '%'
 SELECT *
 FROM table
 WHERE column_name LIKE @.LikeSearch

sql

Problem Excel as OLAP Client with large volume of data

Dear all,

I am using Excel 2000 as my OLAP cube cleint. It's flexible and user friendly when I need to retrive data from cube. However, I am having problem when I need to retrive large volume of data from cube to the excel report (i.e: bank & finance report).

I tried link server but it doesn't make much different.

Can anyone tell me how to solve the problem, if I could only use excel or other license free application as my OLAP Client.

Thanks!

regards,
SamYou didn't mentioned about the problem you're having i.e., Performance related or query based.

If its performance based what is the current configuration of server and database information.

Problem displaying image data from SQL Server

Hello,

I'm having problems saving and then displaying binary data in sql server.

I have a form that takes a file specified by the user and inserts this into sql server:

protected void btnUploadFile_Click(object sender, EventArgs e)
{
if (theFile.PostedFile != null)
{
if (theFile.PostedFile.ContentLength > 0)
{
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
Response.Write(theFile.PostedFile.ContentType.ToString());

if (docBuffer.Length > 0)
{
// save to db
DbAccess dbAccess = new DbAccess(); // my helper function for all db access etc

try
{
dbAccess.BuildCommand("Incentives_SaveDocument");
dbAccess.Parameters.Add("@.docImage", SqlDbType.Image).Value = docBuffer;
dbAccess.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
}
}
}

Stored proc:

ALTER PROCEDURE Incentives_SaveDocument
@.docImage image
AS
SET NOCOUNT ON

INSERT INTO Table1 (theData) VALUES (@.docImage)

RETURN

This appears to work fine. I store the binary data in a image column and if I query the db it shows the row as <Binary>.

The problem I have is with retrieving the data and saving it to a file. The file saves OK but when I open it is contains lots of "squares" that I suppose are the binary - it doesn't show the text.

The code for retrieving/displaying the doc is:

protected void btnView_Click(object sender, EventArgs e)
{
DbAccess dbAccess = new DbAccess();
byte[] byteArray = null;

try
{
dbAccess.BuildCommand("Incentives_RetrieveDocument");
dbAccess.Parameters.Add("@.id", SqlDbType.Int).Value = 6; // id for the doc to return
SqlDataReader reader1 = dbAccess.ReturnDataReader();

while (reader1.Read())
{
if (reader1.HasRows)
{
byteArray = (byte[])reader1["theData"];
}
}
reader1.Close();

FileStream fs = new FileStream("file1", FileMode.CreateNew, FileAccess.Write);
fs.Write(byteArray, 0, byteArray.Length);
fs.Flush();
fs.Close();

FileInfo fileInfo = new FileInfo("file1");

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
HttpContext.Current.Response.ContentType = "application/msword";
HttpContext.Current.Response.WriteFile(fileInfo.FullName);
HttpContext.Current.Response.End();

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
dbAccess.CloseDbConnection();
}

The stored proc:

ALTER PROCEDURE Incentives_RetrieveDocument
@.id int
AS
SET NOCOUNT ON

SELECT * FROM Table1 WHERE id = @.id

RETURN

I would be grateful for any advice on this - its the first time I've worked with BLOB data.

Thanks

SiWhen you recreate a file from the image stored on the database, does it have the same extension?|||

Hello,

I've worked out what is was. I was uploading a byte array without the binary data in it. I needed the following to get it to work:

Stream dataStream = theFile.PostedFile.InputStream;
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
int n = dataStream.Read(docBuffer, 0, theFile.PostedFile.ContentLength);

Si

Problem designing Chart

I am trying to create a bar chart that looks like this:

http://www.webfound.net/chart3.jpg (sketch from a user).

ignore the first set of bars in the above, I'm just trying to create the rest for AZ and IL..the other 2 sets of bars in my chart in SSRS 2005

I have created a dataset that runs a stored proc and resturns the following values:

http://www.webfound.net/values1.jpg

My bar chart looks like this in layout mode:

http://www.webfound.net/chart_layout.jpg

And my results do not look like what I want:

http://www.webfound.net/chart_results.jpg

Questions

1) Why is it not giving me just 3 bars for IL and 3 bars for AZ based on the series groups GrossGoal, InHouse2, and ProjFee? It's instead just showing one bar for all 3 fields - as seen in the legend

I should be getting 3 columns per category...see below

http://msdn2.microsoft.com/en-us/library/ms160349.aspx

"Series groups are optional. The series group list is used to provide dynamic groups of data in the chart. For example, in a column chart, data from the categories list will result in a column for each series group, in each category."

http://msdn2.microsoft.com/en-us/library/ms155847.aspx

2) How can I get this to work for both IL and AZ? this is more complicated than originally thought

Was able to resolve this with the help of Jon http://weblogs.sqlteam.com/jhermiz/category/197.aspx

I had to do 2 things to get this to work:

1) do not use any fields in series

2) add GrossGoal, InHouse, and ProjFee fields into the Data section of the bar chart

3) Add the Category field that contains the group names (which I was already doing)

Problem deleting publication

Hi All,

I have a problem, a user deleted some tables from a couple db's dealing
with replication. I now get this error on those db's.

The process could not execute 'sp_repldone/sp_replcounters on
servername

I have deleted one of the database's and neither are listed under
publications but they still show up under replication monitor.

Any ideas on how to delete them would be great. When I try I get

Error 208: Invalid object name 'sysarticles'
Invalid object name 'sysschemaarticles'
Could not use view or function 'sysextendedarticlesview' because of
binding errors.

TIA
DaveI figured it out, hope this helps someone.

I went into the distribution db and went thru every table and removed
any reference to the databases that were giving me problems. That got
rid of the ghost entries in the replication monitor folder. Oddly I
still had the red X's on the folders them selves but nothing under the
folders had X's and a refresh didnt make them go away. A
sp_MSload_replication_status did though.

Now just the database of the two that were needed still had the repl
hand icon on the database. The good old sp_removedbreplication got rid
of that.

Thanks,
Dave

Problem Database

I am creating an application in V W D I am new to this so please bear with me I have a form that user is to fill out and if it completes it is suppose to update the sql database and then send user to another page and tell them there Ticket Number. I thought I had it working and was using a sql command to tell the ticket number by doing a select Top 1 in descending order to get the last record entered and it worked or so I thought I then looked and found my database was stuck on 10 entries and will not accept any additional records but the application does not throw any errors. Any ideas would be greatly appreciated.

%@. Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<link href="http://links.10026.com/?link=css/CAD.css" rel="stylesheet" type="text/css" />
<title>Log the Call</title>

</head>
<body>
<form id="form1" runat="server">
<div>


<asp:TextBox ID="Email" runat="server" Style="z-index: 101; left: 285px; position: absolute;
top: 273px"></asp:TextBox>


<br />
<asp:Label ID="Label1" runat="server" Style="z-index: 103; left: 78px; position: absolute;
top: 169px" Text="First Name" Width="120px"></asp:Label>

<asp:TextBox ID="PhoneX" runat="server" Style="z-index: 104; left: 665px; position: absolute;
top: 266px"></asp:TextBox>
<asp:TextBox ID="RoomN" runat="server" Style="z-index: 105; left: 508px; position: absolute;
top: 188px"></asp:TextBox>
<asp:TextBox ID="LName" runat="server" Style="z-index: 106; left: 288px; position: absolute;
top: 185px"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Style="z-index: 107; left: 293px; position: absolute;
top: 164px" Text="Last Name" Width="107px"></asp:Label>
<asp:Label ID="Label3" runat="server" Style="z-index: 108; left: 667px; position: absolute;
top: 245px" Text="Phone Extension" Width="117px"></asp:Label>
<asp:Label ID="Label4" runat="server" Style="z-index: 109; left: 286px; position: absolute;
top: 253px" Text="Email Address" Width="114px"></asp:Label>
<asp:Label ID="Label5" runat="server" Style="z-index: 110; left: 510px; position: absolute;
top: 171px" Text="Room Number" Width="104px"></asp:Label>
<asp:Label ID="Label6" runat="server" Style="z-index: 111; left: 78px; position: absolute;
top: 239px" Text="Location" Width="111px"></asp:Label>
<asp:Label ID="Label7" runat="server" Style="z-index: 112; left: 511px; position: absolute;
top: 248px" Text="Problem Type" Width="99px"></asp:Label>
<asp:Label ID="Label8" runat="server" Style="z-index: 113; left: 74px; position: absolute;
top: 329px" Text="Additional Information" Width="119px"></asp:Label>
<asp:TextBox ID="Fname" runat="server" Style="z-index: 114; left: 76px; position: absolute;
top: 187px"></asp:TextBox>
<asp:TextBox ID="AddInfo" runat="server" Height="103px" Style="z-index: 115; left: 69px;
position: absolute; top: 369px" Width="636px"></asp:TextBox>
<asp:Button ID="submit_Button" runat="server" Style="z-index: 116; left: 74px; position: absolute;
top: 500px" Text="Submit Ticket" PostBackUrl="~/LogCallConfirmed.aspx" />

<asp:DropDownList ID="Location" runat="server" Style="z-index: 117; left: 77px; position: absolute;
top: 257px">
<asp:ListItem>Administration</asp:ListItem>
<asp:ListItem>Annex</asp:ListItem>
<asp:ListItem>Billing</asp:ListItem>
<asp:ListItem>Clinic</asp:ListItem>
<asp:ListItem>Contact Lense</asp:ListItem>
<asp:ListItem>Dispensary</asp:ListItem>
<asp:ListItem>SpecialityCare</asp:ListItem>
<asp:ListItem>Spec Lab</asp:ListItem>
<asp:ListItem></asp:ListItem>
<asp:ListItem Selected="True">N/A</asp:ListItem>
</asp:DropDownList>

<asp:RequiredFieldValidator ID="FNameRequired" runat="server" ControlToValidate="Fname"
ErrorMessage="First Name Required" Style="z-index: 118; left: 242px; position: absolute;
top: 191px">*</asp:RequiredFieldValidator>
<asp:RequiredFieldValidator ID="LNameRequired" runat="server" ControlToValidate="LName"
ErrorMessage="Last Name Required" Style="z-index: 119; left: 445px; position: absolute;
top: 188px">*</asp:RequiredFieldValidator>
<asp:RequiredFieldValidator ID="RoomRequired" runat="server" ControlToValidate="RoomN"
ErrorMessage="Room Number Required" Style="z-index: 120; left: 665px; position: absolute;
top: 191px">*</asp:RequiredFieldValidator>
<asp:RequiredFieldValidator ID="PhoneXRequired" runat="server" ControlToValidate="PhoneX"
ErrorMessage="Phone Extension Required" Style="z-index: 121; left: 834px; position: absolute;
top: 268px">*</asp:RequiredFieldValidator>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="ProblemType"
ErrorMessage="Problem Type Can not be N/A" Style="z-index: 122; left: 622px;
position: absolute; top: 272px">*</asp:RequiredFieldValidator>

<asp:RequiredFieldValidator ID="LocationRequired" runat="server" ControlToValidate="Location"
ErrorMessage="Location can not be N/A" Style="z-index: 123; left: 199px; position: absolute;
top: 259px">*</asp:RequiredFieldValidator>
<asp:DropDownList ID="ProblemType" runat="server" Style="z-index: 124; left: 512px;
position: absolute; top: 266px">
<asp:ListItem>Hardware</asp:ListItem>
<asp:ListItem>Software</asp:ListItem>
<asp:ListItem>Network</asp:ListItem>
<asp:ListItem>Unknown</asp:ListItem>
<asp:ListItem Selected="True">N/A</asp:ListItem>
</asp:DropDownList>
<asp:RegularExpressionValidator ID="EmailValidator1" runat="server" ControlToValidate="Email"
ErrorMessage="Please enter valid E-mail address" Style="z-index: 125; left: 442px;
position: absolute; top: 276px" ValidationExpression="\w+([-+.']\w+)*@.\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator>
<asp:ValidationSummary ID="CallLogValidationSummary" runat="server" Style="z-index: 126;
left: 301px; position: absolute; top: 491px" />

<asp:Literal ID="TicketN" runat="server"></asp:Literal>
<asp:Literal ID="TimeDateStamp" runat="server"></asp:Literal></div>
</form>
</body>
</html>

chalakki:

by doing a select Top 1 in descending order to get the last record entered and it worked or so I thought

This is not a desirable methodology. What happens when multiple users are working in your application? You cannot really guarantee that the highest ticket number is the same record that was just inserted, as another insert might sneak in there before the highest ticket number is retrieved.

chalakki:

I then looked and found my database was stuck on 10 entries and will not accept any additional records but the application does not throw any errors. Any ideas would be greatly appreciated.

You've not shown us any of your data access code, so we really don't have anything to go on to help you. Have you done any debugging?

|||

Default.aspx.vb

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub submit_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit_Button.Click
Dim PTicket As New SqlDataSource
PTicket.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
PTicket.InsertCommandType = SqlDataSourceCommandType.Text
PTicket.InsertCommand = "INSERT INTO PTicket (LName, Fname, RoomN, Location, Email, ProblemType, PhoneX, AddInfo, TimeDateStamp)VALUES(@.LName, @.Fname, @.RoomN, @.Location, @.Email, @.ProblemType, @.PhoneX, @.AddInfo, @.TimeDateStamp)"
PTicket.InsertParameters.Add("LName", LName.Text)
PTicket.InsertParameters.Add("FName", Fname.Text)
PTicket.InsertParameters.Add("RoomN", RoomN.Text)
PTicket.InsertParameters.Add("Location", Location.Text)
PTicket.InsertParameters.Add("Email", Email.Text)
PTicket.InsertParameters.Add("ProblemType", ProblemType.Text)
PTicket.InsertParameters.Add("PhoneX", PhoneX.Text)
PTicket.InsertParameters.Add("AddInfo", AddInfo.Text)
'PTicket.InsertParameters.Add("TicketN", TicketN.Text)
PTicket.InsertParameters.Add("TimeDateStamp", DateTime.Now())
'PTicket.InsertParameters.Add("LName", LName.Text)
Dim rowsAffected As Integer = 0
Try
rowsAffected = PTicket.Insert()
Catch ex As Exception
'TO Do: Probably would want to write to windows error logs

Server.Transfer("LogCallProblem.aspx")
Finally
PTicket = Nothing
End Try
If rowsAffected <> 1 Then
Server.Transfer("LogCallProblem.aspx")
Else
Server.Transfer("LogCallConfirmed.aspx")
End If

End Sub
End Class

LogCallConfirm.aspx

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"
SelectCommand="SELECT TOP (1) TicketN FROM PTicket ORDER BY TicketN DESC"></asp:SqlDataSource>

</div>
</form>

Sorry this all new to me! I have never tried using a forum to resolve problems Any assitance would be greatly appreciated

|||I am a little confused. You said it had all seemed to be working. Does this mean that you are seeing the TicketN value incrementing?|||

OK the issue is the page forwards the user to a confirm page when the user imputs the data and hits submit, Then if no exceptions the user is sent to the Confirm Page. There the page is suppose to look up the TicketN Number and displays it to the page so user has a reference to look up, but number 11 is all it displays. The fist time I thought it and enter another page it also displayed 11 so I went to the admin page to view the table there is no additional rows have been added.

I am really confused about it as I get no erros on debug. Also if there is another alternative to giving the user there TicketN number on the confirmation page I am all for it.

Thanks

|||

Sorry to answer your question it is not incrementing and no new data is being added to the table

|||OK, what's happening is that your button's click event is not even running, due to the presence ofPostBackUrl="~/LogCallConfirmed.aspx". This is posting your form to the LogCallConfirmed.aspx page, which is not what you want. Remove this attribute and value altogether and the form will now post back to itself, calling your click event as you desire.|||

Apparently I am missing something. I tried the information you posted and I still can not add to the database. I have even tried creating a new form with not reference to the LogcallConfirmed or Problem page and I still can't place information into the database. I will continue to see what I can do if you have any ideas on where else I could be going wrong I would appreciate any advice you can offer. Thanks in advance

|||Are you seeing any error messages at this point? Maybe you can post your new (presumably simpler) form if you are still having problems with it. If you still have error handling code in it, be sure that you are not just "swallowing" the error but are actually giving some sort of indication that an error occurred.

Problem Creating Login ID

I restored a database called 'MyDatabase' from a backup file. It created a user for me with the same name but no login with the same name. When I tried to create a login id with the same name (MyDataBase), it does not let me the same login id have privile
ges to access that database. I tried to do this under the 'Database Access' tab. Please help.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Problem Creating Login ID

I restored a database called 'MyDatabase' from a backup file. It created a u
ser for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privile
ges to access that database. I tried to do this under the 'Database Access'
tab. Please help.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

problem conneting to SQL server 2000 on Win2k3 from ASP page using dsn connectio

Hi,
the log in user has default database set and also in the
connection string information i am specifying database
name along with DSN.But still i am not able to
connect.Please help me in this

>--Original Message--
>Make sure that your login has its default database
>properly set. Another option is to ensure that your DSN
>is changing the database from the default to the one you
>wish to use.
>Sincerley,
>Invotion Engineering Team
>Advanced Microsoft Hosting Solutions
>http://www.Invotion.com
>
>connect.I
>responding
>pointing
>then
>connection
>.
>If you go into the DSN and choose "test connection" and enter the same id
and password you specify in your ASP page does the test work?
If you use trusted authentication in your ASP page connection string will
it work?
Use SQL Profiler and/or netmon to see exactly what is happening when you
see the "hang". It may not have anything to do with the connection, it may
be a problem with the code after the initial connection.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem connecting using Windows XP Professional

Greetings,

I installed SQL Express 2005 on my machine with XP Professional. My machine name is ROBERT and my user name is Rob. I did not get asked during the installation to supply an administrator password.

Following the installation when I try to connect using a C# app and setting IntegratedSecurity to true I get told that ROBERT\Rob is not authorised to connect to the database (paraphrased as I do not have the exact error message with me).

How do I go about adding my user name to the database if I do not know what the "sa" password is ?

Thanks,

Robert

Hi Robert...you are having trouble with your Windows user most likely because you are not a valid user on the server as of yet...by default, the only valid users on the server are members of the BUILTIN\Administrators group and the SQL service account(s) and the sa (if appropriate).

If you don't know the sa password, you'll have to get somebody who is either a member of the Administrators group or who knows the SQL service account password(s) to login and add your windows user as a valid server login and provide the appropriate permissions to the appropriate database(s).

Look for the following commands in SQL Books Online for more help there:

CREATE LOGIN...

CREATE USER...

HTH,