|
Hey folks,
i've run into a sort of brick wall this weekend, and can't find much that will work....here's the issue.
i need to do an insert into 2 tables, for an invetory control app,
I know this query is wrong, its just a description of what i want to do...
Insert into PC (PC_Service, PC_Serial, mon.mon_name, PC_Model, PC_Name, PC_Loc, PC_IP) <br />
values(1, 'SerialNujmber','SerialNujmber', 'Model1', 'deviceName', 1, '192.168.0.1') <br />
select *<br />
from mon inner join pc<br />
on pc.pc_id = mon.mon_to_pc <br />
where <br />
pc.pc_serial = '123456789'
or
"Insert into Mon ( Mon_loc, Mon_Serial, Mon_serial2, mon_service, mon_to_pc) <br />
values ('1', 'KR-032DVX-47602-19Q-ABKQ', 'kermit the frog', '1', (select PC_ID from PC where pc_serial = 'fasdf'))"
the first is the overall desire...the second is my last resort..
Any ideas will be helpful
thank!!!
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
You could do something like this:
INSERT INTO Mon (Mon_loc, Mon_Serial, Mon_serial2, mon_service, mon_to_pc)
(SELECT '1', 'KR-032DVX-47602-19Q-ABKQ', 'kermit the frog', '1', PC_ID
FROM PC where pc_serial = 'fasdf')Or if you are using a stored procedure, get the PC_ID into a variable, and use it in the INSERT. The above query will insert multiple records if the SELECT PC_ID FROM PC happens to return multiple records, although by looking at the name of the fields, I wouldn't think so for this particular case.
I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
That Worked!!!!
THANKS
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
You're welcome! I'm glad I could help! 
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I am not that good in asp.net and sql.
I need a simple piece of code to see if a value is in a datatable.
Allready have this but how to proceed:
Dim LUConn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EventMngt")
Dim LUString As String = "SELECT * FROM C_Registered_horses WHERE Horse_Name = " & Horse_Name.Text
Dim objLUConnCommand As New SqlCommand(LUString, LUConn)
Dim objConnReader As SqlDataReader
Try
LUConn.Open()
objConnReader = objLUConnCommand.ExecuteReader()
Catch Err As Exception
MessageLabel.Text = "Horsename not found. Try again or use Passport number." & Err.Message
Horse_Name.BackColor = System.Drawing.Color.LightGoldenrodYellow
btnSubmitNext1.Visible = "false"
Finally
LUConn.Close()
MessageLabel.Text = LUString
Horse_Name.BackColor = System.Drawing.Color.LightGreen
btnSubmitNext1.Visible = "true"
End Try
Any help is welkom.
|
|
|
|
|
ExecuteReadering is not enough, you have to actually Read something:
objConnReader = objLUConnCommand.ExecuteReader()
If objConnReader.Read Then
' At least one record was returned
Else
' No records returned
End If If you want to process multiple records, then call Read in a loop
objConnReader = objLUConnCommand.ExecuteReader()
Dim counter As Integer
While objConnReader.Read Then
' Process the record
counter = counter + 1
Wend I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
LuisR thankx for the info and it helps.
Is it possible to use the retrieved valuas ore should one use a other aproach.
|
|
|
|
|
objConnReader = objLUConnCommand.ExecuteReader()
Dim counter As Integer
While objConnReader.Read
' Process the record
lstNames.Items.Add objConnReader.GetString(0) ' First column
counter = counter + 1
WendThe data reader has methodos for returning different types of data from a specified column index (GetInt32, GetString, and so on). Use GetOrdinal to get the column index from the column name.
See my reply to Albanian on why you should use parameters instead of concatenating strings for building a SQL query.
I hope this helps!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
-- modified at 12:44 Monday 19th December, 2005
|
|
|
|
|
It Does, Thanks for the help
|
|
|
|
|
Dim LUConn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EventMngt")
Dim LUString As String = "SELECT * FROM C_Registered_horses WHERE Horse_Name = " & "'" & Horse_Name.Text & "'"
Dim objLUConnCommand As New SqlCommand(LUString, LUConn)
Dim objConnReader As SqlDataReader
Try
LUConn.Open()
objConnReader = objLUConnCommand.ExecuteReader()
Catch Err As Exception
MessageLabel.Text = "Horsename not found. Try again or use Passport number." & Err.Message
Horse_Name.BackColor = System.Drawing.Color.LightGoldenrodYellow
btnSubmitNext1.Visible = "false"
Finally
LUConn.Close()
MessageLabel.Text = LUString
Horse_Name.BackColor = System.Drawing.Color.LightGreen
btnSubmitNext1.Visible = "true"
End Try
' "SELECT * FROM C_Registered_horses WHERE Horse_Name = " & "'" & Horse_Name.Text & "'"
|
|
|
|
|
Hi Albanian,
Cant see the difference between my code and your answer
|
|
|
|
|
a diffrence is here
"SELECT * FROM C_Registered_horses WHERE Horse_Name = " & "'" & Horse_Name.Text & "'"
open the sql quote
& "'"
set query
& Horse_Name.Text
and close sql quote
& "'"
ur code:
"SELECT * FROM C_Registered_horses WHERE Horse_Name = " & Horse_Name.Text
|
|
|
|
|
Albanian wrote: SELECT * FROM C_Registered_horses WHERE Horse_Name = " & "'" & Horse_Name.Text & "'"
See this article on why concatenating SQL queries is a huge security risk:
http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^]
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I try this code to check if a name is on a table by cheking the name if this one is entered in a asp:TextBox. This is the code -->
Protected Sub Horse_Name_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
' Check if Horse_Name is in FEI file
Dim LUConn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EventMngt")
'Dim LUString As String = "SELECT * FROM C_Registered_horses WHERE Horse_Name = " & Horse_Name.Text
Dim LUComm As SqlDataAdapter
Try
LUConn.Open()
LUComm = New SqlDataAdapter("SELECT * FROM C_Registered_horses WHERE Horse_Name = " & Horse_Name.Text, LUConn)
Catch Err As Exception
MessageLabel.Text = "Horsename not found. Try again or use Passport number." & Err.Message
Finally
LUConn.Close()
MessageLabel.Text = LUString
End Try
End Sub
The aboce code is executed without no error but the faulty name is not discovered, the catch statement is not catching a error. I cant see what is wrong.
|
|
|
|
|
"SELECT * FROM C_Registered_horses WHERE Horse_Name = " & Horse_Name.Text should be
"SELECT * FROM C_Registered_horses WHERE Horse_Name = '" & Horse_Name.Text & "'"
No error is getting detected becuae you are not doing anything except opening a connection to the database. You need to execute your SQL for anything to happen.
Make your dataadapter a sqlcommand, and executereader on it (into a sqldatareader) then look inside your datareader and see if rows are returned
|
|
|
|
|
In addition to Andew's reply. You might also want to read up on SQL Injection Attacks[^] becuase your code is susceptable to attack.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I need some code in VB.Net that will get all the mac address
of all the network cards on our network. I beleive a broadcast
command would be the best way to do this. Does anyone know
where I can find some code to do this?
|
|
|
|
|
I'm not sure how to do this in VB.NET, but it would require some sort of extended procedure to do this in SQL Server. But, perhaps you posted this in the wrong forum? Oops! 
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I think there should be a way to do this. In the calling program, I have a list of numbers that I'm building in an sql statement so that the statement looks like this:
"SELECT firstname FROM tblPerson WHERE personid in (9,2,4)"
that works fine, but I need to use this as a stored procedure now and I need to be able to send in "(9,2,4)" as parameter. Is there a way I can make the sql use the string by reference as regular sql like this:
SELECT firstname FROM tblPerson WHERE personid in @idlist
where @idlist is a varchar string that I passed in from my app?
My articles
BlackDice
|
|
|
|
|
Nevermind. I got it to work. i just turned the whole sql statement into a string, saved it to a variable and called exec() on the variable
My articles
BlackDice
|
|
|
|
|
Black Dice,
You can create a sql Server function that takes a delimited list as a parameter and returns a table.
then in your select list you join onto this function
SELECT firstname, lastname
FROM employee e INNER JOIN
fn_MyFUnction(@DelimitedList) f On e.ID = F.ID
"Never Code by Coincidence"
-- Andy Hunt
|
|
|
|
|
Hi all,
I have made a windows service containing a method with a sql connectstring which returns the data in a datatable.
How do I get that into a datagrid on a windows form?
I hope some one can help me out - thanx.
modified 2-Feb-21 21:01pm.
|
|
|
|
|
for this u have to make a seperate application which connects to database and show u the records in datagrid
u cant use a window service for displying records as per my knowledge...
as it is a service no an application
abhinav
|
|
|
|
|
Hi everyone,
I've a quest for you.
I have to read data from a DBF file using Microsoft Jet 4.0.
Connection and data reading are OK up to about 1045 times. From that moment,
the driver send always an exception with the message:
"System resource exceeded"
I've tried to switch to a ODBC driver (through DSN) but the problem still remains.
My machine has a WinXP SP2 installed, with SQLServer 2000 SP4.
If someone has a tip...
Thanks,
Vilmer
|
|
|
|
|
check this link
http://www.codeguru.com/forum/archive/index.php/t-24213-p-2.html
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
