Can''t write to SQL db help needed.
-
A few weeks ago I moved the SQL db to a new machine. The primary db has tables
that are viewed through a webpage. I loaded up the db in my browser, saw all the
data was there and then left it thinking the migration to a new server was
successful.
Soon after I went to test to insure that I could write to the db and found this
was not possible. The website was done in frontpage. Pages are accessing the db
directly, by that I mean they are not going through the system DSN on the
webserver (which is a separate server from the SQL db.
I can view pages just fine. But when I go to submit a form, nothing is saved. I
loaded up Access to see if I could write to the db. Here is what I found. If I
open a table with Design view I can add or delete columns etc. But I can not
directly add data to any fields when opening the table directly.
I created on the SQL server another db and went through the same steps I tried
with the previous db and found I had no problems adding data to fields. My
assumption is it seems that in the migration the db has locked tables from
allowing new data. Is this possible? What other things could have gone wrong, or
more precisely is there a way to fix this?
Unfortunetley I have very little SQL server experience, as such I am really at a
loss on how to fix this. I have gone over and over through out the SQL
Enterprise Manager console to find a solution. Everything is the same as the
previous server.
Also, I should mention I am using SQL authentication, not Windows. I have
verified several times the password is correct.
-
1) Do the tables in the new db have primary keys?
2)Is the user allowed to insert data (permissions?)
-
- Did you sync the sids when moving the database? If you did not sync the sids in sysusers and master..syslogins this will cause serious issues.
- Did you name the new server the same as the old. If not this will cause issues as well. Look in master..sysservers for the name of your old server. If this is the case this table will have to be updated along with sysjobs table in the msdb database in case you have any jobs that run.
-
Well it appears that the issue has to do with 'Primary Keys'. More specifically in my case the field is not auto numbering so to speak. I first checked the tables using EMS SQL Manager lite and found ALL the tables did NOT contain a primary key. I am a SQL newbie, but I did know that a primary key is needed. Ok, did that and done.
The problem seems to be narrowed down to the issue that previously when data was inserted via the webpage form, the ID field would auto number itself for the data, save, and then go on to the next record. Right now in order to save any data I have to manually fill in the ID field. This is where I am at a loss.
-
Are you allowed to change the field back to autonumbering?
-
Sounds like you need to make the primary key field an identity field so it will automatically generate a unique field for you on insert.
-
Thanks, that solved it. I connected using Access and took a look at the fields where I saw the ID field was "No" for identity. Once set I can now save data to the db via the webpage.
-
Good deal!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply