|
Okay, I've been trying for a couple of hours and I can't seem to get this. No matter what I try I get 0 rows updated.
I have three tables:
1. tblParts
PartNumber
ID
2. tblLegs
AnchorBoltTemplate
PartID
3. tblAnchorTemplate
LegPartNumber
AnchorTemplatePartNumber
Part numbers have a "common name" that is a varchar (Parts.PartNumber), and a "real" ID which is Parts.ID
What I want to do is take the AnchorTemplate.AnchorTemplatePartNumber and put it in Legs.AnchorBoltTemplate
However, both fields in AnchorTemplate are the "common name" and I have to get the real PartID. I think this is part of the problem because my query doesn't know which partID I want.
This is what I have thus far:
declare @temp varchar(16)<br />
update Legs<br />
set AnchorBoltTemplate = @temp<br />
where exists<br />
(<br />
<br />
select parts.id from parts<br />
inner join anchortemplate atm on atm.legpartnumber=parts.partnumber<br />
inner join anchortemplate atm2 on atm2.LegPartNumber = parts.partnumber<br />
inner join legs on legs.partID = parts.ID<br />
where @temp = parts.id<br />
)
Does anyone see what the heck I am doing wrong???
|
|
|
|
|
UPDATE
legs
SET
legs.AnchorBoldTemplate = @temp
FROM
legs
INNER JOIN parts ON parts.ID = legs.parts.ID
INNER JOIN anchorTemplate ON anchorTemplate.legPartNumber = parts.partNumber
WHERE
parts.partID = @temp
Maybe, I don't have Enterprise Manager to test it but this works when written syntactically correct.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Based on your description, it sounds like you want to do this:
UPDATE tblLegs
SET AnchorBoltTemplate = AnchorTemplatePartNumber
FROM tblAnchorTemplate atm
INNER JOIN tblParts ON atm.LegPartNumber = tblParts.PartNumber
WHERE tblLegs.PartID = tblParts.ID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi All,
Greetings...i have a doubt...i've to separate date and time in sql table..its displaying both date and time...can any one tell me the query for that...
Thanks in Advance
Babu
|
|
|
|
|
select datepart(year,timestamp) + '/' + datepart(month,timestamp) + "'/' + datepart(day,timestamp) as date,datepart(hour,timestamp) + ':' + datepart(minute,timestamp) + ':' +<br />
datepart(second,timestamp) as time from sometable
Fairly simple using the datepart function.
Be adviced: The result is a string but you can convert it back to a datetime type.
WM.
What about weapons of mass-construction?
|
|
|
|
|
this SQL code takes current date and time of ur PC
select substring(cast(getdate()as varchar),1,11) as datte, <br />
substring(cast(getdate()as varchar),14,8) as ttime
|
|
|
|
|
Thnaks it is useful for me
Regards,
Jayamaruthi.A
|
|
|
|
|
|
select cast(cast(getdate() as int) as datetime) as DateVal,
getdate()-cast(getdate() as int) as TimeVal
|
|
|
|
|
I am trying to issue a command to MS SQL to perform a backup to a disk file, from a VB.NET 2005 program.
The command structure is as follows :-
BACKUP DATABASE Weighbridge TO DISK = 'C:\SQLBackup\MyServerName\MyDatabaseName_20060803_1108.BAK'
My program reports the following error :-
Incorrect syntax near '\'.
My code is as follows :-
<br />
Private Sub IssueBackupCommand(ByVal Server As String, ByVal Database As String, ByVal BackupPath As String)<br />
<br />
Dim sConnectionString As String<br />
Dim sCommand As String<br />
Dim sSql As String<br />
<br />
Dim connection As New System.Data.SqlClient.SqlConnection<br />
Dim command As New System.Data.SqlClient.SqlCommand<br />
<br />
Try<br />
sConnectionString = "Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security=True"<br />
<br />
sCommand = BackupPath & "\" & Database & "_" & Format(Now(), "yyyyMMdd") & "_" & Format(Now(), "HHMM") & ".BAK"<br />
sSql = "BACKUP DATABASE " & Database & " TO DISK = '" & sCommand & "'"<br />
<br />
With connection<br />
.ConnectionString = sConnectionString<br />
.Open()<br />
With command<br />
.Connection = connection<br />
.CommandType = CommandType.Text<br />
.CommandText = sCommand<br />
.ExecuteNonQuery() '<--- Error produced here<br />
End With<br />
.Close()<br />
End With<br />
<br />
Catch ex As System.Exception<br />
MessageBox.Show(ex.Message)<br />
Finally<br />
command.Dispose()<br />
command = Nothing<br />
If connection.State <> ConnectionState.Closed Then connection.Close()<br />
connection.Dispose()<br />
connection = Nothing<br />
End Try<br />
<br />
End Sub
Any help appriciated
Thanks
Steve Jowett
-- modified at 6:53 Thursday 3rd August, 2006
|
|
|
|
|
Is the error issued by the database??
thanks,
SeEa
|
|
|
|
|
The error seems to be issued by SQL Server. When I run a query consisting of :-
BACKUP DATABASE master TO DISK 'C:\master.BAK'
I get the following message returned :-
Msg 102, Level 15, State 1, Line 1<br />
Incorrect syntax near 'C:\master.BAK'.
Steve Jowett
|
|
|
|
|
backslash is an escape character.
Try C:\\master.bak
WM.
What about weapons of mass-construction?
|
|
|
|
|
Tried your suggestion, but still get the same result
Msg 102, Level 15, State 1, Line 1<br />
Incorrect syntax near 'c:\\master.bak'.
Steve Jowett
|
|
|
|
|
I have found the problem. There is a small error in the VB.NET code
.CommandText = sCommand
Should be
.CommandText = sSql
Da Darrrrrrrr
Thanks everyone for your help anyway
Steve Jowett
|
|
|
|
|
Hahaha, ok clearly missed that one
Glad it works now.
WM.
What about weapons of mass-construction?
|
|
|
|
|
Hi ,
I want to read the distinct records with multiple columns.
for example,
tableA
--------
OID CODE
1 READ
2 READ
3 WRITE
4 WRITE
select distinct CODE, OID from tableA
it gives,
READ 1
READ 2
WRITE 3
WRITE 4
but expected result was,
READ 1
WRITE 3
|
|
|
|
|
man this sql script is not work like this ....
u should use the group by function to get that effect and any aggr functions
select CODE, min(OID) from tableA
group by CODE
This should fetch you the result
thanks,
SeEa
|
|
|
|
|
hmm not sure exactly what your looking for so I'm going to assume you want:
the lowest OID for each distinct CODE
which is:
select CODE, MIN(OID) from tableA group by CODE
which would give your desired result.
DISTINCT is no way appropriate for what your trying to do in this case!
|
|
|
|
|
don't assume you have OID in number format
OID CODE
x READ
y READ
z WRITE
..
this scenorio
expected result will be,
OID CODE
x READ
z WRITE
|
|
|
|
|
if you didn't want us to assume , then you should have stated clearly in your orig post, instead of wasting our time.................
dunderhead
|
|
|
|
|
In that case also teh above approach of grouping by should work !!!
thanks,
SeEa
|
|
|
|
|
Hi,
I need an suggestion how to run sql server in local. In my system sql server was not running in local, but when I connect to our remote data base with it's IP ,username and password, I can able to connect, but not in the local system sql server.I am using windows XP OS. I tried to register a new connectio to my local but i am getting an error the Acdess denied or connection faild.
Plz any one suggest me how to troubleshoot this problem
Regards
Exelio
|
|
|
|
|
Try the sql authentication method and make sure that the username and the password supplied are correct..
thanks,
SeEa
|
|
|
|
|
Hi, good day.
I have a question about the checkbox in datagrid.
The problem is i have a datagrid in my page and i have 4 checkbox per row in the datagrid. I use the radio button to select the record and i can check the checkbox for certain specify action.
For an example, i select the record to be print in different type of report format. The checkbos is use to select which report format need to be printed out.
Did anyone can help me to solve this problem? Thanks..
-- modified at 22:53 Wednesday 2nd August, 2006
Best Regards,
Pei Sun
|
|
|
|