no rob. not in this version of the database… I have built… dropped… built and dropped the databaset numerous times during this creation/learning process. I did though, assign primary key / index to the “stuid” column name. because I did that… I was unsure if assigning an ID which increments automatically would be a good idea or not.
You have two basic options. The first is you would break the queries into Select\Insert\Update into separate commands. Run the Select query and if nothing is returned, then you would run the Insert and have the current information that is being inserted and do something with it. The second, if stuID is a Identity key, then you can place this under the Insert:
SELECT scope_identity();
This will get the last inserted row identity. You are using ExecNonQuery() which returns rows affected, so I believe that would need to be ExecReader() so that you could get the returned stuID that was created. You could place them in an array and then run another command like:
$array = 1,4,5,6,7
$query = "Select * From tablename Where stuID In ('{0}')" -f ($array -join "','")
which would give you a command to return all columns from all of the ids:
Select * From tablename Where stuID In ('1','4','5','6','7')
Rob,
I kinda like the way the script is working now… any searchs I do with this data is gonna go off the student ID … it is unique throughout the district.
moving forward with this design/project… im going to be adding 7 other schools to the mix. so making another database or adding a table for each school will be my direction. ( not sure yet of how to design this) from what ive read of sql so far… with the student id being unique, im thinking a separate table for each building. so searchs can go off building and student id if I know the building a student is supposed to be in.
basically the database is a long term storage of the assigned password for all users. we are rolling out google docs… all students require passwords, and active directory accounts. the support of the network has escalated ten fold due to this… so im trying to script as much as possible to make it easier to maintain. creation of new students each year… not to mention changes throughout the year, and a “roll up” of students going up a grade(which hasn’t been thought through yet) is a night mare. I have scripts that create students based off a daily downloaded csv file… I already create home drives and email addresses and assign 4 digit passwords.
so im liking your second option.
so I would replace execreader() and remove ExecnonQuery()? would this cause any issues with the way the script is currently written?
your example of the newly created array is a little confusing to me. im going to have to research/read in order to understand it better.
my powershell skills are much better then my sql skills… im not an expert in either… but im learning. im also prone to building things way out of my skill level… but hey… how else does one learn. im loving the addition of sql to powershell skilss that I have been doing of late. I have so many ideas running through my head.
once again… thanks for the assistance.
So, I would highly suggest reading up on relational databases and normalization. If your student id’s are unique in all schools, then that should be the primary key of that table. When reading about database, you should learn about one to one, one to many and many to many relationships. A student has one school so there would be a table with school information (tblSchools), like ID, Name, Address, Main Number that are associated with that school. You would add a School_ID to your Student table (tblStudent) and associate the Student with School. This would ensure your are not repeating the same thing over and over (e.g. Name, Address) for each student and simply have an ID associated with a single record that contains the school information. There are many free articles and resources explaining normalization, so I would read up and if you are unsure ask on a SQL forum. I would recommend everything in a single database from what you’ve mentioned
A non-query insinuates it’s not going to return data. So, you would need to execute the insert and get the ID back. Here is a snippet of the code I’ve used in the past to do DB queries:
# Set DB server $SQLServer = "MyServer" #use Server\Instance for named SQL instances! # Set DB Name $SQLDBName = "MyDB" # Set DB User ID $SQLDBUserID = "user" # Set DB Password $SQLDBUserPW = "myPW" # Generate a query string $SqlQuery = @" Select * From Table "@ # Create a SQL connection $SqlConnection = New-Object System.Data.SqlClient.SqlConnection # Set the connection string $SqlConnection.ConnectionString = "Server = $SQLServer;Database = $SQLDBName;User ID = $SQLDBUserID;Password = $SQLDBUserPW;" # Create a SQL command $SqlCmd = New-Object System.Data.SqlClient.SqlCommand # Set the command text to the query string $SqlCmd.CommandText = $SqlQuery # Set the command connection to the SQL connection $SqlCmd.Connection = $SqlConnection # Create a new SQL adapter $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter # Set the adapter query to the SQL command $SqlAdapter.SelectCommand = $SqlCmd # Create a new SQL DataSet $SqlCmd.CommandTimeout = 120 $DataSet = New-Object System.Data.DataSet # Fill the DataSet with the SQL Adapter info $nRecs = $SqlAdapter.Fill($DataSet) $nRecs | Out-Null # Close the SQL connection $SqlConnection.Close() $objTable = $DataSet.Tables[0] #Create a Powershell object with data $objTable | Select Column1, Column2