|
|
Adding to the other comment, check that the firewall settings allow for external communication of SQL Server, and that it was installed (in the case of MSDE, I don't know about SQL Server) with DISABLENETWORKPROTOCOL=0 (not default).
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
In a database i use not a automatic index but assign numbers on the fly.
When a record gets deleted that number becoms available.
Is there any sql command to search the database and retrieve that number te be reused. So if the numbers in a row look like 1..4,6....12 i would like to use 5 as the next number.
|
|
|
|
|
SQL Server 2000:
Select top 1 ID+1<br />
from tablename TN<br />
where not exists (Select ID from TableName where ID=TN.ID+1)<br />
order by ID asc
note that the table must have IDs for this statement to work.
|
|
|
|
|
I am not that good into using ASP.NET but i good get the firts available number by executing:
lblCount = KSComm.ExecuteScalar()
But it gives me a error looking like this:
[InvalidCastException: Specified cast is not valid.]
ASP.free_aspx.Page_Load(Object sender, EventArgs e) in C:\Inetpub\KiddinSys\free.aspx:22
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
|
|
|
|
|
gharryh wrote:
lblCount
I'm assuming by the Hungarian notation that you are using that lblCount is a Label. In which case you cannot assign the result of the SQL statment to it because the SQL statement does not return a Label.
lblCount.Text = KSComm.ExecuteScalar().ToString() would work.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Please explain Hungarion Notation
|
|
|
|
|
Your variable name was lblCount. It uses Hungarian Notation - that is a way of writing into the variable name the type information. (e.g. the lbl prefix suggests that the variable refers to a Label object.) It was common in languages such as C++. Some ex-C++ programmers continue using it, although the Microsoft guidelines for writing software in .NET applications suggest that you don't use it.
Typically people now only use hugarian notation for user controls so they can differentiate easily between a variable that references the control, and some variable that is being used to hold a value from the control. For example:
lblCount is the control
count is a value that represents a property of the control (typically the Text property). Normally, this is used when extracting the value from the control, or when a value is being generated for the control.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
drop table tmp
select * into tmp from bpb
select * from bpb
select * from tmp
The result of table bpb and tmp is the same from the query analyzer.
But if I create a stored procedure like :
CREATE PROCEDURE spTest3 AS
drop table tmp
select * into tmp from bpb
select * from bpb
select * from tmp
GO
, the result for table bpb and tmp isn't the same. How could it possible?
<italic>Work hard, Work effectively.
|
|
|
|
|
Hello,
I have a table that contains rows for multiday periods:
ID StartDate EndDate
1 1/1/2005 8:34:53 1/5/2005 16:34:01
2 1/6/2005 9:21:02 1/11/2005 17:00:02
3 1/12/2005 8:00:00 1/12/2005 12:34:56
4 1/12/2005 13:01:00 1/16/2005 17:31:00 I get a date (no time) for the user (say 1/12/2005) and I would like to find all records in which that date is contained. For 1/12/2005, the query should return both records 3 and 4. For 1/9/2005 only record 2 should be returned.
I've looked at the DATEPART function but it returns only one part of the datetime. How can I compare the date without taking into account the time?
Thanks!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Try this:
DECLARE @Day datetime
SET @Day = '2005/1/12'
SELECT
ID,
StartDate,
EndDate
FROM
[Table]
WHERE
@Day Between
Convert(datetime, DateDiff(dd, 0, StartDate), 1)
And
Convert(datetime, DateDiff(dd, 0, EndDate), 1)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Thanks for your answer. I had already fixed it by creating a FUNCTION that basically converted the datetime to a float, FLOORed it, and converted again to datetime, and it works nice!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I have designed a MS Access 2000 Database which has three tables CUSTOMERS, BOOKINGS & HOLIDAYS to control data entry I have designed three vb.NET Forms. A user is able to SELECT, INSERT, UPDATE and DELETE records on each table.
CUSTOMERS & HOLIDAYS are working fine – no worries. The problem is with the INNER JOIN Table BOOKINGS. The tables are linked by the attribute Coding, the Pk in HOLIDAYS and the Fk in BOOKINGS.
HOLIDAYS table attributes are:
Coding (NUMBER). Pk
Out (DATE)
Return (DATE)
Airport (TEXT)
Country (TEXT)
Destination (TEXT)
Hotel (TEXT)
Rating (NUMBER)
Night (NUMBER)
Price (CURRENCY FIXED)
BOOKINGS table attributes are:
Ref (NUMBER)
CustID (NUMBER)
Coding (NUMBER)
Date (DATE)
Adult (NUMBER)
Child (NUMBER)
Deposit (CURRENCY FIXED)
Balance (CURRENCY)
Cost (CURRENCY)
The Error/Information message, which is just a list, is:
Parameter[0]’Airport’has no default value.
Following in the same syntax is: Country, Destination, Hotel, Night, Out, Price, Rating, and Return
The error message then changes slightly to 'Original_attribute name'.
Parameter[9]'Original_Coding’has no default value.
Then the following are listed as above: Original_Country, Original_Destination, Original_Hotel, Original_Night, Original_Out, Original_Price, Original_Rating, and lastly Original_Return. All the messages return - has no default value???
Note the error message appears on the BOOKINGS UPDATE, although all the attributes mentioned are in the HOLIDAYS table, also the errors are not returned in the order of the attributes in the HOLIDAYS table (see above).
Below is the BOOKINGS UPDATE Statement I have used:
command = "UPDATE BOOKINGS SET Ref = " & txtRef.Text _<br />
& ",CustID = " & txtCustID.Text _<br />
& ", [Coding] = " & txtCoding.Text _<br />
& ", [Date] = '" & txtDate.Text _<br />
& "', Adult = " & txtAdult.Text _<br />
& ", [Child] = " & txtChild.Text _<br />
& ", Deposit = " & txtDeposit.Text _<br />
& ", Balance = " & txtBalance.Text _<br />
& ", Cost = " & txtCost.Text _<br />
& " WHERE Ref = " & txtRef.Text & "" _
It looks to me like an Array problem or the way I have set up the attributes, but what is it and how do I fix it?
I have attached a copy of the actual error message, the Database and the relevant Form for anybody to look at
Any help would be appreciated, thank you in advance.
Regards jetski4419 
jetski4419
|
|
|
|
|
Are you sure you are running the right SQL statment? Your last line ends with an underscore, indicating that the command continues, is that right? If so what is the remainder of the command? Can you show how you are running the command in the database? (There may be something there that is amiss)
Also, you are placing the contents of the user interface controls directly in to the SQL Statement. This is extremely bad practice as it is the source of security flaws in your application. You should read SQL Injection Attacks and Tips on How to Prevent Them[^]
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thank you for your reply.
Sorry about the confusion, there is no underscore at the end of the UPDATE Statement, I must have typed it in when I was sorting the message out prior to sending it.
The full code for the update stament is shown below:
Private Sub btnBookingUPDATE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBookingUPDATE.Click<br />
<br />
If MessageBox.Show("Do you wish to update the record", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = DialogResult.Yes Then<br />
End If<br />
<br />
Try<br />
'Open Connection<br />
OleDbConnection1.Open()<br />
Dim command As String<br />
<br />
'set up an SQL update<br />
command = "UPDATE BOOKINGS SET Ref = " & txtRef.Text _<br />
& ",CustID = " & txtCustID.Text _<br />
& ", [Coding] = " & txtCoding.Text _<br />
& ", [Date] = '" & txtDate.Text _<br />
& "', Adult = " & cboAdult.Text _<br />
& ", [Child] = " & cboChild.Text _<br />
& ", Deposit = " & txtDeposit.Text _<br />
& ", Balance = " & txtBalance.Text _<br />
& ", Cost = " & txtCost.Text _<br />
& " WHERE Ref = " & txtRef.Text & ""<br />
<br />
OleDbDataAdapter1.UpdateCommand.CommandText = command<br />
lblSQL.Text = command<br />
<br />
'do the update<br />
OleDbDataAdapter1.UpdateCommand.ExecuteNonQuery()<br />
Catch exceptionObject As Exception<br />
MessageBox.Show(exceptionObject.Message)<br />
Finally<br />
OleDbConnection1.Close()<br />
<br />
End Try<br />
End Sub
I see you point about placing the contents of the user interface controls directly into the SQL Statement, I'll do that in future and thank you for the link.
Regards
jetski4419
|
|
|
|
|
I am using .net 2003. I created a webform that displays data in a datagrid. I want to refresh this page every 5 minutes. This webform is for internal use only.
I added a timer control to my webform. I set the interval to 300000 (5 Mins). After reading the help topic on timers I realize I have to create a event handler to refresh the data (call page_load()?) when the timer has elapsed. However I have never done this so I am not sure how to procede.
Thanks for all the help
NewbieDave
|
|
|
|
|
|
hello my Article updated by myself if wants to benefit search for author Ahmad or search for xml guest book
Ahmed Erarslan
MCAD,MCDBA,MCP
MCSD.NET
|
|
|
|
|
I have one table having two primary keys here if i created one unique index and now i want to create another nonunique index taking another columns. now my questions are :
1) can i take the columns which i already included in unique index, to build nonunique index?
2) If i take these columns then what that cost on performance of query wheather time required to run query is more or less or no effect?
T.I.A
Shashank
|
|
|
|
|
under2811 wrote:
I have one table having two primary keys
You cannot have two primary keys. Unless you mean that you have a primary key with two columns.
You can have as many unique and non-unique indexes as you wish using what ever combination of columns you wish (including using the same column in two, or more, indexes). As I recall, there is a maximum of 16 columns per index.
under2811 wrote:
If i take these columns then what that cost on performance of query wheather time required to run query is more or less or no effect?
The only impact on performance by adding extra indexes is when you do an INSERT, UPDATE and DELETE commands because these will result in the indexes changing. Obviously SELECT statements get faster because they can use the indexes to get at the data they need more efficiently.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello friends!!
Here I am giving you some details of table and indexes
CREATE TABLE TEST_EMP (
SSN VARCHAR(9) NOT NULL,
CCODE VARCHAR(4) NOT NULL,
GRADE VARCHAR(2) NULL,
CONSTRAINT GRADE_SSN_CCODE_PK
PRIMARY KEY CLUSTERED (SSN, CCODE),
EMPID VARCHAR(5) NOT NULL,
EMPNAME VARCHAR(10))
--------------------------------------------
NOW I WANT TO ADD TWO COLUMNS MORE WHICH FREQUENTLY USED IN JOIN CONDITION for select & insert i.e.
AUTONUMBER int
MODIFIEDDATE DATETIME
NOW I WANT TO CREATE ANOTHER Index (IX_TEST_EMP)
FOR COLUMNS
SSN
CCODE
AUTONUMBER
MODIFIEDDATE
so including that two columns(SSN and CCODE) which already have index (GRADE_SSN_CCODE_PK),
will that cause more cost on query or that doesnot matter?
and if I exclude these two columns(SSN and CCODE) then wheather my select and insert clause
will cause same cost as when i include these two columns(SSN,CCODE)?
T.I.A
Shashank
|
|
|
|
|
I want to create strong passwords. I read an article on the Microsoft website a while ago where they said that strong passwords consisted of upper case and lower case letters, numbers, and symbols. And the symbols they mentioned are ` ~ ! @ # $ % ^ & * ( ) _ + - = { } | [ ] \ : " ; ' < > ? , . /
But some of these symbols can be used in SQL statements. Which of these symbols are not advised to be used as part of your password?
Thanks
|
|
|
|
|
|
Hi,
I need to alter column size of particular table with constraints, table which has primary key and referred to another table.
I have used like this "alter table my_table_name alter column my_column_name varchar(50)"
It shows me error as
Server: Msg 5074, Level 16, State 8, Line 7
The object 'PK_DiagnosisSummary' is dependent on column 'my_column_name'.
Server: Msg 5074, Level 16, State 1, Line 7
The index 'my_column_name' is dependent on column 'Code'.
Server: Msg 5074, Level 16, State 1, Line 7
The object 'FK__Diagnosis__DiagC__51DA19CB' is dependent on column 'my_column_name'.
Server: Msg 4922, Level 16, State 1, Line 7
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.
Let me know how to alter field size of this table with constraints and indexed.
Be simple and Be sample.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
